postgresql14管理（六）-备份与恢复

定义

备份（backup）：通过物理复制或逻辑导出的方式，将数据库的文件或结构和数据拷贝到其他位置进行存储；

还原（restore）：部分恢复。使用备份文件将数据库恢复到备份时的状态，该时间点之后的数据变更无法恢复；

恢复（recovery）：先做还原，然后使用处于备份时间点至故障点产生的日志文件（WAL），将数据库恢复到最新状态；

分类

物理备份（Physical Backup）：直接复制数据库相关的文件，一般情况，物理备份比逻辑备份快，占用的空间也更大。PostgreSQL支持在线、离线的物理备份，实际环境中应该以物理备份为主。

逻辑备份（Logical Backup）：将数据库的结构和数据导出为sql文件，还原时通过文件中的SQL语句和命令重建数据库并恢复数据。逻辑备份通常需要更多的备份时间、还原时间。逻辑备份可以作为物理备份的补充，或者用于测试目的的数据导入导出。

方式

在线备份（Online Backup）、热备份（Hot Backup）：pg处于启动状态时的备份；

离线备份（Offline Backup）、冷备份（Cold Backup）：pg处于关闭状态时的备份；

方法

全量备份（Full Backup）：一次备份所有，备份时间长，恢复时间短；
增量备份（Incremental Backup）：备份与上一次改变的文件，备份时间短，恢复时间长；
差异备份（Differential Backup）：针对第一次完全备份后发生变化的所有文件进行备份，备份、恢复时间居中；

pg通过一个基准备份（Base Backup），加上不断备份的事务日志文件（WAL），做到增量备份的效果。

工具

pg_dump：逻辑备份工具，支持单个数据库（可以指定模式、表）的导出，可以选择导出的格式；

pg_dumpall：逻辑备份工具，用于导出整个数据库集群，包括公用的全局对象；

pg_basebackup：物理备份工具，为数据库集群创建一个基准备份。或者设置基于日志传输或流复制的从节点的初始化；

psql：交互式命令行工具，也可以用于导入逻辑备份产生的 SQL 文件；

pg_restore：逻辑还原工具，用于还原 pg_dump 导出的归档格式的备份文件；

COPY：专有sql语句，将表中的数据复制到文件，或者将文件中的数据复制到表中；

第三方开源：pgAdmin、Barman、pg_probackup、pgBackRest

逻辑备份与恢复

#plain格式：转储一个可读的脚本
pg_dump -U postgres -d hrdb -f "D:\bak\hrdb.sql"
#导入
pgsql -U postgres newdb -f "D:\bak\hrdb.sql"

#-Fc 自定义格式，不可直接查看
pg_dump -U postgres -d hrdb -f "D:\bak\hrdb.dmp" -Fc
#恢复
pg_restore -U postgres -d hrdb "D:\bak\hrdb.dmp"

备份整个pg

#连接每个数据库都要输入密码，可用密码选项代替
pg_dumpall -U postgres -f "D:\bak\cluster.sql"
#复原
psql -f "D:\bak\cluster.sql" postgres

导出表数据

-- -a表示只导出数据（不包含结构），-t指定要导出的表，-T表示排除的表
pg_dump -a -t 'emp*' -T employees testdb > testdb_table
-- 导出时也可以指定其他的导出格式，并且采用相应的方式进行数据导入

#连接对应数据库
\c hrdb
#COPY支持不同的写入/读取文件格式：text、csv 或者 binary。默认是 text。
COPY employees TO "D:\bak\cluster.txt" (format csv, header true)
#改为csv格式，包含表头

#复制导出数据到新表
COPY newemployees FROM "D:\bak\cluster.txt" (format csv, header true)

备份大型数据库

#先转储再压缩
pg_dumpall | gzip > cluster.sql.gz
#先解压再导入
gunzip -c cluster.sql.gz | psql new

#分割导出文件为clusteraa、clusterab
pg_dumpall | split -b 1G - cluster
#恢复
cat cluster* | psql newdb

COPY products to PROGRAM 'gzip > /bak/products.dat.gz'
COPY products FROM PROGRAM 'gunzip < /bak/products.dat.gz'

#并发备份
pg_dump -j 8 -F d -f out.dir testdb
#还原
pg_restore -j 8 -d newdb testdb_dir

物理备份与恢复

由于离线备份需要停服务，所以使用场景少。以下介绍在线备份。

pg对于数据的修改，都会写入一个称为预写式日志（WAL）的文件中，位于数据目录的pg_wal（pg_xlog）子目录中。当系统出现故障需要恢复时，可以通过重做最后一次检查点（checkpoint）以来的 WAL 日志执行数据库的恢复。
基于以上机制，可以先创建一个文件级别的完全备份，然后不断备份生成的 WAL 文件，达到增量备份的效果。这些备份操作都可以在线执行，因此对于无法接受停机的业务系统至关重要。

预写式日志（Write-Ahead Logging）是实现可靠性和数据完整性的标准方法，同时还能减少
磁盘 IO 的操作，提高数据库的性能。

归档日志模式（archive log）

配置

修改 postgresql.conf

#记录信息级别，可选minimal、replica、logical
wal_level = replica
#是否启动日志归档
archive_mode = on
#执行日志归档命令
# test检测文件是否存在，!取反，%f是归档文件，所以是判断文件不存在
# %p是文件完整路径
# windows改为 'copy %p D:\\bak\\%f'
archive_command = 'test ! -f /var/lib/pgsql/wal_archive/%f && cp %p
/var/lib/pgsql/wal_archive/%f'
# 文档切换的超时时间
archive_timeout = 300

启动服务pg_ctl restart

-- 查看wal归档状态
SELECT * FROM pg_stat_archiver

基准备份

该函数会在集群的数据目录中创建一个名称为 backup_label 的备份标签文件，记录本次备份的相关信息，包括起始时间和备份标识。

-- 启动一个备份
SELECT pg_start_backup('basebackup_date');

另外，如果存在自定义的表空间，还会创建一个表空间映射文件，名称为 tablespace_map，记录
pg_tblspc/中的表空间符号链接的信息。

PostgreSQL进入到备份模式，然后就可以使用操作系统命令（cp、tar、rsync 等）复制数据目录

cp -r /var/lib/pgsql/14/data/ /var/lib/pgsql/14/backups/basebackup_date/

该函数将会终止备份模式，并且执行一次 WAL 段文件切换。

-- 结束备份
SELECT pg_stop_backup();

另外，该函数还会在 pg_wal （ pg_xlog ） 目 录 中 生 成 一 个 ‘wal分段数字.backup’ 文件
（00000001000000000000002E.00000060.backup），其中存储了本次备份的历史信息。

时间点恢复（Point-in-Time Recovery）

#基准备份后创建的表
\c hrdb
create table t1(id int);
insert into t1 values(1);

pg_ctl stop
mkdir /monidiushi
mv /var/lib/pgsql/14/data/* /monidiushi
#启动提示目录不是数据库集群目录
pg_ctl start

#通过基准备份恢复
cp -R /var/lib/pgsql/14/backups/basebackup_date/* /var/lib/pgsql/14/data/
#检查符号链接
ls /var/lib/pgsql/14/data/pg_tblspc/ -l

#删除基准备份的wal日志
rm -rf /var/lib/pgsql/14/data/pg_wal/*
#将未归档wal文件复制到原目录
cp -R /monidiushi/pg_wal/* /var/lib/pgsql/14/data/pg_wal

restore_command 用于指定还原归档 WAL 文件的命令，recovery_target_time 用于设置恢复目标，即恢复到哪个时间点。pg支持多种恢复目标的设置，默认为恢复到最新的状态。

#创建recovery.conf
echo "restore_command = 'cp /var/lib/pgsql/wal_archive/%f %p'
#recovery_target_time = '2019-3-28 12:05 GM'" >> recovery.conf

pg_ctl start
#windows下：pgctl start -D "data绝对路径"

检查

select * from t1;