Upgrade a database in-place major version

This page describes how to perform a database in-place major version upgrade of an AlloyDB for PostgreSQL cluster. To learn about database in-place major version upgrade use cases, workflow, and automatic backups, see Database in-place major version upgrade overview.

Plan a major database version upgrade

Use the following steps to plan a major database version upgrade:

Find your current database major version.
Console
1. In the Google Cloud console, go to the Clusters page.
  
  Go to Clusters
2. Select a cluster from the list. The Overview page appears.
3. Locate the database major version in the Version field.
gcloud
For information about installing and getting started with the gcloud CLI, see Install the gcloud CLI. For information about starting Cloud Shell, see Use Cloud Shell.

Run the following command to get the cluster details, including the current major version:
```
gcloud alloydb clusters describe CLUSTER_ID --region=REGION
```
Make the following replacements:
- CLUSTER_ID: the cluster ID
- REGION: the cluster location or region
REST v1beta
Run the following request to get the cluster details, including the current major version:
```
GET https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID
```
Make the following replacements:
- CLUSTER_ID: the cluster ID
- PROJECT_ID: the project ID
- REGION: the cluster location or region
To send your request, use one of the following options:
curl (Linux, macOS, or Cloud Shell)

The following command assumes that you are signed into the gcloud CLI with your user account by running gcloud init or gcloud auth login, or by using Cloud Shell which automatically signs you into the gcloud CLI. You can check the active account by running gcloud auth list.

Execute the following command:

curl -X GET \ -H "Authorization: Bearer $(gcloud auth print-access-token)" \ -H "Content-Type: application/json; charset=utf-8" \ -d @request.json \ "https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID"
PowerShell (Windows)

The following command assumes that you are signed into the gcloud CLI with your user account by running gcloud init or gcloud auth login. You can check the active account by running gcloud auth list.

Run the following command:

$cred = gcloud auth print-access-token $headers = @{ "Authorization" = "Bearer $cred" } Invoke-WebRequest ` -Method GET ` -Headers $headers ` -Uri "https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID| Select-Object -Expand Content
Identify a target database major version from the following table. For a complete list of the database versions that AlloyDB supports, see Database versions and version policies.

Source major version Supported target major version(s)

POSTGRES_14 POSTGRES_15
Review the features offered in each database major version.
- PostgreSQL 15
- PostgreSQL 14
Identify any incompatibilities that you need to address. New major versions can introduce incompatible changes that might require you to modify the application code, the schema, or the database settings.
Before you start the major version upgrade on your production cluster, we recommend that you clone your cluster and test the major version upgrade on the cloned cluster.

In addition to verifying that the upgrade completes successfully, run tests to ensure that the application behaves as expected on the upgraded cluster.

Source major version	Supported target major version(s)
POSTGRES_14	POSTGRES_15

Prepare the cluster for a major version upgrade

To complete any steps which require you to connect to the database, use AlloyDB Studio, psql, or other connection methods.

Drop or promote your cross-region replicas. Database In-place major version upgrades don't support cross-region replicas. For more information, see Cross-region replication.
Ensure that the encoding and locale settings for the postgres and template1 databases are the same as the template0 database.

Run the following command:
```
SELECT pg_encoding_to_char(encoding), datcollate, datctype
FROM pg_database
WHERE datname IN ('postgres', 'template1', 'template0');
```
If the values for the postgres or template1 databases are different than the values for the template0 database, then the upgrade fails. To resolve this issue, follow these steps:
1. Dump the other (not templated) database. For more information, see Exporting data.
2. Drop the database by runningDROP DATABASE <database_name>;
3. Recreate the database with the same encoding and locale settings as the template0 database by running the following:
```
CREATE DATABASE <database_name>
ENCODING = '<template0_encoding>'
LC_COLLATE = '<template0_datcollate>'
LC_CTYPE = '<template0_datctype>';
```
4. Reload your data. For more information, see Importing data.
If your AlloyDB cluster is a logical replication source, disable downstream subscriptions and drop all logical replication slots. You can re-enable the subscriptions and recreate the logical replication slots after the upgrade. If the AlloyDB instance is only a logical replication target, these steps are not required. To disable subscriptions and drop logical replication slots, follow these steps:
1. Disable each downstream subscription on the subscriber or downstream replication target. Don't disable the downstream subscriptions on the AlloyDB instance being upgraded:
  - If you're using pglogical, run the following command:
```
SELECT * FROM
pglogical.alter_subscription_disable(subscription_name, immediate);
```
    Replace subscription_name in the query with the existing subscription name. If the subscription needs to be disabled immediately, set the value of the immediate parameter to true. By default, the value is false and the subscription is disabled only after the current transaction ends.
    
    For example:
```
postgres=> SELECT * FROM pglogical.alter_subscription_disable('test_sub',true);
alter_subscription_disable
----------------------------
t
(1 row)
```
  - If you're using an extension other than pglogical, run the following command:
```
ALTER SUBSCRIPTION <subscription_name> DISABLE;
```
2. Drop all logical replication slots on the AlloyDB primary instance by running the following command:
```
SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots WHERE slot_type = 'logical';
```
Manage your PostgreSQL extensions. For more information, see Configure database extensions.

Pre-upgrade checks detect extension incompatibilities and surface those violations in the logs, along with suggested actions. For more information, see View pre-upgrade check failures.

You might need to do the following:
1. Drop any extensions that are no longer supported in the target version.
2. Upgrade PostGIS and the related extensions (address_standardizer, address_standardizer_data_us, postgis_raster, postgis_sfcgal, postgis_tiger_geocoder, and postgis_topology) to a supported version in the target PostgreSQL version. For more information, see PostGIS extensions. The following table lists the minimum supported PostGIS extension versions for each PostgreSQL major version:
  
  PostgreSQL version PostGIS minimum supported version
  
  PG14 3.1
  
  PG15 3.2
  
  For example, if your PostGIS version is 3.1.x and you want to upgrade from POSTGRES 14 to POSTGRES 15, use the following command to upgrade the PostGIS extension:
```
ALTER EXTENSION postgis UPDATE TO '3.2.3';
SELECT PostGIS_Version();
```
Verify that connections are allowed for each database, except for template0, by running the following query and checking the datallowconn field for each database:
```
SELECT datname,datallowconn from pg_database;
```
A t value in the datallowconn field means that the connection is allowed. An f value indicates that a connection can't be established. The template0 database must not allow connections.

To allow connections to a database, run the following command:
```
ALTER DATABASE <database> WITH ALLOW_CONNECTIONS = true;
```

PostgreSQL version	PostGIS minimum supported version
PG14	3.1
PG15	3.2

Upgrade the cluster major version in-place

The database in-place major version upgrade can take between 40 minutes and 48 hours to complete, depending on factors like database size, schema size, and the number of read pool instances in the cluster. The primary instance downtime is typically 20 minutes to one hour, and primarily depends on your database schema.

When you place an in-place major version upgrade request, AlloyDB first performs pre-upgrade checks. If AlloyDB determines that your cluster isn't ready for a major version upgrade, the request fails. For more information, see Troubleshoot a major version in-place upgrade.

To upgrade the major database version in place, follow these steps:

Console

In the Google Cloud console, go to the Clusters page.

Go to Clusters
Select a cluster from the list. The Overview page appears.
Click Upgrade to start the database major version upgrade process.
On the Choose a database version step, select one of the available database major versions as the target major version.
Click Continue.
In the Upgrade cluster step in the Cluster ID field, enter the name of the cluster.
Click Start upgrade. You are directed to the Upgrade Status step, where you can check the upgrade status. For more information, see Monitor the major database version upgrade.

gcloud

Start the in-place major version upgrade by running the following command:

gcloud alloydb beta clusters upgrade CLUSTER_ID --region=REGION --version=DATABASE_VERSION --async

The following is an example command:

gcloud alloydb beta clusters upgrade my-cluster --region=us-central1 --version=POSTGRES_15 --async

REST v1beta

Start the in-place major version upgrade by running the following command:

PATCH https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID:upgrade

Request JSON body:

{
  "version": "DATABASE_VERSION"
}

Replace with the enum for the target database major version, which must be later than the current version.

Sample request JSON body:

{
"version": "POSTGRES_15"
}

Monitor the cluster major version upgrade

After the in-place major database version upgrade starts, you can monitor the upgrade status using the Google Cloud console, the gcloud CLI, or the REST API.

Console

Follow these steps to check the upgrade status in the Google Cloud console:

In the Google Cloud console, go to the Clusters page.

Go to Clusters
Select the cluster undergoing the upgrade. The Overview page appears.
Open the Overview page.
Click Upgrade Status. The Upgrade Status page appears, where you can check the status of the upgrade.

gcloud

Follow these steps to check the upgrade status in the gcloud CLI:

Get the upgrade operation ID by running the following command. Before you run the command, replace the CLUSTER_ID variable with the name of the cluster:
```
gcloud alloydb operations list --cluster=CLUSTER_ID --region=REGION_ID --filter=metadata.verb:upgrade
```
The gcloud CLI call used to trigger a major version upgrade. alloydb beta clusters upgrade, returns the operation ID as a synchronous response. Alternatively, use the gcloud alloydb operations list command with the --cluster flag.

The following is an example command:
```
gcloud alloydb operations list --cluster=my-cluster --region=us-central1 --filter=metadata.verb:upgrade
```

Monitor the status of the upgrade by running the following command:

gcloud alloydb operations describe OPERATION_ID
--region=REGION
https://cloud.google.com/sdk/gcloud/reference/alloydb/operations/describe

REST v1beta

Follow these steps to check the upgrade status in the REST API:

Get the upgrade operation ID.

Use the following GET request with the operations.list method to list all upgrade operations and find the one corresponding to the target cluster:
```
GET https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/operations/filter=metadata.verb:upgrade
```
The REST API call returns the operation ID as a synchronous response.

Monitor the status of the upgrade.

Use a GET request with the operations.get method:

  GET https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/locations/REGION/operations/OPERATION_ID

Make the following replacements:

PROJECT_ID: the project ID
REGION: the cluster location or region
OPERATION_ID: the upgrade operation ID, which was retrieved in the previous step.

The following is an example response when the operation is in progress:

{
"name": "projects/PROJECT_ID/locations/REGION/operations/OPERATION_ID",
"metadata": {
"@type": "type.googleapis.com/google.cloud.alloydb.v1.OperationMetadata",
"createTime": "2024-09-16T23:17:39.727319438Z",
"target": "projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID",
"verb": "upgrade",
"requestedCancellation": false,
"apiVersion": "v1",
"upgradeClusterStatus": {
   "state": "IN_PROGRESS",
   "cancellable": true,
   "stages": [
    {
      "stage": "ALLOYDB_PRECHECK",
      "state": "IN_PROGRESS"
    },
    {
      "stage": "PG_UPGRADE_CHECK",
      "state": "IN_PROGRESS"
    },
    {
      "stage": "PREPARE_FOR_UPGRADE",
      "state": "NOT_STARTED"
    },
    {
      "stage": "PRIMARY_INSTANCE_UPGRADE",
      "state": "NOT_STARTED"
    },
    {
      "stage": "CLEANUP",
      "state": "NOT_STARTED"
    }
   ]
 }
},
"done":false
}

The following is a sample response, when the operation is complete:

{
"operations": [
{
 "metadata": {
 "@type": "type.googleapis.com/google.cloud.alloydb.v1betaalpha.OperationMetadata",
 "createTime": "2024-09-16T21:52:17.303861317Z",
 "endTime": "2024-09-16T22:29:13.947527949Z",
 "target": "projects/PROJECT_ID/locations/REGION/clusters/CLUSTER_ID",
 "verb": "upgrade",
 "requestedCancellation": false,
 "apiVersion": "v1beta",
 "upgradeClusterStatus": {
  "state": "SUCCESS",
  "stages": [
    {
      "stage": "ALLOYDB_PRECHECK",
      "state": "SUCCESS"
    },
    {
      "stage": "PG_UPGRADE_CHECK",
      "state": "SUCCESS"
    },
    {
      "stage": "PREPARE_FOR_UPGRADE",
      "state": "SUCCESS"
    },
    {
      "stage": "PRIMARY_INSTANCE_UPGRADE",
      "state": "SUCCESS"
    },
    {
      "stage": "CLEANUP",
      "state": SUCCESS"
    }
   ]
 }
},
"response": {
     …
},
"name":  "projects/PROJECT_ID/locations/REGION/operations/OPERATION_ID",
"done": true
}
]
}

For more information about the response structure, see Upgrade operation response.

Upgrade operation response

The UpgradeCluster operation response includes the following:

status: status of the overall upgrade operation. Possible values are SUCCESS, FAILED, and PARTIAL_SUCCESS.
message: provides a brief summary of the upgrade operation result.
clusterUpgradeDetails: upgrade details for the clusters being upgraded. This field is an array. AlloyDB only allows a single cluster upgrade, so it must have only one entry.
- name: fully qualified name of the cluster.
- upgradeStatus: the status of the cluster upgrade. Possible values are SUCCESS, FAILED, PARTIAL_SUCCESS. PARTIAL_SUCCESS means that one or more read pool instances can't be upgraded.
- clusterType: the cluster type. AlloyDB only lets you upgrade a single PRIMARY cluster. This type must always be PRIMARY.
- databaseVersion: the current database version of the cluster.
- stageInfo: information about the core upgrade stages.
  - status: SUCCESS or FAILED
  - logs_url: link to the logs generated by the stage. Empty for stages that don't generate logs.
- instanceUpgradeDetails: upgrade information for all instances in the cluster.
  - name: fully qualified name of the instance
  - upgradeStatus: SUCCESS or FAILED
  - instanceType: PRIMARY or READ_POOL

The following is a sample upgrade operation response:

"response": {
  "@type": "type.googleapis.com/google.cloud.alloydb.v1alpha.UpgradeClusterResponse",
  "status": "SUCCESS",
  "message": "Cluster upgraded successfully.",
  "clusterUpgradeDetails": [
    {
      "name": "projects/1234/locations/us-central1/clusters/abc",
      "upgradeStatus": "SUCCESS",
      "clusterType": "PRIMARY",
      "databaseVersion": "POSTGRES_15",
      "stageInfo": [
        {
          "stage": "ALLOYDB_PRECHECK", 
          "status": "SUCCESS",
          "logsUrl": "https://console.cloud.google.com/logs/query..."
        },
        {
          "stage": "PG_UPGRADE_CHECK",
          "status": "SUCCESS",
          "logsUrl": "https://console.cloud.google.com/logs/query..."
        },
        {
          "stage": "PRIMARY_INSTANCE_UPGRADE",
          "status": "SUCCESS",
          "logsUrl": "https://console.cloud.google.com/logs/query..."
        },
        {
          "stage": "READ_POOL_INSTANCES_UPGRADE",
          "status": "SUCCESS",
        }
      ],
      "instanceUpgradeDetails": [
        {
          "name": "projects/1234/locations/us-central1/clusters/abc/instances/primary",
          "upgradeStatus": "SUCCESS",
          "instanceType": "PRIMARY",
        },
        {
          "name": "projects/1234/locations/us-central1/clusters/abc/instances/read1",
          "upgradeStatus": "SUCCESS",
          "instanceType": "READ_POOL",
        },
        {
          "name": "projects/1234/locations/us-central1/clusters/abc/instances/read2",
          "upgradeStatus": "SUCCESS",
          "instanceType": "READ_POOL",
        }
      ]
    }
  ]
}

View upgrade logs

AlloyDB publishes all upgrade logs to the postgres_upgrade log name.

To view upgrade related logs, follow these steps:

In the Google Cloud console, go to the Logs Explorer page:
Go to Logs Explorer

If you use the search bar to find this page, then select the result whose subheading is Logging.
Select alloydb.googleapis.com/postgres_upgrade as the log name. This translates to the query "logName="projects/PROJECT_ID/logs/alloydb.googleapis.com%2Fpostgres_upgrade".

Use the following labels to filter the logs:

Label	Description
`LOG_TYPE`	Upgrade stage which generated the log. Possible values are `ALLOYDB_PRECHECK`, `PG_UPGRADE_CHECK`, and `PG_UPGRADE`.
`OPERATION_NAME`	Full operation name for the upgrade operation.
`FILE_NAME`	Populated only for `pg_upgrade_check` and `pg_upgrade` logs, and corresponds to the log files generated by the `pg_upgrade` utility.

The following is a sample query that returns AlloyDB pre-check upgrade logs for a given operation:

logName="projects/project1234/logs/alloydb.googleapis.com%2Fpostgres_upgrade"
labels.LOG_TYPE="ALLOYDB_PRECHECK"
labels.OPERATION_NAME="projects/PROJECT_ID/locations/REGION/operations/OPERATION_ID"

For more information about upgrade checks, see Database in-place major version upgrade overview.

View upgrade logs for a cluster

To view the upgrade logs for a cluster when you don't know the operation ID and the operation has expired, follow these steps:

Query for the AlloyDB pre-check logs for your cluster.

logName="projects/PROJECT_ID/logs/alloydb.googleapis.com%2Fpostgres_upgrade"
labels.LOG_TYPE="ALLOYDB_PRECHECK"
resource.labels.cluster_id=CLUSTER_ID

Find the Operation_ID from the OPERATION_NAME log label.

In the following example, Operation_ID from OPERATION_NAME is operation-1728225968201-623cff6ed1e02-e34b7191-3cd92013.
```
labels.OPERATION_NAME="projects/myproject/locations/us-central1/operations/operation-1728225968201-623cff6ed1e02-e34b7191-3cd92013"
```

Query for all postgres_upgrade logs for a specific operation.

logName="projects/production-1/logs/alloydb.googleapis.com%2Fpostgres_upgrade"
labels.OPERATION_NAME="operation-1728225968201-623cff6ed1e02-e34b7191-3cd92013"

Cancel the database in-place major version upgrade

You can cancel an in-progress major version upgrade operation from the Google Cloud console, the gcloud CLI, or the REST API.

Find the operation ID

To cancel the major version upgrade operation using the gcloud CLI or the REST API, you need the operation ID. You must specify this ID in the gcloud CLI or REST API command so that AlloyDB knows which operation to cancel.

You can't cancel the upgrade after the primary instance upgrade reaches a certain point.

When you start the in-place major version upgrade, the operation ID is returned in the name field of the response. See the Response example.

You can also find the operation ID by making an operations.list call on the AlloyDB cluster.

Cancel the upgrade

To cancel a major version upgrade in place, follow these steps:

Console

In the Google Cloud console, go to the Clusters page.

Go to Clusters
Select a cluster from the list. The Overview page appears.
Click Upgrade status.
Click Cancel. If the upgrade isn't cancelable, this button is grayed out.

gcloud

Use the gcloud alloydb operations cancel command to cancel the operation:

gcloud alloydb operations cancel OPERATION_ID

Replace the OPERATION_ID variable with the ID of the operation.

If cancellable in UpgradeClusterStatus is false in the output of the gcloud alloydb operations cancel command, AlloyDB ignores the cancellation request and continues with the upgrade. In this case, the API doesn't throw an error, and it returns an empty response. For more information about the upgrade status, see Monitor the cluster major version upgrade.

REST v1beta

Run the following command:

POST https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/operations/OPERATION_ID:cancel

Before you use any of the request data, make the following replacements:

PROJECT_ID: the project ID.
OPERATION_ID: the ID of the import or export operation.

If cancellable in UpgradeClusterStatus is false, you can't cancel the upgrade.

To send your request, use one of the following options:

curl (Linux, macOS, or Cloud Shell)

Run the following command:

       curl -X POST \
             -H "Authorization: Bearer $(gcloud auth print-access-token)" \
             -H "Content-Type: application/json; charset=utf-8" \
             -d "" \
            "https://alloydb.googleapis.com/v1/projects/PROJECT_ID/operations/OPERATION_ID/cancel"

PowerShell (Windows)

Run the following command:

       $cred = gcloud auth print-access-token
       $headers = @{ "Authorization" = "Bearer $cred" }

       Invoke-WebRequest `
         -Method POST `
         -Headers $headers `
         -Uri "https://alloydb.googleapis.com/v1beta/projects/PROJECT_ID/operations/OPERATION_ID/cancel"| Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

This REST API call doesn't return any response.

Complete the in-place major version upgrade

To complete the major version upgrade, connect to an AlloyDB instance using AlloyDB Studio, psql, or other connection methods.

If you're using a non-AlloyDB system, consult your system documentation for connection instructions.

After you upgrade your cluster, follow these steps to complete your upgrade:

If you previously disabled pglogical, re-enable pglogical replication. Enabling pglogical replication automatically creates the required replication slot.
1. Drop the pglogical subscription on the destination replica by using the following command:
```
select pglogical.drop_subscription(subscription_name name);
```
  Replace name with the name of the existing subscription. For example:
```
postgres=> select pglogical.drop_subscription(subscription_name:= 'test_sub');
-[ RECORD 1 ]-----+--
drop_subscription |1
```
1. Recreate the pglogical subscription on the destination or replica by providing the following connection information to the AlloyDB primary instance:
```
SELECT pglogical.create_subscription(
 subscription_name :='test_sub',<br>
 provider_dsn := 'host=primary-ip port=5432 dbname=postgres user=replication_user password=replicapassword'
);
```
2. Check the status of the subscription by using the following command:
```
SELECT * FROM pglogical.show_subscription_status('test_sub');
```
3. Test the replication by performing write transactions and verifying that the changes are visible on the destination.

Refresh the database statistics.

After the upgrade completes, run ANALYZE on your primary cluster to update the system statistics. Accurate statistics ensure that the PostgreSQL query planner processes queries optimally. Missing statistics can result in inaccurate query plans, which might degrade performance and take up excessive memory.
Run acceptance tests to ensure that the upgraded system performs as expected.
Verify that the upgraded database in-place major version appears on the cluster's Overview page in the Google Cloud console.

Restore to the previous major version

If your upgraded database system doesn't perform as expected, you might need to revert to the pre-upgrade state. You can achieve this by restoring from a pre-upgrade backup –either the one that AlloyDB automatically creates during the upgrade process or an existing pre-upgrade backup– to create a new cluster with the pre-upgrade state.

To restore to a pre-upgrade state, follow these steps:

Identify a pre-upgrade backup to restore from. During the upgrade process, AlloyDB automatically creates a pre-upgrade backup with the prefix pre-upgrade-bkp. For more information, see View a list of backups.
Initiate a restore from the pre-upgrade backup, which creates a new cluster with the previous PostgreSQL version. For more information, see Restore a cluster from a stored backup.
Connect your application. Update your application with details about the restored cluster and its read replicas. You can resume serving traffic on the restored cluster.

You can also perform a point-in-time recovery to a pre-upgrade point in time. For more information, see Use point-in-time recovery (PITR).

Limitations

The following limitations affect in-place major version upgrades for AlloyDB:

You can't perform an in-place major version upgrade on a secondary cluster.
Upgrading instances that have more than 1,000 databases from one version to another might take a long time, and the upgrade might time out.
AlloyDB doesn't support upgrading clusters that use pg_largeobject_metadata. If select count(*) from pg_largeobject_metadata; is non-zero, then the upgrade fails.
The in-place major version upgrade operation might complete before the per-upgrade backup or the post-upgrade backups completes, especially when you have a large database with fewer objects.
There might be a short delay between the time when writes start again on the upgraded instance and when the post-upgrade backup is created. This means that post-upgrade backup contents might not match the contents of the database from before the major version upgrade.
Pre-upgrade backups might still be created when an in-place major version upgrade fails.
Because automatic upgrade backups are continuous, you can't delete them until they reach the continuous backups and recovery maximum retention. When maximum retention is reached, the backups are garbage collected; alternatively, you can use the gcloud CLI to manually delete the backups. For more information, see Restrictions on deleting backups.

What's next

Learn more about database in-place major version upgrades.
Troubleshoot an in-place major version upgrade.
Learn about database in-place major version upgrade errors.