RDS cannot be upgraded from 11.18 to 12.17

Question

Hi all!

We're encountering difficulties upgrading our RDS PostgreSQL instance from version 11 to 12 due to the presence of tables declared WITH OIDS, which is not supported in the upgrade process.

While we've successfully removed OIDS for all visible databases, it appears there are system or private databases that remain inaccessible for executing this process.

One such database causing the issue is named "postgrestdm20d4yu9wxfnxghlyaxzqt9pz1nbzb".

I don't have access to that database from the admin Postgres user.

I asked to AWS support grant me access to the database so I could delete the OIDs myself but I didn't receive a response still after 20 days.

do you have another idea how I can resolve it?

thanks!

Attachments

Answer

Perform the upgrade in multiple steps, upgrading to each intermediate version (11 to 11.x to 12.x etc). This will allow the precheck to catch any incompatible objects at each step.

Check for tables declared WITH OIDS by running 
SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE' AND table_catalog='your_database' AND table_schema NOT IN ('pg_catalog', 'information_schema') AND oid_is_present IS TRUE;
. Drop any tables returned.

Try upgrading just the data directories and binaries instead of the whole instance. Make a backup, upgrade the data directories on a test server, then restore the backup to a new upgraded instance.

Check the upgrade logs for any errors. The precheck log may provide more details on what objects are blocking the upgrade.

Consider contacting AWS Support for assistance in troubleshooting upgrade issues, as they may have tools or workarounds to help resolve incompatible objects.

RDS cannot be upgraded from 11.18 to 12.17

Relevant content