DevOps Databases
Agent Workflow (MANDATORY)
Before ANY change on a database host or cluster, spawn in parallel, one Agent call each with a name:
- fuse-ai-pilot:explore-codebase - find existing configs, migrations, operator manifests, backup jobs
- fuse-ai-pilot:research-expert - verify the parameter or command for the server's major version at postgresql.org/docs, pgbackrest.org, patroni.readthedocs.io, cloudnative-pg.io
- mcp__context7__query-docs -
/websites/postgresql,/pgbackrest/pgbackrest,/patroni/patroni,/pgbouncer/pgbouncer,/cloudnative-pg/cloudnative-pg
Then read the target read-only (SELECT version(), SHOW ALL, backup info, replication state) BEFORE proposing anything. After writing files, run fuse-ai-pilot:sniper.
Versions (checked 2026-11-10)
PostgreSQL supports a major version for 5 years; 18.6, 17.11, 16.15, 15.19 are supported and 14.24 gets its final release on 2026-11-12 - treat 14 as end of life this week; 13 and older are unsupported; 19 is at RC1 (https://www.postgresql.org/support/versioning/). pgBackRest 2.60.0 (2026-10-18), Patroni 4.1.5 (2026-08-12), PgBouncer 1.26.1 (2026-10-14, fixes three CVEs: run 1.26.1 or later), CloudNativePG 1.30.1 (1.30.x supports Kubernetes 1.34-1.36 and PostgreSQL 14-18). Details and sources inside each reference.
Overview
| Area | Decision in one line |
|---|---|
| Version | New builds on the newest supported major with the current minor; plan a move off any major within 12 months of its final release |
| Pooling | PgBouncer in transaction mode with max_prepared_statements > 0; session features break in that mode |
| Backup | Physical (pgBackRest or CNPG Barman Cloud plugin) + WAL archive for PITR; pg_dump is a complement, never the only copy; a backup counts only after a restore test |
| HA | Patroni + a 3-node DCS (etcd) on VMs; CloudNativePG on Kubernetes; failover automatic, switchover planned |
| Upgrade | pg_upgrade --check first; logical replication route for near-zero downtime; take a new base backup after |
| Maintenance | Keep autovacuum on; watch age(datfrozenxid); never VACUUM FULL on a live table without a window |
| Migrations | lock_timeout on every DDL, CREATE INDEX CONCURRENTLY, NOT VALID then VALIDATE |
| Other engines | MySQL/MariaDB: GTID replication, official dump/backup tools; Redis/Valkey: persistence is a choice, Sentinel for HA |
Critical Rules
- Version first -
SELECT version();before any parameter or command; parameters appear and disappear between majors - Backup proof before any risky step - a listed backup is not a proof; the proof is a completed restore test (restore-test-pitr-runbook.md)
- Destructive actions are proposed, not run - ACTION / EFFECT / PROOF / WAIT-FOR-GO, naming what is unrecoverable (destructive-actions.md)
- Read-only first - discovery queries and
info/statuscommands; the agent never runs a statement against a real database that it has not shown - No credential on a command line or in a file -
CHANGE_MEplaceholders in templates; real secrets fromdevops-secrets,PGPASSFILEor.pgpass - One writer - never promote or restore while another node may still accept writes (split brain)
- Never invent a parameter, flag or CRD field - cite the doc page; unknown = "not verified"
Reference Guide
Concepts
| Topic | Reference | When to Consult |
|---|---|---|
| Versions and config | postgres-versions-config.md | Install, postgresql.conf memory/WAL/checkpoints |
| Roles and auth | postgres-roles-auth.md | pg_hba.conf, SCRAM, least-privilege roles |
| Pooling | connection-pooling-pgbouncer.md | Too many connections, transaction mode pitfalls |
| Backup and PITR | backup-pitr-pgbackrest.md | pgBackRest stanza, WAL archive, restore to a point in time |
| Dump and base backup | backup-logical-physical.md | pg_dump, pg_basebackup, incremental, Barman pointer |
| Replication | replication-streaming-logical.md | Replicas, slots, lag, logical replication |
| High availability | ha-patroni.md | Patroni, etcd, failover vs switchover, split brain |
| Major upgrades | major-upgrades.md | pg_upgrade, logical route, extensions |
| Vacuum | vacuum-bloat-wraparound.md | Bloat, autovacuum tuning, XID wraparound |
| Monitoring | monitoring-queries.md | Activity, locks, slow queries, lag |
| Safe migrations | schema-changes-safe.md | DDL on a busy table |
| Kubernetes | postgres-on-kubernetes-cnpg.md | CloudNativePG cluster, backup, upgrade, alternatives |
| Destructive catalogue | destructive-actions.md | Any irreversible command |
| MySQL / MariaDB | mysql-mariadb-ops.md | Backup, GTID replication, divergence |
| Redis / Valkey | redis-valkey-ops.md | Persistence, Sentinel vs Cluster, eviction |
| MongoDB | mongodb-pointers.md | One-line pointers only |
Templates
| Template | When to Use |
|---|---|
| postgresql-tuned.conf | Starting parameters with the reasoning per line |
| pg_hba.conf.example | SCRAM, TLS, narrow CIDRs, replication entries |
| pgbouncer.ini | Transaction pooling with prepared statements |
| pgbackrest.conf | Repository, retention, archive settings |
| pgbackrest-commands.md | Backup, info, verify, restore command sheet |
| patroni.yml | One Patroni node with etcd3 |
| cnpg-cluster.yaml | CloudNativePG Cluster, ObjectStore, Pooler |
| cnpg-scheduledbackup.yaml | Scheduled base backups via the plugin |
| monitoring-queries.sql | Read-only health queries |
| lock-safe-migration-checklist.md | Before any production DDL |
| restore-test-pitr-runbook.md | Proving a backup and a PITR |
Quick Reference
psql "host=db1 dbname=postgres user=ops" -Atc "select version(), pg_is_in_recovery()" # password from PGPASSFILE
pgbackrest --stanza=main info # backups and WAL range, read-only
patronictl -c /etc/patroni/patroni.yml list # topology and lag, read-only
kubectl --context "$CTX" -n "$NS" get cluster.postgresql.cnpg.io # CNPG status, read-only
Best Practices
DO
- Keep WAL archiving on in production and alert on
pg_stat_archiver.failed_count - Test restores on a schedule and record the date, the target time and the row count compared
- Run PgBouncer, backups and monitoring with their own least-privilege roles
- Put retention,
max_slot_wal_keep_sizeand disk alerts in place before a replica or slot is added
DON'T
- Use
pg_dumpalone as disaster recovery, or run a restore over a live instance - Delete a replication slot, a Cluster or a PVC to "clean up" without the proof in destructive-actions.md
- Edit
postgresql.confon a Patroni or CloudNativePG member by hand: the manager owns it
