AWS RDS Postgres 14.7 - 15.2 upgrade fails. How do I get at the logs?

Question

I've been trying to upgrade from Postgres 14.7 to version 15.2 and fail with the error notice

```
Database instance is in a state that cannot be upgraded: Postgres cluster is in a state where pg_upgrade can not be completed successfully
```
This looks like an RDS error, Postgres doesn't list any error like this. And Postgres errors are normally very specific. I'm wondering if the upgrade is even attempted, or if there is something on the RDS setup that's aborting the upgrade before it even reaches Postgres.

Since upgrading to PG 14, we've increased the instance to db.m5.large, and added a Multi-AZ failover replica. That's all we've changed on the pure RDS side of this since the last major upgrade.

I've run pending OS patches, manually checked everything I can, followed the upgrade checklist. Nothing looks wrong, but the upgrade fails every time. I've tried about 10 times by now over two weeks.

I've already confirmed that there are no illegal `reg` references, that extensions are up to date, etc.  Note that we do *not* run PostGIS, which I see cause some folks trouble on the 14.7 -> 15.2 upgrade earlier.

The instance is configured to export both Postgres error and upgrade logs, but the upgrade logs never publishes to CloudWatch. Previous major upgrade events *do* appear in CloudWatch as expected, but nothing from my current attempts. (I've been upgrading on RDS since 9.4.)

I'm hoping to get to the underlying error so that I can resolve this and move to PG 15.2.

Thanks for any tips or help!

---

Thanks for the link to the docs. Yes, I've read the docs, and I've tried turning it off and on again ;-) I've got a bunch of custom steps I perform on my own as well to pre-flight the system, and I've run all of the standard checks RDS suggests. I have *not* done anything that requires a new instance or a complete database rebuild.
 
My working theory is that something is failing in pre-flight on the RDS checks, and `pg_upgrade` is never invoked. That's why I'm not finding and new upgrade log entries in CloudWatch. I *do* have upgrade entries from 2019-2022, so it looks like I've had that enabled during the previous four major version upgrades.

This time, I'm getting this message from RDS:
```
Database instance is in a state that cannot be upgraded: Postgres cluster is in a state where pg_upgrade can not be completed successfully
```

I translate that to mean roughly  ¯\_(ツ)_/¯. It's an RDS-generated error, but there are zero details to work with. If I had the details, I might know what to do.

Does anyone know how to get at those details?

Thanks

davidadams · Answer

Stefan, thank you for you answer! I had not checked the template1 database. I've now double-checked all of the databases, and don't see anything odd. plpgsql is installed in template1, but that seems normal. Interesting though. I've had a case open with AWS for about 5 days, they confirmed the problem and are trying to track it down. I will add your experience to the notes, maybe that will help.

Thanks again for taking the time to add your note. I'll add this to my own pre-flight list for next year. Assuming I get to 15.x before then!

Stefan · Answer

We had exactly the same issue. In our case, our template1 database had installed some pg extensions and also allow connections configured to false. After allowing connections `psql -c "alter database template1 with ALLOW_CONNECTIONS = true"`, we were able to proceed with upgrade.

nemf · Answer

This page might help you.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html#USER_UpgradeDBInstance.PostgreSQL.MajorVersion.Process

AWS RDS Postgres 14.7 -> 15.2 upgrade fails. How do I get at the logs?

Relevant content