大网数据广告活动
服务器运维问答

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

· 52CE 我爱测

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 u…

问题原文

原帖链接 · Stefan Lasiewski · CC BY-SA 3.0

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.

已采纳解答

原帖链接 · DerfK · CC BY-SA 3.0

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

摘自公开归档,保留原文语言,版式经整理,外部媒体与邮箱地址已移除。

资料来源与适用范围

资料来源
Server Fault
适用范围
以原帖系统、软件版本与发布日期为准(2017-06-22)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY-SA 3.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。