Module 12 — Production Operations: Monitoring, Migration, and the Runbooks That Matter

pg_basebackup: Physical Backup

# Full cluster backup (binary copy of $PGDATA)
pg_basebackup \
  -h localhost \
  -U replicator \
  -D /backup/base_$(date +%Y%m%d) \
  --format=tar \
  --compress=9 \
  --checkpoint=fast \      # force immediate checkpoint before backup starts
  --wal-method=stream \    # stream WAL during backup (ensures backup is consistent)
  --progress \
  --verbose
 
# Estimated size before running:
pg_basebackup --estimate-size -h localhost -U replicator -D /dev/null

Advantages: fast (parallel I/O), complete cluster copy, can be used as base for PITR, can be used directly as a standby.
Disadvantages: not portable across major versions, full cluster only (not table-selective), requires a replication connection.

pgBackRest: Production Backup Tool

For production systems, pgBackRest is the standard. It handles:

• Incremental and differential backups (only changed blocks since last backup)
• Parallel backup and restore
• WAL archiving integrated with backup
• Backup verification
• Remote backup to S3, Azure, GCS

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/backup/pgbackrest
repo1-retention-full=2       # keep last 2 full backups
repo1-retention-diff=7       # keep 7 differential backups
compress-type=lz4
compress-level=6
 
[global:archive]
archive-async=y
archive-get-queue-max=128mb
 
[indexer]
pg1-path=/var/lib/postgresql/data
pg1-host=primary
pg1-user=postgres

# Full backup
pgbackrest --stanza=indexer backup --type=full
 
# Incremental backup (default)
pgbackrest --stanza=indexer backup
 
# Restore to latest
pgbackrest --stanza=indexer restore
 
# PITR to specific time
pgbackrest --stanza=indexer restore \
  --target="2026-05-17 14:30:00" \
  --target-action=promote

Backup Testing

A backup you have never tested is not a backup — it is a false sense of security. Schedule quarterly restore drills:

# Monthly: restore to a test instance and verify
pgbackrest --stanza=indexer restore --target-action=promote \
  --pg1-path=/tmp/restore_test
 
# Verify row counts match production
psql -d restored_indexer -c "SELECT count(*) FROM transactions;"
# Compare to: SELECT count(*) FROM transactions; on production
 
# Verify PITR capability
pgbackrest --stanza=indexer restore \
  --target="2026-05-15 00:00:00" \
  --target-action=pause \
  --pg1-path=/tmp/pitr_test

---

Zero-Downtime Migration Checklist

Every schema change on a live production database must be evaluated against this checklist.

Operations That Lock (Avoid Without a Plan)

-- These acquire AccessExclusiveLock — blocks ALL reads and writes:
ALTER TABLE transactions ADD COLUMN x TEXT NOT NULL DEFAULT 'unknown';  -- PG < 11: full rewrite. Without a DEFAULT at all, this fails immediately on a non-empty table, on any PG version.
ALTER TABLE transactions ALTER COLUMN amount TYPE NUMERIC(40, 8);
ALTER TABLE transactions DROP COLUMN payload;         -- instant, but lock acquired
CREATE INDEX ON transactions (block_height);          -- use CONCURRENTLY instead
VACUUM FULL transactions;
CLUSTER transactions USING idx_transactions_height;

Safe Operations (No Significant Lock)

-- These are safe on live tables:
ALTER TABLE transactions ADD COLUMN notes TEXT;                    -- nullable, instant
ALTER TABLE transactions ADD COLUMN version INT NOT NULL DEFAULT 1; -- PG 11+, instant
ALTER TABLE transactions ALTER COLUMN notes SET DEFAULT 'none';    -- instant
ALTER TABLE transactions ADD CONSTRAINT amount_positive
  CHECK (amount >= 0) NOT VALID;                                   -- instant (skip validation)
ALTER TABLE transactions VALIDATE CONSTRAINT amount_positive;      -- ShareUpdateExclusiveLock
CREATE INDEX CONCURRENTLY idx_transactions_sender_new
  ON transactions (sender);                                        -- no blocking
DROP INDEX CONCURRENTLY idx_transactions_sender_old;               -- no blocking

The Migration Sequence

Adding a NOT NULL column with a default (PG 10 and earlier):

-- Step 1: add nullable (instant)
ALTER TABLE transactions ADD COLUMN region TEXT;
 
-- Step 2: backfill in batches (avoid one giant UPDATE that locks)
DO $$
DECLARE
  batch_size INT := 10000;
  last_id BIGINT := 0;
  max_id BIGINT;
BEGIN
  SELECT max(id) INTO max_id FROM transactions;
  WHILE last_id < max_id LOOP
    UPDATE transactions
    SET region = 'us-east'
    WHERE id > last_id AND id <= last_id + batch_size
      AND region IS NULL;
    last_id := last_id + batch_size;
    PERFORM pg_sleep(0.1);  -- brief pause between batches to reduce I/O pressure
  END LOOP;
END $$;
 
-- Step 3: add NOT NULL constraint (validates only new rows since NOT VALID)
ALTER TABLE transactions ALTER COLUMN region SET NOT NULL;
-- PG 11+: this is instant if all rows are non-null; PG 10-: full scan

Renaming a column (zero downtime):

-- Step 1: add new column
ALTER TABLE transactions ADD COLUMN from_address VARCHAR(66);
 
-- Step 2: sync old to new via trigger (or application dual-write)
CREATE OR REPLACE FUNCTION sync_from_address()
RETURNS TRIGGER AS $$
BEGIN
  NEW.from_address := NEW.sender;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_sync_from_address
BEFORE INSERT OR UPDATE ON transactions
FOR EACH ROW EXECUTE FUNCTION sync_from_address();
 
-- Step 3: backfill existing rows
UPDATE transactions SET from_address = sender WHERE from_address IS NULL;
 
-- Step 4: deploy application reading new column (verify in staging)
-- Step 5: deploy application writing both columns
-- Step 6: deploy application writing only new column
-- Step 7: drop trigger and old column
DROP TRIGGER trg_sync_from_address ON transactions;
ALTER TABLE transactions DROP COLUMN sender;

Adding a new index:

-- Always use CONCURRENTLY — takes longer but does not block
CREATE INDEX CONCURRENTLY idx_transactions_recipient
ON transactions (recipient)
WHERE recipient IS NOT NULL;  -- partial index for non-null values
 
-- Monitor progress
SELECT
  phase,
  blocks_done,
  blocks_total,
  tuples_done,
  tuples_total
FROM pg_stat_progress_create_index
WHERE relid = 'transactions'::regclass;

---

The Five Runbooks

Every production Postgres team needs these written down before they need them. The worst time to write a runbook is during an incident.

Runbook 1: Table Bloat Emergency

Symptoms: table size growing despite stable row count; autovacuum running continuously without catching up; queries slowing down on a table.

Diagnosis:

-- Check dead tuple accumulation
SELECT relname, n_dead_tup, n_live_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;
 
-- Check for blocking long transactions
SELECT pid, usename, state,
  EXTRACT(EPOCH FROM (now() - xact_start)) AS tx_seconds,
  LEFT(query, 80)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start ASC LIMIT 10;
 
-- Check for blocking replication slots
SELECT slot_name, active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

Response:

-- Step 1: terminate idle-in-transaction sessions blocking vacuum
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND xact_start < now() - INTERVAL '5 minutes';
 
-- Step 2: drop inactive replication slots (after confirming consumer is offline)
SELECT pg_drop_replication_slot('stalled_slot');
 
-- Step 3: run manual vacuum with maximum memory
SET maintenance_work_mem = '1GB';
VACUUM (VERBOSE, ANALYZE) transactions;
 
-- Step 4: monitor progress
SELECT phase, heap_blks_scanned, heap_blks_total, num_dead_tuples
FROM pg_stat_progress_vacuum;

Runbook 2: Replication Lag Spike

Symptoms: pg_stat_replication.replay_lag climbing; standby reads returning stale data; alerting on replication bytes lag.

Diagnosis:

-- On primary: check lag breakdown
SELECT application_name, state, write_lag, flush_lag, replay_lag,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS total_lag
FROM pg_stat_replication;
 
-- Check if autovacuum is the WAL generator
SELECT query FROM pg_stat_activity
WHERE query LIKE 'autovacuum%' ORDER BY xact_start;
 
-- Check WAL generation rate
SELECT pg_size_pretty(wal_bytes) AS total_wal,
  wal_fpi, wal_records
FROM pg_stat_wal;

Response:

-- If autovacuum is causing the lag burst:
-- Throttle the specific autovacuum (per-table cost delay)
ALTER TABLE transactions SET (autovacuum_vacuum_cost_delay = 20);
 
-- If standby is applying slowly (apply_lag >> flush_lag):
-- The standby's single-threaded apply is the bottleneck
-- Check standby hardware (CPU, I/O) — nothing to tune in Postgres directly
 
-- If network lag (write_lag >> 0):
-- Network between primary and standby is saturated
-- Check: ifstat, sar -n DEV on both hosts
 
-- Temporary: pause autovacuum on the primary during peak hours
ALTER SYSTEM SET autovacuum = off;
SELECT pg_reload_conf();
-- Remember to re-enable: ALTER SYSTEM SET autovacuum = on; SELECT pg_reload_conf();

Runbook 3: XID Wraparound Warning

Symptoms: PostgreSQL log shows WARNING: database "X" must be vacuumed within N transactions; monitoring alert on age(relfrozenxid).

Diagnosis:

-- Find most at-risk tables
SELECT relname, age(relfrozenxid) AS xid_age,
  pg_size_pretty(pg_total_relation_size(oid)) AS size,
  last_autovacuum, last_vacuum
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;
 
-- Check database-level age
SELECT datname, age(datfrozenxid) AS db_xid_age
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Response:

-- Run aggressive freeze vacuum immediately on the oldest table
VACUUM FREEZE VERBOSE transactions;
 
-- If autovacuum is already running a wraparound vacuum (check pg_stat_activity)
-- Do NOT interrupt it — let it complete
 
-- For the entire database (run during low-traffic window):
VACUUM FREEZE;
 
-- Monitor freeze progress
SELECT phase, heap_blks_scanned, heap_blks_total, num_index_scans
FROM pg_stat_progress_vacuum;
 
-- After vacuum, verify age dropped:
SELECT relname, age(relfrozenxid) FROM pg_class
WHERE relkind = 'r' ORDER BY age(relfrozenxid) DESC LIMIT 5;

Runbook 4: Connection Exhaustion

Symptoms: application errors FATAL: remaining connection slots are reserved for non-replication superuser connections; new connections rejected; pg_stat_activity shows connections at or near max_connections.

Diagnosis:

-- Count connections by state and application
SELECT application_name, state, count(*)
FROM pg_stat_activity
GROUP BY 1, 2
ORDER BY 3 DESC;
 
-- Find connection hogs
SELECT usename, application_name, count(*),
  count(*) FILTER (WHERE state = 'idle') AS idle,
  count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_tx,
  count(*) FILTER (WHERE state = 'active') AS active
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
GROUP BY 1, 2
ORDER BY 3 DESC;

Response:

-- Step 1: immediately kill idle connections from the offending application
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'offending_app'
  AND state = 'idle'
  AND state_change < now() - INTERVAL '5 minutes';
 
-- Step 2: kill idle-in-transaction (they are almost certainly stuck)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - INTERVAL '2 minutes';
 
-- Step 3: temporary emergency measure — reduce max connections per user
ALTER ROLE app_user CONNECTION LIMIT 50;
-- Revert after fixing the connection leak:
ALTER ROLE app_user CONNECTION LIMIT -1;
 
-- Step 4: fix root cause
-- Usually: application not closing connections (missing connection.close())
-- Or: connection pool misconfigured (pool size > Postgres max_connections / num_apps)
-- Or: connection pool not being used (each request creates a new connection)

Runbook 5: Slow Query During Incident

Symptoms: specific query suddenly slow; pg_stat_activity shows many backends waiting; p99 latency spiking.

Diagnosis:

-- Find the slowest currently running queries
SELECT pid, usename,
  ROUND(EXTRACT(EPOCH FROM (now() - query_start))::numeric, 1) AS seconds,
  wait_event_type, wait_event,
  LEFT(query, 150) AS query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start ASC
LIMIT 10;
 
-- Get the plan of a running query (without stopping it)
SELECT query, query_start, wait_event_type
FROM pg_stat_activity WHERE pid = <pid>;
 
-- Check if it is waiting for a lock
SELECT * FROM pg_blocking_pids(<pid>);
 
-- Get the actual plan being used (requires pg_stat_statements or auto_explain)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
WHERE query LIKE '%transactions%'
ORDER BY mean_exec_time DESC LIMIT 5;

Response:

-- If a lock is the problem: find and kill the blocking query
SELECT pg_terminate_backend(blocking_pid)
FROM (SELECT unnest(pg_blocking_pids(<waiting_pid>)) AS blocking_pid) t;
 
-- If stale statistics is the problem (plan changed after data load):
ANALYZE transactions;
 
-- If work_mem spill is the problem:
-- Session-level increase for the specific connection:
ALTER ROLE app_user SET work_mem = '256MB';
 
-- If the query is genuinely wrong (missing index, bad plan):
-- Add the index concurrently (does not block):
CREATE INDEX CONCURRENTLY idx_transactions_new ON transactions (column);
 
-- Emergency: cancel a specific long-running query (gentler than terminate)
SELECT pg_cancel_backend(<pid>);
 
-- Emergency: kill a stuck connection entirely
SELECT pg_terminate_backend(<pid>);

---

auto_explain: Automatic Plan Logging

For queries that are intermittently slow, auto_explain logs the execution plan automatically when execution time exceeds a threshold.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = 1000  # log plans for queries > 1 second
auto_explain.log_analyze = on         # include ANALYZE timing
auto_explain.log_buffers = on         # include buffer stats
auto_explain.log_nested_statements = on  # include plans from functions
auto_explain.sample_rate = 0.1        # log 10% of qualifying queries (reduce noise)

This is invaluable for diagnosing intermittent slowdowns — you get the actual plan at the time of slowness without having to reproduce it.

---

The Production Incident: Connection Exhaustion Cascade

Context: A blockchain indexer with 500 max_connections behind PgBouncer in transaction mode.

What happened:

A deployment introduced a bug: under certain error conditions, the application's database connection was not returned to the pool — it was abandoned while holding an open transaction. The transaction stayed idle in transaction.

At normal load: the leak rate was slow enough that connections accumulated over hours without triggering alerts.

At 2:00 AM, a spike in blockchain traffic increased error rate. The leak accelerated. Within 20 minutes:

• PgBouncer's cl_waiting counter climbed (application connections queuing for a server connection)
• Application timeouts increased (requests waiting for a connection from the pool)
• Application retried timed-out requests (each retry needed its own connection)
• The retry storm caused even more connections to be consumed
• PgBouncer ran out of server connections; applications received connection refused
• Applications retried again — the storm intensified

Peak: 12,000 application connections to PgBouncer, 500 Postgres connections all idle in transaction, zero connections available for legitimate queries.

Resolution sequence:

-- Step 1: kill all idle-in-transaction connections (immediate relief)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';
-- 487 connections killed
 
-- Step 2: identify the application sending the most connections
SELECT application_name, count(*)
FROM pg_stat_activity
GROUP BY 1 ORDER BY 2 DESC;
-- 'indexer-worker' had 312 connections
 
-- Step 3: limit connections from offending app while fix is deployed
ALTER ROLE indexer_worker CONNECTION LIMIT 50;
 
-- Step 4: add the missing timeout to prevent recurrence
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
SELECT pg_reload_conf();

The root fix: idle_in_transaction_session_timeout = '30s' was added to postgresql.conf. Any connection that stays idle-in-transaction for more than 30 seconds is now automatically terminated by Postgres. The connection leak bug was fixed in the application, but the timeout ensures any future bug of the same class is self-healing.

Post-incident addition: a PgBouncer alert on cl_waiting > 100 was added — the waiting queue was the earliest visible signal, 15 minutes before the outage became critical.

---

Summary

---

Course Complete

You have now covered the full operational stack of PostgreSQL — from the 8KB page on disk to the replication slot across availability zones. The mental model this course set out to build in Module 0 is now complete.

The path from here:

Practice: run the pageinspect, pgstattuple, and pg_stat_* queries against a real database. The numbers will be different from the examples — that is the point. Learn to read your specific system.

Operationalize: implement the alerting thresholds, write the runbooks, add idle_in_transaction_session_timeout to every production instance you run.

Go deeper: the Postgres source code is readable C. The storage manager, the executor, the planner — all of it is there. backend/storage/buffer/, backend/executor/, backend/optimizer/ are where the concepts in this course live in code.

The database does not lie. Every behavior has a cause in the mechanics described here. When something unexpected happens in production, the answer is in pg_stat_*, in WAL, in the heap pages — you now have the model to find it.

---