Upgrading Amazon Aurora PostgreSQL from version 14 to 16....

Upgrading Amazon Aurora PostgreSQL from version 14 to 16 requires a manual major version upgrade process, as it involves potentially non-backward-compatible changes. Aurora uses the pg_upgrade utility internally, creating a pre-upgrade snapshot for rollback if needed, but expect brief outages during the process 

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html

Amazon Aurora PostgreSQL-Compatible Edition has added support for PostgreSQL versions 17.6, 16.10, 15.14, 14.19, and 13.22. The update includes the PostgreSQL community's product improvements and bug fixes, and also includes Aurora-specific enhancements.

Dynamic Data Masking (DDM) (16.10 and 17.6 only) is a new database-level security feature that protects sensitive data like personally identifiable information by masking column values dynamically at query time based on role-based policies, without altering the actual stored data. This release also includes a shared plan cache, improved performance and recovery-time-objective (RTO) and improvement for Global Database switchovers.

Amazon Aurora PostgreSQL-Compatible Edition now supports PostgreSQL versions 16.6, 15.10, 14.15, 13.18, and 12.22. Please note, this release supports the versions released by the PostgreSQL community on November 21,2024 and not the previous November 14,2024 release. These releases contain product improvements and bug fixes made by the PostgreSQL community, along with Aurora-specific security and feature improvements such as improved read replica upgrades for reduced downtime, new features for Babelfish and improved Global Database cross-region resiliency for better read availability during unplanned events.  

As a reminder, Amazon Aurora PostgreSQL 12 standard support ends on Feb 28, 2025. You can either upgrade to a newer major version or continue to run Amazon Aurora PostgreSQL 12 past the end of standard support date with RDS Extended Support.

Prerequisites

Note: If you have a recovery point of objective or recovery point objective (RPO) set for your global cluster,we need  to turn that off temporarily, as major upgrades are not  supported with RPO enabled.

Review and address these checks before starting to ensure a smooth upgrade:

• Verify template0 and template1 databases exist and are marked as templates using 

    SELECT datname, datistemplate FROM pg_database;.​

• Drop any invalid databases (datconnlimit = -2), logical replication slots 

  SELECT * FROM pg_replication_slots;  and commit open prepared transactions 

 SELECT count(*) FROM pg_prepared_xacts;

• Update extensions like PostGIS (ALTER EXTENSION postgis UPDATE;) to compatible versions and remove unsupported ones (e.g., chkpass for older targets).

• Confirm no unsupported data types like most reg* types or unknown in user tables.​

Check for unsupported usage:

• Prepared transactions – Commit or roll back all open prepared transactions before attempting an upgrade.You can use the following query to verify that there are no open prepared transactions on your database.

    SELECT count(*) FROM pg_catalog.pg_prepared_xacts;

Reg* data types – Remove all uses of the reg* data types before attempting an upgrade. Except for regtype and regclass, you can't upgrade the reg* data types. The pg_upgrade utility can't persist this data type, which is used by Amazon RDS to do the upgrade.

To verify that there are no uses of unsupported reg* data types, use the following query for each database.

SELECT count(*) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a

  WHERE c.oid = a.attrelid   AND NOT a.attisdropped

      AND a.atttypid IN ('pg_catalog.regproc'::pg_catalog.regtype,

                         'pg_catalog.regprocedure'::pg_catalog.regtype,

                         'pg_catalog.regoper'::pg_catalog.regtype,

                         'pg_catalog.regoperator'::pg_catalog.regtype,

                         'pg_catalog.regconfig'::pg_catalog.regtype,

                         'pg_catalog.regdictionary'::pg_catalog.regtype)

      AND c.relnamespace = n.oid

      AND n.nspname NOT IN ('pg_catalog', 'information_schema');

• Check for invalid databases:

  • Ensure there are no invalid databases. The datconnlimit column in the pg_database catalog includes a value of -2 to mark databases as invalid that were interrupted during a DROP DATABASE operation.
    Use the following query to check for invalid databases:

  • SELECT datname FROM pg_database WHERE datconnlimit = - 2;

The previous query returns invalid database names. You can use DROP DATABASE invalid_db_name; to drop invalid databases. You can also use the following command to drop invalid databases:

SELECT 'DROP DATABASE ' || quote_ident(datname) || ';' FROM pg_database WHERE datconnlimit = -2 \gexec

Handle logical replication slots – An upgrade can't occur if the database has any logical replication slots. Logical replication slots are typically used for AWS DMS migration and for replicating tables from the database to data lakes, BI tools, and other targets. Before upgrading, make sure that you know the purpose of any logical replication slots that are in use, and confirm that it's okay to delete them. If the logical replication slots are still being used, you shouldn't delete them, and you can't proceed with the upgrade.

If the logical replication slots aren't needed, you can delete them using the following SQL:

  SELECT * FROM pg_replication_slots WHERE slot_type NOT LIKE 'physical';

SELECT pg_drop_replication_slot(slot_name);

Use the following query to check for the number of large objects: 

   SELECT count(*) FROM pg_largeobject_metadata;

Upgrade Steps

1. Check available target versions for your current 14.x minor version using AWS CLI:

 aws rds describe-db-engine-versions --engine aurora-postgresql --engine-version 14.x --query 'DBEngineVersions[].ValidUpgradeTarget[?IsMajorVersionUpgrade == true].{EngineVersion:EngineVersion}'.​

2. Test on a snapshot restore or clone: Create a test cluster, apply the upgrade, and validate application performance.​

3. Prepare parameter groups compatible with version 16.​

4. Perform the upgrade via console 

Modify cluster > Engine version > 16.x > Allow major version upgrade),

CLI (aws rds modify-db-cluster --db-cluster-identifier mycluster --engine-version 16.x --allow-major-version-upgrade), or API.​

Post-Upgrade Tasks

Run ANALYZE VERBOSE; on all databases to refresh optimizer statistics, as they aren't carried over. Test workloads thoroughly, upgrade extensions manually, and monitor logs like pg_upgrade_internal.log.

Considerations

Direct upgrades from 14 to 16 are supported if on a compatible minor version (e.g., 14.14+ for some paths); otherwise, step via intermediates. Use blue/green deployments for near-zero downtime if outages are critical. Aurora PostgreSQL 16 offers enhancements like better security roles and performance optimizations.

(1) https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.html

(2) https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.PostgreSQL.MajorVersion.html

(3) https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process.html

(4) https://aws.amazon.com/blogs/database/strategies-for-upgrading-amazon-aurora-postgresql-and-amazon-rds-for-postgresql-from-version-13/

(5) https://www.reddit.com/r/PostgreSQL/comments/1gfnj9b/major_update_from_12_to_16/

(6) https://www.youtube.com/watch?v=_PffWoxuP9A

(7) https://stackoverflow.com/questions/56011770/upgrading-postgres-aurora-major-version

(8) https://www.amazonaws.cn/en/new/2025/amazon-aurora-now-supports-postgresql-16-6-15-10-14-15-13-18-and-12-22/

(9) https://aws.amazon.com/about-aws/whats-new/2025/11/amazon-aurora-minor-versions-17-6-16-10-15-14-14-19-13-22/

(10) https://www.easydeploy.io/blog/upgrade-amazon-aurora-postgresql-latest-version/#