PostgreSQL Replication and Backup Strategies: Data Is Life, and Backups Are Its Lifeline

I have seen far too many teams share the same story: the business is running smoothly, then one night the primary database disk dies. The operations team investigates and discovers that backups have not succeeded since the previous week because the disk filled up and the script failed silently. Or a new intern runs DELETE FROM users WHERE id = 123; in production but leaves out the WHERE; one press of Enter later, the entire table is gone. By the time the DBA arrives, all they can do is face a group of devastated product managers.

Database downtime is merely painful; data loss is catastrophic. Hardware fails (SSDs have finite write endurance, disk tracks age, data centers lose power, and cleaning staff unplug network cables), people make mistakes (dropping databases, deleting tables, changing the wrong data, or running migrations that should never have been run), and there is also ransomware, earthquakes, and floods. High availability (HA) solves "can the system keep running?" while disaster recovery (DR) solves "can the data be recovered?" Neither can replace the other.

PostgreSQL already has a mature ecosystem for replication and backups. This article covers the most important practical topics in one place: streaming replication, the tradeoff between synchronous and asynchronous replication, physical backups and PITR, logical replication, the 3-2-1 strategy, and the most commonly overlooked yet potentially fatal step: recovery drills.

1. Streaming Replication: Every Change on the Primary Reaches the Standby

The foundation of PostgreSQL replication is WAL (Write-Ahead Logging). Before any transaction modifies a data page, it first writes the change to a WAL file. Streaming Replication works by having the primary stream this WAL to a standby, which replays it to achieve near-real-time physical synchronization.

This is physical replication: data is copied byte for byte, so the standby looks exactly like the primary, including its internal data-page layout. It is general-purpose, efficient, and transparent to applications. The drawback is that replication operates at the entire-cluster level; you cannot select individual tables.

A typical primary-standby architecture:

        ┌────────────────┐   WAL stream   ┌────────────────┐
        │    Primary     │ ─────────────▶ │    Standby     │
        │  (read/write)  │                │  (read-only)   │
        └────────────────┘                └────────────────┘
               ▲                               │
               │ Application writes            │ Read queries / failover
               │                               ▼
           ┌────────┐                     Hot standby/reports
           │ Client │                     Connect here to read
           └────────┘

Key primary settings in postgresql.conf:

wal_level = replica            # At least replica; logical replication requires logical
max_wal_senders = 10           # Number of standbys allowed to connect
wal_keep_size = 1024           # MB of WAL retained so standbys do not fall behind
hot_standby = on               # Whether the standby is readable during recovery (set on the standby)
archive_mode = on              # Enable archiving; see the backup section below
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'

The primary also needs a role with replication privileges:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'a-strong-secret';

Allow replication connections in pg_hba.conf:

host replication replicator 10.0.0.0/24 scram-sha-256

Initialize the standby by pulling a full copy from the primary with pg_basebackup:

pg_basebackup \
  -h primary.host -U replicator -D /var/lib/postgresql/data \
  -Fp -Xs -P -R

-R automatically writes a standby.signal file and configures the primary connection details. When the standby starts, it enters continuous recovery mode and keeps receiving WAL.

Failover: if the primary goes down, promote a standby to become the new primary. PostgreSQL 10+ provides pg_promote():

SELECT pg_promote();  -- Run on the standby; it becomes a writable primary within seconds

Do not run this manually in production. Use tools such as Patroni / repmgr / pg_auto_failover to manage leader election, VIP/DNS switching, and split-brain prevention. Handwritten failover scripts are a classic beginner trap: split-brain allows two primaries to accept writes simultaneously, causing direct data conflicts and corruption.

2. Synchronous vs. Asynchronous Replication: A Choice Between Consistency and Performance

Streaming replication is asynchronous by default: once the primary writes WAL and flushes it locally, it tells the client that the commit succeeded without waiting for confirmation from the standby. This means:

• Low write latency, with primary performance unaffected by a slow standby;
• But if the primary fails immediately after committing and that WAL has not yet reached the standby, those changes are lost.

With synchronous replication (synchronous_commit = on plus synchronous_standby_names), the primary must wait until at least one synchronous standby confirms receipt of the WAL before returning success to the client.

synchronous_standby_names = 'FIRST 1 (standby1, standby2)'

The benefit is strong consistency: every transaction successfully committed on the primary is guaranteed to exist on the standby. The drawback is higher write latency, and if every synchronous standby goes down, the primary will hang. To preserve consistency, writes remain blocked until they time out or a standby recovers. This is why synchronous replication is commonly configured as FIRST 1 (a, b), leaving at least one alternative candidate.

How should you choose? Based on my production experience:

• Finance, trading, and ledger systems: use synchronous replication for core tables; losing a single payment is an incident.
• Content, logs, and event streams: asynchronous replication is usually sufficient. If the business can tolerate losing the last few seconds of logs, do not sacrifice performance for them.
• A mixed approach: PostgreSQL can switch synchronization at the transaction level. Critical operations can temporarily run SET LOCAL synchronous_commit = on;, while routine traffic remains fast and asynchronous.

Remember: synchronous replication protects RPO (the amount of data lost) at the expense of RTO (recovery time) and performance. There is no silver bullet.

3. Physical Backups: Full Backups Plus WAL Archiving Can Return to Any Point in Time

Replication solves "can the system keep running now?" Backups solve "can the past be recovered?" PostgreSQL's physical-backup combination is a full pg_basebackup plus incremental WAL archiving. Together, they enable PITR (Point-in-Time Recovery), restoring the database to any second in the past.

   Full base backup          Continuous WAL archive
   pg_basebackup     +       archive_command       =    PITR
   (snapshot at a point)     (every later change)       (restore to any point in time)

Step 1: Create a full base backup. This is an online hot backup and does not lock the database:

pg_basebackup -D /backup/base -Ft -z -P -X fetch
# -Ft creates a tar archive, -z compresses it, and -X fetch includes the WAL required during the backup

Alternatively, use a specialized tool such as pgBackRest or Barman, which provides parallelism, compression, incremental backups, and remote management. These tools are strongly recommended in production; avoid assembling your own scripts around bare pg_basebackup.

Step 2: Continuously archive WAL. archive_command copies every WAL segment produced by the primary to independent storage such as object storage, NFS, or an off-site system:

archive_command = 'aws s3 cp %p s3://my-pg-wal/%f'

WAL filenames increase monotonically, so the archive is naturally ordered.

Step 3: Perform PITR. Suppose someone accidentally drops a table at 3:17 p.m. on Wednesday, and you need to restore the database to 3:16 p.m.:

1. Extract the latest full backup into a new data directory;
2. Place recovery.signal in the data directory;
3. Configure postgresql.auto.conf:

restore_command = 'aws s3 cp s3://my-pg-wal/%f %p'
recovery_target_time = '2026-07-17 15:16:00+08'
recovery_target_action = 'promote'

4. Start PostgreSQL. It replays all archived WAL, stops at the target time, and promotes itself to become the new primary.

This is the core value of physical backups: they do not merely restore the "latest" state; they can restore precisely to "one second before the incident." Logical backups cannot do this.

4. Logical Backups and Logical Replication: Flexible, but Without Strict Consistency Guarantees

Logical backups use pg_dump / pg_dumpall to export SQL statements or a custom format. They work across versions and architectures and can select specific tables or schemas. They are suitable for migrations, pre-upgrade snapshots, and copying production data into a test database.

pg_dump -Fc -d mydb -f mydb.dump            # Custom compressed format; supports parallel restore
pg_dump -d mydb -t users -t orders -f sub.dump   # Export only selected tables
pg_restore -j 4 -d newdb mydb.dump          # Restore in parallel with 4 workers

The critical limitation of logical backups is that they cannot perform PITR. They can only restore the state at the moment of the dump. Dumping a large database may take hours while the data continues to change. Logical backups are therefore a supplement, not a replacement for physical backups plus archiving.

Logical Replication is a different feature. It works at the table level and uses a publication/subscription model: the primary runs CREATE PUBLICATION to publish changes from a set of tables, and the target runs CREATE SUBSCRIPTION to subscribe. Changes are transferred in logical form (INSERT/UPDATE/DELETE) and replayed on the target.

-- Publisher (primary)
CREATE PUBLICATION pub_users FOR TABLE users, orders;

-- Subscriber (another database, possibly on a different PG instance)
CREATE SUBSCRIPTION sub_users
  CONNECTION 'host=primary.host user=rep password=...'
  PUBLICATION pub_users;

Logical replication excels in these scenarios:

• Upgrades across major versions: for PG 13 → PG 16, logical replication is nearly the standard approach to upgrades with zero downtime;
• Selective replication: synchronize only the users table to an analytics database instead of copying the entire cluster;
• Heterogeneous distribution: split data into separate databases for different business units;
• Consolidating multiple databases into a data warehouse or data lake.

The tradeoffs are that logical replication does not transfer schemas (table structures must already exist), does not support sequences, can lag on large transactions, and depends heavily on primary keys. UPDATE/DELETE operations on tables without primary keys can fail.

5. The 3-2-1 Backup Strategy: Do Not Put All Your Eggs in One Basket

The widely accepted rule for backups is called 3-2-1:

• 3 copies (production data plus at least two backups);
• 2 different media types (for example, object storage plus tape, or cloud disks plus a local NAS);
• 1 completely offline or off-site copy (to protect against data-center-level disasters such as fires, floods, and regional outages).

Mapped to a practical PostgreSQL configuration:

                 ┌──────────────────────────┐
                 │ Production PG primary    │ (copy 1)
                 └───────────┬──────────────┘
                             │ Streaming replication
                   ┌─────────▼──────────┐
                   │ Off-site read-only │ (copy 2, different availability zone)
                   │ standby            │
                   └─────────┬──────────┘
                             │ pg_basebackup + WAL archiving
              ┌──────────────┼───────────────┐
              ▼              ▼               ▼
        Object storage S3   Local NAS     Offline cold backup
        (medium A)          (medium B)    (off-site/medium B)

Key principles:

1. Backups must be automated. Run scripts through cron / k8s CronJob and send metrics to monitoring after completion;
2. Verify backup integrity (pg_verifybackup validates base backups). Do not wait until recovery to discover corrupted files;
3. WAL archives and full backups must use separate directories or buckets so a full backup cannot overwrite the archive;
4. Encrypt them: use encryption at rest and in transit. A backup contains the complete dataset, so a backup leak is equivalent to a database leak;
5. Test recovery. The next section covers this step, which matters even more than creating the backup itself.

Retention policy: follow GFS (Grandfather-Father-Son). Retain daily backups for 7 days, weekly backups for 4 weeks, and monthly backups for 12 months. This controls costs while covering incidents discovered late, such as a deletion noticed only the following week.

6. Recovery Drills: A Backup That Has Never Been Tested Does Not Exist

This is the shortest section, but the most important. In my experience, 90% of "failed backup" incidents happen because nobody has ever performed a real recovery.

Common failure scenarios:

• The backup script runs every day, but the destination disk is full, cp fails silently, and nobody notices;
• The WAL archive chain has a gap because archive_command failed for a period, so PITR gets stuck;
• The backup is encrypted, but the key is stored on the same failed machine;
• Recovery documentation is outdated, and the actual commands are incompatible with the new version;
• Nobody on the team knows how to perform PITR. During a real incident, confusion pushes RTO out to several hours.

The remedy: perform a recovery drill every month and treat it like a production incident.

Drill checklist:

1. Test restoration regularly: in an isolated environment, start a complete PG instance from the latest backup and run read-only application checks such as row counts, checksums for critical tables, and sampled comparisons;
2. Test PITR: choose a point in the past and verify that the database can be restored to that exact second;
3. Test failover: run kill -9 on the primary, confirm that Patroni/repmgr switches to the standby within seconds, and verify that the application connection pool reconnects automatically;
4. Record metrics: for every drill, record RTO (recovery duration, with a target such as 15 minutes) and RPO (amount of data lost, with a target such as zero or a few seconds);
5. Update the documentation: add every issue found during the drill to the runbook so the team can follow it during a real incident.

Better still, make this part of the CI pipeline. After every successful backup, automatically restore it in a temporary environment, validate it, and alert immediately on failure. A backup is a promise, and recovery is how you fulfill it. Only a promise that has been fulfilled actually counts.

Summary

1. Replication and backups are different things: replication keeps the system online, while backups make rollback possible. HA cannot replace DR.
2. Streaming replication is the foundation of physical replication: WAL streams to the standby in near real time, but failover should be managed by tools such as Patroni, not handwritten scripts.
3. Choose synchronous or asynchronous replication based on the workload: financial ledgers use synchronous replication to protect RPO, while logs and content use asynchronous replication to preserve performance. Critical transactions can also switch temporarily to synchronous mode.
4. Physical backups + WAL archiving = PITR: this restores the database to any point in time, which logical backups cannot do. It is essential in production.
5. Use 3-2-1 plus recovery drills: keep three copies on two media types, with one copy off-site. Test a real recovery every month. A backup that has never been tested does not exist.

Key takeaway: "Database reliability is not measured by how many backups you have, but by how quickly you can restore the data intact and prove that the process really works." A backup is a written promise; recovery is the moment it is fulfilled. Treat every drill as a rehearsal for an incident, so when a real failure knocks at 4 a.m., you can confidently say: the data is fine; go back to sleep.