PostgreSQL High Availability Cookbook - Second Edition

How to do it...

The full escalation path starts very subtly to avoid major disruptive action. Try to follow 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; ...