Upgrade a cluster's major server version

This page details the AlloyDB for PostgreSQL process for updating database server versions, and explains how to migrate your data to a cluster compatible with a later version of PostgreSQL.

For more information about how to create a cluster, see Create a cluster and its primary instance.

Clusters and PostgreSQL version compatibility

When you create a AlloyDB cluster, you choose the major version of PostgreSQL that's compatible with the instances in the cluster. By default, this is 15.

During maintenance operations to a cluster, AlloyDB performs only minor database version upgrades. For example, if you created a cluster with 15 compatibility, then AlloyDB keeps the database servers upgraded to the latest version of 15 available.

Unlike the minor version upgrades that AlloyDB performs automatically, a major version upgrade requires you to plan, test, and perform the upgrade yourself. Because AlloyDB doesn't support in-place major database version upgrades, the process involves migrating your data from one cluster to another.

You can perform a major version upgrade by moving the data with a file-based data export or by using Database Migration Service. Each solution offers different advantages and can pose different challenges. See the following table for a brief comparison to help you choose the right approach for your scenario.

File-based data export	Migration with Database Migration Service
File-based exports move a one-time snapshot of your databases.	Database Migration Service offers continuous replication capabilities during the migration process, lowering the chance of missing data in your new cluster.
Your application experiences a longer downtime that starts when you export the data. You can't accept database writes in your original cluster until the new cluster is fully operational.	Your application experiences a shorter downtime that starts when you want to switch the application to use the new cluster.
You have greater granular control over what data to include in your export and can choose to not migrate certain tables or other entities.	Database Migration Service automatically migrates all databases present in your instances, and all instances in your cluster. You can't choose to exclude certain tables or views from your migration data.
Your cluster can have the SSL enforcement mode enabled.	For migration purposes, Database Migration Service requires that you disable the SSL enforcement mode on the source cluster.

The next section details the process of performing a major version upgrade, including migrating your data.

Migrate by using file-based data export

To use a database server compatible with a higher major version of PostgreSQL, you need to create a functionally identical cluster in the same region, and then migrate your data into it.

Follow these steps:

Create a cluster that's configured with the major version of PostgreSQL compatibility that you want to use. Create the cluster in the same region as your current cluster.
Set up the new cluster with the new major version to match the current cluster's configuration:
1. Create additional read pool instances as needed.
2. Create secondary clusters as needed.
  
  When you create secondary clusters, you do not need to specify a PostgreSQL major version number. AlloyDB applies the primary cluster's PostgreSQL version to all of its secondary clusters.
3. Update the new cluster's database flags to match the current cluster's flag settings.
4. Enable any extensions that your applications need.
Export your data from the old cluster into files using psql or pg_dump.
Import your data from the files into the new cluster.

Your applications can now connect to the new cluster's instances at their new IP addresses.

Migrate by using Database Migration Service

You can use Database Migration Service to move data from PostgreSQL databases to AlloyDB clusters. Database Migration Service doesn't provide configuration dedicated specifically for AlloyDB data sources, but AlloyDB is PostgreSQL-compatible so you can use configuration intended for generic PostgreSQL sources.

This migration path isn't an in-place upgrade and results in the creation of a new cluster with a different IP address. We recommend you first clone your cluster and perform a test migration to verify if your application is compatible with this approach.

Important considerations

Before you prepare to migrate with Database Migration Service, carefully consider the following limitations to make sure this migration path fits your upgrade scenario.

Limitations

SSL connections must be disabled on your source cluster.
AlloyDB instances configured with Private Service Connect are not supported.
You can't perform instance updates or failover requests on the source cluster during the migration. These operations can cause the migration job to fail.
All standard limitations for PostgreSQL to AlloyDB migrations apply to this scenario. For the full list of other limitations, see Known limitations in the Database Migration Service documentation.

Migration fidelity

Certain data types, such as large objects, aren't migrated. For the full list of supported data, see Migration fidelity in Database Migration Service documentation.

Source database lockout and downtime

Database Migration Service uses continuous migrations to move data to AlloyDB clusters. This type of migration incurs a short (under 10 seconds) lockout on the source database tables, one at a time, as the initial data dump is created.

When the migration is complete, you need to stop all writes on the source database before you can switch your application to the new cluster. This procedure requires some downtime. For a more detailed overview, see Continuous migrations in the Database Migration Service documentation.

Replication limitations

After the migration job moves into the change data capture (CDC) phase, Database Migration Service continuously replicates new data written to your source databases.

For tables that don't have primary keys, only INSERT statements are replicated during the CDC phase. Any CREATE, UPDATE, or DELETE actions performed on tables that don't have primary keys during the CDC phase need to be re-created on the destination database manually to avoid data loss.

Before you begin

Enable the Database Migration Service API.
Enable the API

Make sure that you have the following role or roles on the project:
- One of the following:
  - Cloud AlloyDB > Cloud AlloyDB admin
  - Basic > Owner
  - Basic > Editor
- You must also have the compute.networks.list permission in the Google Cloud project you are using. To gain this permission while following the principle of least privilege, ask your administrator to grant you the Compute Engine > Compute Network User role (roles/compute.networkUser).
- Database Migration admin
Check for the roles
1. In the Google Cloud console, go to the IAM page.
  Go to IAM
2. Select the project.
3. In the Principal column, find the row that has your email address.
  
  If your email address isn't in that column, then you do not have any roles.
4. In the Role column for the row with your email address, check whether the list of roles includes the required roles.
Grant the roles
1. In the Google Cloud console, go to the IAM page.
  Go to IAM
2. Select the project.
3. Click Grant access.
4. In the New principals field, enter your email address.
5. In the Select a role list, select a role.
6. To grant additional roles, click Add another role and add each additional role.
7. Click Save.
Make sure that the VPC network in the Google Cloud project that you are using is configured for private services access to AlloyDB.
Decide in which region you want to create your destination cluster. All the Database Migration Service entities (connection profiles, migration jobs) must be created in the same region as your destination cluster.
Prepare a database user that you want to connect to your cluster and execute migration statements on your source databases. This database user requires a specific set of permissions and roles. We recommend that you create a new database user and designate it specifically for the purpose of performing the migration.

Configure your source instances

Database Migration Service requires specific configuration to be able to connect and copy data from your source cluster to the new destination cluster.

To configure your AlloyDB source instances, perform these steps:

Configure database flags on every instance in your source cluster. Use the following values:

Flag	Value
`alloydb.logical_decoding`	Set to `on`.
`alloydb.enable_pglogical`	Set to `on`.
`max_replication_slots`	This flag defines the maximum number of replication slots the source instance can support. The minimum value for this flag is `50`. Calculate the minimum value by using the following formula: `(the number of databases in your instance) * (the number of simultaneous migration jobs you want to perform) + (slots reserved for table synchronization) + (the number of replication slots you currently use for your read replicas)` Consider an example where the following are true: You don't have read replicas in your source. You have 30 databases on the primary source instance. You want to create 2 migration jobs for the source cluster. You want to use 10 slots for table replication. In such a case, the number of `max_replication_slots` must be at least `70`, calculated as `30 * 2 + 10 + 0`.
`max_wal_senders`	Set this flag to at least 10 more than the `max_replication_slots` value plus the number of senders already used on your instance. For example, if you set the `max_replication_slots flag` to `70` and you already use 2 senders, then `max_wal_senders` should be at least `82` (calculated as `70 + 10 + 2 = 82`).
`max_worker_processes`	Set this flag to at least the number of databases in your instance plus the number of `max_worker_processes` you already use. For example, if you have 30 databases on your source instance and don't use any worker processes, set this flag to `30`.

Disable SSL enforcement mode on every instance in your source cluster.

Configure your source databases

You must install the pglogical extension and grant the required permissions to the database user you designate as the migration user on every database in your instances.

To configure your databases, perform these steps:

Connect to the default postgres database by using the psql client.
Install the pglogical extension by running the following command:
```
CREATE EXTENSION IF NOT EXISTS pglogical;
```
Grant permissions to the migration database user on all schemas except for the information schema and schemas whose names start with the pg_ prefix. Execute the following statements:
```
GRANT USAGE on SCHEMA SCHEMA_NAME to USER_NAME;
GRANT SELECT on ALL TABLES in SCHEMA SCHEMA_NAME to USER_NAME;
GRANT SELECT on ALL SEQUENCES in SCHEMA SCHEMA_NAME to USER_NAME;
```
Replace the following:
- SCHEMA_NAME: the name of a schema present in your database
- USER_NAME: the name of the database user you prepared in the Before you begin section
Repeat this step for every schema in your database except for the information schema, and schemas whose names start with the pg_ prefix. You can list all database schemas with the \dn meta-command.
Grant the remaining required permissions. Execute the following statements:
```
GRANT USAGE on SCHEMA pglogical to PUBLIC;
GRANT SELECT on ALL TABLES in SCHEMA pglogical to USER_NAME;
ALTER USER USER_NAME with REPLICATION;
```
Replace USER_NAME with the name of the database user you prepared in the Before you begin section.
Connect to every other database in your instance and repeat steps 2, 3, and 4.
- You can list all databases in your instance with the \list meta-command.
- You can switch to another database without resetting your psql client connection by using the \connect {database_name_here} command.
Repeat this procedure for every instance in your source AlloyDB cluster.

Run the migration in Database Migration Service