Home · About · Services · Industries · Projects · Products · Process · Blog · FAQ · Contact
PostgreSQL backup strategies: pg_dump, pg_basebackup and PITR When pg_dump is enough, when you need base backups and WAL archiving, and how we run point-in-time recovery with pgBackRest on real PostgreSQL servers.
A migration goes wrong at 14:30 and someone asks: can we get the database back to how it was at 14:20? With only nightly pg_dump files, the honest answer is no. The best on offer is last night at 02:00, which means losing most of a working day of records. That conversation is usually where a proper PostgreSQL backup strategy starts.
The short answer: use pg_dump for portable, per-database copies and for moving data between major versions. Use physical base backups plus continuous WAL archiving when you need to restore a whole cluster quickly or to any point in time (PITR). Most production systems need both, and a tool such as pgBackRest to run the physical side.
The three methods and what each one gives you PostgreSQL has two families of backup. Logical backups export SQL-level objects and data. Physical backups copy the data files of the whole cluster. PITR is not a third tool but a physical base backup combined with an unbroken archive of write-ahead log (WAL) segments, which Postgres replays up to the moment you choose.
pg_dump: one database at a time, consistent as of the moment it started. Portable across major versions and CPU architectures. Can restore a single table. Restore time grows with data size because every index is rebuilt. pg_basebackup: a binary copy of the entire cluster, all databases included. Restores at roughly the speed of copying files, but only to the same major version. On its own it is a snapshot, like a dump. Base backup plus WAL archive (PITR): restore the cluster to any second between the oldest base backup you keep and the last archived WAL segment. Data loss is limited to WAL not yet archived, usually seconds to a minute. The decision comes down to two numbers agreed with whoever owns the system: how much data you can lose (recovery point objective) and how long a restore may take (recovery time objective). A nightly dump means up to 24 hours of loss. On a 200 GB database, a pg_restore that rebuilds every index can also take hours. If either number is unacceptable, you need the physical route.
Logical backups with pg_dump, done properly We covered our basic nightly dump script in an earlier post on backups that restore. Two refinements matter once databases grow. First, the directory format (-Fd) is the only format that supports parallel dumps, which shortens the window on large databases. Second, pg_restore can rebuild in parallel from both custom and directory formats.
# Parallel dump: 4 workers, directory format, zstd compression (PostgreSQL 16+)
pg_dump -U postgres -Fd -j 4 --compress=zstd -f /var/backups/pg/erp_$(date +%F) erp
# Roles and tablespaces are cluster-wide and not in pg_dump
pg_dumpall -U postgres --globals-only > /var/backups/pg/globals_$(date +%F).sql
# Parallel restore into an empty database
createdb -U postgres erp_restored
pg_restore -U postgres -j 4 --no-owner -d erp_restored /var/backups/pg/erp_2026-10-04Each worker opens its own connection, so check max_connections before raising -j. When you dump in order to upgrade, run the pg_dump binary from the newer version against the old server. Newer pg_dump can read older servers; the reverse is not supported.
Keep logical dumps even after you add PITR. They are the only backups you can restore into a different major version, the easiest way to pull back one table someone truncated, and a second, independent copy if the physical chain has a problem.
Physical base backups and WAL archiving A base backup on its own is easy to take. pg_basebackup connects over the replication protocol, so the user needs the REPLICATION attribute and a matching entry in pg_hba.conf. The -X stream option includes the WAL needed to make the copy consistent.
pg_basebackup -U replicator -h 127.0.0.1 \
-D /var/backups/pg/base_$(date +%F) \
-Ft -z -X stream --checkpoint=fast -PFor point-in-time recovery, Postgres must also ship every completed WAL segment somewhere safe. That is controlled in postgresql.conf. wal_level is already replica by default; archive_mode needs a restart to change.
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'
archive_timeout = 60 # force a segment switch at least once a minuteThat cp example is the one in the PostgreSQL documentation, and it is fine for learning. In production we do not hand-roll archive commands. A copy that is not flushed to disk, a full archive volume or a silent permission error can all leave gaps in the chain, and a gap means you cannot recover past it. Watch the failed_count and last_failed_wal columns in pg_stat_archiver, and alert if pg_wal starts growing, because Postgres keeps segments until they archive successfully.
PostgreSQL 17 added native incremental backups: with summarize_wal = on, pg_basebackup --incremental, pointed at the previous backup_manifest, copies only changed blocks, and pg_combinebackup merges a chain into a full backup before restore. It is useful, but it still leaves retention, encryption and off-site copies to you.
Running PITR with pgBackRest For the physical side we recommend pgBackRest. It handles full, differential and incremental backups, parallel compressed WAL archiving, encryption, retention and restore, writing straight to S3-compatible storage such as AWS S3 or a self-hosted MinIO. WAL-G and Barman are reasonable alternatives; the principles below apply to all three.
# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-s3-endpoint=s3.eu-west-2.amazonaws.com
repo1-s3-region=eu-west-2
repo1-s3-bucket=example-pg-backups
repo1-path=/pgbackrest
repo1-retention-full=2
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=CHANGE-ME-long-random-string
compress-type=zst
start-fast=y
process-max=4
[main]
pg1-path=/var/lib/postgresql/16/mainAdd repo1-s3-key and repo1-s3-key-secret to the same file, or set them as PGBACKREST_ environment variables, and make the file readable only by the postgres user. For MinIO, add repo1-s3-uri-style=path. Then point Postgres at it and initialise the repository.
# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
# as the postgres user, after a restart
pgbackrest --stanza=main stanza-create
pgbackrest --stanza=main check
pgbackrest --stanza=main --type=full backup
pgbackrest --stanza=main infoA schedule we often start with is a weekly full backup and daily differentials, with WAL archived continuously. With repo1-retention-full=2 you keep two full backups and everything that depends on them, which gives a recovery window of one to two weeks. Adjust it to the window the business actually needs, and to what storage costs.
Restoring to 14:20 on a given day then looks like this. Stop Postgres first; --delta reuses files that already match, which speeds things up on a large cluster.
sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=main --delta \
--type=time "--target=2026-10-03 14:20:00+05:30" \
--target-action=promote restore
sudo systemctl start postgresqlBehind the scenes this writes restore_command and recovery_target_time into the configuration and creates recovery.signal, which is how PostgreSQL 12 and later enter targeted recovery. Postgres replays WAL until it reaches the target, then promotes and starts a new timeline. Always include the time zone in the target, or you may land hours away from where you meant.
Managed databases and where your copies live On Amazon RDS or Azure Database for PostgreSQL, automated backups and PITR are built in. On RDS you set the retention period, up to 35 days. That covers most accidents, but the copies live in the same account as the database. We still take a scheduled pg_dump to storage in a separate account, so a deleted instance, a compromised account or a region problem does not take the only backups with it.
On self-managed servers, the same rule applies. pgBackRest needs delete rights to expire old backups, so protect the history at the bucket level instead: turn on versioning or object lock, and make sure the keys on the database host cannot delete old object versions or change the bucket policy. A compromised server should not be able to erase its own backups. Keep the encryption passphrase somewhere other than the repository, and make sure more than one person can reach it.
Choosing a strategy: a practical checklist Under about 50 GB, and losing a day is acceptable: nightly pg_dump plus globals, off-site copies and a weekly restore test is enough. Losing hours of data is unacceptable, or restore must be fast: add pgBackRest (or WAL-G) with continuous WAL archiving and keep the nightly dump alongside it. Set archive_timeout so quiet databases still ship WAL regularly. Monitor pg_stat_archiver and pg_wal size, and alert on a missing backup, not only on a failed one. Practise a PITR restore to a specific timestamp on a spare machine at least quarterly, and record how long it took. Remember that a replica is not a backup: a DROP TABLE replicates in milliseconds. Before a major-version upgrade, take a fresh logical dump; physical backups will not restore into the new version. If you cannot say today which second you could restore your database to, or nobody has timed a full restore, that is the gap to close first. The tooling is mature; the work is in configuring it carefully and testing it on a schedule.
Infigini IT Services · 11 Kanwali Road, Dehradun 248001, India · info@infigini.com · +91 95684 87789