Backing up & restoring Postgres using pg_dumpall, split & gzip & --set ON_ERROR_STOP=on

Question

I have a PostgreSQL 9.4 database running on CentOS 7.3. The database is fairly vanilla and installed via https://yum.postgresql.org/ .

I'm trying to back up this database, and test a restore on another host. If I try to restore using --set ON_ERROR_STOP=on, the restore fails. Why is this?

My backup procedure is based on the recommendations at https://www.postgresql.org/docs/9.4/static/backup-dump.html . I use the following command, after a brand new fresh install of Postgres, which includes the initdb.

pg_dumpall --clean | gzip | split --suffix-length=4 --numeric-suffixes --additional-suffix=.split.gz --bytes 1G - postgres.pg_dumpall. 

This produces a series of files like this:

[postgres@db1 backups]$ ls postgres.pg_dumpall.0000.split.gz postgres.pg_dumpall.0001.split.gz ... postgres.pg_dumpall.0040.split.gz [postgres@db1 backups]$ 

To restore, I thought I could use cat, gunzip and psql --set ON_ERROR_STOP=on. However this doesn't work:

[postgres@db2 ~]$ cat postgres.pg_dumpall.*.split.gz | gunzip | psql --set ON_ERROR_STOP=on postgres SET SET SET ERROR: database "foo" does not exist [postgres@db2 ~]$ 

The command will complete if I remove --set ON_ERROR_STOP=on, but then I'm unclear if the backup archive is valid or not.

Answer 1

The --clean option for pg_dumpall adds commands to drop each database then recreate them. If the database does not exist, then the DROP DATABASE foo command will fail with an error, which will stop your script with ON_ERROR_STOP enabled.

Use --clean --if-exists in pg_dumpall to generate commands that will first check to see if the database exists before dropping it. These commands will produce notices instead of errors if the database doesn't exist.

For removing other commands that are causing a problem, you can use sed after gunzip to remove that command from the script. You should understand what the command is doing before removing it (in this case trying to delete the superuser). Multiple sed commands would be separated by ; within the quoted string given to sed. This command should delete the lines where the script will try to drop and recreate the postgres superuser account.

cat postgres.pg_dumpall.*.split.gz | gunzip | sed '/^DROP ROLE.*postgres/d;/^CREATE ROLE.*postgres/d' | psql --set ON_ERROR_STOP=on postgres