{
  "schema_version": 1,
  "skill_name": "postgres",
  "evals": [
    {
      "id": "config-review-for-new-instance",
      "prompt": "We just provisioned a PostgreSQL 16 instance for a read-heavy reporting workload and left every setting at default. What configuration should we review before it goes live, and which changes require a restart versus a reload?",
      "expected_output": "A configuration review that names the operator-critical parameters and classifies each change by when it takes effect: shared_buffers, effective_cache_size, work_mem, and maintenance_work_mem for cache and memory sizing; wal_level (must be replica or higher for archiving and streaming replication), max_wal_senders, and max_replication_slots; archive_mode and archive_command for WAL archiving readiness; autovacuum settings for maintenance; log_min_duration_statement and track_io_timing so slow-query and I/O evidence exist before they are needed. The response states that memory and process-shape parameters (shared_buffers, max_connections, wal_level, max_wal_senders, max_replication_slots, shared_preload_libraries) require a restart while most tuning and logging parameters reload with pg_reload_conf(), and that the runtime source of truth is pg_settings, not the file. It verifies each change with SHOW or current_setting() after applying, and routes connection-pooling and application access patterns to backend-engineering and schema decisions to data-architect.",
      "assertions": [
        "The review names shared_buffers, effective_cache_size, work_mem, maintenance_work_mem, wal_level, archive_mode, and logging parameters",
        "Changes are classified as restart-required (memory and process shape) versus reload-only (pg_reload_conf)",
        "pg_settings is identified as the runtime source of truth rather than the configuration file",
        "WAL archiving readiness (wal_level replica, archive_mode, archive_command) is included for a production instance",
        "Application access patterns route to backend-engineering and schema design routes to data-architect"
      ]
    },
    {
      "id": "backup-restore-plan-wal-pitr",
      "prompt": "Our PostgreSQL 15 instance has archive_mode off and we only take nightly pg_dump snapshots. A user accidentally deleted a row at 14:32 and we need to restore it. What is wrong with the current setup and what should the backup and recovery plan look like?",
      "expected_output": "A diagnosis that the current setup cannot recover the specific row: a nightly dump alone recovers only to the last dump, and archive_mode off means there is no WAL archive to replay. The plan prescribes enabling wal_level replica and archive_mode on with a working archive_command, verifying pg_stat_archiver shows archived_count growing and failed_count flat, taking a base backup with pg_basebackup -X stream, and testing a point-in-time recovery to a recovery_target_time on a scratch instance as the only proof the backup supports recovery. The response explains that a base backup plus continuous WAL gives point-in-time recovery (PITR): restore the base, replay archived WAL up to the target time, verify the row exists at the boundary, then promote. It frames RPO/RTO trade-offs and notes that backup strategy methodology routes to data-engineering while the PostgreSQL mechanics live in this skill, and that a restore is a mutation requiring an explicit human directive.",
      "assertions": [
        "The current setup is diagnosed as unable to recover the deleted row because archive_mode is off and there is no WAL archive",
        "The plan enables wal_level replica and archive_mode with a working archive_command",
        "Archiver health is verified via pg_stat_archiver with archived_count growing and failed_count flat",
        "A base backup with pg_basebackup plus WAL replay to recovery_target_time is described, with a restore drill as the evidence",
        "Backup strategy methodology routes to data-engineering and a restore is treated as a mutation requiring confirmation"
      ]
    },
    {
      "id": "performance-diagnosis-slow-query",
      "prompt": "A reporting query over the orders table (about 5 million rows) started taking 40 seconds this week. The table has an index on order_date. How do I diagnose this with evidence before changing anything?",
      "expected_output": "An evidence-first diagnosis: run the read-only diagnostic to capture identity, config, connection, and index-usage context, then EXPLAIN (ANALYZE, BUFFERS) the actual query and compare estimated to actual rows. The response checks pg_stat_user_indexes for idx_scan on the order_date index and pg_stat_user_tables for seq_scan and seq_tup_read to see whether the planner chose a sequential scan; checks whether planner statistics are stale by looking at n_dead_tup and whether autovacuum has run recently; and only then considers planner parameters such as random_page_cost or effective_cache_size. It treats a plan change over time as a clue (new data volume, new statistics, changed parameter) and verifies any fix by re-running EXPLAIN and re-checking index scan counts. The response explicitly routes adding a new index for the workload to data-architect and keeps this skill's role as measurement and operation, and notes that correlation between a slow query and high dead tuples is not automatically causation.",
      "assertions": [
        "Diagnosis starts with read-only evidence collection and EXPLAIN (ANALYZE, BUFFERS) rather than a config guess",
        "Index usage and seq scan statistics (pg_stat_user_indexes, pg_stat_user_tables) are checked",
        "Stale planner statistics are considered via dead-tuple trends and autovacuum recency",
        "Planner parameters are changed one at a time and verified by re-running the plan",
        "Schema decisions such as adding a new index route to data-architect and correlation is not presented as causation"
      ]
    },
    {
      "id": "replication-and-failover-plan",
      "prompt": "We want a standby for our primary PostgreSQL 16 instance so we can survive a server loss. What do we need to configure, how do we verify the standby is healthy, and what should the failover procedure include?",
      "expected_output": "A replication setup and failover plan: wal_level must be replica or higher, max_wal_senders and max_replication_slots sized for the standby, a physical replication slot created per standby, and the standby built with pg_basebackup -X stream with primary_conninfo and standby.signal. Health is verified on the primary via pg_stat_replication showing state streaming with replay_lsn close to the primary and replay_lag within the agreed bound, and on the standby via pg_is_in_recovery with pg_last_wal_receive_lsn and pg_last_wal_replay_lsn advancing. The failover procedure names who promotes (pg_ctl promote or SELECT pg_promote()), how clients are redirected and verified with a fresh connection, and how the old primary is rejoined with pg_rewind so two primaries never accept writes. The response distinguishes synchronous from asynchronous replication and states the durability trade-off, and treats promotion as a mutation requiring an explicit human directive and a rollback path.",
      "assertions": [
        "Setup covers wal_level, max_wal_senders, max_replication_slots, a replication slot, and pg_basebackup -X stream",
        "Health is verified with pg_stat_replication state streaming and lag within the agreed bound",
        "The failover procedure names promotion, client redirection, and rejoining the old primary with pg_rewind",
        "Synchronous versus asynchronous durability trade-offs are stated explicitly",
        "Promotion is treated as a mutation requiring a human directive and a rollback path"
      ]
    },
    {
      "id": "vacuum-and-bloat-investigation",
      "prompt": "Our pg_stat_user_tables shows n_dead_tup growing on several large tables and some tables' file sizes look much bigger than their live data. What does this mean and what should we do, and what must we not do?",
      "expected_output": "An explanation that rising n_dead_tup means the vacuum loop is falling behind: autovacuum is not reclaiming dead tuples fast enough, and the file-size-to-live-data gap is the bloat signal. The response first looks for why autovacuum lags — long-running transactions pinning old snapshots, not enough autovacuum_max_workers, connection saturation, or tables larger than the autovacuum nap cycle — using pg_stat_progress_vacuum and age(datfrozenxid) to check wraparound risk, then prescribes a targeted, confirmed maintenance vacuum (VACUUM ANALYZE on the specific tables) in a maintenance window. It states the hard boundaries: VACUUM FULL is a last resort that rewrites the table and takes exclusive locks and must never be routine, autovacuum must never be disabled to save load, and a high dead-tuple count is evidence of a lagging maintenance loop, not a diagnosis by itself. Verification is re-running the bloat probe and confirming the trend reversed and autovacuum is keeping up.",
      "assertions": [
        "Rising dead tuples are interpreted as the vacuum loop falling behind, with bloat as the file-size-to-live-data signal",
        "Root causes of autovacuum lag are checked: long transactions, worker starvation, connection saturation, table size",
        "The fix is a targeted confirmed maintenance vacuum, not a blanket or routine VACUUM FULL",
        "VACUUM FULL's lock and rewrite cost and the never-disable-autovacuum boundary are stated",
        "Verification re-runs the bloat probe and confirms the trend reversed"
      ]
    },
    {
      "id": "major-version-upgrade-plan",
      "prompt": "We are on PostgreSQL 15 with the PostGIS and pgvector extensions and want to move to PostgreSQL 16 with minimal downtime. What is the safe plan and what are the traps?",
      "expected_output": "A sequenced major-upgrade plan: read the release notes and upgrade guide for the full version span first, inventory extensions and check each one's major-upgrade notes (PostGIS and pgvector are not binary-compatible across major versions and need reinstall or rebuild on the new binaries), run pg_upgrade --check as a no-change preflight, and rehearse on a scratch environment with the real data shape. The response distinguishes pg_upgrade link mode (fast, but the old cluster shares inodes and is not a clean rollback) from copy mode or logical dump/restore (slower, but keeps a rollback path), prescribes running analyze on the new cluster before debugging any 'slow queries', and requires verifying at the application boundary before decommissioning the old cluster. Logical replication is named as the near-zero-downtime alternative whose migration methodology routes to data-engineering. The upgrade is a mutation requiring a maintenance window, a verified backup, and an explicit human directive.",
      "assertions": [
        "Release notes and upgrade guides for the full version span are read before anything runs",
        "Extension major-upgrade gotchas (PostGIS, pgvector) are checked and handled per extension",
        "pg_upgrade --check is used as a no-change preflight and link mode versus copy mode trade-offs are stated",
        "analyze runs on the new cluster before slow-query debugging, and the old cluster is kept until boundary verification",
        "Logical replication is named as the near-zero-downtime path with migration methodology routed to data-engineering"
      ]
    }
  ]
}

# PostgreSQL Operations — Source Index

> **Last Updated:** 2026-08-03

This index tracks the authoritative sources behind the PostgreSQL operational
skill and the refresh procedure for keeping it current.

## Canonical sources

| Topic | Source |
|---|---|
| Core documentation (current release) | https://www.postgresql.org/docs/current/ |
| Configuration reference | https://www.postgresql.org/docs/current/runtime-config.html |
| Server administration (backup, replication, upgrades) | https://www.postgresql.org/docs/current/admin.html |
| System catalogs and statistics views | https://www.postgresql.org/docs/current/catalogs.html and .../monitoring-stats.html |
| `pg_upgrade` | https://www.postgresql.org/docs/current/pgupgrade.html |
| `pg_basebackup` | https://www.postgresql.org/docs/current/app-pgbasebackup.html |
| Streaming replication | https://www.postgresql.org/docs/current/warm-standby.html and .../streaming-replication.html |
| Continuous archiving and PITR | https://www.postgresql.org/docs/current/continuous-archiving.html |
| `pg_stat_archiver` and statistics views | https://www.postgresql.org/docs/current/monitoring-stats.html |

## Version observations (as of this refresh)

- `pg_stat_replication` exposes `sent_lsn`/`write_lsn`/`flush_lsn`/`replay_lsn`
  and the `*_lag` columns on PostgreSQL 10 and later. Pre-10 servers expose
  `*_location` columns instead; this skill targets PostgreSQL 10+.
- `pg_backup_start()`/`pg_backup_stop()` replaced `pg_start_backup()`/
  `pg_stop_backup()` in PostgreSQL 15. `pg_basebackup` remains the supported
  base-backup path on every supported release.
- Recovery configuration moved from `recovery.conf` to `recovery.signal`/
  `standby.signal` in PostgreSQL 12. `recovery_target_*` parameters are set in
  `postgresql.conf` (or via `ALTER SYSTEM`) and take effect at start time.
- `VACUUM` progress is observable through `pg_stat_progress_vacuum`
  (PostgreSQL 9.6+) and `pg_stat_progress_cluster` (12+).
- `pg_promote()` (PostgreSQL 12+) promotes a standby without shelling out.

## Refresh procedure

1. Re-check the sources above for a new minor or major release.
2. Update the version observations that changed (column renames, renamed
   functions, moved configuration files).
3. Re-run the bundled diagnostic script against a test instance and confirm
   every check still parses: `scripts/pgdiag --psql /path/to/psql --json`.
4. Re-verify the SKILL.md keyword sweep from the validation contract and the
   routing links to `backend-engineering`, `data-architect`, and
   `data-engineering`.

## Related skill sources

- `data-engineering` owns cross-engine methodology: backup strategy, migration
  patterns, and analytical SQL. Its references are the source for engine-spanning
  decisions; this skill covers PostgreSQL operation itself.
- `data-architect` owns schema and data modeling; index *choice* at design time
  lives there, while index *operation and measurement* live in
  `02-indexes-and-query-plans.md`.

# PostgreSQL Configuration Operations

> **Last Updated:** 2026-08-03

This reference covers reading, changing, and verifying PostgreSQL configuration
safely. The runtime source of truth is `pg_settings`, not the configuration
file: values shown by `SHOW`/`current_setting()` reflect reloads, `ALTER
SYSTEM` overrides, and command-line `-c` options.

## Reading configuration

```sql
SHOW shared_buffers;
SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE name IN ('shared_buffers', 'max_connections', 'wal_level', 'archive_mode');
```

The `context` column says when a change takes effect:

- `postmaster` — requires a restart (`pending_restart` becomes true).
- `sighup` — takes effect on reload (`pg_ctl reload` or
  `SELECT pg_reload_conf();`).
- `user`/`superuser`/`backend` — settable per-session or per-role.

## Reload versus restart

Requires restart (memory and process-shape parameters):

- `shared_buffers`, `max_connections`, `wal_level`, `max_wal_senders`,
  `max_replication_slots`, `max_prepared_transactions`,
  `shared_preload_libraries`, `huge_pages`, `dynamic_shared_memory_type`.

Reloads safely (most tuning, logging, and vacuum parameters):

- `work_mem`, `maintenance_work_mem`, `effective_cache_size`,
  `random_page_cost`, `seq_page_cost`, `checkpoint_timeout`,
  `max_wal_size`, `archive_command`, `autovacuum` and its workers,
  `log_min_duration_statement`, `log_statement`, `track_io_timing`.

The bundled `pgdiag` `config` check reports the operator-critical values in
one bounded payload:

```bash
scripts/pgdiag --check config --json
```

## Operator-critical values and what they do

| Parameter | What it controls | Typical signal of trouble |
|---|---|---|
| `shared_buffers` | Postgres's own cache | Too small: heavy `pg_stat_database` read activity while OS cache is idle |
| `effective_cache_size` | Planner's estimate of OS+PG cache | Too small: planner favors index scans that are slower than seq scans in reality |
| `work_mem` | Per-sort/hash memory | Too small: temp-file sorts (`pg_stat_database.temp_files`) |
| `maintenance_work_mem` | Vacuum/index build memory | Too small: slow index builds and slow vacuum |
| `max_connections` | Hard connection cap | Saturation: `pg_stat_activity` near cap, `FATAL: sorry, too many clients` |
| `wal_level` | What WAL carries | Must be `replica` (or higher) for archiving and streaming replication |
| `archive_mode` / `archive_command` | WAL archiving | See `04-backups-wal-pitr.md` |
| `autovacuum` / `autovacuum_max_workers` | Background maintenance | Rising `n_dead_tup`, XID wraparound risk; see `03-vacuum-and-bloat.md` |
| `log_min_duration_statement` | Slow-query logging threshold | 0 if you need every statement; 250-1000ms is a common operations default |
| `track_io_timing` | I/O timing in statistics | Off makes `pg_stat_database` I/O columns meaningless |
| `random_page_cost` | Planner cost of random reads | Overstated on SSD causes seq-scan preference for large tables |

## Changing configuration safely

1. Change in the file (or `ALTER SYSTEM SET ...`) on one named parameter.
2. Determine whether a reload or restart is required from `pg_settings.context`.
3. Prefer reload; schedule restarts with a maintenance window and a verified
   rollback (the previous value).
4. Verify with `SHOW`/`current_setting()` and re-run the affected diagnostic.

Hard boundaries:

- Never change `shared_preload_libraries` without a restart plan — a typo can
  make the server fail to start.
- Never raise `max_connections` without accounting for backend memory per
  connection (`work_mem` is per operation, but each backend holds base memory).
- Connection pooling for application workloads is an application-architecture
  decision that routes to `backend-engineering`; `max_connections` tuning here
  is server capacity, not app design.

# Indexes and Query Plans

> **Last Updated:** 2026-08-03

This reference covers measuring and operating indexes and diagnosing query
plans. Designing which index to add for a new workload is schema design and
routes to `data-architect`; this skill owns reading the evidence and operating
what exists.

## Evidence first: statistics views

```sql
-- Which indexes are actually used, most used first
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC LIMIT 10;

-- Indexes with no recorded scans (write cost with no read benefit)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname, indexrelname;

-- Tables scanned sequentially a lot (candidate for a missing index)
SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC LIMIT 10;

-- Indexes marked unusable (dropped on next vacuum)
SELECT c.relname AS index_name, i.indrelid::regclass AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;
```

The bundled `pgdiag` script runs these probes in its `index_usage`,
`unused_indexes`, `invalid_indexes`, and `seq_scan_heavy` checks.

## Reading a plan

- `EXPLAIN` shows the planner's estimate; `EXPLAIN (ANALYZE, BUFFERS)` executes
  the statement and reports actual rows, timing, and buffer usage. Read the
  estimated-versus-actual row counts: a large mismatch means the planner is
  working from stale or missing statistics.
- `EXPLAIN (FORMAT JSON)` is the machine-readable form:
  `scripts/pgdiag --plan-for "SELECT ..." --json`.
- Node shapes to recognize: `Seq Scan` (whole-table read), `Index Scan` /
  `Index Only Scan` (index seek), `Bitmap Heap Scan` (index + heap filter),
  `Nested Loop`/`Hash Join`/`Merge Join`, `Sort`, `Materialize`.

## Common findings and next steps

| Finding | Likely cause | Check before acting |
|---|---|---|
| `Seq Scan` on a filtered large table | Missing index or planner cost settings | `pg_stat_user_tables.seq_tup_read`; actual row estimate vs `random_page_cost`/`effective_cache_size` |
| Estimated rows far from actual | Stale `pg_statistic` | Has `autovacuum`/autoanalyze run since the last big change? |
| Index with `idx_scan = 0` for weeks | Unused index (write overhead) | Confirm the access path is genuinely gone, then a deliberate drop in a maintenance window |
| Invalid index | Interrupted build / catalog issue | Repair deliberately; never assume it is serving queries |
| `Index Only Scan` not used | Visibility map not set | Check `pg_class.relallvisible`; the heap pages are probably dirty |

## Query-plan hygiene

- Test plans on the real workload shape, not on tiny samples: statistics scale
  with data, and a plan that is right on a 1k-row table is often wrong at 10M.
- Change planner parameters one at a time and re-measure with
  `EXPLAIN (ANALYZE, BUFFERS)` before and after.
- `pg_stat_statements` (extension) is the best aggregate evidence for which
  statements deserve plan work. See `06-extensions.md` for enabling it.
- Never "fix" a slow query by disabling index scans globally
  (`enable_indexscan=off`): treat that as a diagnostic probe, not a fix.

## Verification

After any index change: re-run the affected query's `EXPLAIN (ANALYZE,
BUFFERS)` and compare total time and rows, and re-check `idx_scan` trends on
the next reporting window. A plan change is verified by measurement, not by
assertion.

# Vacuum and Bloat Management

> **Last Updated:** 2026-08-03

This reference covers the maintenance loop that keeps PostgreSQL tables
healthy: autovacuum supervision, dead-tuple and bloat measurement, and the
disciplined use of manual vacuum operations.

## Why vacuum matters

- Vacuum reclaims dead tuple space and refreshes the visibility map and
  planner statistics. Without it, tables bloat and transaction ID wraparound
  becomes an emergency.
- `autovacuum` is on by default; the operational task is *supervision*: is it
  keeping up with the workload?

## Measuring the signals

```sql
-- Dead tuples per table, worst first
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC LIMIT 10;

-- How far tables are from forced anti-wraparound vacuum
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;

-- Is a vacuum running right now, and where?
SELECT * FROM pg_stat_progress_vacuum;
```

The `pgdiag` `bloat` check reports the dead-tuple signal in one bounded payload.

## Interpreting the evidence

- `n_dead_tup` rising faster than vacuum runs complete → autovacuum is falling
  behind. Common causes: not enough `autovacuum_max_workers`, long-running
  transactions pinning old snapshots, connection saturation, or very large
  tables where a full pass takes longer than `autovacuum_naptime`.
- Table file size far above live-data size → heap bloat. Check `pg_class`
  `relpages` against `n_live_tup` × average row width; a bloat estimate query
  can quantify it.
- Index bloat shows as large `relpages` on indexes relative to key count.
- `age(datfrozenxid)` approaching `autovacuum_freeze_max_age` (default 200M) is
  the wraparound warning; below ~50M it is time to act deliberately.

## Acting deliberately

1. Confirm the target and maintenance window with the human before any
   maintenance vacuum; a manual `VACUUM` on a big table is a bounded mutation
   with a measurable cost.
2. Prefer `VACUUM (ANALYZE)` on the specific tables showing the problem over a
   blanket full-cluster pass.
3. `VACUUM FULL` rewrites the table and takes an exclusive lock; it is a
   last resort for severe bloat, never routine. It requires a maintenance
   window and a verified backup path.
4. After acting, re-run the `bloat`/dead-tuple probe and confirm the trend
   direction changed; also confirm autovacuum is keeping up afterwards.

## Config levers (all reload-safe)

- `autovacuum_max_workers`, `autovacuum_naptime`, `autovacuum_vacuum_cost_limit`
  — worker capacity and pacing.
- Per-table overrides via storage parameters (`autovacuum_vacuum_scale_factor`,
  `autovacuum_vacuum_threshold`) for tables that need faster or slower cycles.

## Hard boundaries

- Never run `VACUUM FULL` without a maintenance window and a confirmed backup.
- Never stop autovacuum to "save load" on a production instance — the deferred
  work comes back as bloat and wraparound risk.
- Never present a high `n_dead_tup` as a diagnosis by itself; it is evidence
  that the vacuum loop needs attention, and the cause (long transactions,
  worker starvation, pacing) must be identified before acting.

# Backups: WAL Archiving and Point-in-Time Recovery

> **Last Updated:** 2026-08-03

This reference covers the operational side of backups for PostgreSQL: WAL
archiving readiness, base backups, and point-in-time recovery (PITR). The
cross-engine backup *strategy* (RPO/RTO targets, retention policy, off-site
copies) belongs to `data-engineering`; this skill owns the PostgreSQL
mechanics and their verification.

## The recovery model

A usable recovery point is a **base backup plus the WAL archive that follows
it**: restore the base, then replay archived WAL up to the target time or LSN.
Without continuous archiving you can only recover to the moment of the last
base backup.

## WAL archiving readiness

```sql
-- The three settings that gate archiving (wal_level change needs restart)
SELECT name, setting FROM pg_settings
WHERE name IN ('wal_level', 'archive_mode', 'archive_command', 'archive_timeout');

-- Archiver health: archived_count must grow, failed_count must stay flat
SELECT archived_count, failed_count, last_archived_wal, last_archived_time,
       last_failed_wal, last_failed_time
FROM pg_stat_archiver;
```

The `pgdiag` `wal_archive` check reports the archiver row directly.

Readiness checklist:

- `wal_level = replica` (or higher) and `archive_mode = on`.
- `archive_command` succeeds for every segment; it must be idempotent and
  return zero only on success (a failing command makes the server retry and
  log failures).
- `pg_stat_archiver.failed_count` is not climbing and `last_failed_wal` is
  stale. A rising `failed_count` means archiving is broken *right now*, and
  every segment since then widens the PITR gap.

## Taking a base backup

```bash
# Consistent base backup with WAL included (the supported path)
pg_basebackup -h primary -D /backup/base-2026-08-03 -X stream -c fast -P

# Label and record it
echo "backup of primary at $(date -Is)" > /backup/base-2026-08-03/BACKUP_LABEL
```

- Use `-X stream` (or `-X fetch`) so WAL segments produced during the backup
  are included — a base backup without its WAL is not restorable.
- Record the label and timestamp; a restore target is only as good as the
  backup's metadata.
- Test restore: on a scratch instance, restore the base, point
  `restore_command` at the archive, and verify the server reaches the expected
  recovery point. This is the only evidence that the backup works.

## Restoring to a point in time

1. Restore the base backup to the target data directory.
2. Set `restore_command` (how to fetch archived WAL) and a recovery target —
   `recovery_target_time` (e.g., `'2026-08-03 02:00:00'`),
   `recovery_target_lsn`, or `recovery_target_xid`.
3. Start the server; it replays WAL to the target and stops (or enters
   recovery if `recovery_target_action = promote` on 12+).
4. Verify the data at the boundary (the row/table the incident was about),
   then promote when confident.

Recovery-target kinds: time-based (the `pitr|point.in.time` family — a
wall-clock moment) or position-based (an LSN or timeline marker). Choose the
kind that matches how you know *when things went wrong*; a wall-clock target
is the common case.

## Verification boundaries

| Claim | Minimum evidence |
|---|---|
| Archiving is healthy | `archived_count` increasing and `failed_count` flat over a window |
| A backup exists | Labeled base backup directory with matching WAL |
| Backups support recovery | A full restore-to-time drill on a scratch instance, logged |
| PITR target is reachable | Server reaches the target and the expected rows exist |

## Hard boundaries

- Never claim recoverability from a backup log alone; only a restore drill is
  evidence.
- Never run a restore against the production data directory without an
  explicit human directive; a restore is a mutation.
- Never let `archive_command` return success on failure — silent archiving
  failure widens the recovery gap undetected.
- Never keep only base backups: without WAL there is no point-in-time
  recovery, only point-of-backup recovery.

# Replication and Failover

> **Last Updated:** 2026-08-03

This reference covers streaming replication setup, monitoring, and the
failover runbook. Replication topology design (sync vs async, quorum, cluster
managers) is an architecture decision; this skill owns the PostgreSQL
mechanics and their verification.

## Streaming replication model

A standby connects to the primary with a replication slot, receives WAL
segments as they are produced, and replays them. `wal_level = replica` (or
higher) and `max_wal_senders`/`max_replication_slots` must be sized for the
number of standbys.

Setup essentials:

- Create a physical replication slot per standby
  (`SELECT pg_create_physical_replication_slot('standby-1');`).
- Build the standby with `pg_basebackup -X stream` and configure
  `primary_conninfo` in `postgresql.conf` (12+) with `standby.signal`.
- Confirm the standby is actually streaming: it is `in_recovery` and appears
  in `pg_stat_replication` on the primary.

## Measuring replication health

```sql
-- On the primary: who is streaming and how far behind
SELECT application_name, state, sync_state, client_addr,
       sent_lsn, write_lsn, flush_lsn, replay_lsn, replay_lag
FROM pg_stat_replication;

-- On the standby: is it receiving and replaying?
SELECT pg_is_in_recovery(), pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();
```

The `pgdiag` `recovery` and `replication` checks report both views.

Health rules of thumb:

- `state = streaming` for every standby; anything else (`startup`,
  `catchup`, `backup`) is transitional or stuck.
- Lag should stay within the agreed bound. `replay_lag` measures the gap
  between the primary's current WAL and what the standby has replayed.
- A slot that is far behind but still `streaming` means the standby cannot
  keep up or the network is saturated — the slot's retained WAL grows on the
  primary until it does.

## Synchronous versus asynchronous

- Asynchronous (default): the primary commits without waiting; failover can
  lose the most recent commits.
- Synchronous (`synchronous_standby_names = 'standby-1'`): the primary waits
  for that standby's flush before acknowledging commits. Trade-off: commit
  latency for a durability guarantee.
- Choose deliberately and document the choice; the failover runbook must
  state what durability was promised.

## Failover runbook

A failover plan names: who promotes, how clients are redirected, what happens
to the old primary when it returns, and how the result is verified.

1. **Confirm the directive**: failover is a mutation; it requires an explicit
   human decision naming the target standby.
2. **Check lag and timeline first**: how much data is at risk, and has the
   standby been applying continuously? Promotion with a lagging standby is a
   deliberate data-loss decision, not an accident.
3. **Promote**: `pg_ctl promote` or `SELECT pg_promote();` on the chosen
   standby. With a cluster manager (Patroni, repmgr) use its switchover
   command instead of manual promotion.
4. **Redirect clients**: DNS, connection strings, or the pooler — verify a
   fresh connection lands on the new primary and writes succeed.
5. **Rejoin the old primary** as a standby with `pg_rewind` (it is now
   diverged from the new primary's timeline) — never let two primaries accept
   writes.

## Hard boundaries

- Never promote without a human directive and a stated rollback path.
- Never let two primaries run: the old primary must be fenced or rejoined
  before it can accept writes again.
- Never fail over to a standby with unknown lag and call it "the same data".
- Never disable `archive_mode`/WAL sending to "simplify" replication — the
  archive and the stream are both part of the recovery story.

# Extensions

> **Last Updated:** 2026-08-03

This reference covers inventorying, installing, and upgrading PostgreSQL
extensions, with attention to the upgrade and security gotchas that bite
operators.

## Inventory first

```sql
-- Installed extensions and versions
SELECT extname, extversion FROM pg_extension ORDER BY extname;

-- Available on this installation (name, default version, requires)
SELECT name, default_version, installed_version FROM pg_available_extensions
ORDER BY name;
```

The `pgdiag` `extensions` check reports the installed set.

## Installing an extension

- An extension install is a mutation: it changes the shared catalog and, for
  many extensions, the database schema and behavior. Confirm the target and
  rollback path before running `CREATE EXTENSION`.
- Some extensions must be loaded at server start via
  `shared_preload_libraries` (e.g., `pg_stat_statements`, `timescaledb`,
  `citus`) — a restart is required and a misconfigured library can prevent
  startup.
- Trusted extensions (`pg_available_extensions.trusted = true`) can be
  installed by non-superusers into their own databases; anything else needs
  superuser.
- Extension *choice* and lifecycle policy are infrastructure decisions;
  shared-library availability and packaging are platform concerns that route
  to `platform-engineering`.

## Operating notes for common extensions

| Extension | Operational notes |
|---|---|
| `pg_stat_statements` | Best aggregate query evidence; needs `shared_preload_libraries` + restart; `pg_stat_statements_reset()` to reset |
| `postgis` | Large library; major-version upgrades need a reinstall or upgrade script per database |
| `pgvector` | Indexes are not binary-compatible across major versions — rebuild after a major upgrade |
| `pgcrypto`, `uuid-ossp` | Stable and small; rarely the source of upgrade pain |
| `hstore`, `citext` | Plain catalog extensions; safe through `pg_upgrade` with the `--no-...` flags checked |

## Upgrading extensions

- `ALTER EXTENSION name UPDATE TO 'newversion';` upgrades an installed
  extension when the extension's script provides the path.
- A major PostgreSQL upgrade commonly requires new extension binaries: run
  `pg_upgrade` and then install/re-install the extension versions matching the
  new server, or upgrade each database's extension before decommissioning the
  old cluster.
- Check each extension's release notes for the target version span *before*
  the upgrade window; PostGIS and pgvector in particular publish explicit
  major-upgrade procedures.

## Hard boundaries

- Never install an extension into production without a human directive, a
  named rollback (drop or version pin), and a verified backup path.
- Never load an extension via `shared_preload_libraries` without a restart
  plan and a check that the library exists on disk.
- Never assume an extension survives a major upgrade; verify per extension.

# Upgrades

> **Last Updated:** 2026-08-03

This reference covers upgrading PostgreSQL safely, distinguishing minor
in-place upgrades from major-version upgrades, and the rollback decision at
each step.

## Minor versus major

- **Minor upgrades** (e.g., 16.4 → 16.5) are binary swaps: stop, replace
  binaries, start. No data-format change; a restart is the whole procedure.
- **Major upgrades** (e.g., 15 → 16) change the on-disk format. Options:
  `pg_upgrade` (in-place, fast, link or copy mode) or logical dump/restore
  (`pg_dump`/`pg_dumpall`). Both are mutations requiring downtime and a
  verified backup.

## Before any major upgrade

1. Read the release notes and the upgrade guide for **the full version span**
   — skipping intermediate major versions compounds risks and deprecations.
2. Check extensions (see `06-extensions.md`): which have new binaries, which
   need reinstall, which block the move.
3. Check `pg_upgrade` prerequisites: `--check` mode reports problems without
   changing anything — run it first.
4. Rehearse in a scratch environment with the real data shape, including
   representative table sizes and the workload's slowest queries.

## pg_upgrade flow

```bash
# Pre-flight only — no changes
/path/to/new/bin/pg_upgrade --old-bindir /path/to/old/bin \
  --new-bindir /path/to/new/bin --old-datadir /var/lib/pg15 \
  --new-datadir /var/lib/pg16 --check

# Real run (after stop-writes confirmation and a verified backup)
/path/to/new/bin/pg_upgrade --old-bindir /path/to/old/bin \
  --new-bindir /path/to/new/bin --old-datadir /var/lib/pg15 \
  --new-datadir /var/lib/pg16
```

- Stop writes on the old cluster first; the upgrade moves data between the
  two directories.
- `--link` mode is fast but makes the old cluster unusable until the new one
  works (shared inodes); `--copy` is slower but keeps the old cluster intact
  as a rollback path.
- After the upgrade, run `analyze` on the new cluster (fresh planner
  statistics are mandatory) and re-install/upgrade extensions per their notes.
- Verify at the application boundary — the workflow the database serves —
  before decommissioning the old cluster.

## Logical upgrade paths

- `pg_dumpall`/`pg_dump` + restore into a fresh cluster: universal, slow for
  large data, but the safest for unusual setups.
- Logical replication (publisher/subscriber) as a near-zero-downtime major
  upgrade: run the new major as a subscriber, catch up, switch. This is also a
  migration pattern whose methodology lives in `data-engineering`.

## Rollback decision

- With `--copy` mode (or dump/restore), rollback is "start the old cluster
  again" — but WAL divergence since the upgrade start means the old cluster's
  data reflects the stop point, not anything written to the new one.
- Decide the rollback trigger *before* the window: how much new-write loss is
  acceptable, and who calls the rollback.
- Never delete the old cluster or its backup until the new cluster has
  survived the verification boundary.

## Hard boundaries

- Never run a major upgrade without a human directive, a maintenance window, a
  verified backup, and a rehearsed run.
- Never skip `analyze` on the new cluster and then debug "slow queries" as if
  they were config problems.
- Never decommission the old cluster until the new one is verified at the
  application boundary.

# Diagnostics with Evidence

> **Last Updated:** 2026-08-03

This reference maps symptoms to probes and fixes for PostgreSQL incidents,
with the evidence discipline that separates a real diagnosis from a guess.

## Evidence order

Diagnose in this order — each layer is cheaper than the next and rules out
whole classes of cause:

1. **Identity and version** — what are we actually looking at (primary or
   standby, which version)?
2. **Configuration** — are the operator-critical settings what the workload
   assumes?
3. **Connections** — is the instance saturated, or is the app doing something
   odd?
4. **Indexes and plans** — is the query the problem, or the data shape?
5. **Vacuum/bloat** — is maintenance keeping up?
6. **WAL archiving** — is the recovery story intact?
7. **Replication** — is the standby current and is failover defensible?
8. **Extensions** — did a library or extension change break something?

The bundled `pgdiag` script collects layers 1–8 in one bounded JSON payload:

```bash
scripts/pgdiag --json
scripts/pgdiag --check connections --check wal_archive --json   # targeted
scripts/pgdiag --plan-for "SELECT ..." --json                    # layer 4 probe
```

## Symptom-to-probe table

| Symptom | First probes | Likely next step |
|---|---|---|
| Queries suddenly slow | `pgdiag --json`; `EXPLAIN (ANALYZE, BUFFERS)` on the slow statement | Check plan row estimates vs actual; check `n_dead_tup` trend and planner stats freshness |
| Connections rejected | `pgdiag` `connections`; `pg_stat_activity` state counts | Compare against `max_connections`; look for stuck/idle-in-transaction backends; pooler sizing is `backend-engineering` |
| Backups stop completing | `pgdiag` `wal_archive`; archive destination disk/network | `failed_count`/`last_failed_wal`; fix `archive_command` or storage |
| Standby falls behind | `pgdiag` `replication` + `recovery`; `pg_replication_slots` | Lag columns, slot retention, network saturation; consider sync config |
| Instance is slow overall | `pg_stat_database` I/O + `pg_stat_bgwriter` | Check `track_io_timing` is on; look for checkpoint storms, heavy seq scans |
| Autovacuum stuck | `pg_stat_progress_vacuum`; long-running transactions | Find the snapshot-pinning transaction; tune workers if genuinely starved |
| After an upgrade, "everything is slow" | Planner statistics; extension reinstall | Run `analyze`; verify extensions were upgraded per `06-extensions.md` |

## Evidence discipline

- **Measure before claiming.** "The query is slow" is a symptom; "the plan
  shows a seq scan with 1.5M rows read while the index has zero scans" is
  evidence.
- **Correlation is not cause.** A high `n_dead_tup` and a slow query in the
  same window are correlated, not necessarily causal. State what was measured,
  what changed, and what was verified.
- **Re-run after acting.** A fix is verified when the relevant probe returns
  the expected value, not when the symptom seems quieter.
- **Keep evidence bounded.** Summarize statistics rows and log excerpts;
  never dump full logs or connection strings with passwords into chat.
- **When to stop.** Stop after three non-converging diagnostic passes and
  report the evidence gathered so far, the hypotheses ruled out, and the
  remaining candidates — rather than escalating into unconfirmed changes.

## Log sources

- Server log (wherever `logging_collector` writes) for errors, checkpoints,
  and slow statements when `log_min_duration_statement` is set.
- `pg_stat_activity` for live state; `pg_stat_archiver` and
  `pg_stat_replication` for the recovery story.
- Never parse the full server log into a response; extract the bounded window
  relevant to the incident.

#!/usr/bin/env python3
"""Deterministic tests for the postgres/scripts/pgdiag diagnostic tool.

Runs the script as a subprocess so the tests exercise the real CLI surface
(--help, --json, --check selection, --plan-for, exit codes, JSON payloads).
No PostgreSQL server is required: a fake psql stub is written to a temp
directory at test time and pointed at with --psql. The stub also enforces
the read-only contract by failing any invocation that does not carry the
default_transaction_read_only session setting.
"""
import json
import os
import stat
import subprocess
import sys
import tempfile
import unittest
from pathlib import Path

ROOT = Path(__file__).resolve().parent.parent
SCRIPT = ROOT / "scripts" / "pgdiag"

STUB_TEMPLATE = """\
#!/usr/bin/env python3
import os
import sys

args = sys.argv[1:]
sqls = []
index = 0
while index < len(args):
    if args[index] == "-c":
        sqls.append(args[index + 1])
        index += 2
    else:
        index += 1

if not any("default_transaction_read_only" in item for item in sqls):
    print("read-only session setting missing", file=sys.stderr)
    sys.exit(1)

sql = sqls[-1]

if os.environ.get("PGDIAG_STUB_FAIL") == "1":
    print("injected psql failure", file=sys.stderr)
    sys.exit(1)

if "server_version" in sql:
    print("mydb|dba|16.4|160004|f|2026-07-01 08:00:00+00")
elif "pg_stat_archiver" in sql:
    print("42|0|00000001000000000000002A|2026-07-01 00:00:00+00||")
elif "application_name" in sql:
    print("standby-1|streaming|sync|10.0.0.5|0/2A000000|0/2A000000|0/2A000000|0/2A000000")
elif "pg_last_wal_receive_lsn" in sql:
    print("f|0/2A000000|0/2A000000")
elif "pg_extension" in sql:
    print("plpgsql|1.0")
elif "idx_scan DESC" in sql:
    print("public|orders|orders_pkey|12345|24680|12000")
elif "idx_scan = 0" in sql:
    print("public|events|events_created_at_idx|0")
elif "pg_index" in sql:
    pass
elif "seq_tup_read DESC" in sql:
    print("public|orders|5000|1500000|120")
elif "n_dead_tup > 0" in sql:
    print("public|orders|100000|5000|4.8")
elif "pg_stat_activity" in sql:
    print("active|12")
    print("idle|38")
elif "current_setting" in sql:
    print("max_connections|100")
    print("wal_level|replica")
    print("archive_mode|on")
elif "pg_database_size" in sql:
    print("mydb|128 MB")
elif "EXPLAIN" in sql:
    print('[{"Plan": {"Node Type": "Seq Scan", "Plan Rows": 1000}}]')
elif "current_database()" in sql:
    print("mydb|dba|16.4|160004|f|2026-07-01 08:00:00+00")
else:
    pass
"""


def write_stub(directory: Path) -> Path:
    stub = directory / "fake-psql"
    stub.write_text(STUB_TEMPLATE, encoding="utf-8")
    stub.chmod(stub.stat().st_mode | stat.S_IXUSR)
    return stub


def run_script(*args: str, fail: bool = False) -> subprocess.CompletedProcess:
    env = os.environ.copy()
    if fail:
        env["PGDIAG_STUB_FAIL"] = "1"
    with tempfile.TemporaryDirectory() as tmp:
        stub = write_stub(Path(tmp))
        proc = subprocess.run(
            [sys.executable, str(SCRIPT), "--psql", str(stub), *args],
            capture_output=True,
            text=True,
            env=env,
            timeout=30,
        )
    return proc


class HelpTests(unittest.TestCase):
    def test_help_exits_zero_without_cluster(self):
        proc = subprocess.run(
            [sys.executable, str(SCRIPT), "--help"],
            capture_output=True,
            text=True,
            timeout=30,
        )
        self.assertEqual(proc.returncode, 0)
        self.assertIn("--json", proc.stdout)
        self.assertIn("read-only", proc.stdout.lower())
        self.assertIn("default_transaction_read_only", proc.stdout)

    def test_version_flag(self):
        proc = subprocess.run(
            [sys.executable, str(SCRIPT), "--version"],
            capture_output=True,
            text=True,
            timeout=30,
        )
        self.assertEqual(proc.returncode, 0)
        self.assertIn("pgdiag", proc.stdout)


class JsonRunTests(unittest.TestCase):
    def test_full_json_run_is_parseable(self):
        proc = run_script("--json")
        self.assertEqual(proc.returncode, 0, proc.stderr)
        payload = json.loads(proc.stdout)
        self.assertTrue(payload["ok"])
        self.assertEqual(payload["server"]["version"], "16.4")
        self.assertEqual(payload["server"]["database"], "mydb")
        names = [check["name"] for check in payload["checks"]]
        self.assertIn("identity", names)
        self.assertIn("config", names)
        self.assertIn("index_usage", names)
        self.assertIn("bloat", names)
        self.assertIn("wal_archive", names)
        self.assertIn("replication", names)
        self.assertIn("extensions", names)
        self.assertIn("databases", names)
        for check in payload["checks"]:
            self.assertEqual(check["status"], "ok", check["name"])

    def test_text_output_mentions_server_and_checks(self):
        proc = run_script()
        self.assertEqual(proc.returncode, 0, proc.stderr)
        self.assertIn("Server identity and version", proc.stdout)
        self.assertIn("WAL archiving health", proc.stdout)

    def test_check_subset_selection(self):
        proc = run_script("--check", "identity", "--check", "config", "--json")
        self.assertEqual(proc.returncode, 0, proc.stderr)
        payload = json.loads(proc.stdout)
        self.assertEqual([check["name"] for check in payload["checks"]], ["identity", "config"])

    def test_unknown_check_is_usage_error(self):
        proc = run_script("--check", "bogus", "--json")
        self.assertEqual(proc.returncode, 2)
        self.assertIn("unknown check", proc.stderr)

    def test_psql_error_recorded_per_check_but_run_continues(self):
        proc = run_script("--json", fail=True)
        self.assertEqual(proc.returncode, 1)
        payload = json.loads(proc.stdout)
        self.assertFalse(payload["ok"])
        names = [check["name"] for check in payload["checks"]]
        self.assertEqual(len(names), len(set(names)))
        self.assertTrue(all(check["status"] == "error" for check in payload["checks"]))


class BinaryAvailabilityTests(unittest.TestCase):
    def test_missing_psql_returns_127_with_json_error(self):
        proc = subprocess.run(
            [sys.executable, str(SCRIPT), "--psql", "/nonexistent/psql", "--json"],
            capture_output=True,
            text=True,
            timeout=30,
        )
        self.assertEqual(proc.returncode, 127)
        payload = json.loads(proc.stdout)
        self.assertFalse(payload["ok"])
        self.assertIn("psql binary not found", payload["error"])


class PlanForTests(unittest.TestCase):
    def test_plan_for_single_select_included(self):
        proc = run_script("--plan-for", "SELECT 1", "--json")
        self.assertEqual(proc.returncode, 0, proc.stderr)
        payload = json.loads(proc.stdout)
        plan_check = payload["checks"][-1]
        self.assertEqual(plan_check["name"], "plan")
        self.assertEqual(plan_check["status"], "ok")
        self.assertIsInstance(plan_check["plan"], list)

    def test_plan_for_rejects_write_statement(self):
        proc = run_script("--plan-for", "INSERT INTO t VALUES (1)", "--json")
        self.assertEqual(proc.returncode, 2)
        self.assertIn("read-only statement", proc.stdout)

    def test_plan_for_rejects_multiple_statements(self):
        proc = run_script("--plan-for", "SELECT 1; SELECT 2", "--json")
        self.assertEqual(proc.returncode, 2)


if __name__ == "__main__":
    unittest.main()