This page describes how to use point-in-time recovery (PITR) to restore your primary Cloud SQL instance.
To learn more about PITR, see Point-in-time recovery (PITR).
Log storage for PITR
On May 31, 2024, we launched storing transaction logs for PITR in Cloud Storage. Since this launch, the following conditions apply:All Cloud SQL instances that you create with PITR enabled before this date store the transaction logs that are used for PITR on disk.
All Cloud SQL instances created with PITR enabled on or after this date store these logs in Cloud Storage.
Transaction logs update
regularly and use storage space. Cloud SQL deletes transaction logs with their associated automatic backups automatically. This happens after the value set for thetransactionLogRetentionDays
parameter is met. For more information about this parameter, see Logs and disk usage.
For instances that store transaction logs only on disk, you can configure Cloud SQL to store the logs on Cloud Storage by first deactivating and then re-enabling PITR. You can't move logs from Cloud Storage back to disk.
To ensure that logs for your instance are stored in Cloud Storage instead of on disk, complete the following actions:
- Check whether your instance stores logs on disk or Cloud Storage.
- Check the network architecture of the instance. If the instance is on the old network architecture, then upgrade it to the new network architecture.
- If the size of your logs on disk is causing performance issues for your instance, then deactivate PITR and re-enable it.
Logs and disk usage
For instances having transaction logs stored in Cloud Storage, the logs are stored in the same region as the primary instance. This log storage (up to 35 days for Cloud SQL Enterprise Plus edition and seven days for Cloud SQL Enterprise edition, the maximum length for PITR) generates no additional cost per instance.
Cloud SQL generates transaction logs regularly and these logs use storage space. Cloud SQL deletes the transaction logs automatically with their associated automatic backups. This happens after the value set for the transactionLogRetentionDays
parameter is met. This parameter specifies the number of days that Cloud SQL retains transaction logs. For Cloud SQL Enterprise Plus edition, you can set the number of days of retained transaction logs from 1 to 35, and for Cloud SQL Enterprise edition, you can set this value from 1 to 7.
To find out how much disk the transaction logs use, check the bytes_used_by_data_type
metric for the instance. The value for the data type returns the size of the transaction logs on the disk. For instances that store transaction logs used for PITR on disk, Cloud SQL purges data from the disk daily to meet the transactionLogRetentionDays
PITR setting, as described in Automated backup retention.
If your instance has PITR enabled, and if the size of your transaction logs on disk is causing an issue for your instance:
- You can deactivate PITR and re-enable it to ensure that logs are stored in Cloud Storage in the same region as the instance. However, any existing logs are deleted, so you can't perform a point-in-time restore earlier than the time that you re-enabled PITR.
- You can increase the instance storage size, but the transaction log size increase in disk usage might be temporary.
- We recommend enabling automatic storage increases to avoid unexpected storage issues. This recommendation applies only if your instance has PITR enabled and your logs are stored on disk.
- If you want to delete logs and recover storage, then you can deactivate PITR without re-enabling it. However, decreasing the storage used doesn't shrink the size of the disk provisioned for the instance.
Logs are purged once daily, not continuously. Setting the log retention to two days means that at least two days of logs, and at most three days of logs, are retained. We recommend setting the number of backups to one more than the days of log retention.
For example, if you specify
7
for the value of thetransactionLogRetentionDays
parameter, then for thebackupRetentionSettings
parameter, set the number ofretainedBackups
to8
.
For more information about PITR, see Point-in-time recovery (PITR).
Database recovery model for PITR
When you enable PITR on an instance, Cloud SQL automatically sets the recovery model of the existing and subsequent databases to the full recovery model.
For more information about SQL Server recovery models, see the Microsoft documentation.
Enable PITR
When you create a new instance in the Google Cloud console, the Automated backups setting is automatically enabled.The following procedure enables PITR on an existing primary instance.
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Open the more actions menu for the instance you want to enable PITR on and click Edit.
- Under Customize your instance, expand the Data Protection section.
- Select the Enable point-in-time recovery checkbox.
- In the Days of logs field, enter the number of days to retain logs, from 1-35 for Cloud SQL Enterprise Plus edition, or 1-7 for Cloud SQL Enterprise edition.
- Click Save.
gcloud
- Display the instance overview:
gcloud sql instances describe INSTANCE_NAME
- If you see
enabled: false
in thebackupConfiguration
section, enable scheduled backups:gcloud sql instances patch INSTANCE_NAME \ --backup-start-time=HH:MM
Specify the
backup-start-time
parameter using 24-hour time in UTC±00 time zone. - Enable PITR:
gcloud sql instances patch INSTANCE_NAME \ --enable-point-in-time-recovery
If you're enabling PITR on a primary instance, you can also configure the number of days for which you want to retain transaction logs by adding the following parameter:
--retained-transaction-log-days=RETAINED_TRANSACTION_LOG_DAYS
- Confirm your change:
gcloud sql instances describe INSTANCE_NAME
In the
backupConfiguration
section, you seepointInTimeRecoveryEnabled: true
if the change was successful.
REST v1
Before using any of the request data, make the following replacements:
- PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
- INSTANCE_NAME: the name of the primary or read replica instance that you're configuring for high availability
- START_TIME: the time (in hours and minutes)
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME
Request JSON body:
{ "settings": { "backupConfiguration": { "startTime": "START_TIME", "enabled": true, "pointInTimeRecoveryEnabled": true } } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
REST v1beta4
Before using any of the request data, make the following replacements:
- PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
- INSTANCE_NAME: the name of the primary or read replica instance that you're configuring for high availability
- START_TIME: the time (in hours and minutes)
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME
Request JSON body:
{ "settings": { "backupConfiguration": { "startTime": "START_TIME", "enabled": true, "pointInTimeRecoveryEnabled": true } } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
Perform PITR
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Open the more actions menu for the instance you want to recover and click Create clone.
- Optionally, on the Create a clone page, update the ID of the new clone.
- Select Clone from an earlier point in time.
- Enter a PITR time.
- Select All databases, or specify a database name.
If you specify a database name, only one may be selected. By default, PITR is for all databases. - Click Create clone.
gcloud
Create a clone using PITR.
Replace the following:
- SOURCE_INSTANCE_NAME - Name of the instance you're restoring from.
- NEW_INSTANCE_NAME - Name for the clone.
- TIMESTAMP - UTC timezone for the source instance in RFC 3339 format. For example, 2012-11-15T16:19:00.094Z.
gcloud sql instances clone SOURCE_INSTANCE_NAME \ NEW_INSTANCE_NAME \ --point-in-time 'TIMESTAMP'
REST v1
Before using any of the request data, make the following replacements:
- project-id: The project ID
- target-instance-id: The target instance ID
- source-instance-id: The source instance ID
- restore-timestamp The point-in-time to restore up to
In the JSON request, you optionally can specify up to one specific database name as
the following: "databaseNames": "my-database"
HTTP method and URL:
POST https://sqladmin.googleapis.com/v1/projects/project-id/instances/source-instance-id/clone
Request JSON body:
{ "cloneContext": { "kind": "sql#cloneContext", "destinationInstanceName": "target-instance-id", "pointInTime": "restore-timestamp" } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
REST v1beta4
Before using any of the request data, make the following replacements:
- project-id: The project ID
- target-instance-id: The target instance ID
- source-instance-id: The source instance ID
- restore-timestamp The point-in-time to restore up to
In the JSON request, you optionally can specify up to one specific database name as
the following: "databaseNames": "my-database"
HTTP method and URL:
POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/source-instance-id/clone
Request JSON body:
{ "cloneContext": { "kind": "sql#cloneContext", "destinationInstanceName": "target-instance-id", "pointInTime": "restore-timestamp" } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
Deactivate PITR
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Open the more actions menu for the instance you want to deactivate and select Edit.
- Under Customize your instance, expand the Data Protection section.
- Clear Enable point-in-time recovery.
- Click Save.
gcloud
- Deactivate point-in-time recovery:
gcloud sql instances patch INSTANCE_NAME \ --no-enable-point-in-time-recovery
- Confirm your change:
gcloud sql instances describe INSTANCE_NAME
In the
backupConfiguration
section, you seepointInTimeRecoveryEnabled: false
if the change was successful.
REST v1
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "backupConfiguration": { "enabled": false, "pointInTimeRecoveryEnabled": false } } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
REST v1beta4
Before using any of the request data, make the following replacements:
- project-id: The project ID
- instance-id: The instance ID
HTTP method and URL:
PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id
Request JSON body:
{ "settings": { "backupConfiguration": { "enabled": false, "pointInTimeRecoveryEnabled": false } } }
To send your request, expand one of these options:
You should receive a JSON response similar to the following:
Check the storage location of transaction logs used for PITR
You can check where your Cloud SQL instance is storing the transaction logs used for PITR.
gcloud
To determine whether your instance stores logs for PITR on disk or Cloud Storage, use the following command:
gcloud sql instances describe INSTANCE_NAME
Replace INSTANCE_NAME with the name of the instance.
You can also check the storage location of the transaction logs for multiple instances in the same project. To determine the location for multiple instances, use the following command:
gcloud sql instances list --show-transactional-log-storage-state
Example response:
NAME DATABASE_VERSION LOCATION TRANSACTIONAL_LOG_STORAGE_STATE my_01 SQLSERVER_2019_STANDARD us-central-1 DISK my_02 SQLSERVER_2019_STANDARD us-central-1 CLOUD_STORAGE ...
In the output of the command, the transactionalLogStorageState
field or the TRANSACTIONAL_LOG_STORAGE_STATE
column provides
information about where the transaction
logs for PITR are stored for the instance.
The possible transaction log
storage states are the following:
DISK
: the instance stores the transaction logs used for PITR on disk.CLOUD_STORAGE
: the instance stores the transaction logs used for PITR in Cloud Storage.
Set transaction log retention
To set the number of days to retain transaction logs:
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Open the more actions menu for the instance you want to set the transaction log on and select Edit.
- Under Customize your instance, expand the Data Protection section.
- In the Enable point-in-time recovery section, expand Advanced options.
- Enter the number of days to retain logs, from 1-35 for Cloud SQL Enterprise Plus edition or 1-7 for Cloud SQL Enterprise edition.
- Click Save.
Edit the instance to set the number of days to retain
transaction logs. Replace the following: DAYS_TO_RETAIN: The number of days of transaction logs
to keep. For Cloud SQL Enterprise Plus edition, the valid range is between 1 and 35 days,
with a default of 14 days. For Cloud SQL Enterprise edition, the valid range is
between 1 and 7 days, with a default of 7 days. If no value is specified,
then the default value is used. This is valid only when PITR is enabled.
Keeping more days of transaction logs requires a bigger storage
size.
gcloud
gcloud sql instances patch INSTANCE_NAME \
--retained-transaction-log-days=DAYS_TO_RETAIN
Before using any of the request data,
make the following replacements:
DAYS_TO_RETAIN: the number of days to retain transaction logs. For Cloud SQL Enterprise Plus edition, the valid range is between 1 and 35 days, with a default of 14 days. For Cloud SQL Enterprise edition, the valid range is between 1 and 7 days, with a default of 7 days. If no value is specified, then the default value is used. This is valid only when PITR is enabled. Keeping more days of transaction logs requires a bigger storage size.
HTTP method and URL:
Request JSON body:
To send your request, expand one of these options: You should receive a JSON response similar to the following:REST v1
PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID
{
"settings":
{
"backupConfiguration":
{
"transactionLogRetentionDays": "DAYS_TO_RETAIN"
}
}
}
Before using any of the request data,
make the following replacements:
DAYS_TO_RETAIN: the number of days to retain transaction logs. For Cloud SQL Enterprise Plus edition, the valid range is between 1 and 35 days, with a default of 14 days. For Cloud SQL Enterprise edition, the valid range is between 1 and 7 days, with a default of 7 days. If no value is specified, then the default value is used. This is valid only when PITR is enabled. Keeping more days of transaction logs requires a bigger storage size.
HTTP method and URL:
Request JSON body:
To send your request, expand one of these options: You should receive a JSON response similar to the following:REST v1beta4
PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID
{
"settings":
{
"backupConfiguration":
{
"transactionLogRetentionDays": "DAYS_TO_RETAIN"
}
}
}
Troubleshoot
Issue | Troubleshooting |
---|---|
OR
|
The timestamp you provided is invalid. |
OR
|
The timestamp that you provided is for a time where backups or when binlog coordinates could not be found. |
What's next
- Configure flags on your clone