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
In the Google Cloud console, go to the Clusters page.
Select a cluster from the list. The Overview page appears.
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)
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)
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.
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.
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
andtemplate1
databases are the same as thetemplate0
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
ortemplate1
databases are different than the values for thetemplate0
database, then the upgrade fails. To resolve this issue, follow these steps:Dump the other (not templated) database. For more information, see Exporting data.
Drop the database by running
DROP DATABASE <database_name>;
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>';
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:
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 theimmediate
parameter totrue
. By default, the value isfalse
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;
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:
- Drop any extensions that are no longer supported in the target version.
Upgrade PostGIS and the related extensions (
address_standardizer
,address_standardizer_data_us
,postgis_raster
,postgis_sfcgal
,postgis_tiger_geocoder
, andpostgis_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 thedatallowconn
field for each database:SELECT datname,datallowconn from pg_database;
A
t
value in thedatallowconn
field means that the connection is allowed. Anf
value indicates that a connection can't be established. Thetemplate0
database must not allow connections.To allow connections to a database, run the following command:
ALTER DATABASE <database> WITH ALLOW_CONNECTIONS = true;
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.
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.
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 thegcloud 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 theoperations.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 areSUCCESS
,FAILED
, andPARTIAL_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 areSUCCESS
,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 singlePRIMARY
cluster. This type must always bePRIMARY
.databaseVersion
: the current database version of the cluster.stageInfo
: information about the core upgrade stages.status
:SUCCESS
orFAILED
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 instanceupgradeStatus
:SUCCESS
orFAILED
instanceType
:PRIMARY
orREAD_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:
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
, andPG_UPGRADE
.OPERATION_NAME
Full operation name for the upgrade operation. FILE_NAME
Populated only for pg_upgrade_check
andpg_upgrade
logs, and corresponds to the log files generated by thepg_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 theOPERATION_NAME
log label.In the following example,
Operation_ID
fromOPERATION_NAME
isoperation-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.
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-enablepglogical
replication. Enablingpglogical
replication automatically creates the required replication slot.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
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' );
Check the status of the subscription by using the following command:
SELECT * FROM pglogical.show_subscription_status('test_sub');
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
. Ifselect 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.