PostgreSQL backup best practices: what the official documentation says to do

Eight PostgreSQL backup best practices from the official documentation

The PostgreSQL backup best practices drawn from the official documentation are these: pick the method that matches how much data you can lose, back up roles and configuration as well as tables, store a copy away from the server, archive WAL with a command that never overwrites, monitor the archive, verify backups, restore them on a schedule, and write the procedure down. The table ties eight specific practices to a statement in the official documentation.

How to apply them to logical backups

1. Use the custom or directory format. pg_dump -Fc is compressed by default and allows selective restore. See Postgres dump formats.
2. Save roles on every run. pg_dumpall --globals-only > globals.sql
3. Run a pg_dump at least as new as the server. It refuses servers newer than its own major version, and its output is meant to load into newer versions, not older ones.
4. Restore safely. Create the target with createdb -T template0 newdb, use pg_restore --exit-on-error or --single-transaction so a failure is not silent, and run ANALYZE afterward.

How to apply them to physical backups and WAL archiving

1. Set up and test archiving before the first base backup. The manual gives that order. Archiving needs wal_level at replica or higher, archive_mode = on and an archive_command.
2. Use a command with an overwrite guard. The documented Unix example is archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'. The manual warns against relying on cp -i, because GNU cp returns zero when the file already exists.
3. Protect the archive. It contains effectively everything in the database, so restrict who can read it.
4. Bound the delay. Only completed segments are archived. archive_timeout forces a switch on quiet servers; the manual calls very short values unwise because every forced segment is still full length.
5. Take base backups with pg_basebackup and keep the manifest. The manifest is what pg_verifybackup checks against.
6. Choose the base backup interval deliberately. Longer gaps mean more WAL to store and more to replay during recovery.

A worked example with real settings

A self-hosted 80 GB production database that can lose at most a minute or two of data might use: wal_level = replica, archive_mode = on, the guarded archive_command above pointed at a mounted archive volume, and archive_timeout set to about a minute, which the manual describes as usually reasonable. A weekly pg_basebackup -D backup -Ft -z -P writes compressed tar files with a progress report, the form shown in the reference page.

Alongside it, a nightly pg_dump -Fc gives a portable copy that survives a major version upgrade, which the physical backup does not. Both copies are sent off the server. That follows the 3-2-1 rule described in the US-CERT paper Data Backup Options: 3 copies, 2 media types, 1 off-site. Decide the loss figure first using recovery objectives, and set how long to keep each copy with a retention horizon.

Common mistakes that the documentation warns about

• Copying the data directory of a running server. A plain file-system copy is only usable if the server was shut down, unless you use a consistent snapshot or a base backup.
• Trying to recover to a time during the base backup. The stop point must be after the backup ended.
• Breaking an incremental chain. PostgreSQL does not track which earlier backups an incremental needs.
• Trusting verification alone. The pg_verifybackup page itself says to still perform test restores and check the data.
• A restore_command that returns zero for a missing file. It must return non-zero when asked for a file that is not in the archive.

When a paid tool is worth it

Every practice above can be met with tools that ship with PostgreSQL. What native tools do not give you is someone watching. If backups have failed quietly before, or several people need to see backup status and fetch backup files without shell access to the database server, a managed service is worth paying for. SimpleBackups lists failure notifications, anomaly detection on paid plans and backup files you download for manual restore, from $0 for one job to $49 per month for five, as read on its pricing page on 3 October 2026. For WAL-level recovery, pair it with pgBackRest or your provider's point-in-time feature rather than replacing them. A written restore runbook matters in both cases.