Overview of backups

This page describes how backups of your Cloud SQL instance work. It explains how you can use backups to restore your data to the same or another instance.

For step-by-step directions for scheduling backups or creating an on-demand backup, see Creating and Managing On-Demand and Automatic Backups.

For an overview of how to restore data to an instance from the backup, see Restore from a backup.

What backups provide

Backups help you restore lost data to your Cloud SQL instance. You can also restore an instance that is having problems from a backup. Enable automated backups for any instance that contains necessary data. Backups protect your data from loss or damage.

What backups cost

Cloud SQL retains up to 7 automated backups, plus all on-demand backups, for an instance. The storage used by backups is charged at a reduced rate. See the pricing page for more information.

Backups versus exports

Backups are managed by Cloud SQL according to retention policies, and are stored separately from the Cloud SQL instance. Cloud SQL backups differ from an export uploaded to Cloud Storage, where you manage the lifecycle. Backups encompass the entire database. Exports can select specific contents.

About backup size

Cloud SQL backups are incremental. They contain only data that changed after the previous backup was taken. Your oldest backup is a similar size to your database, but the sizes of subsequent backups depend on the rate of change of your data. When the oldest backup is deleted, the size of the next oldest backup increases so that a full backup still exists.

Types of backups

Cloud SQL performs two types of backups:

On-demand backups

You can create a backup at any time. This could be useful if you are about to perform a risky operation on your database, or if you need a backup and you do not want to wait for the backup window. You can create on-demand backups for any instance, whether the instance has automatic backups enabled or not.

On-demand backups are not automatically deleted the way automated backups are. They persist until you delete them or until their instance is deleted. Because they are not automatically deleted, on-demand backups can have a long-term effect on your billing charges if you do not delete them.

To view the status of an on-demand backup operation:

  1. Use the gcloud sql operations list command to get the operation ID
  2. Use the gcloud sql operations describe command to get the operation's status.
or
  1. Use the REST operations.list API call to get the operation ID
  2. Use the REST operations.get API call to get the operation's status.

Automated backups

Automated backups use a 4-hour backup window. The backup starts during the backup window. When possible, schedule backups when your instance has the least activity.

Automated backups occur every day when your instance was running at any time in the past 36 hours and by default up to seven most recent backups are retained. Automated backups are halted if your instance has been stopped for more than 36 hours. Backup and transaction log retention values can be changed from the default setting. Learn more

Where backups are stored

Backups locations include:

  • Default locations that Cloud SQL selects, based on the location of the original instance
  • Custom locations that you choose when you do not want to use the default location

Default backup locations

By default, Cloud SQL stores backup data in two regions for redundancy. One region can be the same region that the instance is in. The other is a different region. If there are two regions in a continent, the backup data remains on the same continent. Because there is only one region in Australia, backup data from the Sydney region is stored in a location in Asia. For the São Paulo region, backup data is stored in a US-based location.

Custom backup locations

Cloud SQL lets you select a custom location for your backup data. This is useful if your organization needs to comply with data residency regulations that require you to keep your backups within a specific geographic boundary. If your organization has this type of requirement, it probably uses a Resource Location Restriction organizational policy. With this policy, when you try to use a geographic location that does not comply with the policy, you see an alert on the Backups page. If you see this alert, you need to change the backup location to a location the policy allows.

For a complete list of valid regional values, see Instance Locations. For a complete list of multi-regional values, see Multi-regional locations.

See Setting a custom location for backups and Viewing locations for backups.

Automated backup and transaction log retention

Automated backups are used to restore a Cloud SQL instance. A combination of automated backups and transaction logs are used to perform a point-in-time recovery.

While transaction logs are counted in days, automated backups are not guaranteed to occur on a day boundary. Different units are used for these retention settings. Automated backup retention is a count and can be set from one to 365 backups. Transaction log retention is in days and can be set from one to seven. The default value for both is seven.

The lower bounds are useful for test instances, because logs and backups are deleted faster. For transaction logs, disk size doesn't grow as much with lower bounds. Using the higher values for automated backups retention let you restore from further back in time.

See Setting automated backup retention.

See Setting transaction log retention.

Can I export a backup?

No, you can't export a backup. You can only export instance data. See Exporting data from Cloud SQL.

About the special backup user

Cloud SQL creates a special database user, cloudsqladmin, for each instance, and generates a unique instance-specific password for it. Cloud SQL logs in as the cloudsqladmin user to perform automated backups.

How backups affect instance operations

Writes and other operations are unaffected by backup operations.

Troubleshooting

Click the links in the table for details:

For this problem... The issue might be... Try this...
Can't see current operation status. The user interface only shows success or failure. Use these database commands to find out more.
Can't find the operation originator. The user interface doesn't show who started an operation. Use audit logging to find out.
Out of disk space during automated backup. Instance reached hard disk space limits. Check the file system size and quota.
Can't do backup after instance deleted. Instance was deleted. Recreate from an export, or contact customer support if within the grace period.
Automated backup seems stuck. Backup time is correlated with database size. Contact customer support if you really need to cancel the operation.
Restore fails. Dump file may contain database users who do not yet exist. Create the database users before restoring.
Operation isn't valid for this instance. Destination instance size is smaller than the source. Increase the destination instance size.
Increase number of days to keep automated backups. Only seven automated backups are retained. Manage your own manual backups.
Unknown error in backup failure. Backup might have timed out. Check these flags.
Not notified about backup failure. There is no default notification. Set up custom alerts.
Instance is stuck cycling between failure and backup restore states. Too much traffic or too many open connections. Verify autovacuum settings and retry logic.
Data is missing in a backup. Unlogged tables are not included in backups. Don't use unlogged tables.

Can't see current operation status

You can't see the status of an operation in the Google Cloud Console.

The issue might be

The Google Cloud Console reports only success or failure when done, and is not designed to return warnings.

Things to try

Connect to the database and run SHOW WARNINGS.


Can't find the operation originator

You want to find out who issued an on-demand backup operation.

The issue might be

The instance operations page in the Google Cloud Console does not show who initiated an operation.

Things to try

Look in the logs and filter by text to find the user. You may need to use audit logs for private information. Relevant log files include:

  • cloudsql.googleapis.com/postgres.log
  • cloudaudit.googleapis.com/activity may also be available, if Cloud Audit Logs is enabled.


Out of disk space during automated backup

You see the error message [ERROR] InnoDB: Write to file ./ibtmp1 failed at offset XXXX, YYYY bytes should have been written, only 0 were written.

The issue might be

The instance reached a hard limit during an automated backup. Temporary files can expand beyond available disk space during a backup.

Things to try

Check that the disk is not full or out of disk quota. You can either manually increase the disk size or enable auto storage increase.


Can't do backup after instance deleted

You can't do a backup after deleting the instance.

The issue might be

Instance was deleted.

Things to try

  • The grace period for a Cloud SQL instance purge is four days. During this time, customer support can recreate the instance. After instances are purged, no data recovery is possible.
  • If you have done an export, you can create a new instance and then do an import to recreate the database. Exports are written to Cloud Storage and imports are read from there.

Automated backup is stuck

Automated backup is stuck for many hours and can't be canceled.

The issue might be

Backups can take a long time depending on the database size.

Things to try

If you really need to cancel the operation, you can ask customer support to force restart the instance.


Restore from backup fails

A restore operation can fail when one or more users referenced in the SQL dump file do not exist.

The issue might be

Before restoring a SQL dump, all the database users who own objects or were granted permissions on objects in the dumped database must exist. If they do not, the restore fails to recreate the objects with the original ownership and/or permissions.

Things to try

Create the database users before restoring from the SQL dump.


Operation isn't valid for this instance

You see the error message HTTP Error 400: This operation isn't valid for this instance from an API call to instances.restoreBackup.

The issue might be

You cannot restore from a backup of an instance with a storage size (XX GB) that is smaller than the backup size (YY GB).

Things to try

Edit the target instance to increase its storage size.


Instance is stuck cycling between failure and backup restore states

An instance is repeatedly failing because it is cycling between the failure and backup restore states. Attempts to connect to and use the database following restore fail.

The issue might be

  • There could be too many open connections. Too many connections can result from errors that occur in the middle of a connection where there are no autovacuum settings to clean up dead connections.
  • Cycling can occur if any custom code is using retry logic that doesn't stop after a few failures.
  • There could be too much traffic. Use connection pooling and other best practices for connectivity.

Things to try

  1. Verify that the database is set up for autovacuum.
  2. Check if there is any connection retry logic set up in custom code.
  3. Turn down traffic until the database recovers and then slowly turn traffic back up.

Data is missing in a backup

You find you are missing data when performing a backup/restore operation.

The issue might be

Tables were created as unlogged. For example:

CREATE UNLOGGED TABLES.

These tables are not included in a backup.

Things to try

Don't use unlogged tables. Remove UNLOGGED from the CREATE TABLE command.


Increase number of days to keep automated backups

You want to increase the number of days that you can keep automatic backups from seven to 30 days, or longer.

The issue might be

Only seven backups are retained. Backups get pruned regularly due to the cost and size of retaining backups. Unfortunately, this means that the currently visible backups are the only automated backups you can restore from.

Things to try

To keep backups indefinitely, you can create an on-demand backup yourself, as they are not deleted in the same way as automated backups. On-demand backups remain indefinitely. That is, they remain until they are deleted or the instance they belong to gets deleted. Since that type of backup is not deleted automatically, it can affect billing.


Unknown error in backup failure

Backup failed and you see Unknown error.

The issue might be

The backup creation reaching the ten minute timeout. There is a ten minute timeout set on the automated backup, and the backup is supposed to finish in that time.

Things to try

There are two flags that influence the backup creation: checkpoint_timeout and checkpoint_completion_target. At the start of the backup a slow checkpoint is run and it takes checkpoint_completion_target multiplied by checkpoint_timeout.

For example, 900 sec * 0.9 sec = 810 sec = 13.5 min. For this reason a timeout occurs. Decreasing the value of the checkpoint_completion_target fixes the issue in this case.

Not notified about backup failure

An automated backup failed and you received no notification.

The issue might be

When an automated backup fails, an Operation error message appears in the Cloud SQL instance's Details page. Email notifications are not sent out in the event of a backup failure.

Things to try

You can create a Monitoring alert or use Error reporting notifications to set up your own custom notifications.

What's next