How to Restore Everything from pg_dumpall Backup Including ‘postgres’ Role

backuperrorspostgresqlrestore

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?

Best Answer

I ended up using grep for this:

egrep -v '^(CREATE|DROP) ROLE postgres;' dump.sql \
| psql -X -v ON_ERROR_STOP=1 -1 ...
Related Question