PostgreSQL Backup Checklist
PostgreSQL backup checklist for production projects.
Losing a database almost always costs more than setting up high-quality backup. Having dump files does not yet mean the backups will actually help restore the system. A reliable PostgreSQL backup strategy includes regular backups, integrity checks, test restores, and process monitoring.
Below is a practical checklist suitable for both small projects and enterprise systems.
Define your backup strategy
Before setting up, answer two questions:
- How much data loss is acceptable (RPO)?
- How quickly must the system be restored (RTO)?
This determines the choice between:
- logical backups;
- physical backups;
- continuous WAL archiving;
- streaming replication.
Use the appropriate backup type
Logical backups
Suitable for:
- small and medium databases;
- migrations between PostgreSQL versions;
- restoring individual tables.
Tools:
- pg_dump
- pg_dumpall
Example:
pg_dump -Fc mydb > backup.dumpThe -Fc format allows restoring individual objects and using parallel restore.
Physical backups
Best option for:
- large databases;
- fast recovery;
- high-load servers.
Used:
- pg_basebackup
- file snapshots
- specialized backup systems.
Archive WAL
WAL log archiving allows point-in-time recovery (PITR).
Check:
- archive_mode is enabled;
- archive_command is configured;
- archiving actually works;
- WAL files are regularly cleaned up after successful archiving.
Automate backups
Do not run backups manually.
Use:
- cron;
- systemd timers;
- Kubernetes CronJob;
- cloud platform schedulers.
Automation eliminates the human factor.
Keep multiple backup generations
Do not limit yourself to just one latest copy.
For example:
- daily — for the last 7 days;
- weekly — for the last 4 weeks;
- monthly — for the last 12 months.
This approach allows recovery even after late-discovered data corruption.
Store copies separately from the server
If backups are on the same disk as the database, they can be lost simultaneously.
It is recommended to store copies:
- on a separate server;
- in object storage;
- in the cloud;
- on an external NAS.
Follow the 3-2-1 rule:
- at least three copies of data;
- two different media;
- one copy offsite.
Encrypt backups
Especially when using cloud services.
You can use:
- GPG;
- OpenSSL;
- built-in storage encryption.
Remember to store keys securely.
Verify backup success
After completing the task, ensure that:
- the command completed without errors;
- the file has the expected size;
- the backup is not corrupted.
It is useful to set up notifications via:
- email;
- Telegram;
- Slack;
- monitoring systems.
Regularly test restoration
The most common mistake is discovering backups don't work only after a disaster.
At least once a month, you should:
- deploy a test server;
- restore a backup;
- check data integrity;
- ensure applications successfully connect to the database.
If restoration has never been tested, the backup cannot be considered reliable.
Monitor backup size
Keep track of:
- database size;
- growth rate;
- backup creation time;
- available disk space.
A sudden increase in size often indicates application issues.
Document the restoration procedure
During a disaster, there will be no time for experiments.
Documentation should include:
- sequence of actions;
- necessary commands;
- backup location;
- credentials (or their storage location);
- contacts of responsible personnel.
Use specialized tools
For large infrastructures, it is more convenient to use specialized solutions:
- pgBackRest;
- Barman;
- WAL-G;
- pg_probackup.
They support:
- incremental backups;
- compression;
- encryption;
- integrity checking;
- point-in-time recovery;
- working with cloud storage.
Common mistakes
Avoid the following problems:
- storing backups on the same disk as the database;
- lack of recovery testing;
- lack of WAL archiving;
- a single backup without history;
- lack of error notifications;
- manual backup execution;
- lack of free space monitoring;
- storing unencrypted backups with personal data.
Final checklist
Before putting the system into operation, ensure that all points are completed:
Conclusion
PostgreSQL backup is not a single pg_dump command, but a comprehensive process that includes creating, storing, verifying, and regularly testing backups. Only a combination of automation, monitoring, and periodic recovery testing ensures that data can be restored to a working state after any failure. This approach significantly reduces the risks of downtime and data loss for both small projects and large enterprise systems.