PostgreSQL备份与恢复

1. 前言

数据库的备份有多种分类方式。

1. 按照备份后的文件类型，可以分为物理备份（文件系统级别的备份）和逻辑备份（备份后的文件是sql文件或特定格式的导出文件）；
2. 按照备份过程中是否停止数据库服务，可分为冷备份（备份过程中停止数据库服务）和热备份（备份过程中数据库服务开启并可供用户访问）；
3. 按照备份是否是完整的数据库，可分为全量备份(备份是完整的数据库)和增量备份（备份是上一次全量备份后数据库改变的内容）。

Postgresql的常见备份方式有以下三种：文件系统级别的冷备份, SQL转储, 连续归档.

2. Postgresql的常见备份方式

Postgresql常见的备份方式主要有三种: 1. 文件系统级别的冷备份, 2. SQL转储 3. 连续归档

2.1. 文件系统级别的冷备份

这种备份方式需要关闭数据库，然后拷贝数据文件的完整目录。恢复数据库时，只需将数据目录复制到原来的位置。该方式实际工作中很少使用。

2.2. SQL转储

这里我们用到的工具是pg_dump和pg_dumpall。

这种方式可以在数据库正在使用的时候进行完整一致的备份，并不阻塞其它用户对数据库的访问。它会产生一个脚本文件，里面包含备份开始时，已创建的各种数据库对象的SQL语句和每个表中的数据。可以使用数据库提供的工具pg_dumpall和pg_dump来进行备份。pg_dump只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息，因为这些信息是整个数据库集群共用的，不属于某个单独的数据库。pg_dumpall，对集簇中的每个数据库调用pg_dump来完成该工作,还会还转储对所有数据库公用的全局对象（pg_dump不保存这些对象）。 目前这包括适数据库用户和组、表空间以及适合所有数据库的访问权限等属性。

例如，在我的计算机上，可使用如下命令对名为dbname的数据库进行备份：
pg_dump -h 127.0.0.1 -p 5432 -U postgres -d dbname -c -C -f dbname_`date "+%Y%m%d%H%M%S"`.sql

-c
--clean
    输出在创建数据库创建命令之前先清理（删除）该数据库对象的命令。
    这个选项只是对纯文本格式有意义。对于归档格式，你可以在调用 pg_restore 的时候声明选项。

-C
--create
    以一条创建该数据库本身并且与这个数据库联接等命令开头进行输出。（如果是这种形式的脚本，那么你在运行脚本之前和哪个数据库联接就不重要了。）
    这个选项只对纯文本格式有意义。对于归档格式，你可以在调用 pg_restore 的时候声明该选项。

-f file
--file=file
    把输出发往指定的文件。如果忽略这些，则使用标准输出。

联接参数
-h host
--host=host
    声明运行服务器的机器的主机名。如果数值以斜杠开头，则它被用做到 Unix 域套接字 的路径。缺省是从 PGHOST 环境变量中取得的，如果设置了这个环境变量的话，否则，尝试 一个 Unix 域套接字连接。

-p port
--port=port
    声明服务器正在侦听并等待联接的 TCP 端口或本地 Unix 主控套接字文件句柄。缺省时使用环境变量 PGPORT 的值（如果存在），或者是编译时的缺省值。

-U username
    以给出用户身分联接

使用如下命令可对全部pg数据库进行备份。

       
pg_dumpall -h 127.0.0.1 -p 5432 -U postgres  -c  -C -f db_bak_`date "+%Y%m%d%H%M%S"`.sql

需要连续多次输入密码， 等待备份完成，等等时间取决于数据量。

恢复方式很简单。执行恢复命令即可：

# 普通用户权限即可
 psql -h 127.0.0.1 -p 5432 -U postgres -f db_bak.sql

提示：
备份后建议压缩数据以减少占用空间
gzip db_bak_YYYYMMDDHHMISS.sql
并转存到不同设备，保存多份备份
rsync -avz --progress user_name@remote_host:/path_to_backup/db__bak_YYYYMMDDHHMISS.sql.gz /path_to_restore

2.3. 连续归档

这种方式的策略是把一个文件系统级别的全量备份和WAL(预写式日志)级别的增量备份结合起来。当需要恢复时，我们先恢复文件系统级别的备份，然后重放备份的WAL文件，把系统恢复到之前的某个状态。这种备份有显著的优点：

不需要一个完美的一致的文件系统备份作为开始点。备份中的任何内部不一致性将通过日志重放来修正。
可以结合一个无穷长的WAL文件序列用于重放，可以通过简单地归档WAL文件来达到连续备份。
不需要重放WAL项一直到最后。可以在任何点停止重放，并使数据库恢复到当时的一致状态。
可以连续地将一系列WAL文件输送给另一台已经载入了相同基础备份文件的机器，得到一个实时的热备份系统。

2.3.1. 如何进行连续归档呢？

实验环境:
OS: Centos 7.9
Postgresql: 14.6

2.3.1.1. 配置postgresql.conf

首先需要配置/etc/sysconfig/pgsql/postgresql.conf

如果/etc/sysconfig/pgsql目录下没有该配置文件, 可以基于 /usr/pgsql-14/share/postgresql.conf.sample创建一个

archive_mode = on
wal_level = replica

archive_mode = on

archive_command = 'cp /var/lib/pgsql/14/data/%p /var/lib/pgsql/14/backups/walback/%f'

archive_command执行时，%p会被要被归档的文件路径所替代，而%f只会被文件名所替代。

一些注意事项:
/var/lib/pgsql/14/backups/walback替换为归档日志的存放路径。
wal_level只能在服务器启动时修改。但是，archive_command可以通过重载配置文件来修改。
要确保归档的目录是存在的
walback文件夹的用户组和所有者应该是postgres, 否则会备份失败
如果你需要在参数archive_command中嵌入一个真正的%字符，可以使用%%

之后需要重启数据库使配置生效。

2.3.1.2. 制作一个基础备份

接下来需要制作一个非排他的基础备份。Postgresql提供了排他备份和非排他备份两种备份方式，推荐使用非排他的备份方式。

1. 以一个具有运行pg_start_backup权利的用户连接到服务器（不在乎是哪个数据库）并且发出以下命令：

   Select pg_start_backup('backup_label', false, false);

2. 对数据库进行一次文件系统级别的备份。即将postgresql的安装目录下的data目录及其内容复制到其他位置。

3. 在同一个连接中，发出命令：

   select * from pg_stop_backup(false);

这个命令代表结束一次非排他的备份。

3. 参考文档

Postgresql的三种备份方式

26.3. 连续归档和时间点恢复（PITR