PostgreSql 备份恢复

一、概述

  数据库备份一般可分为物理备份和逻辑备份，其中物理备份又可分为物理冷备和物理热备，下面就各种备份方式进行详细说明（一般情况下，生产环境采取的定时物理热备+逻辑备份的方式，均是以下述方式为基础进一步研发编写适合自己业务环境的备份工具或脚本）：

• 物理冷备：在停库的状态下，直接复制 PostgreSQL 的数据文件。备份出来的文件仅可在相同的平台环境中恢复（操作系统+数据库版本相同），仅可全库备份全库恢复。
• 物理热备：在不停库状态下，拷贝数据库的 data 目录及 wal 归档，基于数据库自身的时间点备份恢复技术（Point-In-Time Recovery），通过不停地重放 wal 日志将数据推到备份结束后的任意一个时间点。备份出来的文件仅可在相同的平台环境中恢复（操作系统+数据库版本相同），仅可全库备份全库恢复。
• 逻辑备份：利用 PostgreSQL 中自带的 pg_dump、pg_dumpall 进行 sql 转储，或使用客户端管理工具进行备份（实际也是调用的 pg_dump、pg_dumpall 命令），备份出来的文件可跨平台恢复，且可针对具体的对象进行备份恢复。

数据量越大，备份速度越慢，下列表中所列备份速度为相同环境下，三种备份方式的相对速度：

物理冷备	物理热备	逻辑备份
停库	不停库	不停库
全库备份恢复	全库备份恢复	具体数据库对象备份恢复
相同平台环境恢复	相同平台环境恢复	可跨平台数据库版本恢复
备份速度快	备份速度快	二进制转储备份速度较快，sql 文本转储备份速度一般

二、物理冷备

2.1 备份

--停库
pg_ctl stop--打包数据目录
cd /data/pg13/
tar -cf backup_pg13.tar data

2.2 恢复

备份的机器本地恢复或传到其他相同环境的机器进行恢复，需确保操作系统和数据库版本相同。

--确保恢复环境的数据库没有运行，若运行需关闭
ps -ef|grep postg--将原 data 目录改名
cd /data/pg13
mv data data_old--将打的备份包解压至原 data 所在目录下
tar -xvf backup_pg13.tar--启动数据库
pg_ctl start

三、物理热备

  PostgreSQL 在数据目录的 pg_wal 子目录（10版本之前是 pg_xlog 子目录）中始终维护一个WAL日志文件。该日志文件记录了数据库数据文件的每次改变。最初设计该日志文件的主要目的是为了数据库异常崩溃后，能够通过重放最后一次 Checkpoint 点之后的日志文件，把数据库推到最终的一致状态，避免数据丢失或不一致。此日志文件的机制也提供了一种热备份方案：基础备份加上重放 wal 日志，将数据推到备份结束后的任意一个时间点。

3.1 备份

1）建立 wal 连续归档

• 准备好归档目录，并确保空间充足。
• wal_level 需要 replica 或以上级别。
• 归档模式 archive_mode 开启。
• archive_command 指定一个 shell 命令，且确保命令中的路径存在。

--在大的磁盘空间下，创建归档目录
mkdir /data/pg13/archive -p--将下列参数加入 postgresql.conf 文件中，并重启数据库。
wal_level = 'replica'
archive_mode = 'on'
archive_command = 'test ! -f /data/pg13/archive/%f && cp %p /data/pg13/archive/%f'  # Unix
archive_command = 'copy "%p" "C:\\data\\archive\\%f"'  # Windows

2）制作基础备份

方式一：pg_basebackup

pg_basebackup -D /data/pg13/dbbak

方式二：使用低级 API 制作非排他基础备份

允许其他并发备份运行，既包括那些使用同样的 API 开始的备份，也包括那些用 pg_basebackup 开始的备份。

--发出开始备份标识
select pg_start_backup('label', false, false);--另外窗口备份数据目录
cp /data/pg13/data /data/pg13/databak -rp--发出备份开始的同一窗口发出终止备份标识
select * FROM pg_stop_backup(false);
select pg_switch_wal();

在 PostgreSQL 9.6 之前，只能通过 API 排他备份方式（排他式备份方法已过时，应避免使用）。

--发出开始备份标识
select pg_start_backup('label',true);--另外窗口备份数据目录
cp /data/pg13/data  /data/pg13/databak -rp--发出备份开始的同一窗口发出终止备份标识
select * FROM pg_stop_backup();
select pg_switch_wal();

3.2 恢复

1）停止原数据库

pg_ctl stop

2）基础备份拷贝至原数据目录

mv /data/pg13/data /data/pg13/data_old
cp /data/pg13/dbbak /data/pg13/data -rp

• 如果没有足够的空间，至少要保存集簇的 pg_wal 子目录的内容，因为它可能包含在系统垮掉之前还未被归档的日志。
• 若在其他机器上恢复确保数据目录所有权及权限（所有权 postgres，权限 0700）。
• 若使用了自定义表空间，确保 pg_tblspc 子目录中的软连接指向正确。

3）配置恢复参数

  恢复参数在 PostgreSQL12 版本之前是配置在 recovery.conf 文件中的，而自 PostgreSQL12 版本开始合并到了 postgresql.conf 文件中。默认情况下，恢复将会一直恢复到 WAL 日志的末尾，下面的参数可以被用来指定一个更早的停止点。在 recovery_target、recovery_target_lsn、recovery_target_name、recovery_target_time 和recovery_target_xid 中， 最多只能使用一个。

--postgresql.conf 或 postgresql.auto.conf 中配置均可，当都配置时 postgresql.auto.conf 会覆盖 postgresql.conf 文件中的配置
#archive_mode = 'on'      #恢复期间先关闭归档模式，确认恢复的数据没问题后，再开启
restore_command = 'cp /data/pg13/archive/%f %p'
recovery_target_time = '2023-08-11 15:58:00'    #不配置该参数时，默认恢复到最新时间点restore_command = 'copy "C:\\data\\archive\\%f" "%p"'  # Windows

其他可选配置

--恢复到指定时间
recovery_target_time = '2023-08-10 14:00:00'
--恢复到一致状态后尽快结束
recovery_target = 'immediate'
--恢复到 pg_create_restore_point() 所创建还原点
recovery_target_name = 'string'
--恢复到指定事务ID
recovery_target_xid = 'string' 
--恢复将继续进行的预写日志位置的LSN
recovery_target_lsn = 'pg_lsn'
--指定是否在指定的恢复目标之后停止（on）或在恢复目标之前停止（off），适用于 recovery_target_lsn、recovery_target_time 或者 recovery_target_xid 被指定的情况。默认 on
recovery_target_inclusive = 'boolean' 
--恢复到指定时间线，可以是数字时间线 ID 或特殊值，默认 latest
recovery_target_timeline = 'string'
--达到恢复目标时服务器应该立刻采取的动作
pause：恢复将会被暂停（默认）。
promote：恢复处理将会结束并且服务器将开始接受连接。
shutdown：将在达到恢复目标之后停止服务器。recovery_target_action = 'enum' 

注意:

• 由于在 recovery_target_action 被设置为 shutdown 时，recovery.signal 将不会被移除， 任何后续的启动都将会以立刻关闭为终结，除非该配置被改变或者 recovery.signal 文件被手工移除。
• 如果没有设置恢复目标,如果没有启用 hot_standby，pause设置的动作将和shutdown一样。 如果在升级期间达到恢复目标，pause 的设置将与 promote的行为相同。
• 在任何情况下，如果已配置了恢复目标，但归档恢复在达到目标之前结束，则服务器将关闭，并出现致命错误。

4）创建恢复标识文件

touch $PGDATA/recovery.signal

5）启动数据库

数据库在启动过程中自动应用配置文件中恢复设置，将数据库推进至指定位置

pg_ctl start

6）核对数据确认是否需要继续往下推进

当指定时间点恢复时，启动数据库后默认为只读状态，此时可查询数据进行检查，若不是想要的数据，可继续往后推进（只能向上次恢复的时间点后推进，不能向之前倒推）

--时间点继续推进
pg_ctl stop将 postgresql.auto.conf 中之间继续向后改recovery_target_time = '2023-08-11 15:59:00'pg_ctl start

数据库为只读状态，recovery.signal 恢复标识文件存在时，就可继续推进。

确认数据没问题后，可使用如下命令，结束恢复，数据库恢复读写模式:

select pg_wal_replay_resume();

此时 recovery.signal 恢复标识文件也会自动删除

四、逻辑备份

  pg_dumpall 工具可以将 PostgreSQL 中的所有数据库转储到一个文本文件，它会对集簇中的每个数据库调用 pg_dump 来完成该工作，由于 pg_dumpall 仅能转储文本文件（sql 脚本格式），当数据量大时，改方式效率较低，故一般仅用其导出全局对象（数据库、角色、表空间）的功能，备份数据库则使用 pg_dump 方式：

--导出数据库、角色、表空间定义
pg_dumpall -h localhost -U postgres --port=5432 -f backup.sql --globals-only  --clean

pg_dump 用法：https://xiaosonggong.blog.csdn.net/article/details/122743398