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.

Before you begin

Before completing this task, you must have:

  • Enabled point-in-time recovery and automated backups for the instance.

Enabling point-in-time recovery

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Select the instance for which you want to enable point-in-time recovery.
  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 under backupConfiguration, 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]
    

    Under backupConfiguration, look for pointInTimeRecoveryEnabled: true.

REST

Before using any of the request data below, 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://www.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. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  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

Before using any of the request data below, 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://www.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. Go to the Cloud SQL Instances page in the Google Cloud Console.

    Go to the Cloud SQL Instances page

  2. Select the instance for which you want to disable point-in-time recovery.
  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]
    

    Under backupConfiguration, look for pointInTimeRecoveryEnabled: false.

REST

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

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL:

PATCH https://www.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. To avoid unexpected storage issues, we recommend enabling automatic storage increases when using PITR.

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.