PostgreSQL Operational Runbook

Query & Lock Management — monitoring, diagnosing & terminating sessions

Consolidated reference for AWS RDS & self-managed PostgreSQL.

🗓 Updated 2026-09-18 🏷 Version 1.0 🐘 Engine PostgreSQL 👤 Audience DBA / SRE
📌
How to use this doc. Click any item in the left sidebar (or the Contents menu on mobile) to jump directly to that section. Each code block has a Copy button. Escalate from monitoring → diagnosing → acting, using the flow in §12.

1Active Query Monitoring

1.1All currently active queries

SELECT
  pid,
  user,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS query_time,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE state = 'active';

Use when: you want a live snapshot of everything running right now.

1.2Active queries running longer than 1 minute

SELECT
  pid,
  user,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS query_time,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '1 minute'
  AND state = 'active';

Use when: isolating slow queries. Change interval '1 minute' to '2 minutes', '5 minutes', etc.

2Identifying Long-Running Queries

2.1Exclude the monitoring query itself (cleaner output)

SELECT
  pid,
  usename,
  state,
  query,
  now() - query_start AS running_time
FROM pg_stat_activity
WHERE state = 'active'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY running_time DESC;

Use when: you don't want your own query polluting results.

3Capturing Queries to CSV

⚠️
\copy is a psql client-side command. The file is written on the machine running psql, not the DB server. Ensure the path exists and is writable.

3.1Baseline capture

\copy (
  SELECT
    pid,
    user,
    pg_stat_activity.query_start,
    now() - pg_stat_activity.query_start AS query_time,
    query,
    state,
    wait_event_type,
    wait_event
  FROM pg_stat_activity
  WHERE (now() - pg_stat_activity.query_start) > interval '2 minutes'
    AND state = 'active'
) TO '/home/ubuntu/LR10jul.csv' WITH CSV DELIMITER ',' HEADER;

3.2Continuous / repeated capture

\copy (
  SELECT
    pid,
    user,
    pg_stat_activity.query_start,
    now() - pg_stat_activity.query_start AS query_time,
    query,
    state,
    wait_event_type,
    wait_event
  FROM pg_stat_activity
  WHERE (now() - pg_stat_activity.query_start) > interval '1 minute'
    AND state = 'active'
) TO '/home/ec2-user/LRQ24jul-1425.csv' WITH CSV DELIMITER ',' HEADER;
💡
Tip: rotate the filename with a timestamp so you keep a history without overwriting — e.g. LRQ$(date +%d%b-%H%M).csv.

4Terminating / Cancelling Queries

⚖️
Cancel vs Terminate.
pg_cancel_backend() — soft cancel. Asks the query to stop; safer, respects cleanup. Use first.
pg_terminate_backend() — hard kill. Terminates the backend/connection. Use when cancel fails or the session must be dropped.

4.1Cancel all other active queries

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
  AND pid <> pg_backend_pid();   -- never kill your own session

4.2Terminate long idle sessions (>10 min), excluding RDS admin

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '10 minutes'
  AND pg_stat_activity.state = 'idle'
  AND datname != 'rdsadmin';      -- protect AWS RDS internal DB

4.3Terminate both active + idle (>10 min) — team standard

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '10 minutes'
  AND pg_stat_activity.state IN ('active','idle');
🚩
Caution on 4.3: kills everything (active and idle) older than 10 min and has no datname filter — on RDS it could target rdsadmin. Prefer the escalation 4.1 → 4.2, and always add AND datname != 'rdsadmin'.

5Lock Detection & Blocking Analysis

5.1Blocked queries + who is blocking them — full tree (to CSV)

\copy (
WITH sos AS (
    SELECT array_cat(
        array_agg(pid),
        array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])
    ) AS pids
    FROM pg_locks
    WHERE NOT granted
)
SELECT
    a.pid,
    a.usename,
    a.datname,
    a.state,
    a.wait_event_type || ': ' || a.wait_event AS wait_event,
    current_timestamp - a.state_change AS time_in_state,
    current_timestamp - a.xact_start  AS time_in_xact,
    l.relation::regclass AS relname,
    l.locktype,
    l.mode,
    l.page,
    l.tuple,
    pg_blocking_pids(l.pid) AS blocking_pids,
    (pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] AS last_session,
    COALESCE(
        (pg_blocking_pids(l.pid))[1] || '.' ||
        COALESCE(
            CASE
                WHEN locktype = 'transactionid' THEN 1
                ELSE array_length(pg_blocking_pids(l.pid),1) + 1
            END,
            0
        ),
        a.pid || '.0'
    ) AS lock_depth,
    a.query
FROM pg_stat_activity a
JOIN sos s
    ON a.pid = ANY(s.pids)
LEFT OUTER JOIN pg_locks l
    ON a.pid = l.pid
   AND NOT l.granted
ORDER BY lock_depth
) TO '/home/ubuntu/lock28jun26.csv' CSV HEADER;

Use when: "the database is hung — who holds the lock?" Builds a lock chain / dependency tree (blocked sessions, their blockers, lock depth).

5.2Current locks on tables (flat, simpler view)

SELECT
    t.schemaname,
    t.relname,
    l.locktype,
    l.page,
    l.virtualtransaction,
    l.pid,
    l.mode,
    l.granted
FROM pg_locks l
JOIN pg_stat_all_tables t ON l.relation = t.relid
WHERE t.schemaname <> 'pg_toast'::name
  AND t.schemaname <> 'pg_catalog'::name
ORDER BY t.schemaname, t.relname;

Use when: you want a flat list of locks per table instead of the tree.

6Filter by Client IP (CRM)

\copy (
    SELECT
        pid,
        age(clock_timestamp(), query_start) AS query_age,
        client_addr,
        client_hostname,
        usename,
        wait_event,
        query
    FROM
        pg_stat_activity
    WHERE
        state = 'active'
        AND query NOT ILIKE '%pg_stat_activity%'
        AND client_addr IN ('172.31.47.38', '172.31.39.129', '172.31.1.246',
                             '172.31.29.86', '172.31.31.218', '172.31.14.133')
        AND age(clock_timestamp(), query_start) > interval '1 second'
    ORDER BY
        query_start DESC
) TO '/home/ubuntu/LRQ24jan23-1042am.csv' WITH CSV DELIMITER ',' HEADER;

Use when: isolating traffic from a specific set of application servers (IP whitelist). interval '1 second' effectively captures nearly all active queries.

7Wait Event Analysis

SELECT wait_event_type,
       wait_event,
       count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

Use when: finding the dominant bottleneck.

Wait typeLikely cause
LockContention / blocking sessions
IODisk / read bottleneck
ClientApp not reading results fast enough
LWLockInternal lightweight lock contention

8Idle in Transaction

🔎
Why it matters: an idle in transaction session holds locks and prevents VACUUM, blocking other writers — often the hidden cause of a "hung" database.

8.1List idle-in-transaction sessions

SELECT
  pid,
  user,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS query_time,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE state = 'idle in transaction';

8.2Longest idle-in-transaction sessions (top 100)

SELECT
    pid,
    usename,
    state,
    query,
    now() - query_start AS running_time
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY running_time DESC
LIMIT 100;

8.3Same, with application name (pinpoints the app)

SELECT
    pid,
    application_name,
    usename,
    state,
    query,
    now() - query_start AS running_time
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY running_time DESC
LIMIT 100;

Use when: application_name tells you which service is leaving transactions open.

9Database Uptime

-- Short form
SELECT pg_postmaster_start_time() AS uptime;

-- Human-readable duration since last (re)start
SELECT current_timestamp - pg_postmaster_start_time() AS uptime;

Use when: confirming whether the DB crashed/restarted (e.g. after an OOM or failover).

10Restore / COPY Progress

SELECT
    pid,
    relid::regclass AS table_name,
    command,
    pg_size_pretty(bytes_processed) AS processed,
    pg_size_pretty(bytes_total)     AS total,
    CASE
        WHEN bytes_total > 0
            THEN round((bytes_processed::numeric / bytes_total::numeric) * 100, 2)
        ELSE 0
    END AS percentage_complete
FROM pg_stat_progress_copy;

What it shows: a live view of an in-progress data load (COPY/restore), including a calculated % complete when the DB can estimate the file size.

11MySQL Equivalent

-- List running queries / connections
SHOW PROCESSLIST;

-- Kill a specific connection
KILL <connection_id>;

Mapping: Info = the query · Time = seconds in current state · Command = 'Sleep' ≈ Postgres idle.

12Reference & Cautions

12.1Session states (PostgreSQL)

stateMeaningRisk
activeQuery executing nowCPU / IO load
idleConnected, doing nothingHolds connections
idle in transactionOpen transaction, no query running⚠️ Holds locks, blocks VACUUM
idle in transaction (aborted)Transaction failed, not rolled back⚠️ Same risks
fastpath function callExecuting a fastpath functionRare

12.2Cancel vs Terminate

ActionFunctionImpactUse case
Cancel (soft)pg_cancel_backend(pid)Query stops, connection staysFirst attempt
Terminate (hard)pg_terminate_backend(pid)Connection killed, rollbackCancel failed / stuck session

12.3Recommended escalation flow

Detect → §1 / §2 / §3 (monitor) │ ▼ Diagnose → §5 (locks) / §7 (wait events) / §8 (idle in txn) │ ▼ Act → §4.1 pg_cancel_backend │ (wait 10–30s) ▼ Still stuck → §4.2 pg_terminate_backend (with datname != 'rdsadmin') │ ▼ Post-mortem → §6 (which app/IP caused it), §9 (uptime check)

12.4Safety checklist before killing anything

  • Never run terminate queries without pid <> pg_backend_pid() when targeting all active sessions.
  • Always add AND datname != 'rdsadmin' on AWS RDS.
  • Prefer pg_cancel_backend before pg_terminate_backend.
  • Verify the PID + query text before killing — don't blind-kill by time alone.
  • Never store plaintext DB passwords in docs/tickets — use Secrets Manager / IAM and rotate.
  • Confirm \copy target paths exist and are writable on the psql client.
  • Update \copy filenames/paths per session.

12.5Notes on the original snippets

🧹
  • The mock-task export had a stray semicolon inside the subquery — remove it: ... scl.tm_scl_id ) TO .... A ; inside \( ... ) breaks the statement.
  • COPY ... TO '/var/lib/postgresql/...' runs server-side (superuser / RDS restrictions apply), unlike client-side \copy.
  • A raw password appeared in the source notes (ALTER USER postgres WITH PASSWORD '...'). Remove it from docs and rotate it.

PostgreSQL Operational Runbook · v1.0 · Updated 2026-09-18 · For internal DBA/SRE use.