16

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:

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

16

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.

0
5

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
1

The following commands export data without mixing it with role or privilege data so that you can import it to any role you specify. Also, you can make pg_dump compress the data to a tar file for you.

pg_dump --format=t --blobs --verbose --no-privileges --no-owner \
--password --username mydbuser --database mydbname  \
--file "mydbname_dump_$(date +%Y%m%d).tar"

Or a shorter one for it that does the same thing

pg_dump -F t -bvxOW -U myuser -d mydbname  -f "mydbname_dump_$(date +%Y%m%d).tar"

To restore you will then use almost the same flags and also mention which role you want ownership to belong to.

pg_restore -vxOW --role=myuser2 -U myadminuser -d mydbname "mydbname_dump_$(date +%Y%m%d).tar"

Below is what all these flags mean and some more for additional options like compression level or format to output. You can find more by doing pg_dump --help or pg_restore --help:

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -v, --verbose                verbose mode
  -Z, --compress=0-9           compression level for compressed formats

Options controlling the output content:
  -b, --blobs                  include large objects in dump
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -x, --no-privileges          do not dump privileges (grant/revoke)

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

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.