/devops-databases

name: devops-databases
description: Use when operating (not designing) databases - PostgreSQL installation and tuning (postgresql.conf, pg_hba.conf, roles), PgBouncer pooling, backups and point-in-time recovery (pg_dump, pg_basebackup, pgBackRest, restore tests), streaming and logical replication, Patroni high availability and failover/switchover, major version upgrades (pg_upgrade, logical route), vacuum/bloat/transaction ID wraparound, monitoring queries, lock-safe schema changes in production, PostgreSQL on Kubernetes with CloudNativePG; plus MySQL/MariaDB (GTID replication, backup tools), Redis/Valkey (RDB/AOF, Sentinel vs Cluster) and MongoDB pointers. Not for application schema or ORM modelling (framework experts), generic backup/storage strategy (devops-storage-backup), exporters and dashboards (devops-observability), secret stores (devops-secrets) or managed cloud databases.
versions:
  postgresql: "18.6 / 17.11 / 16.15 / 15.19 supported; 14.24 final release 2026-11-12; 19 RC1"
  pgbackrest: "2.60.0"
  patroni: "4.1.5"
  pgbouncer: "1.26.1"
  cloudnativepg: "1.30.1"
  mysql: "8.4 LTS, 9.7 LTS, 26.7 / 26.10 innovation"
  mariadb: "12.3 LTS, 13.0 rolling"
  redis: "8.10 (8.12 RC2)"
  valkey: "9.1.3 / 9.0.7 / 8.1.11 / 8.0.12 / 7.2.15"
user-invocable: true
references:
  - references/postgres-versions-config.md
  - references/postgres-roles-auth.md
  - references/connection-pooling-pgbouncer.md
  - references/backup-pitr-pgbackrest.md
  - references/backup-logical-physical.md
  - references/replication-streaming-logical.md
  - references/ha-patroni.md
  - references/major-upgrades.md
  - references/vacuum-bloat-wraparound.md
  - references/monitoring-queries.md
  - references/schema-changes-safe.md
  - references/postgres-on-kubernetes-cnpg.md
  - references/destructive-actions.md
  - references/mysql-mariadb-ops.md
  - references/redis-valkey-ops.md
  - references/mongodb-pointers.md
  - references/templates/postgresql-tuned.conf
  - references/templates/pg_hba.conf.example
  - references/templates/pgbouncer.ini
  - references/templates/pgbackrest.conf
  - references/templates/pgbackrest-commands.md
  - references/templates/patroni.yml
  - references/templates/cnpg-cluster.yaml
  - references/templates/cnpg-scheduledbackup.yaml
  - references/templates/monitoring-queries.sql
  - references/templates/lock-safe-migration-checklist.md
  - references/templates/restore-test-pitr-runbook.md
related-skills:
  - devops-storage-backup
  - devops-observability
  - devops-kubernetes
  - devops-k8s-cluster
  - devops-secrets
  - devops-linux
  - devops-networking
  - devops-sre-ops

<objective> Covers running databases in production as an operator: PostgreSQL in depth (config, auth, pooling, backup and PITR, replication, Patroni HA, upgrades, vacuum, monitoring, safe migrations, CloudNativePG), then MySQL/MariaDB and Redis/Valkey essentials and MongoDB pointers. Does not cover application schema or ORMs (framework experts), generic backup and storage design (devops-storage-backup), exporters and dashboards (devops-observability) or managed cloud databases. </objective>

DevOps Databases

Agent Workflow (MANDATORY)

Before ANY change on a database host or cluster, spawn in parallel, one `Agent` call each with a `name`:

1. **fuse-ai-pilot:explore-codebase** - find existing configs, migrations, operator manifests, backup jobs 2. **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 3. **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

1. **Version first** - `SELECT version();` before any parameter or command; parameters appear and disappear between majors 2. **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](references/templates/restore-test-pitr-runbook.md)) 3. **Destructive actions are proposed, not r