Feedback-ID: ia2694551:Fastmail
Date: Wed, 25 Oct 2023 13:27:30 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Aditya D <dsaditya91@gmail.com>
Cc: Ron <ronljohnsonjr@gmail.com>, pgsql-admin@lists.postgresql.org
Subject: Re: User/Roles, Owner, and privileges migration strategy
Message-ID: <202310251127.fhwfz7o7wjio@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAEATte6+NPnY5o+i1b370PZm3L+LdsJ17uMDenBhAp9pKMGmrA@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202310251127.fhwfz7o7wjio%40alvherre.pgsql>
Precedence: bulk

On 2023-Oct-24, Aditya D wrote:

> Thanks a lot Holger and Ron for the reply and valuable inputs. AWS RDS or
> any other PaaS PostgreSQL instances does not support Superuser and to avoid
> multiple pg_dump statements from on-premises to PaaS, is there any
> recommended way?
> 
> Query the catalog tables and form the alter owner statements which will run
> post pg_dump - - no owner or any other suggested method?

I would do this using the following steps:

1. take a "pg_dumpall -g".

2. take a pg_dump (in -Fc or -Fd mode) of each database to migrate.  Do
not use "--no-owner" or anything that breaks or incompletely dumps the
ownership or ACLs or objects.

3. write a script to filter each of the dumps in step 2, to obtain the
list of roles needed in GRANT commands or as object owners.

4. Filter the dump produced by step 1 down to the creation of just the
roles obtained by step 3.

5. Restore each of the dumps from step 2.


If step 5 finishes cleanly, then you're done.  If it throws errors about
ownership or GRANTs, then your filtering scripts in steps 3 or 4 have
some mistake.  Go back and fix the script, clean up from steps 4 and 5
and restart from 3.  Lather, rinse, repeat.

Note that you only need to dump each database exactly twice (first to
produce the dumps you'll test with, second to do the actual migration
once your filtering script for steps 3 and 4 have been perfected.)

If you have objects owned by "postgres" or some other superuser, I'd
change them ahead of time to something else.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"In fact, the basic problem with Perl 5's subroutines is that they're not
crufty enough, so the cruft leaks out into user-defined code instead, by
the Conservation of Cruft Principle."  (Larry Wall, Apocalypse 6)