Postgres

PostgreSQL Operations

Use this skill to operate PostgreSQL safely as the database engine it is: review configuration against workload, find index and query-plan problems, manage vacuum and bloat, set up and verify WAL archiving and point-in-time recovery, run replication with a defensible failover plan, handle extensions, plan major-version upgrades, and diagnose incidents with evidence. This is a tool skill for one named tool (PostgreSQL). Database methodology — backup strategy across engines, migration patterns, SQL analytical patterns — lives in data-engineering; application-level data access patterns belong to backend-engineering; schema and data modeling belong to data-architect and data-engineering. Supabase platform administration is supabase.

Operating contract

1. Read-only discovery before any mutation. Inspect configuration, catalog statistics, and logs first. The bundled pgdiag script collects read-only evidence and opens every session with default_transaction_read_only=on.
2. Confirm the target, scope, and rollback path before acting. Read-only discovery may proceed without confirmation. Mutations — a pg_ctl stop, a promotion, an extension install, a pg_upgrade run — require an explicit human directive naming the instance.
3. A backup is not recovery evidence. Verify restore on a scratch instance on a schedule; never claim recoverability from a backup log alone.
4. Keep evidence bounded. Summarize catalog queries and log excerpts; never dump full logs, postgresql.conf, or connection strings with passwords into chat.
5. Verify at the delivery boundary. A SELECT 1 answer proves connectivity, not health; a replayed pg_basebackup proves recoverability, not that today's WAL is being archived.

The pgdiag script

scripts/pgdiag is an agent-first, read-only diagnostic collector. It shells out to psql, opens every session with default_transaction_read_only=on, and emits bounded JSON. --help works with no server and no psql installed.

scripts/pgdiag --help                      # no cluster needed
scripts/pgdiag --json                      # all checks, machine-readable
scripts/pgdiag --host db1 --dbname app --json
scripts/pgdiag --check identity --check wal_archive --json
scripts/pgdiag --plan-for "SELECT * FROM orders WHERE id = 42" --json

Exit codes: 0 ok, 1 runtime/collection error, 2 usage error, 127 psql binary not found, 124 timeout. --check runs a named subset; --plan-for adds an EXPLAIN (FORMAT JSON) plan for one read-only statement. The script never issues data-changing statements, and the server-side read-only session setting rejects any that slip through.

Operating loop

1. Identify the instance: version, recovery state, configuration file locations, connection string shape, and whether this is primary or standby.
2. Collect evidence: pgdiag --json for config, connections, index usage, bloat signals, WAL archiving, recovery, replication, extensions, and database sizes.
3. Triage against the symptom: map the reported problem to the evidence (slow queries → plans and index usage; stalled backups → archiver; drift → replication lag).
4. Act with confirmation: bounded, scoped mutations after a human directive, with a rollback path named first.
5. Verify: re-run the relevant check and confirm the observable at the delivery boundary.

Configuration

• The runtime source of truth is pg_settings, not the file: SHOW/current_setting() reflect reloads and overrides (ALTER SYSTEM, command-line -c, env PGOPTIONS). pgdiag's config check lists the operator-critical values.
• Know which changes need a reload (pg_ctl reload / SELECT pg_reload_conf()) versus a restart: memory (shared_buffers, max_connections, wal_level, max_wal_senders) requires restart; most tuning and logging parameters reload.
• Check log_destination, logging_collector, and log_min_duration_statement so slow-query evidence exists before you need it; track_io_timing=on makes pg_stat_database I/O timing meaningful.
• Connection pressure: compare pg_stat_activity state counts against max_connections; a connection pooler is an application-architecture decision for backend-engineering.
• GUC rationale, reload-versus-restart tables, and parameter-change review patterns: references/01-configuration.md.

Indexes and query plans

• Evidence first: pg_stat_user_indexes shows idx_scan/idx_tup_read/idx_tup_fetch; a table scanned sequentially with a large seq_tup_read while a filter exists is a candidate for a missing index.
• EXPLAIN (ANALYZE, BUFFERS) on the real workload query beats guessing; compare estimated to actual rows — a large mismatch points at stale planner statistics (a pg_statistic freshness problem) or a bad parameter (random_page_cost, effective_cache_size).
• Unused indexes (idx_scan = 0 over a long window) cost writes and maintenance; invalid indexes (pg_index.indisvalid = false) are dropped on next vacuum and should be repaired or removed deliberately.
• Index choices (BRIN vs btree, partial indexes, covering indexes) are schema design and belong to data-architect; this skill owns measuring and operating what exists.
• Query-plan reading, index-usage SQL probes, and plan-review checklists: references/02-indexes-and-query-plans.md.

Vacuum and bloat

• Vacuum reclaims dead tuples and refreshes planner statistics; autovacuum should do this on its own. Verify it is actually running: autovacuum=on, worker count, and per-table relfrozenxid/n_dead_tup trends.
• Bloat is the gap between table file size and live data: pg_stat_user_tables.n_dead_tup rising faster than vacuum runs is the leading signal; heap bloat from failed or skipped vacuum shows as large relpages with low live tuples.
• If autovacuum lags, the response is a targeted, confirmed maintenance window (VACUUM on specific tables, not a firehose), then a check of why autovacuum fell behind (long transactions, connection saturation, worker starvation).
• Never treat VACUUM FULL as routine: it rewrites the table, takes locks, and needs a maintenance window plus a verified backup path.
• Bloat measurement probes and autovacuum tuning patterns: references/03-vacuum-and-bloat.md.

Backups: WAL archiving and point-in-time recovery

• The recovery model: a base backup plus a continuous WAL archive gives point-in-time recovery (PITR) — restore the base, replay archived WAL up to the target time.
• Recovery targets come in two families: time-based (the pitr|point.in.time pattern is the shorthand for this family — a wall-clock target such as "yesterday 02:00") and position-based (a specific LSN or timeline marker). Both are valid recovery_target inputs.
• WAL archiving readiness is archive_mode=on with a working archive_command and a healthy archiver: pg_stat_archiver must show archived_count growing, failed_count stable, and last_failed_wal empty or old.
• wal_level must be replica (or higher) for both archiving and streaming replication; changing it requires a restart.
• Back up with pg_basebackup (or a dedicated tool) consistently with WAL: label each backup, record its pg_stop_backup() LSN / timeline, and test restore with the archive before trusting it.
• PITR procedure, recovery_target options, restore-to-point-in-time steps, and RPO/RTO framing (methodology in data-engineering): references/04-backups-wal-pitr.md.

Replication and failover

• Streaming replication: standby connects with a replication slot, receives WAL continuously; verify with pg_stat_replication (state=streaming, replay_lsn keeping up, small replay_lag) and the standby's recovery state.
• Decide synchronous vs asynchronous deliberately: synchronous (synchronous_standby_names) trades commit latency for a durability guarantee; asynchronous risks losing the last commits on failover.
• A failover plan is more than a pg_ctl promote: it names who promotes, how clients are redirected, what happens to the old primary on return, and how to verify data (lag at promotion, timeline divergence).
• Promotion is a mutation — confirm the target and scope first. With a replication-manager tool (Patroni, repmgr), use its switchover command instead of manual promotion; rejoin the old primary as a standby, never let two primaries write.
• Streaming setup, slot management, lag measurement, and failover runbooks: references/05-replication-and-failover.md.