Issue Tracking and PostgreSQL Incident Triage

1. Create a useful issue record

Use your team's approved ticket or incident system. Each record should have an incident ID, start time and timezone, environment/cluster, symptom and customer impact, severity, alert links, suspected change, owner, current action, next update time, and resolution evidence. Keep secrets and unredacted customer data out of tickets.

A clear timeline separates observations from hypotheses. Attach query IDs, sanitized log excerpts, dashboards, and before/after measurements so another operator can continue without repeating risky experiments.

2. Identify active work, waits, and failures

SELECT pid, backend_type, usename, application_name, client_addr,
       state, wait_event_type, wait_event,
       now() - query_start AS query_age,
       now() - xact_start AS transaction_age,
       left(query, 240) AS query_sample
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
ORDER BY query_start NULLS LAST;

SELECT pid, pg_blocking_pids(pid) AS blockers,
       wait_event_type, wait_event, left(query, 180) AS query_sample
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Correlate PostgreSQL logs (timestamp, SQLSTATE, database, user, application, and process ID) with application errors, deploys, resource graphs, and replication state. Check whether a problem affects one query, one client, one database, or the whole cluster.

3. Resolve with the least disruptive action

1. Confirm current impact and capture a baseline.
2. Identify the exact process, query, owner, and transaction; ask the service owner where possible.
3. Prefer fixing the cause or allowing a short wait over interrupting work.
4. If approved, pg_cancel_backend(pid) requests cancellation of the current query. Re-check impact and logs.
5. Use pg_terminate_backend(pid) only as an explicitly approved last resort: it disconnects the session and rolls back its active transaction.
6. Verify service recovery, record the outcome, and assign a follow-up to prevent recurrence.

-- First inspect and verify the PID and owner; never paste an unverified PID.
SELECT pid, usename, application_name, state, xact_start, query
FROM pg_stat_activity
WHERE pid = 12345;

-- Only after approval and confirmation of the exact backend:
SELECT pg_cancel_backend(12345);