Use point-in-time recovery

This page describes how to use point-in-time recovery to restore your primary 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 write-ahead logging (WAL) archiving. By default, point-in-time recovery is enabled for Cloud SQL Enterprise Plus edition instances. All of these instances store their WAL logs in Cloud Storage for the number of log retention days that you specify.

On January 9, 2023, we launched storing WAL logs for point-in-time recovery in Cloud Storage. For Cloud SQL Enterprise edition instances, the following conditions apply for point-in-time recovery:

  • New instances that you create with point-in-time recovery enabled after this launch store their logs in Cloud Storage for the number of log retention days that you specify.
  • Existing instances for which you enable point-in-time recovery after this launch also store their logs in Cloud Storage.

  • Existing instances that had point-in-time recovery enabled before this launch continue to have their logs stored on disk.

To see whether an instance's logs are stored in Cloud Storage, check the bytes_used_by_data_type metric for the instance. If the value for the archived_wal_log data type is 0, the instance's logs are stored in Cloud Storage.

After you use a PostgreSQL client such as psql or pgAdmin to connect to a database of the instance, run the following command: show archive_command. If any WALs are archived in Cloud Storage, then you see -async_archive -remote_storage.

All other existing instances that have point-in-time recovery enabled continue to have their logs stored on disk. The change to storing logs in Cloud Storage is available at a later time.

If the logs are stored in Cloud Storage, then Cloud SQL uploads logs every five minutes or less. As a result, if a Cloud SQL instance is available, then the instance can be recovered to the latest time. However, if the instance isn't available, then the recovery point objective is typically five minutes or less. Use the API to check for the latest time to which you can restore the instance, and perform the recovery to that time.

The write-ahead logs used with point-in-time recovery are deleted automatically with their associated automatic backup, which generally happens after the value set for transactionLogRetentionDays is met. This is the number of days of transaction logs that Cloud SQL retains for point-in-time recovery. For Cloud SQL Enterprise Plus edition, the number of days of retained transaction logs can be set from 1 to 35, and for Cloud SQL Enterprise edition, the value can be set from 1 to 7.

When you restore a backup on a Cloud SQL instance before enabling point-in-time recovery, you lose the WAL logs that allow the operability of point-in-time recovery.

For customer-managed encryption key (CMEK)-enabled instances, write-ahead logs are encrypted using the latest version of the CMEK. To perform a restore, all versions of the key that were the latest for the number of days that you configured for the retained-transaction-log-days parameter should be available.

For instances having write-ahead 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 point-in-time recovery) generates no additional cost per instance.

If your instance has point-in-time recovery enabled, and if the size of your write-ahead logs on disk is causing an issue for your instance:

  • You can disable point-in-time recovery and re-enable it to ensure that new logs are stored in Cloud Storage. However, any existing write-ahead logs are deleted.

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

  • We recommend enabling automatic storage increase to avoid unexpected storage issues. This recommendation applies only if your instance has point-in-time recovery enabled and your logs are stored on disk.

  • You can disable point-in-time recovery if you want to delete logs and recover storage. Decreasing the write-ahead logs used doesn't shrink the size of the disk 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, both Automated backups and Enable point-in-time recovery are automatically enabled.

The following procedure enables point-in-time recovery on an existing primary 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-35 for Cloud SQL Enterprise Plus edition, or 1-7 for Cloud SQL Enterprise edition.
  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.

Terraform

To enable point-in-time recovery, use a Terraform resource.

cloud_sql/postgres_instance_pitr/main.tf 
resource "google_sql_database_instance" "postgres_instance_pitr" {
  name             = ""
  region           = "us-central1"
  database_version = "POSTGRES_14"
  settings {
    tier = "db-custom-2-7680"
    backup_configuration {
      enabled                        = true
      point_in_time_recovery_enabled = true
      start_time                     = "20:55"
      transaction_log_retention_days = "3"
    }
  }
  # set `deletion_protection` to true, will ensure that one cannot accidentally delete this instance by
  # use of Terraform whereas `deletion_protection_enabled` flag protects this instance at the GCP level.
  deletion_protection = false
}

Apply the changes

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

  1. Launch Cloud Shell.

  2. Set the default Google Cloud project where you want to apply your Terraform configurations.

    You only need to run this command once per project, and you can run it in any directory.

    export GOOGLE_CLOUD_PROJECT=PROJECT_ID

    Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

  1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf. 
    mkdir DIRECTORY && cd DIRECTORY && touch main.tf

  2. If you are following a tutorial, you can copy the sample code in each section or step.

    Copy the sample code into the newly created main.tf.

    Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

  3. Review and modify the sample parameters to apply to your environment.
  4. Save your changes.
  5. Initialize Terraform. You only need to do this once per directory. 
    terraform init

    Optionally, to use the latest Google provider version, include the -upgrade option: 

    terraform init -upgrade

Apply the changes

  1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations: 
    terraform plan

    Make corrections to the configuration as necessary.

  2. Apply the Terraform configuration by running the following command and entering yes at the prompt: 
    terraform apply

    Wait until Terraform displays the "Apply complete!" message.

  3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Delete the changes

To delete your changes, do the following:

  1. To disable deletion protection, in your Terraform configuration file set the deletion_protection argument to false. 
    deletion_protection =  "false"
  2. Apply the updated Terraform configuration by running the following command and entering yes at the prompt: 
    terraform apply

  1. Remove resources previously applied with your Terraform configuration by running the following command and entering yes at the prompt:

    terraform destroy

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:

Get the latest recovery time

For an available instance, you can perform point-in-time recovery to the latest time. If the instance is unavailable and the instance logs are stored in Cloud Storage, then you can retrieve the latest recovery time and perform the point-in-time recovery to that time. In both cases, you can restore the instance to a different zone by providing a value for the preferred zone.

gcloud

Get the latest time to which you can recover a Cloud SQL instance that's not available.

Replace INSTANCE_NAME with the name of the instance that you're querying.

gcloud sql instances get-latest-recovery-time INSTANCE_NAME

REST v1

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

  • PROJECT_ID: the project ID
  • INSTANCE_NAME: the name of the instance for which you're querying for the latest recovery time

HTTP method and URL: 

GET https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME/getLatestRecoveryTime

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "kind": "sql#getLatestRecoveryTime",
  "latestRecoveryTime": "2023-06-20T17:23:59.648821586Z"
}

REST v1beta4

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

  • PROJECT_ID: the project ID
  • INSTANCE_NAME: the name of the instance for which you're querying for the latest recovery time

HTTP method and URL: 

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME/getLatestRecoveryTime

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

{
  "kind": "sql#getLatestRecoveryTime",
  "latestRecoveryTime": "2023-06-20T17:23:59.648821586Z"
}

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

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

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 write-ahead logs:

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-35 for Cloud SQL Enterprise Plus edition or 1-7 for Cloud SQL Enterprise edition.
  6. Click Save.

gcloud

Edit the instance to set the number of days to retain write-ahead 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 35 (default: 15) for Cloud SQL Enterprise Plus edition and between 1 and 7 (default: 7)for Cloud SQL Enterprise edition. The default value is used 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