PostgreSQL备份&恢复方案

最近因为工作需要，需要对PostgreSQL进行备份，之前仅是简单使用过，从未深入研究，而网上的一些分享文档也总是让我越看越晕，不得以看了PostgreSQL的官方文档，发觉这个功能还是很有意思的，在这里跟大家分享，以下所有内容是基于PostgreSQL11描写的。

概要

pg有三种备份方案，分别是SQL快照备份、文件级别备份和持续存档备份。每个方案都是适合的场景，所以大家在项目中应用时，还是要充分了解各个方案的优缺点，下面分别介绍这三个方案。

一、SQL快照方案

就是使用工具生成一个数据库快照文件，当需要进行数据库恢复时，再使用工具将快照文件导入。

详细介绍

生成快照的工具是pg_dump和pg_dumpall，其中：
pg_dump命令只能备份一个数据库，不可以备份表空间、角色的定义，但可以指定表，并且，若生成文件过大，也可以使用压缩格式。基本命令如：pg_dump -f back_up.sql db_name
具体此命令的参数可以参考官方文档。

pg_dumpall命令可以备份数据库集群的全部内容，包含了表空间、角色的定义，这个命令是重新创建角色、表空间、空数据，然后再为每个数据库调用pg_dump。

快照恢复的工具是psql：

1. 若是使用pg_dump备份时，一定要留意在执行psql恢复前，数据库已在表空间中创建好，因为此命令不会自己创建数据库；
2. 默认情况下，当遇到sql错误后，psql的脚本还会继续执行，若是希望遇到错误后立刻返回，可设置 psql --set ON_ERROR_STOP = on dbname <dumpfile；若是希望整个快照恢复为单个事务，遇错时完全回滚，也可以额外设置，通过将-1或–single-transaction命令行选项传递给psql来指定此模式，但要留意一下，若使用此设置，即使是很小的错误，也会导致数据库回滚到几个小时前的数据。
3. 在恢复之后，最好在每个数据库运行ANALYZE，进行查询分析。

优点

1. 此方案简单便捷，易操作；
2. 执行备份命令不会阻止数据库其他操作，除非是类似于alter table这种独占锁的操作；
3. 可以使用管道方式，在服务器A上备份，在服务器B上恢复，例如 pg_dump -h host1 dbname | psql -h host2 dbname；
4. 备份的快照可以重新加载到新版本的PostgreSQL中，也可以传输到其他机器体系结构中，例如从32位机器转移到64位服务器上。这个优点是其他两个备份方案没有的。

缺点

1. 无法做到实时备份；
2. 一次性恢复，若出错，需要额外较多的成本进行排查和恢复。

二、文件快照方案

就是直接复制PostgreSQL用于在数据库中存储数据的文件。例如直接执行 tar -cf backup.tar /usr/local/pgsql/data；此方案仅适用于备份还原整个数据库集群，不支持单个表、库的备份还原。

优点

1.方案简单便捷、易操作；

缺点

1. 使用时必须停用服务器（虽然官方文档中说，也支持服务器启动时进行备份，但是文档中也描述了坑会比较多）；
2. 拷贝分件可能会比较大，因为会备份更多内容，但相对于sql快照方案会更快一些。
3. 若数据库分布在多个文件系统中，例如数据文件在一块磁盘上，WAL日志在另一块磁盘上，或者表空间位于不同的文件系统上，则不推荐使用此方案。

三、持续存档方案

这个方案解决了前两个备份方案的不足，可以在不停止服务的前提下进行备份，并且可以持续备份。

其实本身PostgreSQL就会在pg_wal目录下存放预写式（WAL）日志，以保证系统崩溃后的安全。而关于WAL日志是什么，感兴趣的同学可以参照官方文档，就我所理解就是先顺序写WAL日志，然后再隔段时间将WAL日志刷到磁盘上，可以减少磁盘IO，而恢复时，即使数据还没有写入磁盘，也可以通过WAL日志进行恢复。

但是WAL日志不能无限写下去，如果一直写下去，磁盘不就写满了么，PostgreSQL有日志回滚机制，每个WAL日志段16M，最多能存放80M的日志，到达极限后，如若再生成新的日志段，就会覆盖之前旧的。基于这个原则，我们就没办法仅使用WAL日志进行备份。

而持续存档的方案就是，通过修改postgresql.conf配置，在后台持续将WAL日志段拷贝到更大的磁盘空间下，并手动定期进行数据基础备份，当进行完毕基础备份后，之前的WAL日志理论上就可以删除了，而在恢复时，可以只基于某个基础备份版本恢复，也可以基于时间点进行恢复。

1. 关于持续归档

关于配置

主要涉及到三个参数：

wal_level = replica 
archive_mode = on #修改此配置需重启服务
archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /var/lib/pgsql/wal_backup/%f && cp %p /var/lib/pgsql/wal_backup/%f)' 

归档命令archive_command，也可以是复杂的脚本，这完全基于各自的业务。例如，我希望在进行基础备份时，不要进行WAL日志的归档，以防止数据出错，并且归档时仅是同机器的拷贝，不计划跨机器，这个参数修改后，只需重新加载配置即可，并且不想使用归档功能后，若不想重启服务，直接设置archive_command=‘’即可

关于触发归档的条件

以下三种方式均可触发归档

1. 手动执行select pg_switch_wal();
2. WAL日志写满后触发归档
   WAL 日志文件默认为 16MB，这个值可以在编译 PostgreSQL 时通过参数 “–with-wal-segsize” 更改，编译后不能修改。
3. 设置 archive_timeout
   可能你的服务流量非常小，迟迟写不满16MB，但又希望可以定期归档，那么可以使用此参数，例如设置成60秒，那么每60秒，就会立即触发归档，但是需要留意，每次归档，都是生成16MB的文件，小心因为你设置的archive_timeout过小，而导致归档目录写满

2. 关于基础备份

pg提供了两个方式，一个是pg_basebackup工具，另一个是在命令行下执行命令。两个方式相同的是，都是在pg_wal目录下产生基础备份文件，并且进行归档，例如下图，执行备份后，生成000000010000000000000058文件和备份文件：

其中000000010000000000000058.00000060.backup中的前缀表示这个备份文件是以000000010000000000000058为基础进行的备份。而这个备份文件非常小，不用担心是否随着数据越来越多，备份文件也会越来越大。当进行完备份后，理论上就可以将这个备份以前的WAL日志进行删除了。

两种备份不同的是，第二种备份较第一种备份更加灵活，具体灵活在哪里，这里不多分享，可以参考文档
这里以第二种备份方案进行步骤描述：
首先，需要增加备份权限的角色，在命令行下执行：

create role repl nosuperuser replication login connection limit 32 encrypted password '111111';

其次，在配置文件pg_hba.conf中添加该角色的连接权限：

host    replication    repl          0.0.0.0/0           md5

最后， 执行脚本 backup.sh（实际使用时，要增加容错）：

touch /var/lib/pgsql/backup_in_progress#防止进行基础备份时，系统进行WAL日志的归档
psql -c "select pg_start_backup('back_up_1st');"#开始备份，并为此次备份起名back_up_1st
tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/11/data/#拷贝数据目录
psql -c "select pg_stop_backup();" #结束备份，可在备份目录下看到已生成备份文件
rm /var/lib/pgsql/backup_in_progress
tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/wal_backup/#将备份期间产生的wal日志进行拷贝

到此，持续归档已完成，接下来是恢复过程。

3. 关于恢复

1. 首先停用服务 pg_ctl stop
2. 编写recovery.conf，放在 /var/lib/pgsql/11/data目录下，当恢复功能成功完成后，该文件自动变更为recovery.done，否则会定期恢复。而该配置文件可参考/usr/pgsql-11/share/recovery.conf.sample

    restore_command='cp /var/lib/pgsql/wal_backup/%f %p'
    recovery_target_name='back_up_1st'#恢复到此版本
    recovery_target = 'immediate'

3. 将拷贝的数据目录进行恢复，忽略如下文件：

	postmaster.pid
	postmaster.opts

忽略如下文件夹中的数据，但仍保留文件夹

	pg_dynshmem/
	pg_replslot/
	pg_wal/
	pg_notify/
	pg_serial/
	pg_snapshots/
	pg_stat_tmp/
	pg_subtrans/

4. 重启服务

	/usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/

至此，恢复步骤已完成

四、总结

之前一直纠结于持续归档的原理，担心PostgreSQL的这个备份方案会过于粗糙，无法满足实际需要，研究一番发现，嗯，是我多虑了。文档很多地方写的很粗，PostgreSQL在很多细节其实支持的非常完善，以后慢慢补充。