Shrink instance storage capacity

This page shows you how to manage the storage capacity of a Cloud SQL instance using storage shrink capabilities.

Before you begin

  1. Review the feature's limitations.

  2. If you're using gcloud CLI, make sure you are using the latest version of Google Cloud SDK and gcloud CLI.

  3. Get the following IAM permissions:

    • cloudsql.instances.getDiskShrinkConfig
    • cloudsql.instances.performDiskShrink

    These permissions are available in either of the following roles. When assigning your role, we recommend applying the principle of least privilege:

    • Cloud SQL Admin (roles/cloudsql.admin)
    • Cloud SQL Editor (roles/cloudsql.editor)

    For more information, see the following resources:

Prepare the instance

Complete the following requirements before you begin.

These steps are the same for primary instances and read replicas.

If updating a read replica, make sure to shrink the capacity of the primary instance first, then shrink the size of the replica.

  1. Get the project ID of the project containing the instance you want to manage.

  2. Check the current state of the instance to make sure it is running. One way to check is to use the describe command:

      gcloud sql instances describe

    Returns the following status:

      RUNNABLE

    If not, start the instance.

  3. Create a backup of the current instance. This lets you restore the instance later if you run into issues during configuration.

  4. If you are running a PostgreSQL instance, set the max_wal_size flag value to less than 5128 MB. If the max_wal_size value is too large, the instance might be slow to start and the operation might fail.

  5. Disable all extensions or features that enable outbound connections (or require outbound data transfer). For example, an outbound connection might be established using one of these extensions or features, and should be disabled before performing a storage shrink operation:

    • dblink extension
    • PL/proxy extension
    • pglogical extension
    • Foreign data wrappers (such as postgres_fdw, oracle_fdw, and tds_fdw)
    • Logical replication in PostgreSQL 10 and later

Shrink the storage capacity of an instance

The following steps are the same for primary instances and read replicas.

  1. Check the target minimum storage capacity of the instance and the estimated operation time. This information can help you plan your storage shrink operation.

    gcloud

    Run the gcloud sql instances get-storage-shrink-config command:

    gcloud sql instances get-storage-shrink-config INSTANCE_NAME

    Replace the following:

    • INSTANCE_NAME: the name of the primary instance.

    The response includes a value for minimalTargetSizeGb. You can use this value to determine the value you want to use for the --storage-size parameter when you run a storage shrink operation.

    The response also returns a message with an estimated operation time, similar to the following:

    ...
"The estimated operation time is 20 minutes".

    REST v1

    Use the GET method to view the instance's target minimum storage capacity.

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

    • PROJECT_ID: the name of the project where the primary instance resides.
    • INSTANCE_ID: the instance ID of the primary instance.

    HTTP method and URL: 

    GET https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID/getDiskShrinkConfig

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    The response includes a value for min_target_size_gb. You can use this value to determine the value you want to use for the --storage-size parameter when you run a storage shrink operation.

    The response also returns a message with an estimated operation time, similar to the following:

    ...
"The estimated operation time is 20 minutes".

    REST v1beta4

    Use the GET method to view the instance's target minimum storage capacity.

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

    • PROJECT_ID: the name of the project where the primary instance resides.
    • INSTANCE_ID: the instance ID of the primary instance.

    HTTP method and URL: 

    GET https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/getDiskShrinkConfig

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    The response includes a value for min_target_size_gb. You can use this value to determine the value you want to use for the --storage-size parameter when you run a storage shrink operation.

    The response also returns a message with an estimated operation time, similar to the following:

    ...
"The estimated operation time is 20 minutes".

  2. Shrink storage capacity of the instance.

    gcloud

    Run the following command to shrink storage on the instance:

    gcloud sql instances perform-storage-shrink INSTANCE_NAME \
  --storage-size=TARGET_STORAGE_SIZE --async

    Replace the following:

    • INSTANCE_NAME: the name of the instance.
    • TARGET_STORAGE_SIZE: the target storage capacity in GB. For example, 100 GB.

    We recommend using the --async flag to wait for the operation asynchronously.

    REST v1

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

    • PROJECT_ID: the ID of the project where the instance resides.
    • INSTANCE_ID: the ID of the instance.
    • TARGET_STORAGE_SIZE: the target storage size in GB.

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID/performDiskShrink

    Request JSON body: 

    {
  "targetSizeGb": TARGET_STORAGE_SIZE
}

    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 of the project where the instance resides.
    • INSTANCE_ID: the ID of the instance.
    • TARGET_STORAGE_SIZE: the target storage size of the instance.

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID/performDiskShrink

    Request JSON body: 

    {
  "targetSizeGb": TARGET_STORAGE_SIZE
}

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

  3. To check the status of the storage shrink operation, see Check the status of an operation.

Cancel storage shrink operation

  1. Get the storage shrink operation ID.

    You can retrieve the operation ID by running the following list command:

    gcloud sql operations list

  2. Send a cancellation request.

    gcloud

    Run the following command to cancel the request:

    gcloud sql operations cancel OPERATION_ID

    Replace the following:

    OPERATION_ID: the ID of the storage shrink operation you want to cancel.

    REST v1

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

    • PROJECT_ID: the ID of the project where the instance resides.
    • OPERATION_ID: the ID of the storage shrink operation you want to cancel.

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID/cancel

    To send your request, expand one of these options:

    You should receive a successful status code (2xx) and an empty response.

    REST v1beta4

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

    • PROJECT_ID: the ID of the project where the instance resides.
    • OPERATION_ID: the ID of the storage shrink operation you want to cancel.

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID/cancel

    To send your request, expand one of these options:

    You should receive a successful status code (2xx) and an empty response.

Check the status of a cancellation request or other operation

To check the status of a request, run the following commands:

gcloud

  1. Get the operation ID for the cancellation request:

    gcloud sql operations list

  2. Check the status of the operation:

    gcloud sql operations describe OPERATION_ID

    Replace the following:

    OPERATION_ID: the ID of the operation you want to check.

REST v1

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

  • PROJECT_ID: the name of the project where the instance resides.
  • OPERATION_ID: the ID of the storage shrink operation.

HTTP method and URL: 

GET https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID

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 name of the project where the instance resides.
  • OPERATION_ID: the ID of the storage shrink operation.

HTTP method and URL: 

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/operations/OPERATION_ID

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Troubleshooting

For information regarding troubleshooting, see Issues updating storage capacity.

What's next