PostgreSQL disaster recovery: cross-region standby and pgBackRest point-in-time restore

#1. Two layers of DR

Use both. The standby gives a fast RTO. The backups cover deletes and corruption that replicate to the standby.

#2. Backups in both regions

# /etc/pgbackrest.conf
[global]
repo1-type=s3
repo1-s3-bucket=pg-backup-primary
repo1-s3-region=ap-south-1
repo1-s3-endpoint=s3.ap-south-1.amazonaws.com
repo1-retention-full=4
repo2-type=s3
repo2-s3-bucket=pg-backup-dr
repo2-s3-region=ap-south-2
repo2-s3-endpoint=s3.ap-south-2.amazonaws.com
repo2-retention-full=4
archive-async=y
process-max=4

[sales]
pg1-path=/var/lib/pgsql/17/data

WAL is pushed to both repositories. If the primary region's bucket is lost, the DR bucket is complete.

#3. Standby in the DR region

Plain streaming standby

pgbackrest --stanza=sales --repo=2 --type=standby restore \
  --recovery-option="primary_conninfo=host=pg-sales-primary.corp.local user=replicator sslmode=require"
systemctl start postgresql-17

Patroni standby cluster: a full Patroni cluster in DR whose "standby leader" replicates from the primary cluster:

bootstrap:
  dcs:
    standby_cluster:
      host: pg-sales-primary.corp.local
      port: 5432
      create_replica_methods: [pgbackrest, basebackup]
      restore_command: pgbackrest --stanza=sales --repo=2 archive-get %f "%p"

Watch the lag with pg_stat_replication on the primary, and pg_last_xact_replay_timestamp() on the standby.

#4. Region failover runbook

1. Declare DR. If the primary is still reachable, stop it (fence it) so it can't accept writes.
2. Check the standby's replay lag and last replay timestamp, and note the data loss window.
3. Promote it:
   • Plain standby: SELECT pg_promote();
   • Patroni standby cluster: patronictl edit-config and remove the standby_cluster section. The standby leader becomes the leader.
4. Repoint DNS or the connection endpoint to the DR leader.
5. Enable backups to the DR repo with a fresh full backup on the new timeline.
6. Tell the application owners the RPO you actually achieved.

#5. Point-in-time restore (logical corruption)

pgbackrest --stanza=sales --repo=2 --type=time \
  --target="2026-10-22 08:14:00+05:30" --target-action=promote \
  --pg1-path=/var/lib/pgsql/17/restore restore

Restore to a separate server or directory, copy out the affected data, or switch over to it.

#6. Failback

Rebuild the old primary as a standby of the DR leader (pg_rewind if its timeline only diverged a little, otherwise a fresh restore). Let it catch up, then do a planned switchover in a maintenance window.

Tip

Test a PITR restore from the DR bucket only at least once a quarter. That proves the WAL in DR is complete and that encryption keys and credentials work there.