PostgreSQL in Production: The Backup, Replication, and Failover Setup We Deploy for Every Client

We've onboarded dozens of engineering teams to fractional DevOps engagements. In almost every case, the PostgreSQL setup looks the same: a single primary instance, automated snapshots that have never been restored, no replica, and a failover plan documented in a Notion page nobody has tested.

The backups exist. They just don't work — or more precisely, nobody has confirmed they work. That's not hypothetical risk; we've seen it materialize three times across client engagements. A backup that has never been restored is a backup whose restore you're practicing for the first time during an incident.

Here's the standard database reliability setup we deploy when we start a new engagement. It's not overengineered for scale. It's the minimum configuration for a production database that you can trust.

Part 1: Backups that are verified, not just scheduled

The pg_dump baseline

For most companies under 500GB, logical backups via pg_dump are the right starting point. They're portable, human-readable, and restorable into any PostgreSQL instance without matching block-level compatibility. We schedule them as a Kubernetes CronJob (or cron on a dedicated instance) running daily, producing gzipped plain SQL:

pg_dump -U postgres --clean --if-exists \
  -h $PGHOST $PGDATABASE | gzip \
  > backup-$(date +%Y-%m-%d).sql.gz

The --clean and --if-exists flags mean the dump drops and recreates every object during restore — no partial-state edge cases, no "table already exists" errors when restoring into a non-empty database.

WAL archiving for point-in-time recovery

Daily backups give you a 24-hour recovery window at best. If the database corrupts at 11pm and your backup ran at midnight, you lose a full day of data. For any database with meaningful write volume, you want WAL archiving: PostgreSQL's write-ahead log shipped continuously to object storage, giving you point-in-time recovery (PITR) to any moment in the retention window.

We configure WAL-G for this — it handles WAL shipping to S3 (or GCS), runs base backups, and handles the restore procedure. The configuration is four lines in postgresql.conf:

archive_mode = on
archive_command = 'wal-g wal-push %p'
archive_timeout = 60
wal_level = replica

With this in place, your recovery point objective (RPO) drops from 24 hours to roughly 60 seconds — the maximum lag before an unarchived WAL segment is forced out. For most startups, this is the highest-leverage database reliability improvement available, and it costs almost nothing (WAL-G storage is typically under $20/month for databases under 100GB).

The weekly restore test

This is the part teams skip. We run a weekly automated restore into a throwaway instance — restore the most recent backup, run a sanity-check query, assert the row count is within 5% of production. The test runs as a CronJob, and if it fails, it pages the on-call engineer. This is not optional. A backup that can't be restored is not a backup.

The restore test also serves as a forcing function for keeping restore documentation current. If the procedure changed and the test still passes, the docs are right. If the procedure changed and the test fails, you learn that in a controlled context — not during an incident at 3am.

Part 2: Streaming replication for read offload and failover

The read replica

A streaming replica serves two purposes: it offloads read queries from the primary (relevant once your application is read-heavy), and it's the fastest failover path when the primary fails. Setting up streaming replication is straightforward in modern PostgreSQL — the replica connects to the primary, streams WAL records continuously, and stays within seconds of the primary's state.

For Kubernetes deployments, we use CloudNativePG (the CNCF-graduated PostgreSQL operator). It handles replica provisioning, connection routing, and failover within the cluster. For non-Kubernetes setups, native PostgreSQL streaming replication with a primary_conninfo in recovery.conf (or standby.signal in Postgres 12+) works the same way.

The replica runs in a separate availability zone from the primary. This isn't belt-and-suspenders paranoia — it's the difference between a replica that fails with the primary (same AZ outage) and one that's available when you need it most.

Monitoring replication lag

A replica that's 4 hours behind is not a useful failover target. We instrument replication lag as a Prometheus metric:

SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))
  AS replication_lag_seconds;

Alert thresholds: warn at 30 seconds, page at 5 minutes. If the replica is falling behind consistently, it's either under-resourced or the WAL volume exceeds what it can apply — both are fixable, but you need to know.

Part 3: Failover that has been tested

Automatic vs. manual failover

Automatic failover sounds better than it is. A database that promotes its replica automatically on primary failure also promotes it automatically on false-positive failure detection — network partition, monitoring misconfiguration, a flapping health check. The result is a split-brain scenario where both the old primary and the promoted replica accept writes, and you spend the next day resolving data divergence.

For most clients we work with, we configure assisted manual failover: the monitoring system pages the on-call engineer when the primary goes unhealthy, the engineer confirms it's a real failure, and promotion is a single command. The extra 2–3 minutes to confirm before promoting is worth the protection against split-brain. The only exception is setups using CloudNativePG or Patroni, which have fencing mechanisms that prevent split-brain — in those cases, automatic failover is safe.

The failover runbook

Every team needs a failover runbook, and it needs to be tested at least once per quarter. The test is not a tabletop exercise — it's an actual promotion during a maintenance window. You shut the primary down, verify the replica takes over, verify the application reconnects, verify no data was lost, and write up what took longer than expected.

The runbook we leave with clients covers five steps:

1. Confirm the primary is genuinely unreachable (not a monitoring false positive)
2. Check replica lag — do not promote if the replica is more than 30 seconds behind without understanding why
3. Issue the promotion command (SELECT pg_promote(); or the operator's equivalent)
4. Update the application's database connection string (or confirm the connection pool has failed over to the read endpoint)
5. Provision a new replica from the promoted primary so you're not running single-point-of-failure post-failover

Step 4 is often where clients discover a gap: the application is hardcoded to a DNS name for the primary, and there's no automatic way to redirect traffic to the promoted replica. The fix is a PgBouncer connection pooler (or your cloud provider's RDS Proxy equivalent) sitting in front of the database with a DNS entry that's updated during failover — the application connects to the pooler, the pooler connects to wherever the current primary is.

Connection pooling

No database reliability discussion is complete without connection pooling, because the most common production database problem we see is connection exhaustion — not primary failure. PostgreSQL forks a process per connection. At a few hundred concurrent connections, connection overhead consumes meaningful memory and CPU. At a few thousand, the database falls over.

PgBouncer in transaction pooling mode sits between the application and PostgreSQL, multiplexing hundreds of application connections onto a configured maximum of 20–50 actual Postgres connections. The application sees no difference; the database sees a stable, bounded number of clients. We set max_client_conn at 500, default_pool_size at 25 per database, and alert if the pooler's wait queue exceeds 10 connections. That alert fires before any request fails.

What the full setup looks like

• Daily logical backup: pg_dump --clean → gzipped → S3, retained 60 days
• Continuous WAL archiving: WAL-G shipping to S3, enabling PITR to any point in the retention window
• Weekly automated restore test: CronJob restoring the latest backup into a throwaway instance, verifying row counts, alerting on failure
• Streaming replica: separate AZ, replication lag monitored and alerted
• Connection pooler: PgBouncer in transaction mode, sitting in front of both primary and replica
• Failover runbook: written, tested quarterly, stored in Git
• Metrics: replication lag, connection pool wait queue, query latency percentiles, connections per database — all in Prometheus

None of this is exotic. All of it is available in open-source tools. The reason teams don't have it isn't technical complexity — it's that setting it up properly takes focused time that most engineering teams don't have when they're shipping product. That's exactly where a fractional DevOps engagement pays for itself: one week of focused infrastructure work that runs reliably for years.

"We'd been meaning to sort out the database backup situation for eight months. It was the thing everyone knew was a problem and nobody had time to fix properly. Two weeks in, it was done, tested, and off everyone's worry list."

A note on managed databases

If you're on AWS RDS or Aurora, many of these pieces are provided by the managed service — automated backups, Multi-AZ standby, and a tested failover mechanism (for Aurora) you don't have to build yourself. The managed service is a reasonable choice if you're not already on Kubernetes or don't want to own the operational surface. If you are running PostgreSQL on Kubernetes (or on bare metal), the setup above is what you need to reach equivalent reliability. We've deployed it across both environments and the operational burden after initial setup is low — the monitoring and the weekly restore test do the work.