This page describes how to upgrade the database major version by upgrading yourCloud SQL instance in-place rather thanby migrating data.

Introduction

Database software providers periodically release new major versions that containnew features, performance improvements, and security enhancements.Cloud SQL takes innew versions afterthey're released. After Cloud SQL offers support for a new major version,you can upgrade your instances to keep your database updated.

You can upgrade the database version of an instance in-place or bymigrating data.In-place upgradesare a simpler way to upgrade your instance's major version. You don't need tomigrate data or change application connection strings. With in-place upgrades,you can retain the name, IP address, and other settings of your current instanceafter the upgrade. In-place upgrades don't require you to move data files andcan be completed faster. In some cases, the downtime is shorter than whatmigrating your data entails.

Plan a major version upgrade

1. Confirm that you have the required role to perform a majorversion upgrade:Cloud SQL Owner orCloud SQL Admin.

2. Choose a target major version.

   gcloud

   For information about installing and getting started with the gcloud CLI, seeInstall the gcloud CLI. For information about starting Cloud Shell, seeUse Cloud Shell.

   To check the database versions that you can target for an in-place upgrade on your instance, do the following:

   1. Run the following command.

   gcloud sql instances describeINSTANCE_NAME

   ReplaceINSTANCE_NAME with the name of the instance.

   2. In the output of the command, locate the section that is labeledupgradableDatabaseVersions.
   3. Each subsection returns a database version that is available for upgrade. In each subsection, review the following fields.
   • majorVersion: the major version that you can target for the in-place upgrade.
   • name: the database version string that includes the major version.
   • displayName: the display name for the database version.

   REST v1

   To check which target database versions are available for a majorversion in-place upgrade, use theinstances.get methodof the Cloud SQL Admin API.

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

   • INSTANCE_NAME: The instance name.

   HTTP method and URL:

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

   To send your request, expand one of these options:

   curl (Linux, macOS, or Cloud Shell)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   curl -X GET \
        -H "Authorization: Bearer $(gcloud auth print-access-token)" \
        "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME"

   PowerShell (Windows)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   $cred = gcloud auth print-access-token
   $headers = @{ "Authorization" = "Bearer $cred" }
   
   Invoke-WebRequest `
       -Method GET `
       -Headers $headers `
       -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

   You should receive a JSON response similar to the following:

   upgradableDatabaseVersions:{  major_version: "POSTGRES_15_0"  name: "POSTGRES_15_0"  display_name: "PostgreSQL 15.0"}

   REST v1beta4

   To check which target database versions are available for majorversion in-place upgrade of an instance, use theinstances.get method of the Cloud SQL Admin API.

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

   • INSTANCE_NAME: The instance name.

   HTTP method and URL:

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

   To send your request, expand one of these options:

   curl (Linux, macOS, or Cloud Shell)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login , or by usingCloud Shell, which automatically logs you into thegcloud CLI . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   curl -X GET \
        -H "Authorization: Bearer $(gcloud auth print-access-token)" \
        "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME"

   PowerShell (Windows)

   Note: The following command assumes that you have logged in to thegcloud CLI with your user account by runninggcloud init orgcloud auth login . You can check the currently active account by runninggcloud auth list.

   Execute the following command:

   $cred = gcloud auth print-access-token
   $headers = @{ "Authorization" = "Bearer $cred" }
   
   Invoke-WebRequest `
       -Method GET `
       -Headers $headers `
       -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

   You should receive a JSON response similar to the following:

   upgradableDatabaseVersions:{  major_version: "POSTGRES_15_0"  name: "POSTGRES_15_0"  display_name: "PostgreSQL 15.0"}

   For the complete list of the database versions that Cloud SQL supports, seeDatabase versions and version policies.

3. Consider the features offered in each database major version and addressincompatibilities.

   • PostgreSQL 18
   • PostgreSQL 17
   • PostgreSQL 16
   • PostgreSQL 15
   • PostgreSQL 14
   • PostgreSQL 13
   • PostgreSQL 12
   • PostgreSQL 11
   • PostgreSQL 10

   New major versions introduce incompatible changes that might require you tomodify the application code, the schema, or the database settings. Beforeyou can upgrade your database instance, review the release notes of yourtarget major version to determine the incompatibilities that you mustaddress.

4. Perform theprecheck for upgrades.

5. Test the upgrade with a dry run.

   Perform a dry run of the end-to-end upgrade process in a test environmentbefore you upgrade the production database. You canclone your instanceto create an identical copy of the data on which to test the upgrade process.

   In addition to validating that the upgrade completes successfully, runtests to ensure that the application behaves as expected on the upgradeddatabase.

   Note: Ensure that you have enough disk space before running a major version upgrade.If you haven't enabledautomatic storage increases,take note of the disk storage used by the upgraded dry run instance. This helps youdetermine whether you need to increase the storage capacity for theproduction instance before upgrading.

6. Decide on a time to upgrade.

   Upgrading requires the instance to become unavailable for a period oftime. Plan to upgrade during a time period when database activity is low.

Prepare for a major version upgrade

Before you upgrade, complete the following steps.

1. Check theLC_COLLATE value for thetemplate andpostgres databases. The character set for each database must been_US.UTF8.

   If theLC_COLLATE value for thetemplate andpostgres databases isn'ten_US.UTF8, then the major version upgrade fails. To fix this, if either database has a character set other thanen_US.UTF8, then change theLC_COLLATE value toen_US.UTF8 before you perform the upgrade.

   To change the encoding of a database:

   1. Dump your database.
   2. Drop your database.
   3. Create a new database with the different encoding (for this example,en_US.UTF8).
   4. Reload your data.

   Another option is to rename the database:

   1. Close all connections to the database.
   2. Rename the database.
   3. Update your application configurations to use the new database name.
   4. Create a new, empty database with the default encoding.

   We recommend that you perform these steps on a cloned instance before applying them to a production instance.

2. Manage your remainingPostgreSQL extensions.

   Most extensions work on the upgraded database major version. Drop any extensions that are no longer supported in your target version. For example, drop thechkpass extension if you're upgrading to PostgreSQL 11 or later versions.

   You can upgradePostGIS and its related extensions to their latest supported versions manually.

   Sometimes, upgrading from PostGIS versions 2.x can create a situation where there are leftover database objects that aren't associated with the PostGIS extension. This can block the upgrade operation. For information about resolving this issue, seeFixing a broken postgis raster install.

   Sometimes, upgrading to PostGIS version 3.1.7 or later can't complete due to objects using deprecated functions. This can block the upgrade operation. To check the upgrade status, runSELECT PostGIS_full_version();. If there are warnings present, then drop any objects using the deprecated functions and update the PostGIS extension to any intermediate or higher version. After you complete these actions, run theSELECT PostGIS_full_version(); command again. Verify that no warnings appear. Then, proceed with the upgrade operation.

   To learn more about upgrading your PostGIS extensions, seeUpgrading PostGIS. For issues associated with upgrading PostGIS, seeCheck the version of your PostgreSQL instance.
3. Manage your custom database flags. Check the names of any custom database flags that you configured for your PostgreSQL instance. For issues associated with these flags, seeCheck the custom flags for your PostgreSQL instance.
4. When performing an upgrade from one major version to another, attempt to connect to each database to see if there are any compatibility issues. Ensure that your databases can connect to each other. Check thedatallowconn field for each database to ensure that a connection is allowed. At value means that it's allowed, and anf value indicates that a connection can't be established.
5. If you use theDatadog installation to upgrade your Cloud SQL instance to PostgreSQL 10 or later versions, then before you perform the upgrade, drop thepg_stat_activity() function.

Known limitations

The following limitations affect in-place major version upgrades for Cloud SQL for PostgreSQL:

• You can't perform an in-place major version upgrade on anexternal replica.
• Upgrading instances that have more than 1,000 databases from one version toanother might take a long time and time out.
• Use theselect * from pg_largeobject_metadata; statement to query for the number of large objects in each PostgreSQL database of your Cloud SQL instance. If the result from all of your databases is more than 10 million large objects, then the upgrade fails. Cloud SQL rolls back to the previous version of your database.
• Before you perform an in-place major version upgrade to PostgreSQL 16 and later, upgrade thePostGIS extension for all of your databases to version 3.4.0. For PostgreSQL 18, upgrade to PostGIS version 3.6.0.
• Before you perform an in-place major version upgrade to PostgreSQL 17, upgrade therdkit extension for all of your databases to version 4.6.1.
• Before you perform an in-place major version upgrade to PostgreSQL 16, 17, or 18, upgrade thepg_squeeze extension for all of your databases to version 1.6, 1.7, or 1.8 respectively.
• If you're using PostgreSQL versions 9.6, 10, 11, or 12, then version 3.4.0 of the PostGIS extension isn't supported. Therefore, to perform an in-place major version upgrade to PostgreSQL 16 and later, you must first upgrade to an intermediate version of PostgreSQL (versions 13, 14, or 15).

• If you install thepg_ivm extension for your instance, then you can't perform a major version upgrade. To fix this, uninstall this extension and then perform the upgrade. For more information about the extensions, seeConfigure PostgreSQL extensions.

• If you enable thevacuum_defer_cleanup_age andforce_parallel_mode flags, then you can't perform a major version upgrade. To fix this, delete these flags and then perform the upgrade. For more information about the flags, including how to delete them, seeConfigure database flags.

  Note: For PostgreSQL 16 and later, thevacuum_defer_cleanup_age flag is deprecated and theforce_parallel_mode flag is renamed todebug_parallel_query.

Assess upgrade readiness for your instance

Cloud SQL lets you run a precheck on your instance before a major versionupgrade. This precheck is a long-running operation (LRO) that checks if yourinstance is ready for an upgrade. It helps find potential problems likeincompatibilities, configuration issues, or data problems prior to the upgrade operation.

The precheck either confirms your instance can be upgraded, or lists issuesyou need to fix first and their solutions. These issues might be dueto incompatible extensions, unsupported dependencies, or data format problems.

The precheck mainly reads your instance's metadata and performs checks. Thesetasks don't affect your instance's performance or cause downtime. We highlyrecommend running the precheck as it helps prevent upgrade failures andunexpected downtime.

When you run the precheck, one of the following happens:

• No issues found: The precheck finished successfully, and no problemswere found.
• Upgrade blocking issues found: The precheck finished successfully,but it found errors that stop the upgrade. The issues must be resolvedprior to the upgrade.
• Non-blocking warnings found: The precheck finished successfully andfound warnings, but none of them stop the upgrade.

Depending on the precheck's results, you can either proceed with the upgradeor fix the identified issues before upgrading.

Limitations

When using the major version upgrade precheck, consider these limitations:

• The instance state must be set toRUNNING.
• The instance must be a primary instance. precheck doesn't supportreplica instances.

• The instance must not have any blocking operations pending. If ablocking operation is pending, then the precheck results in an errorwith the following message:

  Operation failed because another operation was already in progress. Tryyour request after the current operation is complete.

• The precheck needs to connect to all databases on the instance. If adatabase is inaccessible, locked, or unresponsive, the precheck mightfail or show errors. Although the precheck doesn't affect your instance'sperformance or cause downtime, we recommend running the precheck whendatabase load is low.

Before you begin

• Make sure the Cloud SQL Admin API is enabled for your instance.
• Confirm you have thecloudsql.instances.preCheckMajorVersionUpgradeIAM permission.

Perform the precheck

To perform the major version upgrade precheck, do the following:

Review precheck findings

After the precheck finishes, your instance is either ready for upgrade, orit has issues that need your attention.

Ready for upgrade

If the precheck finishes successfully and thepreCheckResponse array isempty, it means no issues or warnings were found. Your instance is ready forthe major version upgrade. To continue, seePerform the major version upgrade.

Not ready for upgrade

If the precheck ran successfully and thepreCheckResponse array containsissues, your instance isn't ready for the upgrade and needs attention. Theidentified issues might or might not block the upgrade. These issues arenoted in thepreCheckResponse with the following message types:

If your instance only hasINFO orWARNING messages, you can upgrade it,but you might have issues after the upgrade. We recommend reviewing themessage details and resolving the issue before upgrading. If your instancehasERROR messages, you must resolve these issues before upgrading.

Each issue type includes amessage and anactions_required field. Revieweach issue to understand its type and how to resolve it. For more informationabout common issues and their solutions, seeCommon major version upgrade precheck errors.

After you resolve the issues, re-run the precheck to confirm your instanceis ready for the upgrade. Then, proceed with upgrading your instance once theprecheck is clear.

Perform the major version upgrade

You can upgrade the major version of a single Cloud SQL instance, oryou can upgrade the major version of a primary instance and include all ofits replicas in the upgrade, including cascading replicas and cross-regionreplicas.

Upgrade the major version of a single instance

When you initiate an upgrade operation for a single instance, Cloud SQL doesthe following:

1. Checks the configuration of your instance to ensure thatthe instance is compatible for an upgrade.
2. After Cloud SQL verifies the configuration,then Cloud SQL makes the instance unavailable.
3. Makes a pre-upgrade backup.
4. Performs the upgrade on the instance.
5. Makes your instance available.
6. Makes a post-upgrade backup.

resource "google_sql_database_instance" "instance" {  name             = "postgres-instance"  region           = "us-central1"  database_version = "POSTGRES_14"  settings {    tier = "db-custom-2-7680"  }  # 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}

When you place an in-place upgrade request, Cloud SQL first performs apre-upgrade check. If Cloud SQL determines that your instance isn'tready for an upgrade, then your upgrade request fails with a message suggesting howyou can address the issue. See alsoTroubleshoot a major versionupgrade.

To include replicas in a major version upgrade, you can't use theGoogle Cloud console or Terraform. You can only usegcloud CLI or the Cloud SQL Admin API.

Automatic upgrade backups

When you perform a major version upgrade, Cloud SQL automatically makestwo on-demand backups, called upgrade backups:

• The first upgrade backup is thepre-upgrade backup, which is madeimmediately before starting the upgrade. You can use this backup to restoreyour database instance to its state on the previous version.
• The second upgrade backup is thepost-upgrade backup, which is madeimmediately after new writes are allowed to the upgraded database instance.

When youview your list ofbackups, theupgrade backups are listed with typeOn-demand. Upgrade backups are labeled sothat you can identify them quickly.For example, if you're upgrading from PostgreSQL 14 to PostgreSQL 15, yourpre-upgrade backup is labeledPre-upgrade backup, POSTGRES_14 to POSTGRES_15.and your post-upgrade backup is labeledPost-upgrade backup, POSTGRES_14 toPOSTGRES_15.

As with other on-demand backups, upgrade backups persist until you delete themor delete the instance. If you have PITR enabled, you can't delete your upgradebackups while they're in your retention window. If you need to delete yourupgrade backups, you must disable PITR or wait until your upgrade backupsare no longer in your retention window.

Complete the major version upgrade

After you finish upgrading your primary instance, perform the followingsteps to complete your upgrade:

1. Refresh the database statistics.

   RunANALYZE on your primary instance to update the system statistics after the upgrade. Accurate statistics make sure that the PostgreSQL query planner processes queries optimally. Missing statistics can lead to bad query plans, which in turn might degrade performance and take up excessive memory.

2. Perform acceptance tests.

   Run tests to make sure that the upgraded system performs as expected.

Troubleshoot a major version upgrade

Cloud SQL returns an error message if you attempt an invalid upgradecommand, for example, if your instance contains invalid database flags for thenew version.

If your upgrade request fails, check the syntax of your upgrade request. If therequest has a valid structure, try looking into the following suggestions.

View error logs

If any issues occur with a valid upgrade request, then Cloud SQLpublishes error logs toprojects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres-upgrade.log. Each log entry contains a label with theinstance identifier to help you identify the instance with the upgrade error.Look for such upgrade errors and resolve them.

To view error logs, use the Google Cloudconsole::

1. In the Google Cloud console, go to theCloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.

3. In theOperations and logs pane of the instanceOverview page, clicktheView PostgreSQL error logs link.

   TheLogs Explorer page opens.

4. View logs as follows:

   • To list all error logs in a project, select the log name in theLogname log filter.

   For more information on query filters, seeAdvanced queries.

   • To filter the upgrade error logs for a single instance, enter thefollowing query in theSearch all fields box, after replacingDATABASE_ID

   with the project ID followed by the instance name in this format:project_id:instance_name.

   resource.type="cloudsql_database"resource.labels.database_id="DATABASE_ID"logName:"projects/PROJECT_ID/logs/cloudsql.googleapis.com%2Fpostgres-upgrade.log"

   For example, to filter the upgrade error logs by an instance namedshopping-db running in the projectbuylots, use the following queryfilter:

   resource.type="cloudsql_database"resource.labels.database_id="buylots:shopping-db"logName:"projects/buylots/logs/cloudsql.googleapis.com%2Fpostgres-upgrade.log"

   You can either review all logs reported within a given timeframe, or you can filter logs by severity. A common option for troubleshooting might include selecting the following filters:

   • Emergency
   • Alert
   • Critical
   • Error

Log entries with thepg_upgrade_dump prefix indicate that an upgrade error hadoccurred. For example:

pg_upgrade_dump: error: query failed: ERROR: out of shared memoryHINT: You might need to increase max_locks_per_transaction.

Additionally, log entries labeled with a.txt secondary filename might listother errors that you might want to resolve before attempting the upgrade again.

All filenames are found in thepostgres-upgrade.log file. To locate a filename,look at thelabels.FILE_NAME field.

Filenames that might contain errors to resolve include:

• tables_with_oids.txt: This file contains tables that are listed with object identifiers (OIDs). Either delete the tables or modify them so that they don't use OIDs.
• tables_using_composite.txt: This file contains tables that are listed using system-defined composite types. Either delete the tables or modify them so that they don't use these composite types.
• tables_using_unknown.txt: This file contains tables that are listed using theUNKNOWN data type. Either delete the tables or modify them so that they don't use this data type.
• tables_using_sql_identifier.txt: This file contains tables that are listed using theSQL_IDENTIFIER data type. Either delete the tables or modify them so that they don't use this data type.
• tables_using_reg.txt: This file contains tables that are listed using theREG* data type (for example,REGCOLLATION orREGNAMESPACE). Either delete the tables or modify them so that they don't use this data type.
• postfix_ops.txt: This file contains tables that are listed using postfix (right-unary) operators. Either delete the tables or modify them so that they don't use these operators.

Check the memory

If the instance has insufficient shared memory, you might see this errormessage:ERROR: out of shared memory. This error is more likely to occur ifyou have in excess of 10,000 tables.

Before you attempt an upgrade, set the value of themax_locks_per_transactionflag to approximately twice the number of tables in the instance. The instanceis restarted when you change the value of this flag.

Check the connections capacity

If your instance has insufficient connection capacity, you might see thiserror message:ERROR: Insufficient connections.

Cloud SQL recommends that you increase themax_connectionsflag value by the number of databases in your instance. The instance isrestarted when you change the value of this flag.

Check for an ambiguous column reference

Cloud SQL automatically performs a pre-upgrade check to identify user-definedviews that depend on system catalog views, such aspg_stat_activity orpg_stat_replication. The column structure of these system catalog views canchange between major PostgreSQL versions. If you have views thatselect * orrely on the column order of these system views, then they might becomeincompatible after an upgrade, resulting in an error, such asERROR: column reference "column_name" is ambiguous.

The pre-upgrade check detects such views by checking for dependencies. Ifincompatible views are found, the upgrade process is stopped and anerror message is displayed. This message lists the incompatible views in eachdatabase that need to be addressed.

Example Error Message

• Forpg_stat_activity related issues:

  Pleaseremovethefollowingusagesofviewsthatdependonfunctionsreturningdatatypesofpg_stat_activitybeforeattemptinganupgrade:(database:my_db,schemaname:public,viewname:my_stat_activity_view)

• Forpg_stat_replication related issues:

  Pleaseremovethefollowingusagesofviewsthatdependonfunctionsreturningdatatypesofpg_stat_replicationbeforeattemptinganupgrade:(database:my_db,schemaname:public,viewname:my_replication_stats_view)

To resolve such issues and proceed with the upgrade:1. Identify the views listed in the pre-upgrade check error message.

1. Drop these views usingDROP VIEWview_name;.

2. Retry the major version upgrade.

3. Once the upgrade is complete, recreate the views. Ensure the new viewdefinitions are compatible with the schema of the system catalog views in thecurrent PostgreSQL version. You might need to explicitly list columns instead ofusingselect * to avoid future issues.

For a more-detailed example of the problem and further insights, see thisstack overflow discussion

Check for SRFs in CASE statements

If you are upgrading your instance from version 9.6 and using set returningfunctions in your CASE statements, then you might see this error messageERROR: set-returning functions are not allowed in CASE. This issue occurs asfrom version 10 onwards using set-returning functions in CASE statements is disallowed.

To resolve this issue and upgrade your instance successfully, ensure that any CASE statements utilizing set-returning functions are modified to avoid their use before retrying the upgrade. Some commonly used SRFs include the following:

• unnest()
• generate_series()
• array_agg()
• regexp_split_to_table()
• jsonb_array_elements()
• json_array_elements()
• sonb_each()
• json_each()

Check views created on custom casts

If you have a view created on a custom cast, then an error message similar to the following appears:ERROR: cannot cast type <type_1> to <type_2>.This issue occurs because of permission issues on custom created casts.

To resolve this issue, update your instance to[PostgreSQL version].R20240910.01_02

For more information, seeSelf-service maintenance.

Check event trigger ownership

In Cloud SQL, all event triggers must be owned by a user with thecloudsqlsuperuser role. Cloud SQL performs a pre-upgrade check tovalidate ownership of all event triggers. If an event trigger is owned by auser who lacks thecloudsqlsuperuser role, then the upgrade process is haltedand you might get an error message, such as:

Pleaseensurethattheownersofalleventtriggershavethecloudsqlsuperuserroleassignedtothembeforeattemptinganupgrade:(database:your_db,triggerNameyour_trigger,owner:non_super_user)

To resolve this issue, either change the owner of the event trigger to a userthat has thecloudsqlsuperuser role, such aspostgres, or grant thecloudsqlsuperuser role to the current owner.

To identify event triggers with owners lacking the required role, run thefollowing command:

SELECTt.evtnameAStrigger_name,r.rolnameAScurrent_ownerFROMpg_event_triggertJOINpg_rolesrONt.evtowner=r.oidWHERENOTpg_has_role(r.rolname,'cloudsqlsuperuser','member');

The results show any event trigger with an owner who doesn't have thecloudsqlsuperuser role.

Check generated columns from unlogged tables

If you have an unlogged table which has generated columns you might see the errormessageERROR: unexpected request for new relfilenumber in binary upgrade mode.This issue occurs due to discrepancies in the persistence characteristics betweentables and their sequences for generated columns.

To address this issue, do the following:

1. Drop unlogged tables: if possible, drop any unlogged tables that are linked to generated columns. Make sure that data loss can be safely mitigated before proceeding.
2. Convert to permanent tables: temporarily, convert unlogged tables to permanent tables using the following steps:
   1. Convert the table to a logged tableALTER TABLE SET LOGGED;
   2. Perform major version upgrade
   3. Convert the table back to an unlogged tableALTER TABLE SET UNLOGGED

You can identify all such tables by using the following query :

SELECTrelnamespace::regnamespace,c.relnameAStable_name,a.attnameAScolumn_name,a.attidentityASidentity_typeFROMpg_catalog.pg_classcJOINpg_catalog.pg_attributeaONa.attrelid=c.oidWHEREa.attidentityIN('a','d')ANDc.relkind='r'ANDc.relpersistence='u'ORDERBYc.relname,a.attname;

Check the custom flags for your PostgreSQL instance

If you're upgrading to a PostgreSQL instance, version 14 or higher, then checkthe names of any customdatabase flagsthat you configured for the instance. This is because PostgreSQLplacedadditional restrictions on allowed names for custom parameters.

The first character of a custom database flag must be alphabetic (A-Z or a-z).All subsequent characters can be alphanumeric, the underscore (_) specialcharacter, or the dollar sign ($) special character.

Remove extensions

If you're upgrading your Cloud SQL instance,then you might see this error message:pg_restore: error: could not executequery: ERROR: role "16447" does not exist.

To resolve this issue, follow these steps:

1. Remove thepg_stat_statements andpgstattuple extensions.
2. Perform the upgrade.
3. Reinstall the extensions.

MVU operation running for a longer duration

There are two underlying tasks associated with a major version upgrade:

• Precheck operation: Returns a timeout error if not finished within three hours.
• Upgrade operation: Returns a timeout error if not finished within six hours.

If the instance has an ongoingMAJOR_VERSION_UPGRADE operation fora length of time longer than expected, theninvestigate the PostgreSQL error logs. This might be caused by common issues such as:

• A large number of tables, views, or indexes
• Insufficient resources such as CPU or memory
• Major transactions blocking the shutdown of databases for the upgrade process to begin. You can use the Google Cloud console to check current processes.

Common major version upgrade precheck errors

Issues found by themajor version upgrade precheckfall into these categories:

• Incompatible extensions: These areCloud SQL for PostgreSQL extensions on your instance that don't work withthe new major version.

• Unsupported dependencies: These aredependencies that either aren't supported by the new major version orneed updates to work with it.

• Database incompatibilities: These areproblems with your database or data that might happen after a majorversion upgrade. This includes differences in database structures, datatypes, encoding, collation, or system catalog changes specific to the newversion.

Incompatible extensions

The following table lists common errors related to incompatible extensionsthat the major version upgrade precheck might find:

Unsupported dependencies

The following table lists common errors related to unsupported dependenciesthat the major version upgrade precheck might find:

Database incompatibilities

The following table lists common errors related to data formatincompatibilities that the major version upgrade precheck might find:

Restore the primary instance to the previous major version

If your upgraded database system doesn't perform as expected, then youmight need to restore your primary instance to the previous version.You do so by restoring your pre-upgrade backup to a Cloud SQLrecovery instance, which is a new instance running the pre-upgrade version.

To restore a primary instance to the previous version, perform the following steps:

1. Identify your pre-upgrade backup.

   SeeAutomatic upgrade backups.

2. Create a recovery instance.

   Create a new Cloud SQLinstance using the major versionthat Cloud SQL was running when the pre-upgrade backup was made. Setthe sameflags andinstancesettings that the originalinstance uses.

3. Restore your pre-upgrade backup.

   Restoreyour pre-upgrade backup to the recovery instance. This might take severalminutes to complete.

4. Add your read replicas.

   If you're using read replicas, then add the read replicas individually.

5. Connect your application.

   Having recovered your database system, update your application with detailsabout the recovery instance and its read replicas. You can resume servingtraffic on the pre-upgrade version of your database.

FAQs

The following questions might come up when upgrading the database major version.

Is my instance unavailable during an upgrade?

Yes. Your instance remains unavailable for a period of time while Cloud SQL performs the upgrade.

How long does an upgrade take?

Upgrading a single instance typically takes less than 10 minutes. If your instance configuration has a small number of vCPUs or memory, then your upgrade might take more time.

If your instance hosts too many databases or tables, or your databases are very large, then the upgrade might take hours or even time out because the total upgrade time corresponds to the number of objects in your databases. If you have multiple instances that need to be upgraded, then your upgrade time increases proportionately. If you include replicas in your upgrade, then the upgrade operation can take up to an hour to complete, depending on the number of replicas that your primary instance has.

Can I monitor each step in my upgrade process?

While Cloud SQL lets you monitor whether an upgrade operation is still in progress, you can't track the individual steps in each upgrade.

Can I cancel my upgrade after I've started it?

No, you can't cancel an upgrade once it has started. If your upgrade fails, Cloud SQL automatically recovers your instance on the previous version.

What happens to my settings during an upgrade?

When you perform an in-place major version upgrade, Cloud SQL retains your database settings, including your instance name, IP address, explicitly configured flag values, and user data. However, the default value of the system variables might change. For example, the default value of thepassword_encryption flag in PostgreSQL 13 and earlier ismd5. When you upgrade to PostgreSQL 14, the default value of this flag changes toscram-sha-256.

To learn more, seeConfigure database flags. If a certain flag or value is no longer supported in your target version, thenCloud SQL automatically removes the flag during the upgrade.

What's next

• Learn aboutoptions for connecting to an instance.
• Learn aboutimporting and exporting data.
• Learn more about [setting database flags][flags].