PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

The full escalation path starts very subtly to avoid major disruptive action. Perform these steps carefully, assuming eth0 is the network interface that PostgreSQL is using:

1. Connect to the database as a superuser and execute the following query for PostgreSQL 9.2 and higher versions:

SELECT pid, client_port, state, 
       now() - query_start AS duration, query 
  FROM pg_stat_activity 
 WHERE now() - query_start > INTERVAL '2 seconds' 
   AND state != 'idle' 
 ORDER BY duration DESC; 

2. Use this query for 9.1 and lower versions:

SELECT procpid AS pid, client_port, 
       now() - query_start AS duration, current_query 
  FROM pg_stat_activity 
 WHERE now() - query_start > INTERVAL '2 seconds' 
   AND current_query != '<IDLE>' 
 ORDER BY duration DESC; 

3. Starting ...