postgres - pg_dump and pg_restore without roles

Question

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

score 30 · Answer 1 · edited Sep 23 '20 at 10:36

30

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

edited Sep 23 '20 at 10:36

Colin 't Hart

9,455
15
36
44

answered Sep 22 '20 at 22:35

Duke

401
4
2

score 20 · Accepted Answer · answered Apr 05 '17 at 08:00

20

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.

answered Apr 05 '17 at 08:00

slardiere

646
4
6

score 5 · Answer 3 · answered Feb 15 '21 at 13:14

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

score 0 · Answer 4 · answered Jan 18 '23 at 10:08

Alternatively, you can restore a database backup into another database and at the same time create the privileges for another user without any prerequisites with regard to how pg_dump has been run by using the options

--dbname <new_database> --no-owner --role <new_user>

Do not use --no-privileges, as this would suppress all GRANTs during restore.

postgres - pg_dump and pg_restore without roles

4 Answers4