HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

postgres - pg_dump and pg_restore without roles

Submitted by: @import:stackexchange-dba··
0
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 --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

Solution

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

Code Snippets

$ pg_dump -O -x mydb_development | gzip > mydb-2020-09-22.bak.gz

Context

StackExchange Database Administrators Q#169070, answer score: 24

Revisions (0)

No revisions yet.