postgres-ops

PostgreSQL operations reference: replication setup, autovacuum tuning with the math behind it, slow-query triage, backup/restore with pgBackRest, and a set of health-check SQL. These are playbooks and configs distilled from running PostgreSQL in production, generalized for reuse on PostgreSQL 14+.

Structure

.
├── docs/
│   ├── streaming-replication.md   # primary/standby setup, slots, lag monitoring, failover
│   ├── vacuum-tuning.md           # autovacuum math for high-churn tables, wraparound
│   └── slow-query-triage.md       # pg_stat_statements workflow + EXPLAIN checklist
├── backup/
│   ├── pgbackrest.conf            # full + diff + incr schedule, retention, S3, encryption
│   └── restore-runbook.md         # full restore, PITR, single-table, drill checklist
├── sql/
│   └── health_queries.sql         # cache hit, replication lag, locks, bloat, wraparound
├── LICENSE
└── README.md

Usage

• Setting up a standby — follow docs/streaming-replication.md end to end; it covers slots, sync-vs-async trade-offs, lag monitoring queries, and manual failover mechanics.
• Autovacuum falling behind — docs/vacuum-tuning.md walks through the trigger math and the throughput budget, so you tune the right knob (trigger vs cost limit vs xmin horizon).
• Slow queries — docs/slow-query-triage.md is the workflow: rank with pg_stat_statements, explain with real parameters, work the checklist.
• Backups — adapt backup/pgbackrest.conf (bucket, retention, cipher pass via environment), wire the cron schedule from the header comment, and rehearse backup/restore-runbook.md before you need it.
• Health checks — run sections of sql/health_queries.sql ad hoc or lift them into your monitoring; all queries are read-only and need only the pg_monitor role.

# Quick health pass on any cluster:
psql -d mydb -f sql/health_queries.sql

Conventions

• Written against PostgreSQL 14–16; version-specific behavior is called out inline (e.g. max_slot_wal_keep_size, pg_stat_statements column names).
• Commands assume Linux + systemd with data in /var/lib/postgresql/16/main — adjust paths for your packaging.
• Secrets (cipher passphrases, replication passwords) never belong in these files in production; use environment variables or a secrets manager.

License

MIT — see LICENSE.