Re: User/Roles, Owner, and privileges migration strategy

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Am 22.10.23 um 04:51 schrieb ADITYA DUVURI:
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 - 
image.png

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.

One of the solutions is to have multiple pg_dump statements like -
  • pg_dump - schema only with no owner no ACL
  • pg_dump  -schema only with ACL | sed "Grant All"
  • pg_dump -schame only with owner | sed "Alter owner"
The above solution might have a performance impact since the source might contain millions of objects and performing pg_dump is an expensive operation multiple times. Is there any other way to perform different ownership of objects and grant migration from source to target PostgreSQL instances for that specific database and in an automated way?

Source version of PostgreSQL instance can be - 10,11,12
Target version of PostgreSQL instance can be - 14, 15

Thanks & Regards,
Aditya D

On Fri, 20 Oct 2023 at 20:39, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:


On Friday, October 20, 2023, ADITYA DUVURI <adilove1987@xxxxxxxxx> wrote:
Hi Team,

Can you please let us know what is the best strategy or different approaches to migrate users/roles, privileges and owner to different PaaS PostgreSQL instance like AWS RDS or GCP cloud SQL from on-premises?

Execute the migration scripts you have sitting in version control against the new server then restore the dumped data.

David J.


Ordinary Dump files are made to be restored by a superuser role, so that GRANT and ALTER statements can be executed in any order.

Dump files created with the option --no-owner can be played back by any user capable of creating objects, but the created objects will be owned by the creating user.


-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012

Attachment: OpenPGP_signature
Description: OpenPGP digital signature


[Index of Archives]     [Postgresql Home]     [Postgresql General]     [Postgresql Performance]     [Postgresql PHP]     [Postgresql Jobs]     [PHP Users]     [PHP Databases]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Forum]

  Powered by Linux