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.
- Download / Install Postgres client (psql) v10 or newer
- Take backup of the cluster using pg_dumpall --no-role-passwords flag
- Restore to target cluster (as is normally done)
- 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.
aurora postgres major upgrade - pg_dumpallasked 4 years ago
Migrating Aurora Postgres to RDS Postgresasked 23 days ago
Update RDS Aurora Postgres from 13.4 to 14.4asked 9 days ago
Aurora Postgres Sequences Skipping Valuesasked 6 days ago
XA Global Transaction Support for Aurora Postgresasked 10 months ago
Aurora read replica for RDS Postgres and long-term useasked 3 months ago
Missing Aurora Function after upgrade from aurora pg 12 to pg 13asked 10 months ago
Major version upgrade of RDS Aurora Postgres cluster via CloudFormation with custom parameter group failsasked 5 months ago
Upgrading Aurora Postgres from 10.16 to 11.11asked a year ago
Upgrade RDS Postgres engine from 9.6.11 to 10.6 - Migrate to AuroraAccepted Answerasked 4 years ago