Step by Step Upgrade AWS Aurora to PostgreSQL 15

Amazon Aurora PostgreSQL-Compatible Edition now supports PostgreSQL major version 15 (15.2). Following is step by step tutorial to upgrade Amazon Aurora to PostgreSQL 15. 

 1- Have a version-compatible parameter group ready.

2- You can use the following query to verify that there are no open prepared transactions on your instance.

SELECT count(*) FROM pg_catalog.pg_prepared_xacts;

3- 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 (used by Amazon Aurora to do the upgrade) can't persist this data type. 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');

  

4- Drop logical replication slots.

The upgrade process can't proceed if the Aurora PostgreSQL DB cluster is using any logical replication slots.    

You can check for logical replication slots using the following query:

SELECT * FROM pg_replication_slots;

you can delete them using the following SQL:

SELECT pg_drop_replication_slot(slot_name);

5- Upgrade the Instance 

aws rds modify-db-cluster \

    --db-cluster-identifier mydbcluster \

    --engine-version new_version \

    --allow-major-version-upgrade \

    --no-apply-immediately