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?

  • 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... – Mikko Rantalainen Aug 27 '14 at 6:00

I ended up using grep for this:

egrep -v '^(CREATE|DROP) ROLE postgres;' dump.sql \
| psql -X -v ON_ERROR_STOP=1 -1 ...
| improve this answer | |
  • I think your regular expression should only match DROP and CREATE, not ALTER. Otherwise that should do the job. – Mikko Rantalainen Jun 20 '17 at 15:49
  • @MikkoRantalainen good point, fixed. – 11181 Jun 20 '17 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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