It’s time again for the another PGSQL Phriday, this time, the question has been asked: How do you do PostgreSQL backups?
Honesty up front.
I’m very much just beginning my journey of learning PostgreSQL. I’ve been documenting that learning over here at Simple-Talk (more on the way there), including backups. For this post, I’m not going to tell you about my “experience” maintaining a PostgreSQL backup routine because, well, there isn’t any. Instead, I have something else to say about backups that I learned, the hard way I might add, while working in SQL Server, that is 100% applicable to PostgreSQL.
Backups Do Not Matter
You heard me. In a post that’s supposed to be about how you do backups, I’m telling you backups don’t matter, and yeah, I’m serious.
What do I mean? Am I saying that you shouldn’t backup your PostgreSQL database?
Aitch, Eee, double hockey sticks, NO!!!
What I’m saying is very simple. Backups don’t matter. Restores do.
Here’s a very simple thought to keep in your head: I am only as good as my last restore. You can take backups any way you want, up to and including pg_dump (which is a form of backup, but not really a backup as much as an export, if you ask me). And you really should be doing some kind of backup on your systems. Things go wrong. If you don’t have explicit backups, ready to go, you’re in trouble. However, you can have hundreds of backups, taken 5 different ways so that they include backing up the Write Ahead Log (WAL) for a point in time recovery. If you can’t restore them, what does it matter?
What Now?
Well, now, in addition to setting up your backups, hopefully following the excellent advice you’re going to get from all the other PGSQL Phriday posts, you start practicing restores. Make sure you know, exactly, how to recover that database that you so carefully created a backup for. While you have to have the backup first (egg before chicken), that backup is only useful if you can restore it. Learn how.
After learning how, make darned sure you keep copies, yes, copies, of scripts around that enable you to restore your databases. Then, regularly, test those scripts. This does three things. First, it validates your scripts are working. Yay! Second, it provides more practice for you doing restores, helping to ensure that you’re keeping your skills up for the day when it counts. Third, you’re validating that your backups work, because you can restore them.
At my last job as a full-time DBA, we had automated testing for all our backups, just to validate them, but also to ensure we knew we could restore. Plus, every few weeks (and, frankly, probably not as often as we should have), we did practice manual recoveries. Again, just to be sure we could. I strongly recommend if you have important information stored in a PostgreSQL database, you validate that you can recover that thing.
Conclusion
While I don’t have as much knowledge of PostgreSQL to share as others, I do have a ton of experience in and around databases and data management that I can share. There’s an old saying, experience is not transferable. While that is true, it is possible to learn from the experience of others. While you may not have experienced the database you can’t recover because you either had no backup, or because you didn’t know how, both of which I have experienced, you can learn from my bad examples. Let my experience be a guide to you. Yes, get out there and set up your backups. Then, restore that backup so that you validate it worked.