PostgreSQL数据库持续归档和即时恢复

我自己电脑上的PostGIS数据库中保存了很多数据，经历过两次个人服务器硬盘损坏的事故后，一直在考虑设置对数据库进行持续归档，以避免某天个人电脑出问题，要格式化（虽然对苹果电脑的品质还是比较放心的）。经过很多资料搜索，觉得PostgreSQL数据库的持续归档(Continuous Archiving)和即时恢复(Point-in-time Recovery)功能比较适合我的需求。

> 最早我是直接使用 `rsync` 命令将本地电脑中数据库文件目录同步到NAS上，但是这样做的问题比较多。一是一次同步的时间比较长，因为有大量的文件需要进行差异比较；二是这样做属于文件直接覆盖，跳过了PostgreSQL内部的处理，容易导致数据库一致性检测不通过；三是备份时数据库必须关闭，以保证一致性；四是需要手动执行，虽然可以设置为定期执行，但是个人电脑并不是一直开着的，所以不太容易设置一个合适的cron配置。

# 原理

根据[官方文档](https://www.postgresql.org/docs/current/continuous-archiving.html)的介绍，PostgreSQL数据库会一直维护一个“预写日志”(Write ahead log, WAL)，这样当数据库崩溃重启的时候，就可以根据这个预写日志重放数据操作，以达到恢复数据库的目的。基于这个设计，我们可以将某时刻的数据库文件和该时刻之后的预写日志进行备份，当要恢复数据库的时候，直接根据备份的日志回放数据库操作，就可以正确恢复数据库文件。比较类似于Git等版本管理系统。备份的某时刻的数据库文件就是“基准备份”(base backup)，只需要备份一次即可；后续生成的预写日志进行持续归档。

# 设置

在PostgreSQL的设置文件 `postgresql.conf` 中找到以下配置项并进行修改：

```conf
wal_level = replica  # replica 或更高
archive_mode = on
archive_command = '' # 具体的备份命令
```

其中 `archive_command` 是每当生成一个WAL的时候数据库的备份操作。最简单的就是直接复制到一个地方，例如

```conf
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
```

该命令把生成的WAL通过 `cp` 命令复制到 `/mnt/server/archivedir/%f` 处，其中 `%p` 表示WAL文件的路径， `%f` 表示WAL文件的名字。这里显然是通过某种协议（如SMB、WebDAV等）将服务器上的某个路径挂载到了 `/mnt/server` 路径下，那么 `cp` 命令就将文件复制到了服务器上。此外，还可以使用 `scp` 命令，直接通过SSH协议将文件传送到服务器上，

```conf
archive_command = 'scp -p -O %p user@host:/path/to/backup/%f'
```

由于不能输入密码，所以可以通过安装 `sshpass` 实现密码输入功能，但是要以明文形式保存密码，不够安全。更好的办法是生成SSH密钥，使用 `ssh-copy-id` 将密钥发送到服务器上，然后通过密钥进行登录。

# 使用

## 生成基准备份

最简单的办法是使用 `pg_basebackup` 命令，直接将数据库导出到某一个位置，例如可以直接是上文的 `/mnt/server/archivedir` 目录。

```bash
pg_basebackup -Ft -D/mnt/server/archivedir
```

如果不将服务器路径挂载到本地，该命令是不支持直接保存到远程服务器上的。但是可以在本地创建一个临时目录，导出到该临时目录里面，然后通过 `rsync` 命令或者 `scp` 命令将文件通过到远程。

## 预写日志备份

其实之前那样配置好了之后，预写日志就可以自动备份了。只要预写日志文件满了，就会通过 `archive_command` 中指定的命令进行保存，然后切换到一个新的预写日志。

如果要手动切换到新的预写日志，可以使用如下SQL语句

```sql
select pg_switch_wal();
```

该命令可以用来测试 `archive_command` 命令是否如预期完成，尤其是使用 `scp` 进行复制的时候。

# 恢复

如果系统出现了问题，或者数据被误删除，那么可以通过以下步骤将数据恢复。

1. 如果服务正在运行，则停止服务。
2. 如果空间足够，将数据目录和所有表拷贝到一个临时目录，以防稍后需要使用。如果空间不足，至少拷贝 `pg_wal` 目录，因为有些预写日志可能在系统崩溃前没有备份。
3. 删除所有数据库数据目录中的现有文件和目录以及所有表。
4. 从文件系统备份（基准备份）中恢复数据库文件。注意需要指定正确的所有权和权限。如果使用表空间，那么验证 `pg_tblspc/` 目录下的符号链接是正确的。
5. 删除 `pg_wal/` 目录下的所有文件。这些来自于文件系统备份，因此可能是过时的。
6. 如果有未归档的WAL文件，将他们拷贝到 `pg_wal/` 目录。
7. 设置 `postgresql.conf` 中的恢复配置，并在数据库数据目录中创建一个 `recovery.signal` 文件。也可以临时修改 `pg_hba.conf` 文件以防其他用户连接到数据库。
8. 启动服务。服务将会进入恢复模式并读取归档的WAL文件。如果恢复因为外部错误而终止，那么重启服务就可以继续恢复。如果恢复完成，服务将会移除 `recovery.signal` 文件，然后开始正常的数据库操作。
9. 检查以下数据库的内容是否已经正确恢复到想要的状态。如果没有，则重新执行第一步。

恢复配置和归档配置类似，是在 `postgresql.conf` 中的配置项 `restore_command` ，基本上是 `archive_command` 的逆操作，例如

```conf
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
restore_command = 'cp /mnt/server/archivedir/%f %p'
```

注意这里的命令在出现错误时要返回非零值。

此外，还可以设定恢复到某一个时间点，在 `postgresql.conf` 中设置

```conf
recovery_target_time = 'timestamp'
```

系统将只恢复到该时间点。

还有一个参数 `recovery_target_timeline` 是用于指定时间线的，类似于平行宇宙。概念就是每次进行数据库恢复，就会创建一个新的时间线，恢复后的操作应用在新时间线上，不会覆盖旧时间线的操作。这样，如果不能确定到底应该恢复到哪个时间点，可以反复尝试。