After you install PostgreSQL on Ubuntu/Debian, it will create a user “postgres”, it have full privileges. It is like “root” user in MySQL.

Connect to PostgreSQL

To connect to PostgreSQL, run following command.

su - postgres

This will change current user to linux user “postgres”. Now run command psql

psql

Example

root@dev1:~# su - postgres
postgres@dev1:~$ psql
psql (9.3.4)
Type "help" for help.

postgres=# 

List All Databases

To view all databases in a PostgreSQL database, use /list command

/list

Create A Database

To create a database, run command

create database DB_NAME;

This is same as in MySQL. Example

postgres=# create database hostonnet;
CREATE DATABASE
postgres=# 

Change to a Database

To change to a database, use “\c” command. In MySQL, we use “use DB_NAME” command.

postgres=# \c hostonnet
You are now connected to database "hostonnet" as user "postgres".
hostonnet=# 

See the psql prompt changed to Database Name.

List All Tables In A Database

To list all Tables in a Database use “\dt” command.

In MySQL, we use “show tables;” command.

hostonnet=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | users         | table | postgres
 public | address       | table | postgres
(2 rows)

hostonnet=#