1

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?

Eugen Konkov
  • 22,193
  • 17
  • 108
  • 158
  • 2
    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 –  Aug 10 '20 at 09:43

1 Answers1

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263