Useful PostgreSQL queries and commands

pg_queries_and_commands.md

Useful PostgreSQL queries and commands

Getting information about database sessions

select pid as process_id,
       usename as username, 
       datname as database_name, 
       client_addr as client_address, 
       application_name,
       backend_start,
       state,
       state_change
from pg_stat_activity;

Show number of connections per database

select datname,count(*) from pg_stat_activity group by datname;

Finding long running queries on PostgresSQL

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

You can change the interval time.

Inspect a running query by PID

SELECT query FROM pg_stat_activity WHERE pid=xxxx;

Find which process is causing High CPU usage:

$ # show the top 10 cpu using threads, sorted numerically
$ ps -eo %cpu,%mem,pid,user,args | sort -k1 -r -n | head -10

Killing long running queries

In order to cancel long running queries you should execute:

SELECT pg_cancel_backend(pid);

The pid parameter is the value returned in the previous step. pg_cancel_backend can take a few seconds to stop the query. If it doesn't work, you can use:

SELECT pg_terminate_backend(pid);

Be careful: pg_terminate_backend is the kill -9 in PostgreSQL. It will terminate the entire process which can lead to a full database restart in order to recover consistency.

Show running queries

SELECT pid, age(clock_timestamp(), query_start), usename, query 
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;