PostgreSQL Operational Runbook: Backup, Recovery, and High Availability

The Stakes of PostgreSQL Backup

PostgreSQL is where enterprise data lives. For regulated industries, the database is the PHI, the cardholder data, the financial ledger. For SaaS products, it is the user data. Backup and recovery is not an operational nicety — it is a compliance requirement under HIPAA, PCI DSS, SOC 2, and ISO 27001, and a business continuity requirement under every industry's operational standards. And yet, production PostgreSQL backup procedures are among the most frequently untested components in enterprise infrastructure.

This runbook covers the backup and high availability architecture that enterprise PostgreSQL deployments require, and — critically — how to validate that it actually works before you need to rely on it.

Backup Methods

pg_basebackup: The Foundation

pg_basebackup takes a consistent base backup of a running PostgreSQL cluster. It creates a copy of the data directory (including all tablespaces) that, combined with WAL files, can be used to restore the cluster to any point in time after the backup. Key parameters:

• --format=tar --gzip: produces compressed tar archives suitable for remote storage
• --wal-method=stream: streams WAL files during the backup, ensuring the backup is self-contained
• --checkpoint=fast: triggers a fast checkpoint at backup start, reducing the time before the backup is consistent

pg_basebackup should be run from a replica, not the primary, to avoid adding backup load to the production primary.

WAL Archiving: Continuous Backup

Write-Ahead Log (WAL) archiving, combined with base backups, enables point-in-time recovery (PITR). PostgreSQL generates WAL files continuously; when archiving is enabled (archive_mode=on, archive_command configured), each completed WAL segment is copied to the archive location. Tools like pgBackRest and Barman automate this process, adding compression, encryption, and S3/Azure/GCS upload to the archive pipeline.

PITR allows recovery to any point after the base backup, down to the transaction level. For regulatory environments with strict data retention requirements, PITR provides evidence of exactly what the database contained at any point in time.

pgBackRest: Enterprise Backup Tooling

pgBackRest is the community standard for enterprise PostgreSQL backup. It provides:

• Incremental and differential backups that only copy changed blocks since the last backup, dramatically reducing backup time and storage for large databases.
• Parallel backup and restore using multiple threads and processes.
• Native S3, Azure Blob, and GCS support for backup destination.
• Backup encryption using AES-256-CBC.
• Delta restore: only restores changed files from the backup, useful for restoring a replica that is slightly behind the backup.

High Availability: Streaming Replication

PostgreSQL streaming replication creates one or more standby servers that receive WAL data from the primary in real time. In the event of a primary failure, one standby is promoted to primary. Key configuration decisions:

• Synchronous vs asynchronous replication: Synchronous replication (synchronous_commit=on) guarantees no data loss at the cost of slightly increased write latency. Asynchronous replication has lower latency but a small risk of data loss (the last few WAL records) on primary failure. For financial data and ePHI, synchronous replication is standard.
• Number of standbys: For high availability, a minimum of one synchronous standby plus one asynchronous standby. The synchronous standby provides zero-RPO failover; the asynchronous standby provides an additional replica for read traffic and a backup failover target.
• Failover automation: Tools like Patroni (most widely used in enterprise), Stolon, and repmgr automate failover detection and promotion. Patroni uses etcd, Consul, or ZooKeeper for distributed consensus on who is the current primary, preventing split-brain.

Recovery Testing: The Unchecked Box

The most common gap in enterprise PostgreSQL backup programs is tested recovery. A backup that has never been restored to a working database is a backup of unknown quality. Recovery testing should be:

• Scheduled quarterly: Restore the most recent base backup plus WAL to a recovery environment and validate that the database is consistent and complete (pg_dumpall comparison, row count validation on critical tables, application smoke tests).
• PITR validation: Test point-in-time recovery to a specific transaction ID or timestamp to validate that WAL archiving is functioning correctly and the archive is complete.
• Failover drill: In a non-production environment, simulate a primary failure and validate automatic failover to the standby. Measure time to failover against the RTO objective. Validate that applications reconnect correctly after the failover.

RTO and RPO Targets

Define explicit Recovery Time Objective (RTO) and Recovery Point Objective (RPO) targets for each PostgreSQL cluster, and validate that your backup and HA configuration can meet them:

• RPO = 0: Requires synchronous streaming replication. Any primary failure that the standby can take over produces zero data loss.
• RPO = minutes: Asynchronous replication with WAL archiving every 1–5 minutes.
• RTO = minutes: Requires automated failover (Patroni) and applications that reconnect automatically after primary change.
• RTO = hours: Manual failover is acceptable; restore from backup plus WAL replay is the recovery path.

OSSeva PostgreSQL Operations

OSSeva Operate for PostgreSQL includes pgBackRest configuration and monitoring, streaming replication health monitoring, automated failover validation via quarterly drills, and incident response coverage for primary failures. Recovery runbooks are authored for your specific topology before they are needed.

Conclusion

PostgreSQL backup and high availability are solved engineering problems. The tools are mature, the procedures are well-documented, and the failure modes are understood. What distinguishes high-performing teams is the discipline to implement the full stack — base backups, WAL archiving, streaming replication, automated failover, and recovery testing — before an incident reveals which part was missing.