一、为什么要备份

数据库的备份对于一个dba来说，说得再怎么重要都不为过 数据库备份有多种方式，如逻辑备份和物理备份 针对pg也有多种备份方式

pg的备份方式有哪些

备份方式/工具 备份类型 备份恢复能力 应用场景
pg_dump 逻辑备份 1.只能备份单个数据库，不会导出角色和表空间相关的信息；2、恢复的时候需要创建空数据库；3、可导出单表 适用于小表或夸平台数据迁移
pg_dumpall 逻辑备份 备份所有数据库，并且备份角色、表空间，不支持增量备份 常用于备份全局对象而非全库数据
pg_basebackup 物理备份 不支持增量备份 常用来搭建流复制环境，做数据初始化，也用于很多公司的常规备份
pg_rman 物理备份 支持全量备份、增量备份、归档备份 生产环境中，可按示划定备份策略，即每周全备，每天增量
快照备份 物理备份 不支持增量备份 常用于需要紧急对数据库做大的改动前的备份，或者对数据库变更前的备份

二、pg 逻辑备份

PostgreSQL中提供了pg_dump、pg_dumpall命令进行数据库的逻辑备份

区别： pg_dumpall是将一个PostgreSQL数据库集群全部转储到一个脚本文件中 pg_dump命令可以选择一个数据库或部分表进行备份

所以这里主要介绍pg_dump pg_dump 主要的特性：

  • pg_dump命令甚至可以在数据库处于使用状态时进行完整一致的备份,不阻塞任何读写

  • pg_dump生成的备份文件可以是一个SQL脚本文件，也可以是一个归档文件

这里需要注意

如果生成的是SQL脚本文件 ，其实就是一些sql ，可以直接执行的sql 如果是归档文件，归档格式的备份文件必须与pg_restore一起使用来重建数据库，这种格式允许pg_restore选 择恢复哪些数据

一般情况都会备份为归档文件方式，因为比较灵活，比如：

pg_dump可以将整个数据库备份到 一个归档格式的备份文件中，而pg_restore则可以从这个归档格式的备份文件中选择性地恢复部分表或数据库 对象，而不必恢复所有的数据。

归档格式的备份文件又分为两种

  • “custom”自定义格式（使用命令项参数“-Fc”来指定） 特点：它允许对归档元素进行选取和重新排列，并 且默认是压缩的；

  • 另一种是tar格式（使用命令项参数“-Ft”来指定） 特点：这种格式的文件不是压缩的，并且加载时不能重 新排序，但是它也很灵活

2.1 pg_dump命令如何使用

-h：指定数据库服务器的主机名或 IP 地址。 -p：指定数据库服务器的端口号。 -U：指定连接数据库时使用的用户名。

-d：指定要备份的数据库名称。

-F：指定导出文件格式，例如 c（自定义格式）、t（纯文本格式）等。 -f：指定导出文件的路径和名称。

-n：指定要导出的架构名称。

-t：指定要导出的表名称。

-j, --jobs=NUM 设置并发导出线程

案例：将数据库 mydb 导出为纯文本格式

pg_dump -h localhost -U myuser -d mydb -F t -f /test/mydb.sql

--只迁移数据库结构
pg_dump -h localhost -U myuser -d mydb -F t -s -f /test/mydb.sql

--将数据库 mydb 中特定的表 mytable 导出为自定义格式
pg_dump -h localhost -U myuser -d mydb -F c -f mytable.dump -t mytable

复杂一点的使用案例：

--db1库下面备份public下的t1、t2表
pg_dump -U postgres -p 5432 -h 127.0.0.1 db1 -n public -t t1 -t t2

--备份public下的满足t1*的所有表
pg_dump -U postgres -p 5432 -h 127.0.0.1 db1 -n public -t 't1*'

2.2 pg_restore命令

文本恢复方式比较简单：

psql -U postgres -p 5433 -h 127.0.0.1 -d db1 -f /root/db1.sql

用pg_dump的自定义备份或tar类型的备份需要使用 pg_restore工具来恢复

pg_restore的连接参数与pg_dump基本相同：

恢复案例：

---C指定创建对应的dbname，若不使用-C则会恢复带-d指定数据库下
pg_restore -U postgres -p 5433 -h 127.0.0.1 -d postgres -C /root/db1.dump 

--需要提前创建好new_dbname
pg_restore -U postgres -p 5433 -h 127.0.0.1 -d db2 /root/db1.dump 

--恢复指定表
pg_restore -U postgres -p 5433 -h 127.0.0.1 -d postgres -n public -t t1 -C /root/db1.dump

三、物理备份

pg最简单的物理备份当然就是冷备，把数据库停下来，然后拷贝数据库的PGDATA目录 这里我们讲的是pg的物理热备

3.1 pg的物理热备

PostgreSQL中通常的热备份方法有以下两种

  • 使用文件系统或块设备级别的快照功能完成备份

  • 使用数据库的PITR方法进行热备份，我们重点讲的方式

3.2 文件系统层热备方式

比如我们使用 使用LVM快照进行热备份 首先得需要去安装 lvm2

首先创建pv 比如有两块盘 /dev/sda /dev/sdb pvcreate /dev/sda

pvcreate /dev/sdb

创建vg

vgcreate vg01 /dev/sda /dev/sdb

创建lv

lvcreate -n lv01 vg01 -L 2000M

在lv上创建文件系统

sudo mkfs.xfs -f -i size=512,attr=2 -l lazy-count=1 -d su=1m,sw=2 -L lv01 /dev/vg01/lv01

挂着盘

进行快照热备份

1、登录数据库执行checkpoint，比如直接执行 checkpoint;

2、lvcreate -s -n snap201402221343 vg01/lv01 -L 500M

3、把快照盘进行挂载，拷贝到其他路径

感兴趣的可以去采用这种方式备份，这种方式也有缺点；

  • 不能指定数据库进行备份，只能针对整个集群进行备份

  • 恢复时也只能针对这个集群进行恢复，不能单独针对某个库进行备份

3.3 pg热备 pg_basebackup

pg_basebackup 本文采用的是文件系统备份 + 持续归档 的备份技术 需要pg开启如下参数设置

wal_level = replica # 或更高级别
archive_mode = on
archive_command = 'test ! -f /home/postgresql/archive/%f && cp %p /home/postgresql/archive/%f'

pg_basebackup 封装了底层的 pg_start_backup 和 pg_stop_backup等命令，并提供了一些非常方便的特性

3.4 pg_basebackup备份流程

  • 打开pg basebackup:执行pg basebackup命令时，会启动一个新的PostgreSQL进程

  • 获取数据库信息: pg basebackup进程首先连接到正在运行的PostgreSQL数据库，并获取数据库的基本 息，如数据库版本号、表空间、WAL日志位置等

  • 创建备份目录: pg basebackup会在备份目录下创建一个新的子目录，用于存储备份文件

  • 备份数据文件: pg basebackup会依次备份数据库的所有数据文件(包括表、索引、存储过程等) 到备目录 中。备份时，它会逐个文件读取并写入到备份目录中。

  • 备份WAL日志: 在备份数据文件之后，pg basebackup会备份数据库的WAL日志文件。WAL日志文件含了 数据库的所有修改操作，用于在恢复时重新应用这些修改。

  • 完成备份:备份完成后，pg basebackup进程会向PostgreSQL数据库发送一个标识备份完成的信号

总的来说，pg basebackup的原理就是通过连接到PostgreSQL数据库并读取数据文件和WAL日志文件，将它 们复制到指定的备份目录中，从而实现数据库的备份。备份完成后，可以使用这些备份文件来进行数据库的还 原和恢复操作。

3.5 pg_basebackup 使用介绍

备份过程中会需要备份数据文件和wal文件，这里分两种方式： wal日志fetch模式

stream模式备份差异

用参数来控制 --wal-method=none|fetch|stream

1、fetch模式 wal日志和数据文件备份串行备份，备份开始时记录wal开始点，在数据文件备份完成后，从wal日志的开 始点和结束点一次性写入到文件中

  • 优点：日志文件目录和数据文件压缩在同一目录
  • 缺点：备份开始时刻的日志需要一直保存下来，也就说pg的wal_keep_segments需要足够大去保存日志 文件，如果备份数据期间，日志开始时刻的日志已经被移除，那么备份就会失败

2、stream模式（建议使用） wal日志和数据文件备份并行备份，这就要求wal_max_sender必须保证不小于2，两个进程在备份开始时 分别接收数据文件和wal日志，最后生成两个压缩文件

  • 优点：不会出现fetch模式因wal_keep_segments不够而失败，并且并行备份，备份速度得到提升，尤其在高吞吐的环境中。
  • 缺点：wal日志和数据文件分开存放，在备份恢复时，需要手工去移动文件夹

四、pg 恢复

这里主要是针对pg_basebackup的恢复 pg_basebackup整个恢复过程相对比较简单，流程如下

1、停止 PostgreSQL 进程

2、移除数据目录下的所有文件和子目录。

3、将文件系统备份恢复到数据目录，确保恢复后的文件和目录有正确的权限

rm -rf /home/postgresql/data/*
$ tar -xvzf /home/postgresql/backup/base.tar.gz -C /home/postgresql/data
$ tar -xvzf /home/postgresql/backup/pg_wal.tar.gz -C /home/postgresql/data/pg_wal

4、在 /home/postgresql/data/ 目录中创建 recovery.signal 文件，该文件指示 pg 在启动时进入恢复模式， 并将在恢复成功后自动删除。

$ touch /home/postgresql/data/recovery.signal

5、启动 postgresql 进程，开始恢复数据

恢复实际上有两种工作模式

  • 在数据目录下创建一个名为 standby.signal 的文件，服务器启动后将进入 standby 模式。服务器进入恢复 状态，并且在达到归档的 WAL 的末端时不会停止恢复，而是通过连接到由primary_conninfo 设置指定的 主服务器或通过使用 restore_command 获取新的 WAL 段，继续尝试恢复

  • 在数据目录中创建一个叫做 recovery.signal 的文件，服务器启动后将进入目标恢复模式。目标恢复模式 在归档的 WAL 段文件被完全重放或达到 recovery_target 时结束

4.1 恢复目标

简单来说pg_basebackup 恢复的时候需要恢复到哪里，哪个时间点

默认情况下，恢复模式会处理完所有可用的 WAL 段文件，即通过递增 WAL 段命名中的序列号不断获取下一个 WAL 段文件，直到获取失败为止，最后会将数据库恢复到「相对最新」的状态。因此在恢复过程中出现的类似 「文件未找到」的错误通常是正常的，尤其是在恢复结束时

一般情况下 我们需要指定恢复到某个时间点 这时我们需要指定恢复目标 我们可以且仅可从以下参数中选择一个来指定恢复目标

recovery_target：唯一可用的值是 immediate，到达基础备份结束时的一致状态即停止恢复。 recovery_target_lsn：设置恢复目标为指定的 LSN。 recovery_target_name：设置恢复目标为指定的命名恢复点（通过pg_create_restore_point() 创建命名恢复 点）。

recovery_target_time：设置恢复目标为指定的时间戳。

recovery_target_xid：设置恢复目标为指定的事务 ID。

4.2 时间点恢复概述和实现方法

4.2.1 什么是时间点恢复

时间点恢复是指将数据库恢复到特定的时间点状态。这对于误删除数据或数据库被破坏的情况非常有用。

PostgreSQL 支持两种时间点恢复方法：基于 PITR（点时间恢复）的恢复和基于逻辑日 志的恢复。

4.2.2 基于 PITR 的恢复

基于 PITR 的恢复利用数据库事务日志来还原数据库至某个时间点的状态。它需要先执行完整备份，然后应用 从该备份之后发生的所有事务。

4.2.3 配置 WAL 归档

为了进行 PITR 恢复，需要在 PostgreSQL 中启用 WAL（Write-Ahead Logging）归档。WAL 归档记录了所有 修改数据库的操作，并将其写入磁盘上的日志文件中。启用 WAL 归档可以使 PITR 成为可能。

以下是一个启用 WAL 归档的示例配置：

wal_level = replica

archive_mode = on wal_keep_segments=1024

archive_command = 'cp %p /path/to/archive/%f'

此配置指定了 WAL 级别、启用了归档模式，并将WAL 日志文件复制到 /path/to/archive/ 目录中

五、pg_basebackup备份恢复案例

5.1 测试数据

CREATE TABLE tb1(
id serial primary key,
name varchar(200),
ctime TIMESTAMPTZ NOT NULL DEFAULT now()
);
INSERT INTO tb1 (name) VALUES('sssss');
INSERT INTO tb1 (name) VALUES('rrtsssss');
SELECT * FROM tb1;
-- 切换WAL日志
SELECT pg_switch_wal() ;

5.2 创建备份

/bin/pg_basebackup -Ft -Pv -Xf -z -Z5 -p 5432 -D /home/backup/backupfull
  • 查看备份文件内容查看备份内容

5.3 恢复到最近时间点

插入测试记录
INSERT INTO tb1 (name) VALUES('huifu');
SELECT * FROM tb1;
停掉服务删除数据库目录
rm -rf $PGDATA/data
解压基础备份文件
tar -xf /home/backup/backupfull/base.tar.gz -C /var/lib/pg15/data/
指定恢复参数（12+ 版本 postgresql.auto.conf）# before 11版本 recovery.conf
restore_command = 'cp /home/postgresql/log/archive/%f %p'
recovery_target_timeline = 'latest'
启动服务开始恢复
检查数据是否恢复成功

5.4 恢复到指定时间点

删除数据一条数据，记录时间

delete from tb1 where id=1;

插入数据另一条数据，记录时间

INSERT INTO tb1 (name) VALUES('niewu');

恢复到删除id=1这条之前的所有数据

指定恢复参数（12+ 版本 postgresql.auto.conf）# before 11版本 recovery.conf 

指定恢复参数（12+ 版本 postgresql.auto.conf）# before 11版本 recovery.conf restore_command = 'cp /home/postgresql/log/archive/%f %p' recovery_target_time = '2024-03-07 13:45:00+08'

5.5 恢复到指定还原点

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

\df pg_create_restore_point

创建还原点

SELECT pg_create_restore_point('restore_point'); delete * from tb1;

指定恢复参数（12+ 版本 postgresql.auto.conf）# before 11版本 recovery.conf

restore_command = 'cp /home/postgresql/log/archive/%f %p' recovery_target_name = 'restore_point'

5.6 恢复到指定事务

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

指定恢复参数（12+ 版本 postgresql.auto.conf）# before 11版本 recovery.conf restore_command = 'cp /home/postgresql/log/archive/%f %p' recovery_target_xid = 3385

pg_basebackup备份脚本

$ cat fullbackup.sh
#!/bin/bash
# 完全备份参数
full_backup_dir="/home/backup/pgfullbackup" # 备份目录
full_backup_label="full_backup" # 完全备份标签
# 连接账户
pg_username="backup" # PostgreSQL 用户名
pg_host="127.0.0.1" # PostgreSQL 主机地址
pg_port="5432" # PostgreSQL 端口
# 当前日期和时间
echo ---------------备份开始时间---------------------
datetime=$(date +%Y%m%d%H%M%S)
echo $datetime
# 创建完全备份
create_full_backup() {
# 删除之前的完全备份
# rm -rf $full_backup_dir
# 创建新的完全备份目录
# mkdir -p $full_backup_dir
# 使用pg_basebackup命令创建完全备份
pg_basebackup -U $pg_username -h $pg_host -p $pg_port -D $full_backup_dir/$full_backup_label-$datetime -Ft -Pv -z --label=$full_backup_label
echo "创建完全备份成功"
}
# 执行完全备份脚本
create_full_backup
# 备份结束时间
echo ----------------备份结束时间----------------------
endtime=$(date +%Y%m%d%H%M%S)
echo $endtime

六、pg_rman备份工具介绍

6.1 什么是pg_rman

pg_rman是Postgresql的一款插件，用于备份和还原PostgreSQL数据库；它需要对整个数据库集群，存档 WAL和服务器日志进行物理在线备份。pg_rman支持使用PostgreSQL9.0之后从备用站点获取备份，还支持存 储快照备份

6.2 pg_rman的特点

  • 仅使用一个命令即可对整个数据库（包括表空间）进行备份。

  • 只需一个命令即可从备份中恢复。

  • 支持增量备份和备份文件压缩，以便占用更少的磁盘空间。

  • 管理备份版本并显示备份目录。

  • 支持存储快照

6.3 pg_rman支持的命令

  • init --初始化备份目录。

  • backup --进行在线备份。

  • restore --还原。

  • show --显示备份历史记录。详细信息选项显示每个备份的附加信息。

  • validate --验证备份文件。未经验证的备份不能用于还原和增量备份。

  • delete --删除备份文件。

  • purge --从备份目录中删除已删除的备份。

6.4 pg_rman安装

可以从以下链接下载对应版本的pg_rman； <https://github.com/ossc-db/pg_rman/releases>

安装方法1：

从源码编译安装，缺包编译报错需要下载对应包任何重试

$ cd pg_rman
$ make
$ make install --将生成的pg_rman拷贝到/opt/pgsql/15/bin、/usr/bin

安装方法2：

RPM包安装

#默认生成在/usr/pgxxx下面 --将生成的pg_rman拷贝到/opt/pgsql/15/bin、/usr/bin
rpm -ivh pg_rman-x.x.xx-x.pgxx.rhelx.x86_64.rpm

6.5 使用pg_rman 前期准备

6.5.1 创建备份和归档目录

建议：备份和归档的目录需要单独挂盘，不能跟数据目录放一起，因为会影响在线系统IO，推荐挂NFS远程盘 进行远程备份

创建备份目录

mkdir /home/backup/pg_basebackup

创建wal归档目录

mkdir /home/backup/pg_arch

配置环境变量

echo 'export BACKUP_PATH=/home/backup/pg_basebackup'>>~/.bash_profile 
echo 'export ARCLOG_PATH=/home/backup/pg_arch'>>~/.bash_profile

6.5.2 修改PG归档配置

--在$PGDATA目录下创建arch.sh,保留30天归档
test ! -f /pg_arch/$1 && cp --preserve=timestamp $2 /pg_arch/$1 ; find /pg_arch/ -type -f
mtime +30 -exec rm -f {} \;
--修改postgresql.conf配置
archive_mode=on #开启归档
archive_command= 'arch.sh %f %p' #shell脚本
archive_timeout=900 #强制wal日志切换时间
--重启PG
pgrestart

6.5.3 pg_rman 初始化

--初始化
PGPASSWORD=XXX $PGHOME/bin/pg_rman -D $PGDATA -B /pg_basebackup -A /pg_arch -P -v init
--修改/pg_basebackup/pg_rman.ini配置文件,配置备份、归档保存天数
ARCLOG_PATH='/pg_arch'
SRVLOG_PATH='//pgsql//data/pg_log'
#配置压缩参数
COMPRESS_DATA=YES
#归档日志保留天数
KEEP_ARCLOG_DAYS=14
#KEEP_ARCLOG_FILES=1000 --归档保留个数
#备份保留天数
KEEP_DATA_DAYS=30

6.6 设置备份策略

6.6.1 全库备份策略

整个数据库备份和存档备份

--可以配置到crontab中每周日凌晨一点执行一次
0 1 * * 0 root sh pg_fullback.sh

--脚本pg_fullback.sh，备份完必须执行validate，否则备份不能用于还原
PGPASSWORD=XXX $PGHOME/bin/pg_rman -D $PGDATA -B /pg_basebackup -A /pg_arch -P -v -b full backup
PGPASSWORD=XXX $PGHOME/bin/pg_rman -B /pg_basebackup validate

6.6.2 增量备份策略

增量备份和存档备份

--可以配置到crontab中每日晚上执行一次
0 23 * * * root sh inrback.sh
--脚本inrback.sh
PGPASSWORD=XXX $PGHOME/bin/pg_rman -D $PGDATA -B /pg_basebackup -A /pg_arch -P -v -b
incremental backup
PGPASSWORD=XXX $PGHOME/bin/pg_rman -B /pg_basebackup validate
--查看备份情况
pg_rman -a show
$ pg_rman show

6.6.3 还原操作

-确认实例已经停掉
pgstop
--备份
cp -r $PGDATA /pgsql/12/data_bak
mkdir -p /pg_arch/arch_bak
cp -r /pg_arch/* /pg_arch/arch_bak
--还原到指定时间点or最新
PGPASSWORD=XXX $PGHOME/bin/pg_rman -D $PGDATA -B /pg_basebackup -A /pg_arch -P -v restore

--recovery-target-time '2020-12-02 03:04:54'
PGPASSWORD=XXX $PGHOME/bin/pg_rman -D $PGDATA -B /pg_baseback

6.6.4 后续操作

-修改权限
chmod 700 $PGDATA
chown -R postgres:postgres $PGDATA
--启动PG
pgstart
--启动完后注释掉postgres.conf最后还原配置
#restore_command='xxx'
#recovery-target-timeline='xxx'
--确认没问题后干掉arch的备份目录,不删除会导致下次整库备份报错
rm -rf /pg_arch/arch_bak
--找时间重新做一份整库备份

6.7 操作指令大全

1、初始化指令
PGPASSWORD=XXX $PGHOME/bin/pg_rman -D $PGDATA -B /pg_basebackup -A /pg_arch -P init

2、全库备份指令（用于还原需要在执行验证指令）
PGPASSWORD=XXX $PGHOME/bin/pg_rman -D $PGDATA -B /pg_basebackup -A /pg_arch -P -v -b full backup

3、增量备份策略（用于还原需要在执行验证指令）
PGPASSWORD=XXX $PGHOME/bin/pg_rman -D $PGDATA -B /pg_basebackup -A /pg_arch -P -v -b incremental backup

4、standby节点全库备份
pg_rman backup -D /home/postgres/pgdata_sby（备节点） -b full --host=masterIP --standby-host=standbyIP --standby-port=5432

5、验证备份文件。未经验证的备份不能用于还原和增量备份。
PGPASSWORD=XXX $PGHOME/bin/pg_rman -B /pg_basebackup validate

6、备份查看指令
$PGHOME/bin/pg_rman -a show

7、备份删除指令
pg_rman delete 2024-01-30 13:30:30

8、删除已删除的备份，尽管该delete命令从文件系统中删除了实际数据，但是仍然保留一些已删除备份的目录信息
pg_rman purge

七、企业级备份经验浅谈

1、制定整个备份策略（最好有一个备份调度系统）:根据业务需求，制定合理的备份策略。包括备份频率、 备份介质、备份存储位置等。确保数据库的备份充分且及时。

2、定期进行备份巡检:定期巡检数据库备份与恢复的可行性以及备份是否有效，确保备份的完整性和可恢复 性。同时，记录每个实例的巡检恢复的时间，以便参考和分析。

3、多地或者云备份:将数据库备份存储在不同的地理位置，确保备份的安全，比如备份在分布式文件系统， 或者简单搭一个比较的的nfs文件系统。在发生灾难性事件时，能够快速恢复数据

4、自动化备份与恢复（如果有条件的话）:使用自动化工具或脚本定期执行备份和恢复操作。减少人为错误 的发生，并能够按照预定计划进行备份和恢复。

5、监控备份与恢复状态:监控备份和恢复的状态，及时发现备份失败或恢复错误的情况，并进行相应的处 理，如果可以的话指定整个恢复流程是可重试的

6、记录备份信息:详细记录数据库备份和恢复的策略、方法、时间等信息。在应急情况下，能够快速找到相 关的信息，以便进行恢复

总结：怎么样指定一套比较完整的备份恢复方案，这是面试中经常问道的问题，也是作为dba必须要去掌握 的，简短说，就是要保证备份的安全性，备份的有效性，备份任意时间段可恢复性