Re: User/Roles, Owner, and privileges migration strategy - Mailing list pgsql-admin

On 10/21/23 21:51, ADITYA DUVURI wrote:

Hi Team,

Let me take a user scenario here, I have an On-premises PostgreSQL instance with 50 databases. Out of which I need to migrate only 15 databases. There are around 10 different users and roles and millions of different database objects.

In this example at the database level the owner for some objects are - 

When I perform pg_dump with owners and ACL. the statements formed are -

• Create Table2
• Alter Owner Table2 to user2
• Grant All privileges on Table2 to user2

The issue while restoring the above dump file created in any on-premises/AWS/GCP PostgreSQL instance fails since the grant statements have to be executed first before the Alter owner statement.

Did you first run "pg_dumpall --globals-only" against the on-prem server?  (Maybe you did, and I missed it.)

That has all of the CREATE and ALTER ROLE statements.  You'll have to edit it first, though, to remove references to role "postgres", and other statements that are outdated, or irrelevant to AWS RDS.