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.
Best Answer
The
--clean
option forpg_dumpall
adds commands to drop each database then recreate them. If the database does not exist, then theDROP DATABASE foo
command will fail with an error, which will stop your script withON_ERROR_STOP
enabled.Use
--clean --if-exists
inpg_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
aftergunzip
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 tosed
. This command shouldd
elete the lines where the script will try to drop and recreate thepostgres
superuser account.