PGSQL Phriday #002: PostgreSQL Backup and Restore

Thanks to Ryan Booz we now have the #PGSQLPhriday blogging series. The second edition is about backups. And of course also about restore.

Backups? Do you need that?

If your data is not important, you certainly don’t need to bother about backups. Then again, why do you even store the data in the first place?

For anyone else, especially everyone who runs business critical or mission critical applications, having a backup is important. Equally important, but sometimes ignored, is the ability to restore the backup.

The requirements for backups are manifold:

• How quickly must you be able to restore the backup (SLA)?
• Do you need to be able to restore every transaction (catch all changes) or is a snapshot (backup freuency, hourly, daily, weekly, monthly) sufficient?
• How can the backup integrity be verified?
• Where do store the backup, and make sure that a disaster (as example: lost of data center) does not affect the backup?
• Who can restore the backup, what is the plan for that?
• …

Your #PGSQLPhriday task

Describe how you do backups for your PostgreSQL databases.

• Which tool(s) are you using, where do you store backups, how often do you do backups?
• Are there any recommendations you can give the reader how to improve their backups?
• Any lesser known features in your favorite backup tool?
• Any new and cool features in a recently released version?
• Bonus question: Is pg_dump a backup tool?

How does #PGSQLPhriday work?

• Post your blog post about “Describe how you do backups for your PostgreSQL databases” by Friday, November 4th.
• Include “PGSQL Phriday #002” in the title or first paragraph of the blog posting.
• Link back to this blog posting, example: “ads asked in ‘PGSQL Phriday #002’ if pg_dump is a backup tool, and I strongly object, here’s why”.
• Announce your blog post in one or any of these places:
  • Twitter, using the #PGSQLPhriday hashtag
  • PostgreSQL Slack in the #pgsqlphriday channel
  • Responding to the thread on the pgsql-general email list that announced this month’s blog topic
• Interact with other blog posts about this topic, maybe someone else has a different opinion about pg_dump being a backup tool.

Looking forward to your blog posts!

Summary

Here is the summary posting with all the blog postings.