One of the single biggest reasons to go with a Platform as a Service (PaaS) offering like AWS RDS are the things it does for you, like making it really easy to restore to a point in time. Let’s take a look at it.
Restore to a Point in Time
When connect up to the console and look at your databases, all you have to do is select the “Maintenance and Backups” tab to get details on what backups are being taken:
That’s the basics of what’s happening with your backups. However, you can get more detail by choosing the “Automated Backups” page on the left:
And now you see all the information about the backups. But the real magic to restore to a point in time is over on the right where it says “Actions.” Clicking on that you get a drop-down menu:
Clicking on that opens up another page. I’m going to need to break that down some, because, interestingly enough, you don’t have to define much in the way of the “restore”, but because, well, hang on a sec.
“Restoring” a Database
The very top of the new page is pretty straight forward and easy enough to understand, what is the date and time you’d like to restore to:
But now you have to remember that, PostgreSQL doesn’t overwrite existing databases on a restore. In fact, you’re going to create a new instance, and then restore to that. So, the very next section is instance settings:
I’ve chosen to name my instance hamshackradio-bu. This is because I don’t want to drop my existing database prior to running this restore. I could do that, then restore to a point in time. Or, I can rename stuff to keep things online until I’m ready. Either way, the next section is instance configuration, but it remembers the settings from my existing database. Same thing with availability:
I won’t show you everything, but you have to configure storage, accepting the defaults or making changes now since you are creating a new instance, connectivity, authentication and tags. Then, and here’s the one trick, you have to provide a database name in “Additional Settings.” Otherwise, it won’t create a database from the backup:
Here, I kept the database name the same as my original since it’s on it’s own instance. With all that defined, you can click the button that says restore to a point in time. First, you’ll see it creating the instance:
That step, could take a long time, depending on how many logs it’s replaying to get your database back to a moment in time.
Conclusion
Yes, the conclusion, because that’s it. That’s all that has to happen. I wait a bit, there’s a new instance with my database restored to the point in time I specified. Now, I’m cheaping out on my backups within PostgreSQL, so I’ve only got 7 days available. You can pay for more. But really, getting a restore done is that simple. With all the defaults loaded for you, the amount of work is minimal. You don’t need knowledge of how PostgreSQL works, just how AWS RDS works.
You can script all this too. In fact, it’s probably a good idea to do that so that you can automate this whole process. I’ll post how to do that another time.