PG-备份恢复

1. 备份恢复

1.1 备份方式

备份方式分为物理备份和逻辑备份

• 物理备份：通过冗余数据文件提供数据保护，在文件系统对数据目录，参数文件进行物理拷贝，复制到其他路径或存储设备中的方法称为物理备份
• 逻辑备份：利用工具按照一定逻辑将数据库对象导出到文件，需要时再利用工具把逻辑备份文件重新导人到数据库中，除了利用工具导出，用 CREATE TABLE AS, COPY等 SQL 命令保存数据副本的方式也被认为是逻辑备份的一种 。 逻辑备份是数据库对象级的备份

1.2 备份恢复目标衡量指标

根据自身业务情况和特点，对数据库系统的备份和恢复制定完善的策略 。
RTO ( Recovery Time Objective ）和 RPO (Recovery Point Objective ）是规划备份和恢复策略时最重要的两个衡量指标 。

• RTO 是恢复时间目标：指从故障发生开始到业务系统恢复服务所需的时间

• RPO 是恢复点目标：指故障发生后可以容忍丢失多少数据

1.3 PG支持的备份方法

有三种不同的基本方法来备份PostgreSQL数据：

• SQL转储
• 文件系统级备份
• 增量备份和基于时间点恢复(Point-in-time recovery，缩写PITR)

2. SQL转储(逻辑备份恢复)

SQL 转储方法的思想是创建一个由SQL命令组成的文件，服务器将利用其中的SQL命令重建与转储时状态一样的数据库。

• pg_dump：将单个数据库抽取为一个脚本文件或其他归档文件，不会转储关于角色或表空间
• pg_dumpall: pg_dump+可以转储一个数据库集簇的全部内容

2.1 pg_dump工具

2.1.1 备份

pg_dump [connection-option...] [option...] [dbname]

pg_dump是用于备份一种PostgreSQL数据库的工具。即使数据库正在被并发使用，它也能创建一致的备份。pg_dump不阻塞其他用户访问数据库（读取或写入）。

pg_dump只转储单个数据库。要备份一个集簇或者集簇中 对于所有数据库公共的全局对象（例如角色和表空间），需要使用pg_dumpall

转储可以被输出到脚本或归档文件格式。

• 脚本格式：SQL 命令的纯文本文件，可以通过psql根据SQL命令重构数据库到它被转储时的状态
• 归档文件格式：必须要使用pg_restore工具恢复。最灵活的输出文件格式是“自定义”格式（-Fc）和“目录”格式（-Fd）。它们允许选择和重排序所有已归档项、支持并行恢复并且默认是压缩的。“目录”格式是唯一一种支持并行转储的格式。

pg_dump的优势：

• pg_dump可以用其他格式创建文件以支持并行和细粒度的对象恢复控制。
• pg_dump的输出可以很容易地在新版本的PostgreSQL中载入。

pg_dump 选项

选项	说明
dbname	指定要被转储的数据库名
-a|--data-only	只转储数据，而不转储模式（数据定义）。表数据、大对象和序列值都会被转储。
-b | --blobs	转储中包括大对象。这是当--schema、--table或--schema-only被指定时的默认行为。
-B | --no-blobs	在转储中排除大对象
-c | --clean	在输出创建数据库对象的命令之前输出删除它们的命令
-C | --create	包含创建 Database 的命令
-E encoding |--encoding=encoding	指定的字符集编码创建转储
-f file | --file=file	将输出到指定文件
-F format | --format=format	选择输出的格式。format格式：[p|plain, c|custom, d|directory, t|tar] p: 输出一个纯文本形式的SQL脚本文件 c: 输出适合于pg_restore输入的自定义格式 d: 将表和其他对象输出为文件，并保存在一个目录中 t：输出为 tar 包
-j, --jobs=NUM	并行度
-n, --schema=PATTERN	指定 Schema
-N, --exclude-schema=PATTERN	排除指定的 Schema
-s, --schema-only	指定转储包含Schema
-t, --table=PATTERN	转储指定的表
-T, --exclude-table=PATTERN	排除指定的表

2.1.2 恢复

1）pg_dump生成的文本文件可以由psql工具支持SQL文件即可恢复。

psql dbname < dumpfile

# 默认情况下，psql脚本在遇到一个SQL错误后会继续执行
# 设置ON_ERROR_STOP变量,让它遇到错误时退出psql
psql --set ON_ERROR_STOP=on dbname < infile

# pg_dump和psql读写管道的能力使得直接从一个服务器转储一个数据库到另一个服务器
pg_dump -h host1 dbname | psql -h host2 dbname

在执行恢复前，需要先创建数据库dbname，对象拥有着及其相关授权用户

pg_dump产生的转储是相对于template0。这意味着在template1中加入的任何语言、过程等都会被pg_dump转储。如果在恢复时使用的是一个自定义的template1，则必须从template0创建一个空的数据库

2）转储格式自定义格式时，只能用pg_restore工具恢复

# 备份语法
pg_dump -Fc dbname > filename
# 恢复语法
pg_restore -d dbname filename

# 备份
pg_dump -Fc -p 1921 devdb > devdb.dat

# 恢复
pg_restore -p 1921 -d devdb devdb.dat

# 使用-j参数控制并行度
pg_dump -j num -F d -f out.dir dbname
# 恢复
# pg_restore -j来以并行方式恢复一个转储

2.2 pg_dumpall工具

​ pg_dump每次只转储一个数据库，而且它不会转储关于角色或表空间（因为它们是集簇范围的）的信息。为了支持方便地转储一个数据库集簇的全部内容，提供了pg_dumpall程序。pg_dumpall备份一个给定集簇中的每一个数据库，并且也保留了集簇范围的数据，如角色和表空间定义。

# 转储
pg_dumpall > dumpfile

# 恢复
psql -f dumpfile postgres

恢复一个pg_dumpall转储时常常需要具有数据库超级用户访问权限，因为它需要恢复角色和表空间信息。

pg_dumpall工作时会发出命令重新创建角色、表空间和空数据库，接着为每一个数据库pg_dump。这意味着每个数据库自身是一致的，但是不同数据库的快照并不同步。

集簇范围的数据可以使用pg_dumpall的--globals-only选项来单独转储。

2.3 处理大型数据库

​ 在一些具有最大文件尺寸限制的操作系统上创建大型的pg_dump输出文件可能会出现问题。幸运地是，pg_dump可以写出到标准输出，因此你可以使用标准Unix工具来处理这种潜在的问题。

# 1.1 使用压缩转储
pg_dump dbname | gzip > filename.gz

# 1.2 恢复
gunzip -c filename.gz | psql dbname
cat filename.gz | gunzip | psql dbname

# 2.1 使用split。 split命令允许你将输出分割成较小的文件以便能够适应底层文件系统的尺寸要求
pg_dump dbname | split -b 1m - filename
# 2.2 恢复
cat filename* | psql dbname

3. 文件系统级别备份

​ 先停止数据库，然后直接使用操作系统自带命令复制PostgreSQL用于存储数据库中数据的文件到合适位置即可。

tar -cf backup.tar /usr/local/pgsql/data

rsync --checksum

4. 增量备份

从一个基础备份时间开始的连续的归档WAL文档序列。

4.1 开启WAL归档

任何数据的修改首先写入WAL日志，然后才对数据文件进行修改。PostgreSQL在数据集簇目录的$PGDATA/pg_wal/子目录下存储预写式日志（WAL）。

启用WAL归档，需设置wal_level配置参数为replica或更高(级别：minimal<replica<logical)，设置archive_mode为on，并且使用archive_command配置参数指定一个shell命令。在archive_command中，%p会被将要归档的文件路径所替代，而%f只会被文件名所替代（路径名是相对于当前工作目录而言的，即集簇的数据目录）

4.1.1 创建归档目录

mkdir -p /ups/data/pgdata/12/arch_wal
chown postgres:postgres /ups/data/pgdata/12/arch_wal

4.1.2 修改wal_level参数

# 1.1 vi postgresql.conf
wal_level = 'replica'

# 1.2 SQL命令方式修改
psql -c "ALTER SYSTEM SET wal_level = 'replica';"

4.1.3 修改archive_mode参数

# 1.1 vi postgresql.conf
archive_mode = 'on'

# 1.2 SQL命令方式修改
psql -c "ALTER SYSTEM SET archive_mode = 'on';"

4.1.4 修改archive_command参数

# 1.1 vi postgresql.conf
archive_command = 'cp %p /ups/data/pgdata/12/arch_wal/%f'

# 1.2 SQL命令方式修改
psql -c "ALTER SYSTEM SET archive_command = 'cp %p /ups/data/pgdata/12/arch_wal/%f';"

4.1.5 压缩的归档日志

适用压缩归档wal日志文件

-- 使用gzip来压缩归档文件
archive_command = 'gzip < %p > /var/lib/pgsql/archive/%f'
-- 使用gunzip恢复
restore_command = 'gunzip < /mnt/server/archivedir/%f > %p'

4.2 制作基础备份

​ 在较低的PostgreSQL版本中，使用pg_start_backup和pg_stop_backup这些低级API创建基础备份，从PostgreSQL9.1版开始有了pg_basebackup实用程序，使得创建基础备份更便捷，pg_basebackup用普通文件或创建tar包的方式进行基础备份，它在内部也是使用pg_start_backup和pg_stop_backup低级命令。

4.2.1 使用低级API制作基础备份

低级API备份类型有2种

• 非排它低级备份：允许其他并发备份运行
• 排它低级备份：只能在主节点上制作备份，并且不允许并发备份任务

低级API创建基础备份步骤

• 执行pg_start_backup命令开始备份（超级用户或具有该函数执行权限的用户连接到数据库服务）

  • 判断是否开始WAL归档
  • 强制进入全页写模式(full_page_writes = on)
  • 创建一个检查点
  • 排它基础备份还会创建一个$PGDATA/backup_label文件，该文件包含如下内容：
    • START WAL LOCATION: 0/A000028 (file 00000001000000000000000A)
    • CHECKPOINT LOCATION: 0/A000060 # 记录由命令创建的检查点的 LSN 位置
    • BACKUP METHOD: pg_start_backup # 基础备份的方法，如：pg_start_backup 或pg_basebackup
    • BACKUP FROM: master # 备份来源，指是从 master 或 standby 做的基础备份
    • START TIME: 2020-06-12 09:43:29 CST # 开始时间戳
    • LABEL: bk1 # 指定的标签
    • START TIMELINE: 1 # 时间线

• 使用系统命令创建数据目录副本

  • 使用rsync、tar、cp、scp等命令都可以创建数据目录的副本。在创建过程中可以排除pg_wal和pg_replslot目录、postmaster.opts文件、postmaster.pid文件，这些目录和文件对恢复并没有帮助

  •   # 隐藏因文件在备份期间被改变的触发的警告消息
      tar --warning=no-file-changed --warning=no-file-removed 
    

  • 可以备份中忽略无关重要的文件，如下：

    • $PGDATA/pg_wal/*

    • 目录pg_dynshmem/、pg_notify/、pg_serial/、pg_snapshots/、pg_stat_tmp/和pg_subtrans/的内容（但不是这些目录本身）可以从备份中省略，因为它们在postmaster启动时会被初始化。

    • 临时统计数据的目录(默认值：pg_stat_tmp）

    • 任何以pgsql_tmp开始的文件

    • 关系缓冲数据(pg_internal.init)文件

• 执行pg_stop_backup命令结束备份

  • 恢复full_page_writes到之前的值

  • 写一个备份结束的XLOG记录

  • 切换WAL段文件

  • 创建一个备份历史文件（$PGDATA/pg_wal/*.backup），该文件包含$PGDATA/backup_label文件内容及pg_stop_backup命令的时间戳

    •   START WAL LOCATION: 0/A000028 (file 00000001000000000000000A)
        STOP WAL LOCATION: 0/A001ED8 (file 00000001000000000000000A)
        CHECKPOINT LOCATION: 0/A000060
        BACKUP METHOD: pg_start_backup
        BACKUP FROM: master
        START TIME: 2020-06-12 09:43:29 CST
        LABEL: bk1
        START TIMELINE: 1
        STOP TIME: 2020-06-12 09:58:27 CST
        STOP TIMELINE: 1
      

  • 删除$PGDATA/backup_label文件

低级API函数使用

-- pg_start_backup 函数
10:09:24 [local]:5432 postgres@postgres=# sf pg_start_backup
CREATE OR REPLACE FUNCTION pg_catalog.pg_start_backup(label text, fast boolean DEFAULT false, exclusive boolean DEFAULT true)
 RETURNS pg_lsn
 LANGUAGE internal
 PARALLEL RESTRICTED STRICT
AS $function$pg_start_backup$function$

-- pg_start_backup 语法
pg_start_backup(label text [, fast boolean [, exclusive boolean ]])
/*
* label ：参数是用户定义的备份标签字符串，一般使用备份文件名 加日 期 作为备份标签。
* fast  ：参数默认值是 false，表示是否尽快开始备份
* exclusive ：参数决定 pg_start_backup 是否开始一次排他基础备份，【已经被废弃】
*/

-- pg_stop_backup 语法
pg_stop_backup() -- 完成执行排他的在线备份
pg_stop_backup(exclusive boolean) 	-- 结束执行排他或者非排他的在线备份

低级API创建基础备份过程

# 1. 执行pg_start_backup开始备份
psql -c "SELECT pg_start_backup('base', false, false);"

# 2. 创建数据目录的副本
cd /ups/data/pgdata/backups
tar -czf base.tgz /ups/data/pgdata/12/pg_root /ups/data/pgdata/12/pg_usr  --exclude=/ups/data/pgdata/12/pg_root/postmaster.pid --exclude=/ups/data/pgdata/12/pg_root/postmaster.opts --exclude=/ups/data/pgdata/12/pg_root/pg_snapshots --exclude=/ups/data/pgdata/12/pg_root/pg_stat_tmp --exclude=/ups/data/pgdata/12/pg_root/log/* --exclude=/ups/data/pgdata/12/pg_root/pg_dynshmem --exclude=/ups/data/pgdata/12/pg_root/pg_notify --exclude=/ups/data/pgdata/12/pg_root/pg_serial --exclude=/ups/data/pgdata/12/pg_root/pg_subtrans 

# 3. 执行 pg_stop_backup 结束备份
psql -c "SELECT pg_stop_backup(false) ;"

4.2.2 pg_basebackup创建基础备份

pg_basebackup用法

pg_basebackup takes a base backup of a running PostgreSQL server.

Usage:
  pg_basebackup [OPTION]...

Options controlling the output:
  -D, --pgdata=DIRECTORY receive base backup into directory
  -F, --format=p|t       output format (plain (default), tar)
  -r, --max-rate=RATE    maximum transfer rate to transfer data directory
                         (in kB/s, or use suffix "k" or "M")
  -R, --write-recovery-conf
                         write configuration for replication
  -T, --tablespace-mapping=OLDDIR=NEWDIR
                         relocate tablespace in OLDDIR to NEWDIR
      --waldir=WALDIR    location for the write-ahead log directory
  -X, --wal-method=none|fetch|stream
                         include required WAL files with specified method
  -z, --gzip             compress tar output
  -Z, --compress=0-9     compress tar output with given compression level

General options:
  -c, --checkpoint=fast|spread
                         set fast or spread checkpointing
  -C, --create-slot      create replication slot
  -l, --label=LABEL      set backup label
  -n, --no-clean         do not clean up after errors
  -N, --no-sync          do not wait for changes to be written safely to disk
  -P, --progress         show progress information
  -S, --slot=SLOTNAME    replication slot to use
  -v, --verbose          output verbose messages
  -V, --version          output version information, then exit
      --no-slot          prevent creation of temporary replication slot
      --no-verify-checksums
                         do not verify checksums
  -?, --help             show this help, then exit

Connection options:
  -d, --dbname=CONNSTR   connection string
  -h, --host=HOSTNAME    database server host or socket directory
  -p, --port=PORT        database server port number
  -s, --status-interval=INTERVAL
                         time between status packets sent to server (in seconds)
  -U, --username=NAME    connect as specified database user
  -w, --no-password      never prompt for password
  -W, --password         force password prompt (should happen automatically)

示例

# pg_basebackup创建基础备份
${PGHOME}/bin/pg_basebackup -Ft -Pv -Xf -z -Z5 -p 1921 -D /ups/data/pgdata/11/backups

4.3 脚本备份归档日志文件

archive_command脚本

使用脚本定义archive_command，并完成以下需求

archive_command = 'local_backup_script.sh "%p" "%f"'

• 将数据拷贝到安全的场外数据存储
• 批处理WAL文件，这样它们可以每三小时被传输一次，而不是一次一个
• 与其他备份和恢复软件交互
• 与监控软件交互以报告错误

5. 基于时间点和还原点的恢复

通过重做WAL日志可以将数据库恢复到最近的时间点或指定时间点，还可以恢复到指定的还原点。

5.1 使用一个连续归档备份进行恢复步骤

1. 停止服务器
2. 将整个集簇数据目录和表空间复制到一个临时位置，至少要复制数据库集簇的pg_wal子目录下文件存储起来
3. 移除所有位于集簇数据目录和正在使用的表空间根目录下的文件和子目录
4. 从文件系统备份中恢复数据库文件（将基础备份文件恢复数据库文件），需要保证pg_tblspc/中的符号链接被正确地恢复
5. 移除pg_wal/目录中的任何文件，这些是来自于文件系统备份而不是当前日志，因此可以被忽略。
6. 拷贝步骤2上未归档的WAL段文件到pg_wal
7. 恢复postgresql.conf配置设置并创建$PGDATA/recovery.signal文件
8. 启动服务
9. 检查数据库内容，确保恢复到期望的状态

5.2 时间线

​ 无论何时当一次归档恢复完成，一个新的时间线被创建来标识恢复之后生成的WAL记录序列。时间线ID号是WAL段文件名的一部分，因此一个新的时间线不会重写由之前的时间线生成的WAL数据。实际上可以归档很多不同的时间线。

​ 每次当一个新的时间线被创建，PostgreSQL会创建一个“时间线历史”文件，它显示了新时间线是什么时候从哪个时间线分支出来的。系统在从一个包含多个时间线的归档中恢复时，这些历史文件对于允许系统选取正确的WAL段文件非常必要。

恢复的默认行为是沿着相同的时间线进行恢复，该时间线是基础备份创建时的当前时间线。

查看时间线

pg_controldata $PGDATA |grep 'TimeLineID'

​ 当数据库初始化时，initdb 创建的原始数据目录的 TimeLineID 为 1。 每进行一次恢复，TimeLineID 将加1。

[postgres@progs pg_root]$ pg_controldata $PGDATA |grep 'TimeLineID'
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1

时间线文件

​ 每进行一次恢复操作后，创建一些时间线文件（$PGDATA/pg_wal/*.history），这些时间线文件用于区分原始和恢复数据库集簇。每个时间线都有一个相应的TimeLineID, TimeLinelD是一个从1开始的4字节无符号整数。时间线文件的命名格式是“TimeLineID.history”。如：当前的TimeLinelD为2，时间线文件的名即为00000002.history。

5.3 恢复案例

恢复点相关参数

#recovery_target = ''		# 'immediate' to end recovery as soon as a
#recovery_target_name = ''	# the named restore point to which recovery will proceed
#recovery_target_time = ''	# the time stamp up to which recovery will proceed
#recovery_target_xid = ''	# the transaction ID up to which recovery will proceed
#recovery_target_lsn = ''	# the WAL LSN up to which recovery will proceed
#recovery_target_inclusive = on # Specifies whether to stop:
recovery_target_timeline = 'latest'	# 'current', 'latest', or timeline ID
#recovery_target_action = 'pause'	# 'pause', 'promote', 'shutdown'

测试数据

-- psql devdb dev
CREATE TABLE tb1(
	id serial primary key,
    ival INT NOT NULL DEFAULT 0,
    description TEXT,
    ctime TIMESTAMPTZ NOT NULL DEFAULT now()
);


INSERT INTO tb1 (ival) VALUES(1);

SELECT id, ival, description, ctime FROM tb1;

-- 切换WAL日志
SELECT pg_switch_wal() ;

创建基础备份

${PGHOME}/bin/pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5432 -D /ups/data/pgdata/12/backups

[postgres@progs pg_root]$ ${PGHOME}/bin/pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5432 -D /ups/data/pgdata/12/backups
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/C000028 on timeline 1
104464/104464 kB (100%), 2/2 tablespaces                                         
pg_basebackup: write-ahead log end point: 0/C000138
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
[postgres@progs pg_root]$ ls -l /ups/data/pgdata/12/backups
total 7456
-rw------- 1 postgres postgres 2871315 Jun 12 11:12 16384.tar.gz
-rw------- 1 postgres postgres 4756098 Jun 12 11:12 base.tar.gz
[postgres@progs pg_root]$ 
# 查看备份文件内容
[postgres@progs pg_root]$ tar -tf /ups/data/pgdata/12/backups/16384.tar.gz
PG_12_201909212/
PG_12_201909212/16385/
PG_12_201909212/16385/1255
PG_12_201909212/16385/1255_fsm

5.3.1 恢复到最近时间点

插入测试记录

INSERT INTO tb1 (ival) VALUES(2);
SELECT id, ival, description, ctime FROM tb1;

移除故障数据库目录（先停服务）

[postgres@progs pg_root]$ pg_ctl -mi stop
waiting for server to shut down.... done
server stopped
[postgres@progs pg_root]$ rm -rf $PGDATA/pg_root

解压基础备份文件

tar -xf /ups/data/pgdata/12/backups/base.tar.gz -C /ups/data/pgdata/12/pg_root/

配置恢复参数

# 12+ 版本 postgresql.conf
# before 11版本 recovery.conf
restore_command = '/usr/bin/lz4 -d /ups/data/pgdata/12/archive_wals/%f.lz4 %p'
recovery_target_timeline = 'latest'

启动服务开始恢复

pg_ctl -D /ups/data/pgdata/12/pg_root start

检查数据是否恢复成功

-- psql devdb dev
SELECT id, ival, description, ctime FROM tb1;

5.3.2 恢复到指定时间点

在5.3.1章节中参数recovery_target_time指定时间点恢复即可，其它步骤一样。

recovery_target_time = '2020-06-12 12:18:18.634808+08'

5.3.3 恢复到指定还原点

前提：基础备份+还原点。通常用于重大业务发布场景。

创建还原点

-- psql
df pg_create_restore_point
                                  List of functions
   Schema   |          Name           | Result data type | Argument data types | Type 
------------+-------------------------+------------------+---------------------+------
 pg_catalog | pg_create_restore_point | pg_lsn           | text                | func

-- 创建还原点
SELECT pg_create_restore_point('restore_point');

恢复过程

恢复过程跟上面章节内容一致，只需移除参数recovery_target_timeline，并将recovery_target_name配置成'restore_point'后进行恢复即可

recovery_target_name = 'restore_point'

5.3.4 恢复到指定事务

主要知道待恢复的事务ID号，配置recovery_target_xid参数值进行恢复即可

recovery_target_xid = 3385

5.3.5 恢复到指定时间线

只需配置参数，其它恢复过程都一致

# 12+ 版本 postgresql.conf
# before 11版本 recovery.conf
restore_command = '/usr/bin/lz4 -d /ups/data/pgdata/12/archive_wals/%f.lz4 %p'
recovery_target_timeline = 2
recovery_target_time = '2020-06-12 12:18:18.634808+08'