Reducing the storage impact of Point-in-Time Recovery

Point-In-Time Recovery (PITR) is a critical capability for enterprise applications.  It allows database administrators to recover from accidental data deletion by restoring their production databases to a time before the incident.  

Cloud SQL for PostgreSQL launched support for PITR in July 2020, allowing you to recover from disasters like data corruption or accidental deletion by restoring your Cloud SQL instance to a previous time. We’re excited to announce an additional enhancement to PITR for Cloud SQL for PostgreSQL that makes enabling PITR an even easier decision: for instances with Point-in-Time Recovery newly-enabled, the write-ahead logs being stored for PITR operations (which are the transaction logs that are used to go back in time) will no longer consume disk storage space.  Instead, when you enable PITR for new instances, Cloud SQL will store transaction logs collected during the retention window in Google Cloud Storage, and retrieve them when you perform a restore.  Because transaction logs can grow rapidly when your database experiences a burst of activity, this move will help reduce the impact these bursts have on your provisioned disk storage.  These logs will be stored for up to seven days in the same Google Cloud region as your instance at no additional cost to you.  

PITR is enabled by default when you create a new Cloud SQL for PostgreSQL instance from the Google Cloud console, and transaction logs will no longer be stored on the instance for instances that have PITR newly enabled.  If you have already enabled PITR on your PostgreSQL instances, this enhancement will be rolled out to your instances at a later point.  If you want to take advantage of this change sooner, you can first disable and then re-enable PITR on your instance (which will reset your ability to perform a point-in-time restore to the time at which PITR was re-enabled).  On instances with this feature enabled, you’ll notice that consumed storage on your instance will reduce relative to the volume of write-ahead logs (WAL) generated by your instance.  The actual amount of storage your logs consume will vary by instance and by database activity - during busy times for your database, log size may shrink or grow.  However, these logs will now only be stored on your instance long enough to successfully replicate to any replicas of the instance and to ensure that they are safely written to Cloud Storage; afterwards, they will be removed from your instance.

We’re excited to continue to enhance Cloud SQL for PostgreSQL to ensure that disaster recovery is easy to enable, cost effective, and seamless to use.  Learn more about this change in our documentation.