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.

Extensions

• Inventory first: pg_extension (installed) and pg_available_extensions (available) tell you what exists and what versions are on disk; pgdiag's extensions check does this.
• Extension installs change the shared catalog and some extensions change the database in ways that are hard to reverse — an install is a mutation with a rollback path, not a CREATE EXTENSION reflex.
• Major-version upgrades usually require re-installing or re-building extensions (e.g., PostGIS, pgvector) on the new binaries; check each extension's upgrade notes before pg_upgrade.
• Trusted extensions can be installed by non-superusers into their own databases; extension policy and shared-library availability are infrastructure decisions for platform-engineering.
• Common extensions, lifecycle, and version-upgrade gotchas: references/06-extensions.md.

Upgrades

• Minor upgrades are in-place binary swaps (restart); major upgrades (e.g., 15 → 16) change on-disk format and need pg_upgrade or a logical dump/restore.
• Plan the path first: read the release notes and upgrade guide for the full version span, check extensions and unsupported features, pick the method (pg_upgrade with link mode, or logical), and rehearse in a scratch environment with the real data shape.
• pg_upgrade is a mutation requiring downtime and a verified backup: stop writes, run the upgrade with the --old/--new binaries, run analyze on the new cluster, and verify at the application boundary before decommissioning the old.
• Logical replication (publisher/subscriber) can serve as a near-zero-downtime major-upgrade path; it is also a migration pattern whose methodology lives in data-engineering.
• Version matrices, upgrade runbooks, and rollback decisions: references/07-upgrades.md.

Diagnostics with evidence

Diagnose in evidence order: identity/version → configuration → connections → index usage and plans → vacuum/bloat → WAL archiving → replication → extensions.

• pgdiag --json gathers the first evidence layer in one bounded payload; re-run the affected check after any change.
• Slow query → EXPLAIN (ANALYZE, BUFFERS) plus pg_stat_user_indexes/seq_tup_read; check planner statistics freshness before touching random_page_cost.
• Backup stalled → pg_stat_archiver: failed_count, last_failed_wal, and the archive target's disk/network.
• Standby falling behind → pg_stat_replication lag columns, slot retention (pg_replication_slots), and network saturation between primary and standby.
• Never present correlation as cause: a slow query and a high n_dead_tup are evidence, not a diagnosis — state what was measured, what changed, and what was verified.
• Failure-mode routing and symptom→probe→fix tables: references/08-diagnostics.md.

Reference routing

Included artifacts

• scripts/pgdiag: read-only diagnostic collector (stdlib-only, --json, --check, --plan-for, --help without a cluster).
• tests/test_pgdiag.py: deterministic tests against a fake psql stub, including the read-only contract.
• references/: nine dated, source-indexed references covering the operational topics above.

Verification boundary

Hard boundaries

• Never run a mutation (pg_ctl stop, promote, pg_upgrade, extension install, maintenance VACUUM) without an explicit human directive naming the target and a stated rollback path. Read-only discovery may proceed freely.
• Never present unverified claims as evidence: state what was measured, when, and how.
• Never expose full logs, postgresql.conf contents, or connection strings containing passwords.
• Never run pgdiag with a write-capable session; the tool itself is read-only by design.

When not to use

• Application-level data access patterns (connection pooling in app code, ORM usage, query construction, transactions in services) — that is backend-engineering.
• Schema design and data modeling (tables, keys, normalization, dimensional models) — that is data-architect and data-engineering.
• Database methodology across engines (backup strategy, migration patterns, analytical SQL) — that is data-engineering.
• Supabase platform administration (managed projects, CLI stack, the self-hosted Supabase stack) — that is supabase; plain PostgreSQL operations without Supabase conventions belong here. To measure an agent's Supabase task competence, use the skill's agent evals harness reference.
• Other database engines (Redis, MongoDB, Elasticsearch, vector stores) — those stay in data-engineering references; this skill owns PostgreSQL only.