From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
cc: Aditya D <dsaditya91@gmail.com>, Ron <ronljohnsonjr@gmail.com>,
        pgsql-admin@lists.postgresql.org
Subject: Re: User/Roles, Owner, and privileges migration strategy
In-reply-to: <202310251127.fhwfz7o7wjio@alvherre.pgsql>
References: <202310251127.fhwfz7o7wjio@alvherre.pgsql>
Comments: In-reply-to Alvaro Herrera <alvherre@alvh.no-ip.org>
	message dated "Wed, 25 Oct 2023 13:27:30 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1004360.1698256270.1@sss.pgh.pa.us>
Date: Wed, 25 Oct 2023 13:51:10 -0400
Message-ID: <1004361.1698256270@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1004361.1698256270%40sss.pgh.pa.us>
Precedence: bulk

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> 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?

> I would do this using the following steps:
> ...

I kind of suspect that that's going to be a dead end.  If you can't
run dump and restore as superuser, it's going to be very hard to deal
with a multi-user database, unless you use --no-owner which of course
doesn't restore the multiple object ownerships.

You might have to resort to separately dumping the objects belonging
to each user, and then running each restore as that user.

			regards, tom lane