Postgres 14.12 upgrade failing on the pg_restore step

Question

I am trying to update my database from Postgres 14.12 to either Postgres 15 or Postgres 16. I have attempted both, and I get the same error. I keep running into the following error:

> Database instance is in a state that cannot be upgraded: Postgres cluster is in a state where pg_upgrade can not be completed successfully.

In the error/pg_upgrade_internal.log file from the upgrade, it mentions that the pg_restore for main database on the server fails. The error message is the following:

> Consult the last few lines of "/rdsdbdata/upgrade/db/pg_upgrade_output.d/20241018T160220.813/log/pg_upgrade_dump_16413.log" for
the probable cause of the failure.

As far as I could find, there is no way for me to access this log file.

So my question ends up being: Is there a way for me to look at this file? If not, is there a location within AWS RDS that can give me more information on the failure?

re:Post Agent · Answer

Unfortunately, you cannot directly access the specific log file mentioned in the error message as it's located within the internal RDS infrastructure. However, there are a few steps you can take to troubleshoot and gather more information about the upgrade failure:

1. Check CloudWatch Logs: RDS typically streams its logs to CloudWatch. You might find relevant information about the upgrade process and any errors in the PostgreSQL log group for your RDS instance.

2. Review the PostgreSQL error logs: Connect to your RDS instance and query the PostgreSQL error logs using SQL commands. This might provide additional context about the upgrade failure.

3. Use the AWS RDS console: Navigate to the RDS console and check the "Events" tab for your database instance. This often contains important notifications about upgrade attempts and failures.

4. Contact AWS Support: If you have an appropriate support plan, you can open a ticket with AWS Support. They have access to more detailed logs and can provide specific guidance on your upgrade issue.

5. Pre-upgrade checks: Before attempting the upgrade again, ensure that your database is in a consistent state. Run ANALYZE and VACUUM operations, and check for any corrupt indexes or tables.

6. Consider using pg_dump and pg_restore: For databases smaller than 100 GB, you might want to consider using pg_dump to export your data from the 14.12 instance, create a new instance with the desired version (15 or 16), and then use pg_restore to import the data. This approach can sometimes bypass issues encountered during in-place upgrades.

7. Check compatibility: Ensure that all your database objects, extensions, and custom functions are compatible with the target PostgreSQL version. Incompatibilities can cause upgrade failures.

If you continue to face issues, it's recommended to engage with AWS Support for a more in-depth investigation of the upgrade failure.  
**Sources**  
[Troubleshooting Amazon Managed Workflows for Apache Airflow - Amazon Managed Workflows for Apache Airflow](https://docs.aws.amazon.com/mwaa/latest/userguide/troubleshooting.html)  
[PostgreSQL pg_dump and pg_restore utility - Database Migration Guide](https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatabases.postgresql-rds-postgresql-full-load-pd_dump.html)  
[Choosing a major version for an RDS for PostgreSQL upgrade - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.MajorVersion.html)

Postgres 14.12 upgrade failing on the pg_restore step

Add your answer

Relevant content