aurora postgres major upgrade - pg_dumpall


Hello, I am planning for Aurora postgres (aurora - 1.3.1, pg engine 9.6) to Aurora postgres (aurora 2.2, pg - 10.6). Since there is no native aws method of doing the upgrade, manual upgrade has to be done for which will use pg_dump on old version and pg_restore on new version. Before this I want to extract all users and roles and create them on the target so that pg_restore does not complain about user/group not found. But when I am trying to use pg_dumpall with -g, it complains saying no permission on pg_authid table.

Researching on this looks like rds does not provide access to this table, so what are my options to extract users/roles and their corresponding passwords. I would want to avoid a situation where I would have to create users with new passwords. Kindly advice how to deal with this.

Edited by: nmakb on Apr 4, 2019 8:08 PM

asked 4 years ago130 views
2 Answers

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

answered 4 years ago

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.

answered 4 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions