Databases

Cloud Spanner launches point-in-time-recovery capability

#spanner

Cloud Spanner, a horizontally scalable relational database, recently launched a point-in-time recovery (PITR) capability that provides complete data protection against inadvertent data deletion or updates due to a user error. Spanner already provides Backup and Restore and Import/Export, which recover the database to the last state when the backup or the export was taken. With PITR capability, Spanner now offers continuous data protection with the ability to recover your past data to a microsecond granularity. This helps enterprises quickly fix data corruption to reduce risk and loss of business, and minimize impact on customer experience.

PITR is simple and flexible to use and provides data protection with greater control and granularity. All you need to do is configure a database's version retention period to retain all versions of data and schema, from a minimum of one hour up to a maximum of seven days. Spanner will take care of the rest. In the case of logical data corruption, depending on the situation, you have the choice of recovering the complete database or just restoring specific portions of the database—saving you precious time and resources, as you don’t have to restore the whole database.

Let’s take two common real-life examples: first, John, a database administrator at a multinational financial company, accidentally deletes a live table in production and figures out the mistake based on customer complaints after a day. Second, Kim, a site reliability engineer at a national online retailer, rolls out a new payment processing engine that corrupts their consumer payments database while trying to perform multiple schema changes. If the version retention period in Spanner’s PITR capability is configured correctly, it can save the day for both John and Kim. John can perform a stale read specifying a query condition and timestamp in the past, then write the results back into the live database to recover the table. Kim can use the backup or export capability by specifying a timestamp in the past to back up or export the entire database, respectively, and then restore or import it to a new database.

Setting up and recovering an entire database with PITR

The version retention period is at the database level, hence we first need to go to the desired Database Details page to set a new retention period. By default, it’s been set to one hour for every database created. Now, with the PITR feature, we can set this period up to seven days with minute/hour/day granularity.

The figure below shows how to set a database’s retention period in the UI console:

set a databases retention period.jpg
Click to enlarge

You can do this for each database in your instance. Now, let’s see how you can create a backup at a point in time in the past (version time) in the UI and restore from that backup. The figure below shows the creation of the backup in the UI:

cloud spanner demo.jpg
Click to enlarge

When you list the backups for a database in the UI, you can see the Version time differs from the Backup Creation time, informing you that backup data is from a database version at a point in time in the past.

database details.jpg
Click to enlarge

Now you can restore the database to recover the complete database within the same instance or in another instance within the same region or multi-region in which you are using Spanner.

Note that the restored database will have the same version retention period that was in the original database at the time of backup creation; it won’t default back to one hour.

If the seven-day maximum data retention window for PITR does not meet your needs, Spanner continues to offer data protection options for longer retention times with backups (maximum one-year retention window) and export capability, which enables you to export data in CSV or Avro file formats that you can keep for as long as you need. 

Learn more 

PITR is now available in all Google managed regions globally. You are charged for the additional storage consumed for storing all the versions of the key in the version retention period. If you choose to use Backup/Restore or Import/Restore capability for recovering the data, you will pay for them as per their pricing.