Postgresql - backup restore on different owner?

Question

My question is similar to this I even vote up it, but unfortunately in my case I have text dump and I can not restore using pg_restore =(

zcat /home/kes/work/projects/bennet/db/bennet.sql.gz | \
    pg_restore -h 127.0.0.1 -p 5432 -U bennet --no-owner --role=bennet  -d bennet
pg_restore: error: input file appears to be a text format dump. Please use psql.

But psql has no role and no-owner options

Is there a way to restore text dump on different owner?

If the original owner does not exist in the target db, you could use psql -U new_owner -f yourscript.sql and simply ignore the errors that ownership can't be restored — user330315
– user330315, Commented Aug 10, 2020 at 9:43

Laurenz Albe · Accepted Answer · 2020-08-10 09:39:32Z

1

No. You have two options:

Manually edit the SQL script. This is cumbersom and error-prone.
Restore the complete dump to a new, empty scratch database using psql and dump that with --role and --no-owner as you need.

answered Aug 10, 2020 at 9:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql - backup restore on different owner?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related