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 --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%.tar
The 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 exist
Why 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
Best Answer
The error occurs when
pg_restore
set the ACLs : you can use--no-acl
to preventGRANT
commands.With the
-Ft
option inpg_dump
, you can skip roles and ACLs only inpg_restore
. You can also edit the catalog with--list
if you need more details.