PostgreSQL的备份恢复

• 1. 备份的方式
• 1.1 \copy
• 1.2 逻辑备份
• 1.2.1 PG的逻辑备份实现方式
• 1.2.2 对应的逻辑恢复
• 1.3 物理备份
• 1.3.1 切换日志文件
• 1.3.2 pg_basebackup
• 1.3.3 pg_basebackup的函数式实现

PostgreSQL 入门系列博文讲解（B站同步介绍视频：https://space.bilibili.com/282421760）
支持一下，关注一波微信公众号：【 钥道不止 】

1. 备份的方式

• 元命令：\copy
• 逻辑备份：pg_dump、pg_dumpall
• 物理备份：pg_basebackup

1.1 \copy

\copy 元命令，可以导出数据为text、csv、binary格式，还不支持导出为sql语句。
普通用户也能执行 \copy test ,客户端操作copy sql命令，只能超级用户执行，服务端操作，效率更高。
#导出数据
\copy TABLENAME to '/home/postgres/a.txt';
#指定导出为csv格式
\copy TABLENAME to '/home/postgres/a.csv' with csv header;
#导入数据,追加方式
\copy TABLENAME from '/home/postgres/a.txt';
复制

1.2 逻辑备份

逻辑备份：也称之为转储。是利用工具通过读取数据库记录集，且将记录集写入文本文件或自定义转储格式来实现。这种记录集的读出与物理位置无关。是数据库对象级别的备份。

1.2.1 PG的逻辑备份实现方式

• pg_dump：只能针对一个数据库，能够生成一个数据库的一致性备份
• pg_dumpall：针对整个数据库簇

pg_dump  只针对指定的单个数据库，生成数据库的一致性备份。
pg_dumpall 直接转储整个数据库集簇。

pg_dump [OPTION]... [DBNAME]
-h, --host=HOSTNAME ： 连接到指定数据库主机地址
-p, --port=PORT ： 连接到指定数据库端口
-U, --username=NAME ： 连接使用的用户名
-d, --dbname=DBNAME ： 导出的数据库名字
-a, --data-only：只导出数据
-E encoding,--encoding=encoding：指定字符集编码
-j, --jobs=NUM ： 导出并发数
-f, --file=FILENAME ： 导出文件名
-F, --format=p|c|d|t ： 导出数据格式（text, 自定义转储格式，目录，tar）
-n, --schema=schema：导出指定模式
-t, --table=table：导出指定表
-s, --schema-only：只导出对象定义不导出数据
-Z0~9：使用gzip压缩
-c：创建对象前先删除
-C：创建对象
--disable-triggers：禁用触发器
-S,--superuser=username：指定超级用户

例子：
# 导出单表(public模式下的testdump表)
pg_dump -d postgres -U test1 -t public.testdump >singletable.sql 
# 导出整个库（postgres数据库）
pg_dump -d postgres -U test1 >singledatabase.sql
# 只导出库结构（postgres数据库结构）
pg_dump -d postgres -U test1 -s >schemaonly.sql
# 导出指定模式
pg_dump -d postgres -U test1 -n tt_id_seq >ceshi.sql
# 导出test库并指定导出的文件名
pg_dump -d postgres -U test1 -Fc -f test.dump

# 导出整个数据库集簇
pg_dumpall >full.sql  
复制

1.2.2 对应的逻辑恢复

• 生成的文本可以使用psql进行恢复
• 纯数据可以用copy恢复
• 自定义转储格式只能使用pg_restore恢复

# 恢复：psql 或 pg_restore
pg_restore [OPTION]... [filename]
-h, --host=HOSTNAME ： 连接到指定数据库主机地址
-p, --port=PORT ： 连接到指定数据库端口
-U, --username=NAME ： 连接使用的用户名
-d, --dbname=DBNAME ： 连接的数据库名字
-a, --data-only：只导入数据
-j, --jobs=NUM ： 导入并发数
-f, --file=FILENAME ： 导出文件名
-F, --format=p|c|d|t ： 文件数据格式（text,  自定义转储格式，目录，tar）
-n, --schema=schema：导入指定模式
-t, --table=table：导入指定表
-s, --schema-only：只导入对象定义不导入数据
-c：创建对象前先删除
-C：创建对象
--disable-triggers：禁用触发器
-S,--superuser=username：指定超级用户

例子：
psql -f ooxx.sql文件
# pg_restore可精细化恢复指定的对象，例如指定恢复那张表
pg_restore -Fc -C -d postgres test.dump
复制

1.3 物理备份

物理备份：是文件系统级别的备份.

# 查看归档模式
show archive_mode;
# 查看归档文件路径
show archive_command;
# 可选配置参数
show wal_keep_size;
show full_page_writes;
select name,setting,context from pg_settings where name like 'archive%' or name = 'wal_level';

需要确认的参数：修改postgresql.conf
# 开启归档，且归档命令正确。
archive_mode = on
archive_command = 'cp %p /pgarch/%f'
# wal_level级别：至少replica
wal_level = replica

# 重启生效
pg_ctl restart
复制

1.3.1 切换日志文件

# 切换log日志文件
select pg_rotate_logfile();
# 切换xlog日志文件
select pg_switch_xlog();

# 手动执行归档（切换log和xlog日志文件）
select pg_switch_wal();

# 将wal日志持久化到磁盘
checkpoint;
复制

wal日志文件的一般格式：0000 0001 0000 0000 0000 0001

前8位，即：0000 0001表示的是数据库的时间线

1.3.2 pg_basebackup

pg_basebackup命令参数

pg_basebackup
-D 备份的数据文件路径
-F 制定pg_basebackup生成备份数据格式，支持两种 p(plain)和t(tar)格式
	p(plain)格式是指生成的备份数据和主库上的数据文件布局一样。
	t(tar) 格式，将备份文件打个tar包并存储到制定目录
-X 参数设置在备份的过程中产生的wal日志包含在备份日志中，两种方式f(fetch) 和 s(stream)
	f(fetch) 是指wal日志在基准备份完成后备传送到备节点
	s(stream) 主库上除了启动基准备份，还会额外启动wal发送进程。（推荐这种方式）
-v 启用verbose模式，命令打印各阶段日志。
-P 显示数据文件、表空间文件传输百分比
复制

pg_basebackup备份案例

# 备份
pg_basebackup -D /usr/data/backup -Ft -Xs -v -P 
# 恢复（解压后的恢复步骤在不同版本中存在些许差异）
解压备份的tar包到目录(数据目录和wal日志目录分开备份打包压缩)
复制

pg_basebackup恢复时因数据库版本的差异

# 12版本之前是在数据目录下配置 recovery.conf 配置文件
vi recovery.conf
# 归档命令的反向cp
restore_command = 'cp /pgarch/%f %p'
# 恢复到最近时间点【或手动在xlog插入时间点select pg_create_restore_point('20220426');若创建多个同名的则恢复到第一个为止】
recovery_target_timeline = 'latest'

然后启动数据库，恢复后recovery.conf文件会变成recovery.done文件
恢复到指定时间点：recovery_target_time = '指定时间'【格式：select now();】
恢复到指定事务：recovery_target_xid = 事务号【select txid_current();】
参数recovery_target_inclusive为on则恢复到同一时间点所有事务都commit/abort，off则恢复到同一时间点第一个事务commit/abort；事务号也是同理

# 12版本之后将参数都放到了postgresql.conf文件中
vi postgresql.conf
restore_command = 'cp /pgarch/%f %p'
recovery_target_timeline = 'latest'

从PG12开始，由于该文件不存在，由下面两个新文件进行替换：
recovery.signal：告诉PostgreSQL进入正常的归档恢复
standby.signal：告诉PostgreSQL进入standby模式

touch recovery.signal
pg_ctl start

从PG12开始，恢复完成后将删除recovery.signal或standby.signal文件，
但其中的参数postgresql.conf仍然保留。
只要PostgreSQL不在恢复中，它们就会被忽略。
如果需要设置这些恢复参数建议使用ALTER SYSTEM，
如果需要重置它们建议使用ALTER SYSTEM RESET。
复制

1.3.3 pg_basebackup的函数式实现

# 打开强制检查点
select pg_start_backup(now()::text);
cat $PGDATA/backup_label

# 为t时可以开始手动拷贝实例数据目录和表空间备份
select pg_is_in_backup();

# 关闭强制检查点
select pg_stop_backup();
复制