This page describes how backups of your Cloud SQL instance work. You canperform backups on your primary instance.

For step-by-step directions for scheduling or managing backups, seeCreate and manage on-demand and automatic backups.

For an overview of how to restore data to an instance from the backup, seeOverview of restoring an instance.

Note: This page contains features related to Cloud SQL editions. For more information about Cloud SQL editions, seeIntroduction to Cloud SQL editions.

What backups provide

Backups help you restore lost data to your Cloud SQL instance.Additionally, if an instance is having a problem, you can restore it to aprevious state by using the backup to overwrite it. Enable automated backups forany instance that contains necessary data. Backups protect your data from lossor damage.

Note: Backup and recovery is only applicable to primary instances.Enabling automated backups, along with transaction logging, is also required forsome operations, such as clone and replica creation.

What backups cost

By default, Cloud SQL retains 7 automatedbackups for each Cloud SQL Enterprise edition instance and 15 automated backups for each Cloud SQL Enterprise Plus edition instance , in addition to on-demand backups. You canconfigure how many automated backups to retain (from 1to 365). Wecharge a lower rate for backup storage than for other types of instances.

As part of deleting an instance, you can take afinal backup of your data. This way, you can recreate any instances that you delete. However, if you don't take a final backup, then after you delete an instance, Cloud SQL deletes all backups. For more information, seeRecovery backups.

Seethe pricing pagefor more information.

Backups versus exports

Backups are managed by Cloud SQL according to retention policies, and arestored separately from the Cloud SQL instance. Cloud SQL backupsdiffer from anexport uploadedto Cloud Storage, where you manage the lifecycle. Backups encompass theentire database. Exports can select specific contents.

Backup and restore operations can't be used to upgrade a database to a laterversion. You can only restore from a backup to an instance with the samedatabase version.

To upgrade to a later version, consider using theDatabase Migration Service orexporting and then importing yourdatabase to a new Cloud SQL instance.

About the backup size

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

You cancheck the size of an individual backup. The backup size represents the billable size for each backup.

Types of backups

Cloud SQL performs three types of backups:

• On-demand backups
• Automated backups
• Final backups
• Retained backups

On-demand backups

You can create a backup at any time. This could be useful if you are about toperform a risky operation on your database, or if you need a backup and you donot want to wait for the backup window. You can create on-demand backups forany 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. Becausethey are not automatically deleted, on-demand backups can have a long-termeffect on your billing charges.

Automated backups

Automated backups are taken daily, within a 4-hour backup window. Thebackup starts during the backup window. When possible, schedule backupswhen your instance has the least activity.

We recommend that you don't delete any automated backups because they're needed to supportpoint-in-time recovery.

During the backup window, automated backups occur every day your instance isrunning. One additional automated backup is taken after your instance is stoppedto safeguard all changes prior to the instance stopping. Up to seven most recentbackups are retained, by default.You canconfigure how many automated backups to retain,from 1 to 365.Backup and transaction log retention values can be changed from thedefault setting.Learn more.

Final backups

Final backups allow you to take a backup of your Cloud SQL instance before you delete the instance. This is useful to retain the instance data after you delete the instance. You can use the final backup later to either create an instance or to restore to an existing instance. For more information about accessing and viewing details about your final backup, seeView a list of final backups.

By default, Cloud SQL retains the final backup for 30 days. However, you can customize how long Cloud SQL retains the backup, from 1 day to 365 days. You can thenrestore the instance from the backup as long as it's available. Final backups are charged similar to other backups for the number of days retained.

Unlike automated backups and on-demand backups, which are associated with an instance and are available only when the instance exists, you can view and use final backups for restore operations after Cloud SQL deletes the instance.

Note: For managing final backups, Cloud SQL uses a new set of APIs. There's no change to the permissions for managing these backups. For more information on using final backups, seeManage final backups.

Retained backups

Retained backups are backups that are retained by Cloud SQL after aninstance is deleted. These backups consist of on-demand backups andautomated backups created when the instance was live. When you delete aninstance, these backups become independent of your instance and are storedat the project level. Retained backups are different fromfinal backups,which are the last backups taken at time of instance deletion.

You can update the description of these backups to makeit easier to manage them in your Google Cloud project. Retained backups can berestored to a new or existing Cloud SQL instanceat any time.

For these backups, the retention period is defined by the type of backup it isand can't be changed after the instance is deleted.On-demand backups are kept indefinitely until either thebackup is manually deleted, or the project containing the backup is deleted.Automated backups are deleted on a rolling basis, onebackup per day, after the instance is deleted. The rolling period is definedbased on theretention settingof the instance prior to deletion. For example, if your instance'sautomated backup retention setting was set to 7, then the latestautomated backup is deleted 7 days after the instance deletion.

Retained backups can be deleted manually at any time. However, when you deletea retained backup, the deleted backups can't be recovered.

Since instance names can be used after an instance is deleted in Cloud SQL,retained backups are stored in your Google Cloud project with a fieldcalledinstance_deletion_time. This field allows you to identify whethera particular backup belongs to a live or deleted instance. You can also updatethe description of a backup to make it easier to manage them.

For more information on how to enable retained backups for your newor existing instances, seeManage retained backups. Formore information on how to restore an instance from a retained backup, seeRestore from a retained backup.

Where backups are stored

Backups locations include:

• Default locations that Cloud SQLselects, based on the location of the original instance.
• Custom locations that you choose when you do notwant to use the default location.

Default backup locations

If you do not specify a storage location, your backups are stored in the multiregion that isgeographically closest to the location of your Cloud SQL instance. For example, if yourCloud SQL instance is inus-central1, your backups are stored in theus multi-region by default. However, a default location likeaustralia-southeast1 is outside of a multi-region. The closest multi-region isasia.

Note: When restoring data from a backup, restore it to an instance in aregion that's available. To view a list of all backups for an instance in a region that'sundergoing an outage, use the- wildcard with thegcloud sql backups list --instancecommand or thebackupRuns.list API. For more information, seeViewing a list of backups during an outage.You can then restore the data from the backup to a new or existing instance in a region that's not undergoing an outage.

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 aResource 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 theBackups page. If you see this alert, you need to change the backup location to a location the policy allows.

When selecting a custom location for a backup, consider the following:

• Cost: one cluster in your instance may be in a lower-cost region than the others.
• Proximity to your application server: you might want to store the backup as close to your serving application as possible.
• Storage utilization: you need enough storage space to keep your backup as it grows in size. Depending on your workload, you might have clusters of different sizes or with different disk usages. This might factor into which cluster you choose.

For a complete list of valid regional values, seeInstance Locations. For a complete list of multi-regional values, seeMulti-regional locations.

For more information about setting locations for backups and seeing the locations of backups taken for an instance, seeSet a custom location for backups andView backup locations.

Note:If you change the location where backups are stored, existing backups remain in their original location.

Automated backup and transaction log retention

Automated backups are used torestorea Cloud SQL instance. A combination of automated backups and transactionlogs are used toperform a point-in-time recovery.

Automated backups can be retained for up to a year by configuring theretention period whereas on-demand backups persist until you delete the backupsor until your instance is deleted.

While transaction logs are counted in days, automated backups are notguaranteed to occur on a day boundary. Different units are used for theseretention settings. Automated backup retention is a count and can be set from1 to 365 backups.

Transaction log retention is in days. For Cloud SQL Enterprise Plus edition instances, the range is from 1 to 35 days, with a default of 14 days. For Cloud SQL Enterprise edition instances, the range is from 1 to 7 days, with a default of 7 days. For both Cloud SQL Enterprise Plus edition and Cloud SQL Enterprise edition instances, the transaction log retention setting must be less than the backup retention setting.

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

Note: The transaction log retention setting must alwaysbe less than or equal to the backup retention setting.

Transaction logs older than the last backup are automatically deleted.

Logs are purged once daily, not continuously. When the number of days of logretention is the same as the number of backups, insufficient log retention canresult. For example, setting log retention to seven days and backup retention toseven backups means that between six and seven days of logs will be retained.

We recommend setting the number of backups to at least one more than the days oflog retention to guarantee a minimum of specified days of log retention.

High write activity to the database can generate a large volume of transactionlogs, which can consume significant disk space, and lead to disk growth forauto storage increase enabled instances. We recommend sizing instance storageto account for transaction log retention.

SeeSetting automated backup retention.

SeeSetting transaction log retention.

Can I export a backup?

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

About the special backup user

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

How backups affect instance operations

Writes and other operations are unaffected by backup operations.

Backup rate limitations

Cloud SQL limits the rate for backup operations on the data disk. You are allowed a maximum of five backup operations every 50 minutes perinstance per project. If a backup operation fails, it does not count towardsthis quota. If you reach the limit, the operation fails with an errormessage that tells you when you can retry.

Let's take a look at how Cloud SQL performsrate limiting for backups.

Cloud SQL uses tokens from a bucket to determine how many backup operationsare available at any one time. Each instance has a bucket. There's a maximum offive tokens in the bucket that you can use for backup operations. Every 10minutes, a new token is added to the bucket. If the bucket is full, the token overflows.

Each time you issue a backup operation, a token is granted from the bucket. Ifthe operation succeeds, the token is removed from the bucket. If it fails, thetoken is returned to the bucket. The following diagram shows how this works:

Backup and data integrity checks

Cloud SQL performs background database integrity checks automaticallyto identify any potential data integrity issues. These checks are done asoffline processes by restoring a sampling of customer-initiated backups orrecovery backups.

Recovery backups

Before you delete an instance, you can take afinal backup ofyour data. You can also enableretained backups prior to deleting your instanceto retain all automatic and on-demand backups. For more information,seeManage retained backups.

You can restore from a retained or final backup to a new instance, anexisting instance, an instance in a different project, or a new instance inanother region. For more information,seeRestore an instance.

Note: Restoring from a final or retained backup doesn't retain the IP addressof the deleted instance. To restore your instance, including the IP address inthe case of accidental deletion, Cloud SQL retains thebackups of deleted instances for four days. To recover a deleted instance to theexact state at the time of deletion, contactGoogle Cloud Customer Care within four days.

Cloud SQL also attempts to retain at least one last good daily backupof every active instance, if there are no good backups available as part of theautomated backup policy. This backup can be used for recovery purposes bycontacting Google Cloud Customer Care.

Unlogged tables

Unlogged tables are automatically wiped during backup restore.

Troubleshooting

Issue	Troubleshooting
You can't see the current operation's status.	The Google Cloud console reports only success or failure when the operation is done. It isn't designed to show warnings or other updates. Run thegcloud sql operations list command to list all operations for the given Cloud SQL instance.
You want to find out who issued an on-demand backup operation.	The user interface doesn't show the user who started an operation. Look in thelogs 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 IfCloud Audit Logs is enabled and you have the required permissions to view them,cloudaudit.googleapis.com/activity may also be available.
After an instance is deleted, you can't take a backup of the instance.	If you delete an instance without taking afinal backup of the data, then no data recovery is possible. However, if you restore the instance, then Cloud SQL also restores the backups. For more information on recovering a deleted instance, seeRecovery backups. If you have done an export operation, create a new instance and then do an import operation to recreate the database. Exports are written to Cloud Storage and imports are read from there. Note:Cloud SQL recommends that you take a final backup of your data before you delete the instance. This way, you can recreate any instances that you delete accidentally without contactingCloud Customer Care.
An automated backup is stuck for many hours and can't be canceled.	Backups can take a long time depending on the database size. If you really need to cancel the operation, you can ask customer support toforce restart the instance.
A restore operation can fail when one or more users referenced in the SQL dump file don't exist.	Before restoring a SQL dump, all the database users who own objects or were granted permissions on objects in the dumped database must exist in the target database. If they don't, the restore operation fails to recreate the objects with the original ownership or permissions. Create the database users before restoring the SQL dump.
You want to increase the number of days that you can keep automatic backups from seven to 30 days, or longer.	You can configure the number of automated backups to retain, from 1 to 365. Automated backups get pruned regularly based on the retention value configured. Unfortunately, this means that the currently visible backups are the only automated backups you can restore from. To keep backups indefinitely, you cancreate an on-demand backup, as they are not deleted in the same way as automated backups. On-demand backups remain indefinitely. That is, they remain until they're deleted or the instance they belong to is deleted. Because that type of backup is not deleted automatically, it can affect billing.
An automated backup failed and you didn't receive an email notification.	To have Cloud SQL notify you of the backup's status,configure a log-based alert.
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.	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 noautovacuum 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 otherbest practices for connectivity. Things to try: Verify that the database is set up forautovacuum. Check if there is any connection retry logic set up in custom code. Turn down traffic until the database recovers and then slowly turn traffic back up.
You find you are missing data when performing a backup/restore operation.	Tables were created as unlogged. For example: CREATE UNLOGGED TABLE .... These tables are not included in a restore from a backup: The contents of unlogged tables doesn't survive failover on an HA instance. Unlogged tables don't survive postgres crashes. Unlogged tables are not replicated to read replicas. Unlogged tables are automatically wiped during backup restore. The solution is to avoid using unlogged tables if you want to restore those tables through a backup. If you're restoring from a database that already has unlogged tables, then you can dump the database to a file, and reload the data after modifying the dumped file toALTER TABLE toSET LOGGED on those tables.

What's next

• Schedule automated backups.
• Create an on-demand backup.
• Restore from a backup.