aurora postgres major upgrade - pg_dumpall

The only way to do is to manually dump and restore.

The only way to take backup of Globals in Postgres (for e.g. SEQUENCEs / USERs / GROUPs etc.) is using pg_dumpall, which fails on RDS Postgres, since it tries to access pg_authid (which is blocked for all RDS users, including rds_superuser / Masteruser) but pg_dumpall allow a --no-role-passwords flag, support for which starts in Postgres v10+. This flag allows users to take a dump of all Postgres Globals (including Users, but excluding User-Passwords) and it works with RDS.

This workaround might still work for you even if you are on postgresql version 9.6 but instead of using client tools like pg_admin you will have to use the psql utility tool as psql is backward compatible, so even if the Postgres database is on Version 9.6, psql v10 is able to properly recognize and reliably take backups of all preceding versions. This is not the official recommendation as pg_dumpall version 10 is suited for Postgres 10 only and Postgres community doesn't suggest to use Postgres utility such as psql, pg_dump pg_dumpall for different version. However, it can still act as a workaround to achieve your use case.

In summary these are the steps you need to perform to achieve this same.

1. Download / Install Postgres client (psql) v10 or newer
2. Take backup of the cluster using pg_dumpall --no-role-passwords flag
3. Restore to target cluster (as is normally done)
4. Run ALTER USER WITH PASSWORD to set User passwords that require logging into the Cluster. [For e.g. ALTER USER abc WITH PASSWORD 'abc';].

However, it is highly recommended to test the workaround first before implementing it in production.

I understand that this is indeed a very valid use case and there should be some way to export global objects like roles, users, etc. There is already an existing feature request for the same.