PostgreSQL教程（7）数据备份与还原

PostgreSQL 原生支持逻辑备份（pg_dump、pgdump_all）和物理备份（pg_basebackup），同时还有第三方提供的成熟备份工具，如pg_rman、pgBackRest等，可以根据自己需要灵活选择

一、PostgreSQL的逻辑备份与还原

1、pg_dump + pg_restore

pg_dump可以在数据库处于运行状态下通过快照的形式进行一致性备份，不会阻塞其他用户的读写请求。需要注意的是pg_dump只能备份单个数据库，不能备份数据库公共全局对象，如角色和表空间

pg_dump备份常用选项

-h host：指定数据库主机IP

-p port：指定数据库端口

-U user：指定数据库名
-W：按提示输入密码

-f：将备份输出到指定文件中
-F | --format=c|d|t|p ：指定备份格式，默认为p表示备份文件为sql格式；c为二进制格式，只能使用pg_restore还原；d为备份到指定目录中，并且提供压缩；t为备份为tar格式

-a | --data-only：只备份数据，不包含表结构

-s | --schema-only：只备份表结构

-c | --clean：是否生成清理该数据库对象的语句，如drop table

-C | --create：是否包含create database语句

-n | --schema：只备份匹配的schema下的内容

-t | --table：只备份匹配的表

-T | --exclude-table=TABLE_NAME：指定不需要备份的表

--inserts：将备份数据转为insert格式，方便导入到其他数据库中。不使用该选项的话默认是使用copy的方式进行数据还原，速度会比insert快很多，所以如果是pgsql之间进行备份还原不建议使用该选项

--exclude-schema=SCHEMA_NAME：指定不需要备份的SCHEMA

--lock-wait-timeout=10s：备份时锁超时时间，避免备份期间有事务一直未执行完成导致备份卡住

pg_dump备份示例

#备份linuxe库的数据为SQL格式然后进行还原
pg_dump -h 192.168.0.103 -U tanglu linuxe > linuxe.sql
psql -h 192.168.0.103  -U tanglu -d linuxe -f linxue.sql  #恢复数据前需要先创建好数据库


#只备份表数据
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W testdb -a > testdb.sql

#只备份表结构
pg_dump -h 127.0.0.1 -U postgres -p 5432 -W testdb -s > testdb.sql

pg_restore还原示例

-d：指定恢复备份文件到哪个schema中

pg_dump -h 192.168.0.103 -U tanglu -Fc linuxe > linuxe.dump  #-F指定格式为dump专有格式
pg_restore  -h 192.168.0.103  -U tanglu -d linuxe  linxue.dump  #还原数据到linuxe库中

2、pg_dumpall

通过反复调用pg_dump实现整库备份，包括角色和表空间，但是只能备份为SQL文本格式，所以通常使用它来备份全局的角色和表空间而不是数据

#备份+还原所有库
pg_dumpall -h 192.168.0.103 -f pg_all.sql   
psql -h 192.168.0.103 -U tanglu -d linuxe -f pg_all.sql

#只备份角色
pg_dumpall -r > pg_roles.sql

#只备份表空间
pg_dumpall -t > pg_tablespace.sql

二、 PostgreSQL的物理备份

pg_rman、pg_basebackup（使用物理备份方式需要先配置好归档文件，非归档模式下数据库将覆盖掉日志文件）

1、pg_rman常用选项

-B：指定备份数据存放路径

-D：指定需要备份的数据路径

-b：指定备份方式，如full、incremental

2、pg_rman的使用（该工具需要先安装）

pg_rman -B /data/backup/pgsql/ -D /data/postgresql/data/  init  #初始化备份目录
pg_rman -h 192.168.0.104 -Upostgres -B /data/backup/pgsql/ -D /data/postgresql/data/ -d linuxe -b full  backup #全备
pg_rman -h 192.168.0.103  -D /data/postgresql/ -b incremental backup  #增量备份
pg_rman -B /data/backup/pgsql/ validate  #备份后需要进行数据校验
pg_rman show  #查看历史备份
pg_rman delete "2020-08-28 22:16:22"  #删除某个历史备份

#恢复数据
rm -rf /data/postgresql/data/*
pg_rman restore  #一条命令完成恢复

3、pg_basebackup的使用（在进行主从部署的时候已经接触过该工具，pg_basebackup作为备份工具的时候可以把指定实例中所有数据（全量备份+WAL日志）都拷贝出来，在还原的时候会先恢复全备数据，然后再重放WAL日志）