Getting Started with PostgreSQL for Beginners

I hope these commands will hep beginners to start with PostgreSQL.

We have “pg_ctl” command which we can use to control the postgresql database. We can see this commands behavior as below.
>> How to check the postgresql service status.

$ su – root
# service postgresql­9.2 status
(OR)
#su - postgres
$ /opt/PostgreSQL92/bin/pg_ctl ­D /opt/PostgreSQL92/data status # We need to pass data directory path after ­D.

>> How to start the postgresql service.

 $ su – root
# service postgresql­9.2 start

>> How to start the postgresql as a postgres user(operating system user).

 $ /opt/PostgreSQL92/bin/pg_ctl ­D /opt/PostgreSQL92/data start. 

>> How to stop the postgresql.

$ su – root
# service postgresql­9.2 stop
(OR)
#su - postgres
$ /opt/PostgreSQL92/bin/pg_ctl ­D /opt/PostgreSQL92/data stop ­mf 

>> How to reload the postgresql.
Reload postgresql means, force the postgresql to allow the modifications which we do in postgresql.conf/pg_hba.conf.

 $ su – root
# service postgresql­9.2 reload 

(OR)
$/opt/PostgreSQL92/bin/pg_ctl ­D /opt/PostgreSQL92/data reload 

**Note: Any modifications in pg_hba.conf must require a reload operation of a postgresql database. But, all the parameters in postgresql.conf don’t imply on the database until and unless we restart the database.
>> How to find which parameter change require restart.
This query list out all the parameters which require your postgresql database restart.

 postgres=# select name from pg_settings where context ~ 'postmaster';

This query list out all the parameters which do not require postgresql database restart but only reload.

 postgres=# select name from pg_settings where context != 'postmaster';

>> How to monitor the database connections like which application user executing which query from which ip. This query list out all the connection details of a cluster.

 postgres=# select * from pg_stat_activity ;

This query list out all the active connections list, i.e, the connections which are doing some actions on the database.

 postgres=# select * from pg_stat_activity where waiting is false;

This query list out all the connections list which are waiting.

 postgres=# select * from pg_stat_activity where waiting is true;

>> How to get the object details.  In postgresql, we have some meta commands which helps you to get the list of objects. Ex:-

 postgres=# \dt ­­> Meta command to get the list of tables.
List of relations


Schema | Name | Type | Owner
­­­­­­­­+­­­­­­­­+­­­­­­­+­­­­­­
public | test_p | table | postgres 

\dv --> Meta command to get the list of views.
\di --> List of indexes.


\d  --> Table description 

You can find all these meta commands by executing “\?” in the psql terminal. These commands are not supported in any other applications. For this, we need to use “psql” terminal as below.

 bash­4.1$ ./psql ­p 5432 ­U postgres postgres
psql.bin (9.1.6, server 9.2.0)

WARNING: psql.bin version 9.1, server version 9.2.
Some psql features might not work.
Type "help" for help.
postgres=#

where p - port, U - user
postgres is a database in the cluster.

>> How to get the current locks in database.

 postgres=# select * from pg_locks;

>> How to check the details of replication between the primary and slave.

 postgres=# select * from pg_stat_replication ;

The above query gives us the state of the replication and lag between the primary and slave servers.
>> How to find the blocked queries.

This query list out all the sessions those are currently waiting on which sessions.

SELECT now()::timestamp,waiting.locktype AS waiting_locktype,waiting.relation::regclass::VARCHAR  ASwaiting_table,waiting_stm.Datname as WDatabase,waiting_stm.current_query AS waiting_query,(extract(epochfrom now()) - extract(epoch from  waiting_stm.query_start))::VARCHAR AS Waiting_Stmt_Total_Time,waiting.mode AS waiting_mode,waiting.pid::VARCHAR AS waiting_pid,other.locktype::VARCHAR AS other_locktype,other.relation::regclass::VARCHAR AS other_table,other_stm.Datname::VARCHAR as ODatabase,other_stm.current_query AS other_query,(extract(epoch from now())­-extract(epoch from other_stm.query_start))::VARCHAR AS Other_Stmt_Total_Time,other.mode AS other_mode,other.pid::VARCHAR AS other_pid,other.granted::VARCHAR AS other_granted FROM pg_catalog.pg_locks AS waiting JOIN pg_catalog.pg_stat_activity AS waiting_stm ON (waiting_stm.procpid = waiting.pid) JOIN pg_catalog.pg_locks AS other On ((waiting.database = other.database AND waiting.relation = other.relation) OR waiting.transactionid = other.transactionid) JOIN pg_catalog.pg_stat_activity AS other_stm ON (other_stm.procpid = other.pid) WHERE NOT waiting.granted AND waiting.pid <> other.pid;

>> How to get the database statistics like hitratio, dml statistics.

SELECT sdb.datname,blks_read,blks_hit,round((blks_hit::float / (blks_read+blks_hit+1) * 100)::numeric, 2) as cachehitratio,xact_commit,xact_rollback,tup_returned,tup_fetched,tup_inserted,tup_updated,tup_deleted,ROUND(100.0*AGE(DATFROZENXID)/ (SELECT SETTING::float FROM PG_SETTINGS WHERE NAME ILIKE 'autovacuum_freeze_max_age')) FROM pg_stat_database sdb INNER JOIN pg_database db on sdb.datname=db.datname WHERE sdb.datname !~ '^(template(0|1))' ORDER BY cachehitratio desc;

The above query will give you the database statistics.