I recently wrote an article about PostgreSQL restores (and by extension, backups) over on Simple-Talk. The restore process within PostgreSQL, without 3rd party involvement, can be a little tricky. However, when you are using a Platform as a Service offering, like Azure Database for PostgreSQL, things get quite a bit easier. Let’s explore this just a little.
Restores Come From Backups
I know we all know this, but as I said in the article over on Simple-Talk, discussing backup strategy is a mistake. You should instead be discussing a restore or recovery strategy. How do you intend to get your database back? How much data are you prepared to lose? How long should a restore take? Answering these questions leads you to a Recovery Point Objective (RPO), which tells you how much data you can risk, and a Recovery Time Objective (RTO), how long will it take. With the RPO & RTO in hand, now you go design backups.
A traditional restore process in PostgreSQL involves taking the server offline, restoring the file structure, then replaying the Write Ahead Log (WAL) to a point in time. Yeah, there are several other steps you should be scripting and practicing for your restores. But that’s it in a nutshell.
What about Azure Database for PostgreSQL? How much control do you have to set your RPO & RTO?
Don’t get too upset, but not that much. You can vary the retention period from 7 days to 35 days. You can also manually run pg_dump when you want. The good news, Azure is managing a point-in-time recovery process for you, at no additional charge, with no additional work on your part. WIN! The RPO is fifteen minutes. For most of us, that’s about what we do for ourselves. For a few of us, that might not be adequate. For many of us, that’s going to be an infinitely superior RPO to what we currently have (usually, no backups at all, or, at best, poorly conceived backups with no restore strategy or practice). The RTO is completely dependent on your data and your WAL, just as it is if you’re restoring locally.
PostgreSQL Restore
You have a couple of options when it comes to restoring your databases within Azure. First up is the Azure Portal, which supports you through a restore process. Second is the Azure command line, which has direct control over restores, geo-redundant restores and flexible server restores. Personally, my strongest recommendation, learn and practice using the CLI. Yeah, you can use the portal, which I’m going to show right here, but you’re better off with the CLI. More control, more flexibility, and you can store the script for replay as opposed to clicking and typing your way through.
Anywho, this is a test server and database I work with regularly in Azure:
If you look at the top, you’ll see several options, including “Restore”:
Clicking on Restore opens another window:
Use is pretty self-explanatory. I have the 7 day retention enabled. I can go to a point in time for the last 7 days. Define it by the date and the time. You do have to restore to a new server, because, just like the on-premises PostgreSQL, you can’t restore to an active server. Give the server a name, pick a location, define a tier. You’re done. Click OK. That’s easy. Now, you might do additional work, migrating logins stuff like that, but effectively, you’re finished.
In order to use the command line, well, it’s a lot easier. The core command is:
az postgres server restoreFrom there, you supply about what you’d expect, the server you’re restoring from, the server you’re restoring too, and the point in time. Ta-Da! Yeah, it’s that easy.
Conclusion
I’m a pretty happy person when it comes to the cloud and cloud services. I like how they take tons of boring work, like setting up backups, and fix them for us. However, I know, that either personal horror shows, I dropped a table by accident, or system ones, there was a major outage in a facility, can lead to the need for restores. So, even though your Azure database is probably safer than if you were managing it, stuff happens. Learn how to recover from that, before it happens.