9

I have a postgres 9.1 server running and I do backups with

pg_dumpall -c -f /nfs/backup/fulldump.sql

I'm testing restoring the backup to another server with

psql -f /nfs/backup/fulldump.sql

while running as user postgres and I get two errors

psql:/nfs/backup/fulldump.sql:27: ERROR:  current user cannot be dropped
psql:/nfs/backup/fulldump.sql:36: ERROR:  role "postgres" already exists

I understand that these are caused by commands

DROP ROLE postgres;
CREATE ROLE postgres;

in the backup dump.

Is it possible to somehow restore the dump from pg_dumpall -c without having a single error?

1
  • Searching around the web hints that single user mode (postgres --single) could be the correct answer. That can be started like described in answer dba.stackexchange.com/a/61784/29183 -- my dump takes around an hour to read in so I have to test it some later time... Commented Aug 27, 2014 at 6:00

1 Answer 1

8

I ended up using grep for this:

egrep -v '^(CREATE|DROP) ROLE postgres;' dump.sql \
| psql -X -v ON_ERROR_STOP=1 -1 ...
3
  • I think your regular expression should only match DROP and CREATE, not ALTER. Otherwise that should do the job. Commented Jun 20, 2017 at 15:49
  • @MikkoRantalainen good point, fixed.
    – 11181
    Commented Jun 20, 2017 at 18:16
  • 3
    I can't believe this is still a problem. Thanks for the grep.
    – Jeff
    Commented Sep 21, 2022 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.