Useful postgresql database administration commands

Some useful commands for administrating postgreSQL database.

1. Viewing all current Postgresql connections to Database server

SELECT * FROM pg_stat_activity ;

For specific database, just add datname parameters into WHERE clause like:

SELECT * FROM pg_stat_activity WHERE datname='DB_NAME';

2. Terminating idle connection

pg_terminate_backend send signals (SIGINT or SIGTERM respectively) to backend processes identified by process ID. The process ID of an active backend can be found from the procpid column of the pg_stat_activity view, or by listing the postgres processes on the server (using ps on Unix or the Task Manager on Windows).

SELECT pg_terminate_backend(pid)
    FROM pg_stat_activity
    WHERE datname = 'DB_NAME'
      AND pid <> pg_backend_pid()
      AND state = 'idle'
      AND state_change < current_timestamp - INTERVAL '5' MINUTE;

In the above example, where terminate all backend processes which has idle state and state_stage at least last 5 minutes of database : 'DB_NAME'.

3. Use pgsql in terminal

- Connect to postgresql

psql DATABASENAME -U USERNAME

-List all table of connecting database:

\dt