PostgreSQL production stack and HA
Choose PgBouncer, pgBackRest, Patroni, CloudNativePG, and Pigsty from recovery, pooling, observability, and failure-domain requirements
A useful production order is: prove recovery → protect connection capacity → make failures observable → make changes safe → then automate failover. High availability does not replace backup, and a replica cannot undo a deletion already replicated to it.
As of 2026-08-02, PostgreSQL 18.4 is the current minor of the newest stable major, while majors 14–18 remain supported. Production systems should run the current minor of their chosen major; being newest does not by itself justify a major upgrade. See the PostgreSQL versioning policy and 18.4 release notes.
Minimum production baseline
| Layer | First question | Common choice |
|---|---|---|
| Database | How are minor updates, roles, TLS, settings, and extensions controlled? | Official PostgreSQL packages or a verified image |
| Connections | Can peak application concurrency exhaust backends? | Application pooling, then PgBouncer where needed |
| Recovery | What are the RPO/RTO, and can recovery happen outside the platform? | pgBackRest, WAL-G, or cloud backup plus an independent copy |
| Observability | Which query, wait event, metric, and log explains an incident? | pg_stat_statements, JSON logs, and metric collection |
| Changes | How are DDL locks, backfills, rollback, and compatibility windows tested? | Expand-and-contract, migration linting, and real PostgreSQL tests |
| Availability | What are the independent failure domains and post-failover data boundaries? | Managed HA, Patroni, CloudNativePG, or Pigsty |
PgBouncer: do not assume transaction pooling
PgBouncer multiplexes many client connections onto fewer PostgreSQL server connections. Repeatedly raising max_connections is not a capacity plan: every backend consumes memory and scheduling resources, and operations need reserved connections for migrations, monitoring, backup, administration, and replication.
| Mode | Server connection returns after | Boundary |
|---|---|---|
session | Client disconnect | Highest compatibility; use when applications depend on session state |
transaction | Transaction end | Common for Web/API traffic, but session features must be tested |
statement | Every statement | Disallows multi-statement transactions; only for tightly controlled workloads |
With transaction pooling, SQL-level PREPARE, session advisory locks, LISTEN, holdable cursors, and many session-state patterns are unsupported or constrained. Protocol-level prepared statements require an appropriate max_prepared_statements configuration and driver testing. Use the PgBouncer feature map as the compatibility source.
Before rollout, test authentication and TLS, prepared statements, temporary tables, migration tooling, connection reset, failover, transaction retries, and any ORM state that may be session-scoped.
pgBackRest, WAL-G, and cloud backup
pgBackRest supports full, differential, and incremental backups, parallel transfer, multiple repositories, WAL archiving, and PITR. It is a strong fit for a complete self-hosted recovery chain. WAL-G is oriented toward object-storage workflows. Neither proves recoverability merely by being installed.
Derive schedules from RPO, WAL volume, restore bandwidth, retention requirements, and measured RTO rather than copying a generic weekly/daily calendar. Continuously check archive gaps, deletion protection, encryption keys, cross-host or cross-account copies, and restore into a disposable instance.
pg_dump remains valuable for logical migration, object selection, and small restores, but cannot provide continuous point-in-time recovery on its own. See Backup, restore, and PITR.
Choosing Patroni, CloudNativePG, or Pigsty
| Environment | Candidate | Adoption requirement |
|---|---|---|
| Managed cloud database | Provider multi-zone HA | Verify region, failover, PITR, extensions, and off-platform restore limits |
| Independent VMs or bare metal | Patroni | Multiple failure domains, a reliable DCS, and network/storage operations skills |
| Existing Kubernetes platform | CloudNativePG | A team already capable of operating Kubernetes, storage, networking, and operator upgrades |
| Multi-cluster self-hosted platform | Pigsty | Acceptance of its Ansible/VM model and verification of integrated component upgrades |
For object-storage backup with CloudNativePG, evaluate the current Barman Cloud Plugin rather than copying deprecated in-tree object-store configuration. Do not introduce Kubernetes solely for one PostgreSQL instance.
Three containers on one host are not three failure domains
They share host power, kernel, storage, and networking failures. Automated election improves availability only when node, storage, and quorum boundaries are genuinely independent.
Adopt in phases
Production baseline
- Current minor, role separation, TLS, and controlled extensions;
- a connection budget and a tested PgBouncer deployment where required;
- independently retained backups, continuous WAL/PITR, and restore drills;
- query, metric, and log observability;
- pre-migration lock tests, timeouts, rollback, and business validation.
Conditional additions
- Add automatic failover only with independent failure domains and an explicit RTO;
- prefer CloudNativePG only when Kubernetes operations already exist;
- add Pigsty when managing multiple self-hosted clusters justifies a platform;
- install pgvector, PostGIS, TimescaleDB, or other extensions only for a measured workload.
Experimental lane
PostgreSQL 19 Beta, newer extensions, and storage engines belong in disposable compatibility environments, not in the production baseline. Keep separate CI lanes for the stable target and the next major; see Safe migrations and zero-downtime schema changes.
Last updated on