Last week for “PGSQL Phriday” I posted the following task:
Describe how you do backups for your PostgreSQL databases.
And I added a bonus question: Is pg_dump a backup tool?
Blog posting summary
Quite a few people posted their backup strategies, in no particular order (or rather in order I’ve seen them online):
- SqlAsylum: PGSQL Phriday # 002: Postgresql Backup and Restore (Twitter posting)
- Ryan Booz: Adventures in PostgreSQL Backups (Twitter posting)
- Chris Ellis: A PostgreSQL Backup Journey (Twitter posting)
- Ryan Lambert: Backups for Postgres - PGSQL Phriday #002 (Twitter posting)
- Lætitia AVROT: How to do proper backups (Twitter posting)
- Grant Fritchey: Validating Backups: PGSQL Phriday #002 (Twitter posting)
- Kirk Roybal: left notes under my initial blog posting (Note: as of October 2023 this blog moved to a new platform, the old comments are not migrated)
If I forgot a blog posting, please contact me on one of the usual social media channels, or per email.
Honorary mention: Robert Haas blogged about backups, but not related to #PGSQLPhriday.
What about pg_dump?
I already explained in my initial #PGSQLPhriday posting why backups are important. The open question is, if pg_dump counts as backup tool.
It depends. Really.
If your requirement is that you need to be able to recover every transaction, then a snapshot from pg_dump will certainly not help you. Any business critical application deverses better than just a snapshot.
If however you just run a website like this blog on PostgreSQL, and things change every once in a while, a snapshot might be sufficient.
Figure out your requirements, and base your decision on that.