Point-in-time recovery

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

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

Point-in-time recovery is enabled by default when you create a new Cloud SQL instance.

Enabling point-in-time recovery

Console

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

    Go to Cloud SQL Instances

  2. Click the instance name to open its Overview page.
  3. Click Edit.
  4. In the Backups, recovery, and high availability section, select Automate backups and Enable point-in-time recovery.
  5. Click Save.
  6. In the Instance details page for the instance, pointInTimeRecoveryEnabled is displayed as true.

gcloud

  1. Display the instance details:
    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
    

    You 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
    
    
  4. Confirm your change:
    gcloud sql instances describe INSTANCE_NAME
    

    In the backupConfiguration section, look for pointInTimeRecoveryEnabled: true.

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:

Performing the 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 Clone.
  3. In the Create a clone window, update the name of the new instance, if needed.
  4. Select Clone from earlier time.
  5. Enter a point-in-time recovery time.
  6. Click Create clone.

gcloud

  • Create a clone of a PostgreSQL instance using PITR
    gcloud sql instances clone SOURCE_INSTANCE_NAME NEW_INSTANCE_NAME \
    --point-in-time TIMESTAMP
          
  • TIMESTAMP UTC timezone in RFC 3339 format, for example, 2012-11-15T16:19:00.094Z. TIMESTAMP indicates the time to which you want to recover the state of the database.

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 from

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 from

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:

Disabling point-in-time recovery

Console

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

    Go to Cloud SQL Instances

  2. Click the instance name to open its Overview page.
  3. Click Edit.
  4. In the Backups, recovery, and high availability section, deselect Enable point-in-time recovery.
  5. Click Save.
  6. In the Instance details page for the instance, pointInTimeRecoveryEnabled is displayed as false.

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, look for pointInTimeRecoveryEnabled: false.

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:

Disk usage and point-in-time recovery

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

If the size of your write-ahead logs are causing an issue for your instance:

  • You can increase the instance storage size, but the write-ahead log size increase in disk usage might be temporary.

  • We recommend enabling automatic storage increase to avoid unexpected storage issues.

  • To delete the logs and recover storage, you can disable point-in-time recovery. Note, however, that decreasing the storage used does not 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 number of backups to one more than the days of log retention to guarantee a minimum of specified days of log retention.

Setting transaction log retention

To set the number of days to retain write-ahead 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. Click the instance name to open its Overview page.
  3. Select Backups from the SQL navigation menu.
  4. Click Edit.
  5. In the Enable point-in-time recovery section, expand Advanced options.
  6. Enter the number of days of logs to store at a time.
  7. Click Save.

gcloud

Edit the instance to set the number of days to retain write-ahead logs:

gcloud sql instances patch instance-name 
--retained-transaction-log-days=days-to-retain

How many 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.

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: