PostgreSQL

 

To list all of the schemas in a database:     \dn

To list of all the databases:    \l

To display the list of existing users, inclusive of roles in PostgreSQL: \du

You can view the list of existing databases by querying the pg_database catalog tables.

SELECT datname from pg_database WHERE datistemplate=false;

                               or

                             \l

Identify all of the active sessions on the database:

SELECT * FROM pg_stat_activity WHERE datname='testdb1';

Terminate all of the active sessions to the database:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='testdb1';

Terminate all of the active sessions for a particular user:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename='agovil';

pg_tables

Pg_indexes

Creating users:

postgres=# CREATE user agovil WITH PASSWORD 'abc@9Mq';

                               or 

$createuser -h localhost -p 5432 -S nchabbra

The -S option specifies that the created user will not have the superuser privileges.

Creating Groups:

CREATE GROUP dept;

ALTER GROUP dept ADD USER agovil,nchabbra;

CREATE GROUP admins WITH USER agovil,nchabbra;

SELECT * FROM pg_group;

Creating Tablespaces:

CREATE TABLESPACE  data_tbs  OWNER  agovil  LOCATION   '/var/lib/pgsql/data/dbs';

SELECT * FROM pg_tablespace;

Initializing a database cluster:

$initdb -D /var/lib/pgsql/data

                    or

$pg_ctl -D /var/lib/pgsql/data initdb

The initdb command is used to initialize or create the database cluster.

The -D switch of the initdb command is used to specify the filesystem location for the database cluster.

A database cluster is a collection of databases that are managed by a single server instance.

Starting the server:

$pg_ctl -D /var/lib/pgsql/data start

Stopping the server:

$pg_ctl -D /var/lib/pgsql/data initdb stop -m fast

with the use of a fast shutdown, there is no wait time involved as all of the user transactions will be aborted and all connections will be disconnected.

$pg_ctl -D /var/lib/pgsql/data initdb stop -m immediate

There may be situations where one needs to stop the PostgreSQL server in an emergency situation, and for this, PostgreSQL provides the immediate shutdown mode. The consequence of this type of shutdown is that PostgreSQL is not able to finish its disk I/O, and therefore has to do a crash recovery the next time it is started.

Displaying the server status:

pg_ctl  -D /var/lib/pgsql/data status

Reloading the server configuration files:   postgresql.conf

$pg_ctl -D /var/lib/pgsql/data reload

postgres=# select pg_reload_conf();

There are some configuration parameters whose changed values can only be reflected by a server load.  These configurations parameters have a value known as sighup for the attribute in the pg_settings catalog table:

SELECT name, setting, unit, (source = 'default') as is_default FROM pg_settings WHERE context ='sighup' AND (name like '%delay' or name like '%timeout') AND setting!='0'; 

Terminating Connections:

Terminate all of the active sessions to the database:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='testdb1';

Terminate all of the active sessions for a particular user:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename='agovil';

Cancel Running queries:

If the rquirement is to cancel running queries and not to terminate existing sessions, then we can use the pg_cancle_backend function to cancel all active queries on a connections.

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE datname='testdb1';

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE usename='agovil';