General commands for PostgreSQL database

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)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.