General commands for PostgreSQL database

Start, Stop and Restart PostgreSQL database

# service postgresql stop
Stopping PostgreSQL: server stopped
ok# service postgresql start
Starting PostgreSQL: ok

# service postgresql restart
Restarting PostgreSQL: server stopped
ok

Verify PostgreSQL Server is up and running

$ /etc/init.d/postgresql status
Password:
pg_ctl: server is running (PID: 6171)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
[Note: The status above indicates the server is up and running]$ /etc/init.d/postgresql status
Password:
pg_ctl: no server running
[Note: The status above indicates the server is down]

Connect the database in PostgreSQL

$ /usr/local/pgsql/bin/psql
\ connect DBNAME

Edit PostgreSQL queries in your favorite editor

\e

List the tables in PostgreSQL

\dt

List the database in PostgreSQL

\d

List of all database present in PostgreSQL

# \l  [Note: This is backslash followed by lower-case L]

List of databases
Name      | Owner    |Encoding
----------+----------+----------
backup    | postgres | UTF8
mydb      | ramesh   | UTF8
postgres  | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

Create a PostgreSQL Database

Method 1:
CREATE DATABASE mydb WITH OWNER ramesh;Method 2:
$ /usr/local/pgsql/bin/createdb mydb -O ramesh
CREATE DATABASE

Delete/Drop an existing PostgreSQL database

# \l
List of databases

Name      | Owner    | Encoding
—---------+---———----+———------
backup    | postgres | UTF8
mydb      | ramesh   | UTF8
postgres  | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

# DROP DATABASE mydb:

Audit and History in PostgreSQL

Similar to the Linux ~/.bash_history file, postgreSQL stores all the SQL command that was executed in a history filed called ~/.psql_history as shown below

Create a PostgreSQL User

Method 1:
# CREATE USER username WITH password 'password';

Method 2:
$ /usr/local/pgsql/bin/createuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Check version of PostgreSQL

$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quittest=# select version();
version
--------------------------------------------------------------------------------------
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)