Point-in-time Recovery

Overview

Cloud Spanner point-in-time recovery (PITR) provides protection against accidental deletion or writes. For example, if an operator inadvertently writes data or an application rollout corrupts the database, with PITR you can recover the data from a point-in-time in the past (up to a maximum of 7 days) seamlessly. If you need longer-term retention of data, you can use either Backup and Restore or Export and Import.

PITR works by letting you configure a database's version_retention_period to retain all versions of data and schema, from a minimum of 1 hour up to a maximum of 7 days. Cloud Spanner stores old versions of data at microsecond granularity and the database maintains an earliest_version_time, which represents the earliest time in the past that you can recover old versions of the data.

Ways to recover data

There are two ways to recover data:

  • To recover a portion of the database, perform a stale read specifying a query-condition and timestamp in the past, and then write the results back into the live database. This is typically used for surgical operations on a live database. For example, if you accidentally delete a particular row or incorrectly update a subset of data, you can recover it with this method. For instructions, see recovering a portion of your database.

  • To recover the entire database, backup or export the database specifying a timestamp in the past and then restore or import it to a new database. This is typically used to recover from data corruption issues when you have to revert the entire database to a point-in-time before the corruption occurred. Note that backing up or exporting a database could take several hours and that you cannot restore or import to an existing database. For instructions, see recovering the entire database.

Performance considerations

Databases with longer retention periods and, in particular, those that frequently overwrite data, use more system resources. This can affect how your database performs, especially if your instance is not provisioned with enough nodes. If your database has a very high overwrite rate (e.g. if your database is overwritten multiple times per day), you might consider increasing the retention period gradually and monitoring the system. Here are some things to be aware of:

  • Increased storage utilization. We recommend setting up storage alerts to ensure that you do not exceed the 2 TB per node storage limit. When you increase the retention period, keep in mind that storage usage will increase gradually as the database accumulates older versions of data. This is because the old data that would have expired under the previous retention period, is no longer expired. So, for example, if you increase the retention period from 3 days to 7 days, you need to wait for 4 days for database storage usage to stabilize. We also provide instructions for estimating the storage increase.

  • Increased CPU usage and latency. Cloud Spanner uses additional computing resources to compact and maintain old versions of data. Monitor your instance and database to ensure that latency and CPU utilization remain at acceptable levels.

  • Increased time to perform schema updates. An increased retention period means that schema versions must be retained for longer durations potentially causing schema updates to be throttled while waiting for server resources. Make sure that you are following best practices for schema updates and staying within the limits for schema updates.