Ivan IP
Ivan IP

Reputation: 11

Copy production DB from one Azure Database for PostgreSQL Single Server to another one in a lower environment

I was working on a project in which customer is asking us to copy a production DB (87 GB) from a production Azure Database for PostgreSQL Single Server (version 11) to another one in pre-production with same version and both are in same Azure region.

I use pgAdmin4 and I dumped it using PSQL tool pg_dump.exe --file "D:\PostgreSQL_backups\xxxx.sql" --host "xxxx.postgres.database.azure.com" --port "xxxx" --username "xxxx@xxxx" --no-password --verbose --role "xxxx" --format=c --blobs --compress "0" --section=pre-data --section=data --section=post-data "xxxx"

And then on target I created a new DB (with same name as source) and used:

pg_restore.exe --host "xxxx.postgres.database.azure.com" --port "xxxx" --username "xxxx@xxxx" --no-password --role "xxxx" --dbname "xxxx" --section=pre-data --section=data --section=post-data --no-owner --no-privileges --disable-triggers --single-transaction --verbose "D:\xxxx.sql"

FYI, DB takes like 45 minutes to back up and another 1 hour and a half to restore. The backups are performed to and restored from a disk in a Windows Server jumphost which is also located in same Azure region.

The problem is, I had to replicate each object permission manually which is not agile at all. Also, the same roles (at least the one that app uses) already exist in target server with same password, and I only noticed that the password for one of the other roles is different that the same one in production (encrypted value is different) but that one doesn't seem to be the one app uses.

For the moment the application can reach the production DB in preprod instance withot issue, confirmed by customer.

So, I wanted to know which parameters to use or procedure to carry on the object level permissions to the other instance. I tried the global backups from the pgAdmin4 but only includes create/alter roles to provide server level permissions and also other GRANTS to include roles into another roles, but there are no grant X privileges on object for X role.

Thank you beforehand!

I did backup and restore using pg_dump and pg_restore using pgAdmin4 tool and I was expecting to get the permissions at object level but that wasn't the case.

Upvotes: 1

Views: 1290

Answers (0)

Related Questions