aurora postgres major upgrade - pg_dumpall

0

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

nmakb
已提問 5 年前檢視次數 1039 次
2 個答案
0

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

nmakb
已回答 5 年前
0

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.

已回答 5 年前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南