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******?@ > 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:

enter image description here

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


The error occurs when pg_restore set the ACLs : you can use --no-acl to prevent GRANT commands.

With the -Ft option in pg_dump, you can skip roles and ACLs only in pg_restore. You can also edit the catalog with --list if you need more details.


Using PostgreSQL 12.4:

$ 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.gz

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.