Spent a few hours dealing with errors on RDS when upgrading from a postgresql 10.6 to 11.1. In case someone else is dealing with the same problems, this might be helpful.
A few things to know about this database:
- PostGIS extension was installed but was not being used
- ip4r extension was installed but was also not used
This probably turned out to be a Good Thing (tm). If you are using PostGIS extensively, I wish you good luck.
Here are the list of errors I got:
On the recent events tab (make sure to sort by time otherwise this may not show)
Database instance is in a state that cannot be upgraded: PreUpgrade checks failed: The instance could not be upgraded because there are one or more databases with an older version of PostGIS installed. Please upgrade all installations of PostGIS and try again.
I naively tried to upgrade PostGIS extension, so I executed:
ALTER EXTENSION postgis_tiger_geocoder UPDATE;
ALTER EXTENSION postgis_topology UPDATE;
ALTER EXTENSION postgis UPDATE;
I could not figure how to upgrade address_standardizer and address_standardizer_data_us (cannot remember the error I got, and could not find a well documented way to upgrade)
I rebooted for good measure and tried again, at one point, I got this error
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt
So I gave up and went the brute force route and dropped all PostGIS related extensions and schemas:
DROP EXTENSION address_standardizer;
DROP EXTENSION address_standardizer_data_us;
DROP EXTENSION postgis_tiger_geocoder;
DROP EXTENSION postgis_topology;
DROP EXTENSION postgis;
I additionally updated all the extensions to their default version (ip4r was updated)
To find the extensions I likely needed to upgrade I used:
SELECT * from pg_available_extensions where default_version <> installed_version
I tried again the upgrade to 11.1 and got
*failure*
There were problems executing ""/rdsdbbin/postgres/bin/pg_dump" --host /rdsdbdata/log/upgrade --port 8192 --username rdsadmin --schema-only --quote-all-identifiers --binary-upgrade --format=custom --verbose --file="pg_upgrade_dump_16402.custom" 'dbname=reporting' >> "pg_upgrade_dump_16402.log" 2>&1"
Consult the last few lines of "pg_upgrade_dump_16402.log" for
the probable cause of the failure.
I opened tickets to AWS Business Support, but with details of all those errors while I was investigating.
NB: it was a bit frustrating as there is no way to get access to those log files.
I had the (really obvious) idea to try to replicate this command locally:
pg_dump" --host <hostname> --port 5432 --username <admin-user> --schema-only --quote-all-identifiers --binary-upgrade --format=custom --verbose --file="pg_upgrade_dump.custom" 'dbname=<db-name>'
Replace <admin-user>, <hostname> and <db-name> with the relevant one.
And I got this error (I had other errors due to table ownership that were not owned by <admin-user> but fixed this):
pg_dump: could not parse numeric array "2281": too many numbers
A Google search only pointed me to this post: https://www.postgresql.org/message-id/1483455943.3206.5.camel%40gunduz.org
Not much information in it, but it gave maybe a hint that the problem could be with ip4r.
I ran:
SELECT proname, proargtypes, pronargs FROM pg_proc WHERE array_length(proargtypes, 1) > pronargs
and it found:
| gip4r_consistent | 2281 792993 21 26 2281 | 3 |
| gip6r_consistent | 2281 793005 21 26 2281 | 3 |
| gipr_consistent | 2281 793017 21 26 2281 | 3 |
instead of removing the rows (not clear what the impact could be ?), I dropped the ip4r extension since it is not used
DROP EXTENSION ip4r;
After all of this, I could successfully upgrade PostgreSQL on RDS from 10.6 to 11.1