Backup, PITR and replication

pg_dump and pg_basebackup, WAL archiving for point-in-time recovery, physical streaming replicas, logical replication and verification.

Two kinds of backup

# logical: portable, per database, restores into any version
pg_dump -Fc -f appdb.dump appdb
pg_restore -d appdb_restore --jobs=4 --clean --if-exists appdb.dump

# only the schema, or only the data
pg_dump --schema-only -f schema.sql appdb
pg_dump --data-only --table=invoice -f invoice.sql appdb

# globals: roles and grants live outside any database
pg_dumpall --globals-only -f globals.sql

# physical: a byte copy of the cluster, needed for PITR
pg_basebackup -D /var/lib/postgresql/replica -h primary -U replicator -X stream -P -R

Point-in-time recovery

# postgresql.conf on the primary
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
archive_timeout = 60s
max_wal_senders = 10
wal_keep_size = 1GB

# recovery settings for a restore
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-18 09:30:00+00'
recovery_target_action = 'promote'
recovery_target_inclusive = on

• archive_command returning non-zero makes PostgreSQL retry the same segment forever, and WAL accumulates on disk until the volume fills. Do not use cp to a local disk in production - use pgbackrest or wal-g.
• A PITR restore replays the base backup then all WAL up to the target. Test the whole path, not just the base restore.
• A dropped table is the classic PITR case: restore to just before the drop, then export the table and re-import it into the live database rather than promoting the old copy.
• Archiving does not back up the base. You need both, and the retention policy for each is different.

Replication

-- on the primary: a role for replication
create role replicator with replication login password '...';

-- on the replica: point it at the primary
-- primary_conninfo = 'host=primary user=replicator password=... application_name=replica1'

-- monitor lag
select client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) as replay_lag_bytes
from pg_stat_replication;

-- logical replication: publish selected tables
create publication app_pub for table invoice, customer;
-- on the subscriber
create subscription app_sub connection 'host=primary dbname=appdb'
  publication app_pub;

A physical replica is read-only and byte-identical, so it is the right target for a failover promotion. Logical replication is more flexible but does not carry DDL, sequences or large objects - it replicates the rows you publish and nothing else.

FAQ

Related

Query planning and performance tuning Next steps: the PostgreSQL ecosystem and migrations

Last refreshed 2026-09-18.