patternsqlMajor
postgres - pg_dump and pg_restore without roles
Viewed 0 times
withoutpg_dumppostgresrolesandpg_restore
Problem
I'm trying to restore a dump without having the appropriate roles on the receiving database.
As mentioned here but also here, you need to have the
I've used the following command line to create my dump:
The restoring line is as follows:
As you can see, both have the
What terribly bugs me is the log below:
Why does it say it needs a role even if the
Did I miss something?
I'm running on Postgres 9.3
As mentioned here but also here, you need to have the
--no-owner as an option, either in pg_dump or pg_restore or both.I've used the following command line to create my dump:
"C:\Program Files\PostgreSQL\9.3\bin\pg_dump.exe" --no-owner -Ft --dbname=postgresql://avo******:AV0******?@127.0.0.1:5432/BI_DB > K:\BI\backup\sort\bck_%timestamp%.tarThe restoring line is as follows:
"C:\Program Files\PostgreSQL\9.3\bin\pg_restore.exe" --host localhost --port 5432 --username "postgres" --dbname "BI_TEST2" --no-password --no-owner --role=postgres --exit-on-error --verbose "D:\D\avo\backup\bck_04042017_1410.tar"As you can see, both have the
--no-owner option but eventually, I have the error below:What terribly bugs me is the log below:
pg_restore: [programme d'archivage (db)] Erreur pendant le traitement de la TOC (« PROCESSING TOC ») :
pg_restore: [programme d'archivage (db)] Erreur à partir de l'entrée TOC 2633 ; 0 0 ACL adm avo******
pg_restore: [programme d'archivage (db)] could not execute query: ERREUR: role « avo****** » does not existWhy does it say it needs a role even if the
--no-owner was specified?Did I miss something?
I'm running on Postgres 9.3
Solution
Using PostgreSQL 12.4:
Here's an example:
$ pg_dump --help
...
-O, --no-owner skip restoration of object ownership in
plain-text format
...
-x, --no-privileges do not dump privileges (grant/revoke)
Here's an example:
$ pg_dump -O -x mydb_development | gzip > mydb-2020-09-22.bak.gzCode Snippets
$ pg_dump -O -x mydb_development | gzip > mydb-2020-09-22.bak.gzContext
StackExchange Database Administrators Q#169070, answer score: 24
Revisions (0)
No revisions yet.