Database Monitoring, Troubleshooting, and Diagnostics Questions

Direct answer

pg_stat_statements tracks cumulative statistics per normalized query (the same query shape with different literal values counted as one entry), so ranking by mean execution time surfaces queries that are consistently slow every time they run, which is a different and often more actionable signal than ranking by total time, which is dominated by whatever runs most often rather than whatever is individually worst.

Structured elaboration

One important correction before the query itself: the column names in the question, total_time, mean_time, and max_time, are the pre-PostgreSQL-13 names. As of PostgreSQL 13, they were renamed to total_exec_time, mean_exec_time, and max_exec_time (with separate total_plan_time/mean_plan_time columns added alongside them, splitting out query-planning time from execution time, which didn't exist as a separate measurement before). I ran this against a real PostgreSQL 16 instance and confirmed the old names no longer exist; the query below uses the current names and aliases the output to match what was asked for, so the result still has the requested column labels.

SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_time_ms,
       round(mean_exec_time::numeric, 2)  AS mean_time_ms,
       round(max_exec_time::numeric, 2)   AS max_time_ms
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat_statements%'
ORDER BY mean_exec_time DESC
LIMIT 5;

The WHERE clause filters out the extension's own bookkeeping query from the results, since otherwise this query would show up ranking itself. pg_stat_statements must be loaded via shared_preload_libraries and its extension created (CREATE EXTENSION pg_stat_statements;) before it will collect anything; it's not on by default.

Interpreting the results. The query column shows the normalized query text with literal values replaced by placeholders ($1, $2, and so on), because pg_stat_statements groups executions of the same query shape together regardless of which specific values were passed; this is different from pg_stat_activity, which shows the literal text of whatever is currently running. calls tells you how many times that shape has executed since the last stats reset, which matters for prioritization: a query with a high mean_exec_time but only 3 calls total is a very different priority than one with a similarly high mean but 50,000 calls. max_exec_time catches queries whose average looks fine but that occasionally spike badly, often a sign of a plan that's sensitive to the specific parameter values passed (a classic cause is a skewed data distribution where one particular value matches far more rows than the planner's cached plan expected).

Prioritizing the ranked list. Ranking by mean_exec_time alone finds individually slow queries, but the actual optimization priority should weigh mean time against call volume: a query at 60ms mean called 50,000 times a day is consuming roughly 50 minutes of cumulative database time daily, while a query at 500ms mean called only 10 times a day costs about 5 seconds cumulative; the first is very likely the better use of tuning effort even though it ranks lower on mean time alone, so I'd look at both this query's mean_exec_time-ranked list and a separate total_exec_time-ranked list before deciding where to spend effort, not just the one the question asked for.

Worked example

I generated real, varied query activity against a seeded 2 million row orders table (a mix of an indexed point lookup, a full-table GROUP BY, an unindexed range scan with an ORDER BY and LIMIT, and a self-join), reset pg_stat_statements, ran each query a realistic number of times, then ran the query above. Actual output:

 query                                                                                                                 | calls | total_time_ms | mean_time_ms | max_time_ms
------------------------------------------------------------------------------------------------------------------------+-------+---------------+--------------+-------------
 SELECT count(*) FROM orders o1 JOIN orders o2 ON o1.customer_id = o2.customer_id WHERE o1.id < o2.id AND o1.id < $1  |     1 |        110.08 |       110.08 |      110.08
 SELECT status, count(*), avg(amount_cents) FROM orders GROUP BY status                                               |     2 |        126.62 |        63.31 |       64.05
 SELECT * FROM orders WHERE amount_cents > $1 ORDER BY created_at DESC LIMIT $2                                       |     1 |         27.72 |        27.72 |       27.72
 SELECT id, status FROM orders WHERE id = $1                                                                          |     5 |          2.59 |         0.52 |        1.22
 SELECT count(*) FROM orders WHERE customer_id = $1                                                                   |     3 |          1.05 |         0.35 |        0.75

Reading this: the self-join tops the list by mean time at 110ms, but it was only called once, so on its own this doesn't say much about production priority, it says this specific shape of query is expensive per-call, worth knowing if it's on a hot path. The GROUP BY query is second at 63ms mean but was called twice, both calls close to the mean (64.05ms max versus 63.31ms mean), so this one is consistently, not occasionally, expensive, a more confident signal that it needs a covering index (an index that already includes every column the query needs, so the database can answer it from the index alone without a separate trip to the table) or a materialized rollup (a precomputed, stored summary table that's refreshed periodically, so the GROUP BY is computed once and then just read) if it runs often in production. The point lookup by id and the count by customer_id are both fast and unremarkable, exactly what you'd expect from indexed equality lookups against a 2 million row table.

Trade-offs and pitfalls

pg_stat_statements numbers are cumulative averages since the last reset (or since the extension was created), so a query that was fast for months and only recently started degrading will still show an overall mean pulled down by its historical fast runs, masking a real, recent regression; for that, look at max_exec_time alongside the mean, or better, sample the view periodically and diff it over time rather than treating one snapshot as the full picture. mean_time ranking, as the direct answer notes, can mislead if read without call volume: it will surface a rarely-run, individually expensive report query above a frequently-run query whose cumulative cost to the system is actually far higher, so treat this ranked list as one input to prioritization, not the final answer.

Direct answer

A service-level objective (SLO) is a target you set for a measurable aspect of reliability, expressed as a percentage over a time window, like "99.9% of queries complete successfully within 200ms over a rolling 30 days." The error budget is just the other side of that same number: however much unreliability the SLO allows before you'd breach it. The practical value for an on-call rotation isn't the target itself, it's using how much of that budget you've already spent to decide whether something needs a page right now or can wait for business hours.

Structured elaboration

Two concrete database-layer SLO examples.

• Query latency SLO: "99.9% of read queries complete in under 100ms, measured over a rolling 30-day window." This is a latency-based SLO; it says nothing about whether the database is up, only about how fast it responds when it is.
• Availability SLO: "The database accepts and successfully commits writes 99.95% of the time it's queried, measured over a rolling 30-day window." This is a distinct SLO from the latency one; a database that's technically reachable but timing out every query is failing the latency SLO while potentially still passing a naive availability check, which is exactly why teams track more than one SLO rather than a single "is it up" number.

Turning a percentage into a budget you can actually track. For the 99.9% SLO over a 30-day month:
$\text{budget}_{\min} = (1 - 0.999) \times 30 \times 24 \times 60 = 43.2 \text{ minutes}$
That's the total allowed "bad" time (failed or too-slow queries, whichever the SLO measures) across the entire month before you've breached the target. For comparison, a looser 99.5% SLO gives:
$\text{budget}_{\min} = (1 - 0.995) \times 30 \times 24 \times 60 = 216 \text{ minutes}$
about 5 times more room. This is the concrete reason SLO selection matters: moving from 99.9% to 99.5% isn't a small relaxation, it's giving yourself 5x the room before you're technically failing your own target, which is a real product and trust decision, not just a number on a dashboard.

Mapping alert thresholds and paging policy to burn rate, not just the raw SLO. The SLO itself (99.9%) is not something you alert on directly; alerting on "we're currently below 99.9% this second" would page constantly on normal noise. Instead, alert on burn rate: how fast you're consuming the error budget relative to how fast you're allowed to consume it and still land on target by the end of the window.

• Fast burn (page immediately): consuming error budget at a rate that would exhaust the entire 30-day budget in a few hours if it kept up. This is the signature of an active incident: something is currently broken badly enough that waiting for business hours would blow well past the monthly target.
• Slow burn (ticket, review at business hours, don't page): consuming budget at a rate that would exhaust it over, say, 3 to 6 days if sustained. This is often a real but non-urgent problem (a slowly growing table causing gradually increasing latency, for example), worth investigating and fixing, but not worth waking someone at 3am for.

Why burn rate beats a static instantaneous threshold. A static threshold ("page if latency exceeds 100ms") pages on every transient blip and says nothing about whether that blip actually threatens the monthly target. Burn-rate alerting directly answers the question the error budget exists to answer: at this rate, will we still meet our target by the end of the window, and if not, how urgently does someone need to act right now to change that.

Worked example

A team sets a 99.9% monthly latency SLO (43.2-minute budget, per the calculation above). A replication-related incident causes elevated latency for 15 minutes straight. That's roughly 15/43.2, about 35% of the entire month's budget consumed in 15 minutes; if that rate continued, the whole month's budget would be gone in well under an hour, a clear fast-burn signature, so this pages immediately, and correctly so even though 15 minutes might sound minor in isolation. Contrast that with a slow degradation where latency creeps from 95ms to 105ms average over two weeks, occasionally dipping just over the 100ms threshold: individually tiny budget consumption per incident, but sustained over weeks it would eventually exhaust the budget too, just on a timescale that gives the team days to investigate and fix a real underlying cause (probably index bloat or data growth outpacing the current schema) rather than needing an immediate page.

Trade-offs and pitfalls

Setting the SLO itself is a product decision as much as an engineering one, and it's easy to pick a number that sounds appropriately serious (99.99%) without checking whether the current architecture can actually sustain it; an unrealistic SLO produces a permanently exhausted error budget, which then trains the team to ignore burn-rate alerts because they're always firing, the exact outcome error budgets exist to prevent. Burn-rate alerting requires picking the fast-burn and slow-burn windows deliberately (the 1-hour and multi-day windows above are a reasonable starting point, not a universal constant), and picking them too aggressively re-creates the static-threshold noise problem in a different shape. An error budget also assumes you're measuring the right thing: a latency SLO measured only at the database layer says nothing about what the end user actually experiences if there's a slow connection pooler or an inefficient application-layer retry policy sitting between the database and the user, so a database-layer SLO is a necessary piece of the picture, not the whole picture.

Direct answer

I find the root blocking session first, because in a chain of blocked transactions everyone downstream is just waiting on the one at the top; fixing anyone else does nothing. In PostgreSQL, pg_blocking_pids(pid) does that lookup directly for you. Once I know which session actually holds the lock, I look at what it's doing and how long it's been doing it before choosing between letting it finish, canceling its current statement, or terminating the connection outright, in that order of preference, because each option gets more disruptive.

Structured elaboration

Finding the blocking chain in PostgreSQL. The built-in function pg_blocking_pids(pid) returns the process IDs currently blocking a given session, which is simpler and more reliable than hand-writing a self-join against pg_locks:

SELECT pid,
       application_name,
       wait_event_type,
       wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

For a manual look at the underlying locks (useful when you want to see the lock mode, not just who's blocked):

SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE relation = 'orders'::regclass
ORDER BY granted, pid;

Finding the blocking chain in MySQL. InnoDB (MySQL's default storage engine) exposes the same shape of information through information_schema (or performance_schema on modern MySQL): SELECT * FROM sys.innodb_lock_waits; in the sys schema view gives a ready-made blocked-versus-blocking pair, and SHOW ENGINE INNODB STATUS includes a TRANSACTIONS section listing the most recent deadlock (a cycle where two or more sessions are each waiting on a lock the other one already holds, so neither can proceed without help, unlike the one-directional chain this whole example is built around) if InnoDB had to resolve one automatically.

Finding the blocking chain in SQL Server. The dynamic management views (DMVs, system views exposing live server state) sys.dm_exec_requests and sys.dm_tran_locks carry the same information under different names: sys.dm_exec_requests.blocking_session_id directly names the blocking session (0 or null means not blocked), and joining to sys.dm_tran_locks on the transaction ID shows the specific lock resource in contention, the same way pg_locks does for PostgreSQL.

Interpreting the lock types you find. A relation-level lock (PostgreSQL) or table-level lock blocks broadly; a tuple or row-level lock is narrower and usually the more common, less disruptive case in day-to-day OLTP (online transaction processing, meaning many small concurrent reads and writes rather than long-running analytical scans) traffic. transactionid as the wait type in PostgreSQL means one transaction is waiting for another to commit or roll back entirely, which is what a plain row-level UPDATE conflict looks like: the second UPDATE isn't waiting on the row's storage, it's waiting on the first transaction's outcome.

Telling a deadlock apart from ordinary blocking. Ordinary blocking, like the chain this whole example walks through, is one-directional: B is stuck behind A, but A itself isn't waiting on anything, so it can sit that way indefinitely, for as long as A holds the lock. A deadlock is a cycle instead: A waiting on a lock B holds while B is simultaneously waiting on a lock A holds, so neither can finish without outside help. PostgreSQL checks for that cycle automatically on a timer (deadlock_timeout, one second by default) and, if it finds one, aborts one of the participating transactions itself with a deadlock detected error. That's the practical tell from pg_stat_activity: a session sitting in pg_blocking_pids() behind a single, non-waiting blocker for more than a second or two is ordinary contention, not a deadlock, because a real deadlock would not survive that long on its own, PostgreSQL would already have broken it. To confirm a deadlock actually happened, look at the logs for the deadlock detected error rather than expecting to catch the cycle live in pg_stat_activity; by the time you query it, the database has usually already resolved it.

Safe remediation, in order of preference.

1. Wait, if the blocking transaction is short-lived and near completion. Most blocking resolves itself in normal operation within milliseconds to a few seconds; only intervene once you've confirmed it's stuck, not just momentarily busy.
2. Cancel the blocking session's current statement, not the connection: SELECT pg_cancel_backend(pid); in PostgreSQL, KILL QUERY <id>; in MySQL. This asks the running statement to stop and roll back, leaving the connection itself alive so the application can retry over the same connection if it's built to do so.
3. Terminate the connection outright only if canceling doesn't clear it (a session can be stuck in a state where it won't respond to a cancel, most commonly waiting on a client-side network read): SELECT pg_terminate_backend(pid); in PostgreSQL, KILL <id>; (as opposed to KILL QUERY) in MySQL. This forcibly closes the connection, which the application will see as a connection error.

Neither option violates data consistency by itself: canceling or terminating a session rolls back its open transaction, exactly the same guarantee the database already gives you for any aborted transaction. The actual risk is application-level, not data-level: whatever partial work that transaction represented needs to be safely retryable, and whoever is holding a connection pool slot (application code borrows an already-open database connection from a shared pool rather than opening a fresh one per request) on that session needs to notice it disappeared and recover cleanly rather than hanging.

Worked example

I reproduced a real blocking chain end to end on a seeded PostgreSQL 16 instance. Session A ran, inside one transaction: BEGIN; UPDATE orders SET status = 'paid' WHERE id = 1; SELECT pg_sleep(15); (holding the row lock open via the sleep, simulating an application that opened a transaction and got stuck before committing). One second later, session B ran UPDATE orders SET status = 'shipped' WHERE id = 1; against the same row. Querying from a third session while both were active:

 pid | application_name | wait_event_type | wait_event    | blocked_by | state  | query
-----+------------------+------------------+---------------+------------+--------+-------------------------------
  92 | order-updater-A  | Timeout          | PgSleep       | {}         | active | ...UPDATE orders SET status='paid'...
  99 | order-updater-B  | Lock             | transactionid | {92}       | active | ...UPDATE orders SET status='shipped'...

pg_blocking_pids(99) returned {92} directly: session B is blocked by session A, full stop, no manual join required. Session A's wait_event_type is Timeout/PgSleep, meaning it isn't blocked on anything else, it's just sitting in pg_sleep(), which is exactly the "stuck application code that forgot to commit" pattern this scenario is modeling. I then ran SELECT pg_cancel_backend(92);. One second later, session 92 had disappeared from pg_stat_activity (its transaction rolled back, since UPDATE plus pg_sleep inside BEGIN never reached COMMIT), and session B's blocked UPDATE went through immediately: querying orders afterward showed status = 'shipped' for that row, confirming B's write landed cleanly with no data corruption, exactly as the "safe remediation" claim above predicts. Note that pg_cancel_backend was sufficient here: A was blocked on its own pg_sleep, which responds to a cancel request, so I never needed the more disruptive pg_terminate_backend.

Trade-offs and pitfalls

Killing the wrong session in a multi-way chain (canceling B instead of A in the example above) accomplishes nothing: B was never holding anything, it was waiting, so the real blocker keeps blocking and you've just added an unnecessary rollback. Canceling a session that's deep into a large multi-statement transaction throws away all of that transaction's work, which is correct from a consistency standpoint but can be expensive in wasted effort if it was 90% done; check how much work is likely to be lost before you cancel, not just how long it's been blocking others. A session that repeatedly shows up as a blocker is a symptom worth escalating past the immediate incident: if the same code path keeps opening a transaction and doing something slow (an external API call, a large batch update) before committing, the fix is shortening that transaction's scope in the application, not learning to cancel it faster every time it recurs.

Direct answer

The database's own error text, "duplicate key value violates unique constraint," looks identical whether the cause is a genuine two-writer race, a re-run migration script, or real pre-existing duplicate data, because the constraint's whole job is to make all three collisions look the same at the SQL layer. Distinguishing them means bringing in evidence the database itself doesn't retain: an independent request log correlated against the failing key, a direct query against the source data for pre-existing duplicates, and a read of the migration script for idempotency. Mitigate the live impact by treating the race case as an expected, catchable outcome rather than an error, without touching the constraint itself.

Structured elaboration

Race condition check

Correlate an application-level log of every write attempt (not just successful writes) against the failing key. That log must be committed in its own transaction, separate from the risky write attempt; logging it inside the same transaction as the write means a failed write rolls the log entry back too, destroying the exact evidence the investigation needs. If two different client or session identifiers attempted the same key within a tight window (milliseconds to low tens of milliseconds), that's the race signature. One client retrying after a long gap and hitting an already-committed row is a normal, correct idempotent retry, not a bug, and should be treated as success by the caller.

Migration script bug check

Read the migration for whether reruns are idempotent (INSERT ... ON CONFLICT DO NOTHING or MERGE, versus a plain INSERT that fails loudly, or worse silently double-inserts, on a partial rerun after a timeout), whether it deduplicates the source data before inserting rather than assuming it's already unique, and whether its own execution log shows any sign it ran twice (two rows in a migration ledger, or overlapping time ranges in its own job log).

Genuinely inconsistent data check

Query the source (legacy or pre-migration) table directly for existing duplicates on the column the new constraint enforces: SELECT key_col, count(*) FROM legacy GROUP BY key_col HAVING count(*) > 1. If duplicates already existed upstream, the constraint is correctly surfacing a pre-existing data-quality problem the old system silently tolerated, not a migration bug or a race.

Immediate mitigation while investigating

For the race case specifically, catch the unique-violation and treat it as expected: re-select the row that already exists, and if its content matches what you intended to write, return success rather than an error to the caller. This is the standard idempotent-upsert pattern (INSERT ... ON CONFLICT (key) DO NOTHING followed by a SELECT, or DO UPDATE if a merge is genuinely safe). It doesn't fix the underlying cause, but it stops user-visible errors for the likely-majority-benign race case while the migration-bug and bad-data hypotheses, which need a human decision, get investigated.

Worked example

Real output from Postgres 16. First, the genuinely-inconsistent-data check against a legacy table that already has a duplicate before any migration touches it:

SELECT idempotency_key, count(*) AS dup_count, max(created_at) - min(created_at) AS time_spread
FROM legacy_payments
GROUP BY idempotency_key
HAVING count(*) > 1;

 idempotency_key | dup_count | time_spread
------------------+-----------+-------------
 idem-7001        |         2 | 00:00:04

Then, a real unique constraint and two genuinely concurrent sessions racing to insert the same new key:

CREATE TABLE payments (
  id bigserial PRIMARY KEY,
  idempotency_key text NOT NULL,
  amount numeric NOT NULL,
  CONSTRAINT payments_idempotency_key_uq UNIQUE (idempotency_key)
);

Session A and session B, started 20 milliseconds apart, both attempt INSERT INTO payments (idempotency_key, amount) VALUES ('idem-9002', 25.00);. Session B wins; session A gets:

ERROR:  duplicate key value violates unique constraint "payments_idempotency_key_uq"
DETAIL:  Key (idempotency_key)=(idem-9002) already exists.

The exact same error text as the pre-existing-duplicate case above; nothing in it says which cause produced it. The request-log correlation query, run against attempts logged in their own committed transactions before each insert was tried, is what actually distinguishes them:

SELECT idempotency_key, count(*) AS attempts, count(DISTINCT client_request_id) AS distinct_clients,
       max(received_at) - min(received_at) AS spread
FROM request_log GROUP BY idempotency_key HAVING count(*) > 1 ORDER BY spread;

 idempotency_key | attempts | distinct_clients |     spread
------------------+----------+-------------------+-----------------
 idem-9002        |        2 |                 2 | 00:00:00.022678

Two distinct clients, 22.6 milliseconds apart: the race signature, versus the legacy duplicate's 4-second spread above, which is far too slow to be a race and instead reflects a real pre-existing double-submit the old system accepted. The order-of-magnitude difference in spread is itself diagnostic.

While building this, logging the request attempt inside the same transaction as the risky insert made the log entry for the losing session vanish entirely when that transaction rolled back on the constraint violation, taking the very evidence needed for this investigation with it. That's exactly the pitfall stated above, discovered by hitting it directly: log attempts in their own committed transaction, always, before the risky write is attempted.

Trade-offs and pitfalls

• Don't disable or drop the constraint to stop the errors; that removes the only thing currently preventing real duplicate business records, even while it's noisy.
• The idempotent-catch mitigation is for the race case only. Applying it blindly to the inconsistent-data case would silently paper over real duplicate business records that need a human decision about which of the conflicting rows is actually correct.
• The transaction-boundary mistake above (logging inside the same transaction as the write it's meant to explain) is easy to make and destroys exactly the evidence a race-versus-bug investigation depends on.

Direct answer

The runbook has five stages: detect with automated checks conservative enough not to trigger on a transient blip, promote the most caught-up healthy replica, verify the promotion actually worked with a real write-and-read round trip (not just a status flag), roll back if verification fails, and communicate throughout rather than only at the end. I also build in a way to validate the whole runbook periodically through a drill, since a runbook that's never been exercised is a guess, not a tested procedure.

Structured elaboration

flowchart TD
    A["Alert: primary unreachable\nfor 3 consecutive 10s health checks"] --> B{"Automated checks:\nTCP port, SELECT 1,\nreplica lag was < 5s?"}
    B -->|"Primary confirmed down,\nreplica was current"| C["Run pg_promote() on\nthe most caught-up replica"]
    B -->|"Ambiguous or replica\nwas already lagging"| Z["Page human,\nno auto-promote"]
    C --> D{"pg_is_in_recovery()\nreturns false?"}
    D -->|"no, still true"| Z
    D -->|"yes, promoted"| E["Smoke test:\nwrite + read round trip"]
    E --> F{"Smoke test passed?"}
    F -->|"no"| G["Rollback trigger:\nrevert app to read-only,\npage human"]
    F -->|"yes"| H["Cut over: update\nDNS/proxy/connection string\nto new primary"]
    H --> I["Fence old primary:\nSTONITH or firewall rule,\nprevent split-brain writes"]
    I --> J["Post to status page\nand incident channel"]
    J --> K["Schedule blameless\npostmortem"]

Detection thresholds and automated checks. A single failed health check is not a failure, it's noise; page or auto-promote only after a consecutive-failure count (for example, 3 checks, 10 seconds apart, so roughly 30 seconds of confirmed unreachability) across more than one check type: a raw TCP connection attempt to the database port, and an actual SELECT 1 query, not just a port-open check, since a database can accept TCP connections while still being unable to serve queries. Before auto-promoting, check the candidate replica's own last-known replication lag; a replica that was already lagging significantly before the primary went down is a worse promotion candidate than a replica that was current, and promoting a badly lagged replica trades an outage for silent data loss.

Promotion. In PostgreSQL, pg_promote() (a built-in function, the modern replacement for the older trigger-file-based method) ends recovery mode on a standby and makes it a normal, writable primary. I verified this directly on a real two-node setup: before promotion, pg_is_in_recovery() returned true; after calling SELECT pg_promote(); and waiting roughly two seconds, it returned false, confirming the promotion completed and the instance had switched out of standby mode.

Verification: don't trust the status flag alone. pg_is_in_recovery() returning false proves the instance left recovery mode; it does not prove the instance is actually accepting and durably persisting writes the way the rest of the system needs. I ran an explicit smoke test on the promoted instance: CREATE TABLE smoke_check (id int, checked_at timestamptz default now()); INSERT INTO smoke_check(id) VALUES (1); SELECT * FROM smoke_check;, and confirmed the row came back correctly. A real runbook uses a pre-existing lightweight table (not a fresh CREATE TABLE on a live production database) reserved for exactly this purpose, so the smoke test itself never risks colliding with real schema.

Rollback triggers. If the smoke test fails, or the newly promoted instance shows an error rate or latency spike immediately after cutover, the rollback path is: put the application into read-only or maintenance mode rather than letting it keep sending writes to a database you no longer trust, and page a human rather than attempting a second automated promotion, since a second automated action compounding on a first failure is how a single-database incident becomes a data-loss incident.

Cutover and fencing the old primary. Once verification passes, update whatever routes traffic to "the primary" (DNS record, proxy configuration, or connection string in a service registry) to point at the newly promoted instance. Critically, the old primary must be explicitly fenced, not just assumed dead: if it comes back online (a network partition healing, rather than a true crash) while still believing it's the primary, and anything is still able to reach it, you now have two writable primaries diverging independently, a split-brain scenario that is far worse than the original outage and can silently lose or duplicate data. Fencing means an explicit action (STONITH, "shoot the other node in the head", a firewall rule blocking it, or forcibly stopping the database process on it) taken as part of the same automated or manual procedure, not left as a follow-up task.

Communicating status. Post to the incident channel and any customer-facing status page at each stage transition (detected, promoting, verifying, cut over, stable), not only at the start and the end; "still verifying" at minute 3 is meaningfully different information than silence followed by "resolved" at minute 8.

Validating the runbook itself. A runbook nobody has run since it was written is a hypothesis, not a tested procedure. Scheduling periodic failover drills, or chaos-engineering-style exercises that deliberately kill a primary in a non-production or carefully scoped production environment, is the only way to find out the promotion function has changed names in a newer database version, the DNS TTL is too long to actually redirect traffic quickly, or the alerting threshold is miscalibrated, before you're relying on all of that for real during an actual outage.

Worked example

Walking through the diagram above on the real promotion I ran: primary at 192.168.107.2 became unreachable (simulated by treating it as down for the exercise). Automated checks: 3 consecutive failed SELECT 1 attempts, replica's last known replay_lag was under 25 milliseconds, so it's a good promotion candidate. Promotion: SELECT pg_promote(); on the replica. Verification: pg_is_in_recovery() flipped from true to false; smoke test INSERT/SELECT round trip succeeded with real data returned. Cutover: application connection string would be updated to point at the (formerly) replica's address. Fencing: the old primary would need to be stopped or firewalled before anything is allowed to write to it again, since in this exercise it was never actually destroyed, just treated as unreachable, and could otherwise still be listening on the network.

Trade-offs and pitfalls

Automating the entire path from detection through promotion (rather than stopping at "detect and page a human") minimizes downtime but directly increases the risk of a wrong automated decision, most dangerously promoting a lagged replica or promoting during a network partition where the primary is actually still healthy and serving traffic to some clients (the split-brain scenario again, approached from the other direction). A common gap in real runbooks is treating "promotion succeeded" and "cutover is safe" as the same thing; they're not, the smoke test exists specifically to catch the gap between them. Skipping the periodic drill is the most common real-world failure of this whole design: the runbook rots as the database version, the DNS setup, or the on-call rotation's familiarity with it all quietly drift out of sync with what's written down.