PostgreSQL备份与恢复

1 常用备份工具

注：推荐使用pgbackrest工具备份

2 pg_dump工具

2.1 备份单个数据库

pg_dump只能备份单个数据库，不会导出角色和表空间相关的信息，便于迁移或异机恢复。常用参数如下：

# pg_dump --help            可以查看用法

-U, --username=NAME    以指定的数据库用户联接

-v, --verbose                     详细输出模式

-F, --format=c|d|t|p          导出文件的格式,-Fc备份为二进制格式, 压缩存储. 并且可被 pg_restore 用于精细还原。-Fp备份为文本, 大库不推荐

-d, --dbname=DBNAME  指定数据库名

-O, --no-owner                 在明文格式中, 忽略恢复对象所属者

-f, --file=FILENAME          导出后保存的文件名

 -j, --jobs=NUM               指定备份的并行度，指定备份路径而非备份文件，会生成很多小文件，还原时指定该目录即可

--insert                             导出成sql语句类型，虽然方便查看数据内容，但是导出耗时长

备份命令：pg_dump -U postgres -v -Fc -d 备份的库名 --no-owner -f 备份的dump文件

如：pg_dump -U postgres -v -Fc -d cosmic_fi --no-owner -f /data/backup/cosmic_fi.dump

备份命令：pg_dump -h 主机 -U 用户名 -p 端口 -d 库名 --inserts > 备份文件名.sql

如：pg_dump -h 127.0.0.1 -U cosmic -p 5432 -d ierp_sys --inserts > ierp_sys.sql

tips：--insert方式备份的sql文件可以直接用输入重定向进行还原：

psql 库名 -U 用户名 <备份文件名.sql

如： psql ierp_sys -U cosmic < /data/ierp_sys.sql

2.1 还原单个数据库（需指定数据库）

pg_restore  可以从pg_dump创建的存档中恢复一个PostgreSQL数据库。常用参数如下：

# pg_restore --help           可以查看用法

-U, --username=NAME     以指定的数据库用户联接

-O, --no-owner                  跳过对象所有权的恢复

-d, --dbname=DBNAME   指定数据库名

-v, --verbose                      详细信息模式

--role=ROLENAME            在恢复之前执行SET ROLE命令

恢复命令：pg_restore -U postgres --no-owner --role 还原目标的用户名 -d 还原目标的库名 -v 备份dump文件名

如：pg_restore -U postgres --no-owner --role mypg -d cosmic_fi -v /pgsql12/backup/cosmic_fi.dump

Tips：还原前需要提前建库，参考以下语句：

create database 库名 with owner=用户名 encoding='UTF8' tablespace=pg_default LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' CONNECTION LIMIT=-1 TEMPLATE template0;

3 pg_dumpall工具

pg_dumpall  只能备份整库，常用参数如下：

# pg_dumpall --help 可以查看用法

-a, --data-only               仅转储数据，而不转储schema

-c, --clean                      重新创建数据库之前清除（删除）数据库

-g, --globals-only          仅转储全局对象，而不转储数据库

-r, --roles-only               仅转储角色，而不转储数据库和表空间

-s, --schema-only          仅转储schema，而不转储数据

-O, --no-owner              以纯文本格式跳过对象所有权的恢复

--inserts                         以INSERT命令（而非COPY命令）的形式转储数据

3.1 备份整个数据库

备份命令：pg_dumpall -h 主机 -U 用户名 -p 端口 --inserts > 备份文件.sql

如： pg_dumpall -h 127.0.0.1 -U cosmic -p 5432 --inserts|gzip > /data/alldatabase1.sql.gz

3.2 还原整个数据库

还原命令：psql -U 用户名 <备份文件名.sql

如： psql -U cosmic < /data/alldatabase1.sql.gz

注：如果从生产环境备份恢复数据到其它环境(开发、测试等),只需要备份恢复苍穹的分库，mc库不需要做恢复，注意同步修改苍穹关于多维数据库的连接信息

3.3 配置定时任务备份数据库

说明：红字部分根据实际的更换

备份全库脚本

准备备份脚本: /var/kingdee/script/pgbak.sh备份脚本：

#!/bin/sh
export DB_PORT=5432
export DB_IP='127.0.0.1'
export PGBinDir=/var/postgresql/soft/pg12.8/bin
export RemoteBackDir=/var/kingdee/pg_backup
export BackFileDir=/var/kingdee/pg_backup
export BackFileName=pgbak_${DB_IP}_${DB_PORT}_`date +%Y%m%d`.sql.gz
mkdir -p ${BackFileDir}
chown -R postgres. ${BackFileDir}
#备份数据库
su - postgres <<EOF
${PGBinDir}/pg_dumpall -U postgres -h ${DB_IP} -p ${DB_PORT} --inserts|gzip > ${BackFileDir}/${BackFileName}
EOF
#如果需要保存到异机，需要将备份文件传到远程存储中
sshpass -p"password" scp /${BackFileDir}/${BackFileName} root@ 数据备份服务器 IP:${RemoteBackDir}/

说明：sshpass传密码需要提前安装 sshpass，password 需要替换为root的实际密码。

定时备份任务配置

进行crontab设置，增加以下定时任务：

#备份 每天凌晨1点备份

0 1 * * * /bin/sh /var/kingdee/script/pgbak.sh >>/var/kingdee/script/log/pgbak.log 2>&1

#备份 每天凌晨5点清理15天前的备份

0 5 * * * find /var/kingdee/pg_backup/ -name 'pgbak_*.sql.gz' -mtime +15 -exec rm {} \;

4 pgbackrest工具

4.1 pgbackrest介绍

主要的功能：

支持并行备份和恢复 ，解决了压缩操作中的瓶颈

支持本地或远程操作， 需配置TLS/SSH 在本地或远程备份、恢复和存档

支持完整、增量和差异备份，增量恢复

支持多个存储库

支持备份轮换和存档过期， 备份完整性检查，页面校验

支持断点备份

支持并行、异步 WAL 推送和获取

支持表空间和链接支持

支持加密

支持S3、Azure 和 GCS 兼容的对象存储

注: （1）备份和还原的pgbaskrest版本必须一致

（2）数据库编译的block_size大小只能是8k，官方默认pg是16k

4.2 pgbackrest配置

pgbackrest命令在工具安装pg数据库时就已经添加进去了，可以直接使用

下文以单机PostgreSQL数据库为例，高可用PostgreSQL请参考：PostgreSQL高可用的物理备份方法

4.2.1 创建必要目录（路径以实际为准）

• 1.1、创建日志目录

# mkdir -p -m 770 /var/log/pgbackrest

# chown postgres.postgres /var/log/pgbackrest/

• 1.2、创建配置目录

# mkdir -p /etc/pgbackrest/conf.d

# touch /etc/pgbackrest/pgbackrest.conf

# chmod 640 /etc/pgbackrest/pgbackrest.conf 

# chown postgres.postgres -R