Postgres Backups and How to Restore Them on ObjectRocket

By February 6, 2020 February 10th, 2020 No Comments

Regular backups and the ability to restore them is a critical piece of database system administration. At ObjectRocket, daily backups with 2 week retention are always included and enabled by default on all of our database products. Our PostgreSQL offering adds an additional bonus which is WAL archiving and the ability to restore to any time within our retention window. 

This is generally called Point-in-Time Recovery (PITR) and I’ll walk you through how it works on our service.

No Fuss Backups

The first major component is the backup process. It’s fully automated and you don’t have to do anything. Included in the cost of all instances is daily backups and we retain two weeks worth of backups at all times. You can view your backup times and the status of previous in our dashboard or via the UI.

Multiple Restore Options

When you hit a scenario and you’d like to restore a backup, we offer a number of options. You can restore:

  • to the most recent WAL update we’ve stored;
  • to a specific base backup;
  • to a specific point in time.

In all cases, your data is restored to a new PostgreSQL instance and at this time, the instance storage must be greater than or equal to the storage on the original instance. This is sometimes called a database fork, but the main idea is that you restore your data to a new and separate database instance, so both the old and new instances stay active until you remove one.

One final note is that restores are currently only exposed via our API . If you’re comfortable with the API, great. Otherwise, our support team would be happy to help you through the process. We plan on adding restores to the UI in the very near future.

Restoring to the latest time

First of all, I have a very simple database that just stores a timestamp and a sequence every minute so we can see where we’ve restored to.

Taking a quick look at our database, we see the last timestamp was at 16:00:58 (UTC).

If I want to restore to the latest data I have in this database I can do that by just providing the instance id of my source instance to a new instance create call.

Once my instance has been restored I can connect and check the last timestamp.

As you can see, all of the data is there and I have a new Postgres instance that is fully caught up to the source Postgres instance.

You may want to use this scenario if you want to create a test database from your current data to experiment with a new feature without the risk of affecting your original data.

Restore to a specific backup

The second option is to restore from a base backup. When listing backups, each has its own id that can be used to restore:

For the example backup above, you see that it was taken at 15:36:15 (UTC). 

If we would like to restore to this exact backup, we can simply add the backup id to the create call.

After the instance has finished loading, we can see that the backup has data/timestamps from before the backup time of 15:36:15.

Restore to a Specific Time

The final, and coolest in my opinion, is to just specify the time you’d like to restore from. You accomplish this by providing the source instance ID and the time to restore to:

Here we’d like to restore to 15:53:00. After our restore is complete, you can see that the data only exists up to that time.

If you look back at the images above, you may see that we did have another timestamp at 15:53:00, but that timestamp occurred at just over .8 seconds after 15:53:00, so it would not get included in this instance. You can get extremely granular with Point in Time recovery to target a time and state in the database.

What’s Next

All of that functionality is there for you to use today. However, we’ll be adding more features and capabilities in the near future. As always, we’ll keep you updated, but let us know if you have any requests.

Steve Croce

Steve Croce

Steve Croce is currently a Senior Product Manager and Head of User Experience at ObjectRocket. Today, Steve leads the UX/UI team through rebuilding out the platform’s user interface, scopes the company’s product and feature roadmap, and oversees the day to day development for ObjectRocket's Elasticsearch and PostgreSQL offerings. A product manager by day, he still likes to embrace his engineer roots by night and develop with Elasticsearch, SQL, Kubernetes, and web application stacks. He's spoken at KubeCon + CloudNativeCon, OpenStack summit, Percona Live, and various ObjectRocket events.