RDS cannot be upgraded from 11.18 to 12.17

0

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

1 Answer
0

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.

profile picture
EXPERT
answered 4 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions