Use point-in-time recovery

This page describes how to use point-in-time recovery to restore your Cloud SQL instance.

To learn more about point-in-time recovery, see Point-in-time recovery.

Log storage for point-in-time recovery

Point-in-time recovery uses transaction logs. These logs update regularly and use storage space. The transaction logs are automatically deleted with their associated automatic backup, which generally happens after about 7 days.

If the size of your transaction logs are causing an issue for your instance:

  • 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.

  • You can disable point-in-time recovery if you want to delete logs and recover storage. Decreasing the storage used doesn't shrink the size of the storage provisioned for the instance.

  • Logs are purged once daily, not continuously. Setting 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 to guarantee a minimum of specified days of log retention.

Enable point-in-time recovery

When you create a new instance in the Google Cloud console, the Automated backups setting is automatically enabled.

The following procedure enables point-in-time recovery on an existing instance.

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Open the more actions menu More actions icon. for the instance you want to enable point-in-time recovery on and click Edit.
  3. Under Customize your instance, expand the Data Protection section.
  4. Select the Enable point-in-time recovery checkbox.
  5. Expand Advanced options.
  6. Enter the number of days to retain logs, from 1-7.
  7. Click Save.

gcloud

  1. Display the instance overview: 
    gcloud sql instances describe INSTANCE_NAME
  2. If you see enabled: false in the backupConfiguration 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.

  3. Enable point-in-time recovery: 
    gcloud sql instances patch INSTANCE_NAME \
--enable-point-in-time-recovery

    If you're enabling point-in-time recovery 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
  4. Confirm your change: 
    gcloud sql instances describe INSTANCE_NAME

    In the backupConfiguration section, you see pointInTimeRecoveryEnabled: true 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
  • start-time The time in the format "HH:MM"

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id

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 project ID
  • instance-id: The instance ID
  • start-time The time in the format "HH:MM"

HTTP method and URL: 

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

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 point-in-time recovery

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Open the more actions menu More actions icon. for the instance you want to recover and click Create clone.
  3. Optionally, on the Create a clone page, update the ID of the new clone.
  4. Select Clone from an earlier point in time.
  5. Enter a point-in-time recovery time.
  6. Select All databases, or specify a database name.
    If you specify a database name, only one may be selected. By default, point-in-time recovery is for all databases.
  7. Click Create clone.

gcloud

Create a clone using point-in-time recovery.

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:

Disable point-in-time recovery

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Open the more actions menu More actions icon. for the instance you want to disable and select Edit.
  3. Under Customize your instance, expand the Data Protection section.
  4. Clear Enable point-in-time recovery.
  5. Click Save.
  6. In the Overview page for the instance, under Configuration, the point-in-time recovery setting is listed as disabled.

gcloud

  1. Disable point-in-time recovery: 
    gcloud sql instances patch INSTANCE_NAME \
--no-enable-point-in-time-recovery
  2. Confirm your change: 
    gcloud sql instances describe INSTANCE_NAME

    In the backupConfiguration section, you see pointInTimeRecoveryEnabled: 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:

Set transaction log retention

To set the number of days to retain transaction logs (from 1 to 7):

Console

  1. In the Google Cloud console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Open the more actions menu More actions icon. for the instance you want to set the transaction log on and select Edit.
  3. Under Customize your instance, expand the Data Protection section.
  4. In the Enable point-in-time recovery section, expand Advanced options.
  5. Enter the number of days to retain logs, from 1-7.
  6. Click Save.

gcloud

Edit the instance to set the number of days to retain transaction logs.

Replace the following:

  • INSTANCE-NAME - The name of the instance you want to set the transaction log on.
  • DAYS-TO-RETAIN - The number of days of transaction logs to keep. The valid range is between 1 and 7. The default value is 7 if not specified. Only valid when point in time recovery is enabled. Keeping more days of transaction logs requires bigger storage size.
gcloud sql instances patch INSTANCE-NAME 

--retained-transaction-log-days=DAYS-TO-RETAIN

REST v1beta4

Before using any of the request data, make the following replacements:

  • days-to-retain: The number of days to retain transaction logs, from 1 to 7
  • 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":
    {
      "transactionLogRetentionDays": "days-to-retain"
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

What's next