RDS Postgres could not be upgraded because one or more databases do not allow connections

Question

I'm using AWS RDS to run a Postgres server, and I'm trying to do a major upgrade from 9.6.11 to 10.15. I'm using aws-cli to request the upgrade, like this:

aws rds modify-db-instance
  --db-instance-identifier my-database
  --engine-version 10.15
  --db-parameter-group-name my-pg10
  --option-group-name my-pg10
  --allow-major-version-upgrade
  --apply-immediately

The database changes from "Available" into "Upgrading" state, then 2-3 minutes later it goes back to "Available" and the version is unchanged. A new file called error/pg_upgrade_precheck.log.1613891689533 appears in the logs tab, and the content looks like this:

------------------------------------------------------------------
Upgrade could not be run on Sun Feb 21 07:14:49 2021
------------------------------------------------------------------
The instance could not be upgraded from 9.6.11.R1 to 10.15.R1 because of following reasons.
Please take appropriate action on databases that have usages incompatible with requested
major engine version upgrade and try again.
- The instance could not be upgraded because one or more databases do not allow connections. 
Please ensure all non-template0 databases allow connections and try again.
----------------------- END OF LOG ----------------------

I tried doing a major upgrade to a different version (10.7) and that failed the same way. I tried doing a minor upgrade to 9.6.20 and that worked just fine.

I'm stumped. How do I make progress upgrading this database?

Nic · Answer 1 · 2021-02-22T00:09:50.390

The Postgres docs describe how databases can be available or unavailable for connection.

It turns out that I had a few other databases hidden away inside this RDS db instance. Here’s how I found them:

SELECT datname, datallowconn
FROM pg_catalog.pg_database;

Then once I found them, simply re-enabling connections on each database was sufficient.

ALTER DATABASE <name> allow_connections true;

After doing this, the major upgrade completed successfully.

RDS Postgres could not be upgraded because one or more databases do not allow connections

1 Answers1