postgresql增量备份系列一

简介

在一些大容量得数据库应用中，采用全量备份得方式，会带来大量时间浪费和开销，此时定期的增量备份可以使得数据存储周期变长。本文讲解几个增量备份工具

pg_basebackup + pg_receivewal（异地归档模式）

使用pg_basebackup进行全量备份，并创建复制槽receivewal_slot，在使用pg_receivewal通过复制槽receivewal_slot对wal的定位进行增量接收wal日志。全量加WAL-archiver就是类似PITR的一个数据回滚技术。只可以支持一次数据增量恢复。

 pg_basebackup -Fp -P -v -D backup/ -h 10.0.0.138 -p 5432 -d dbname=postgres  --create-slot   --slot=receivewal_slot
--对运行库postgres进行备份，生成备份文档backup/
-- receivewal_slot 生成receivewal_slot复制槽，用于pg_receivewal定位wal接收起始节点。

对运行主库进行pgbench 相关操作，刷WAL日志，模拟数据操作。

-- 创建一个标识表，用于观察增量效果psql -c " create table local_end(id  integer);"pgbench -ipgbench -T 200-- 查看当前LSN  
psql -c " select  pg_current_wal_lsn ();"
-- 切换wal文件
psql -c "select pg_switch_wal()"
-- 在创建一个表示表，用于观察增量效果
psql -c " create table local_false(id  integer);"
-- 切换wal文件
psql -c "select pg_switch_wal()"

使用pg_receivewal 进行接收中间空缺的WAL日志

-- 使用捅backup同一个复制槽receivewal_slot  pg_receivewal  -D /home/postgres/back_archivedir -n -v --synchronous --no-loop --slot=receivewal_slot  -h 10.0.0.138 -p 5432 -U postgres -d dbname=postgres

使用pitr原理分别对备份进行增量到0/1B037B00、0/1C001FF8两个LSN节点

在备份库中进行相应操作

cd backup/
touch recovery.signal   --在备份数据目录下创建恢复模式标识文件 
vim    postgresql.conf   --编辑备份库的相关配置参数信息restore_command = 'cp /home/postgres/back_archivedir/%f %p'  #在备份指定其回放时，从归档路径中寻找历史wal
recovery_target_lsn = '0/1B037B00'   ## 增量到指定的LSN停靠点

启动数据库

pg_ctl -D backup/   start

查看日志，此时已经停靠到指定的LSN，并且可以看到0/1B037B00前创建的 local_end 表。

再次进行增量，只需要修改recovery_target_lsn参数便可。使其LSN二次增量的停靠点为0/1C001FF8

使用pg_controldata -D backup/查看备份的信息记录，可以看到其停靠点为0/1C001EF8符合预期

该方法支持多次增量同步，每一次增量只能向后推进时间线不可以向前。

pgBackRest

pgBackRest官网对于该备份工具（全量备份、差异备份、增量备份）
全量备份：pgBackRest 将数据库集群的全部内容复制到备份中。数据库集群的第一次备份始终是 Full Backup。pgBackRest 始终能够直接还原完整备份。完全备份不依赖于完全备份之外的任何文件来实现一致性。
差异备份：pgBackRest 仅复制自上次完整备份以来已更改的数据库群集文件。pgBackRest 通过复制所选差异备份中的所有文件以及上一个完整备份中相应的未更改文件来还原差异备份。差异备份的优点是它比完整备份需要更少的磁盘空间，但是，差异备份和完整备份都必须有效才能还原差异备份。
增量备份：pgBackRest 仅复制自上次备份（可以是另一个增量备份、差异备份或完整备份）以来已更改的数据库集群文件。由于增量备份仅包括自上次备份以来更改的那些文件，因此它们通常比完整备份或差异备份小得多。与差异备份一样，增量备份依赖于其他备份有效才能还原增量备份。由于增量备份仅包括自上次备份以来的文件，因此所有先前的增量备份返回到先前的差异备份、先前的差异备份和先前的完整备份都必须有效，才能执行增量备份的还原。如果不存在差异备份，则所有先前的增量备份都返回到先前的完整备份（必须存在），并且完整备份本身必须有效，才能还原增量备份。
下载pgbackres 进行安装。pgbackrest要求openssl需要再1.1以上版本。查看openssl版本

opnessl  version  ##如果低于1.1版本，此时需要进行手工安装
## 下载最新版本进行安装
yum remove openssl -y 
yum group install 'Development Tools'yum -y install zlib-devel zlib perl*yum -y install gcc zlib-devel bzip2 bzip2-devel readline-devel sqlite sqlite-devel openssl openssl-devel xz xz-devel libffi-devel libuuid-devel perl-core epel-release tk-devel ncurses-libs gdbm-devel libdbi-devel python-backports-lzma perl-IPC-Cmd perl-Data-Dumper更新系统已安装软件yum -y install
tar -zxvf openssl-3.4.0.tar.gz mv openssl-3.4.0 openssl 
cd openssl/
./config --prefix=/usr/local/openssl --openssldir=/usr/local/openssl --shared zlib
make 
make test ## 查看测试使得否通过
make ininstall 
## 软连接替换依赖
sudo mv /usr/lib64/libssl.so /usr/lib64/libssl.so.old
sudo ln -s /usr/local/openssl/lib64/libssl.so /usr/lib64/libssl.sosudo mv /usr/lib64/libcrypto.so /usr/lib64/libcrypto.so.old
sudo ln -s /usr/local/openssl/lib64/libcrypto.so /usr/lib64/libcrypto.so
## 动态库配置
echo "/usr/local/openssl/lib64" >> /etc/ld.so.conf.d/openssl-3.1.4.conf  
ldconfig -v
##默认环境编配
echo "export PATH=/usr/local/openssl/bin:\$PATH" | sudo tee /etc/profile.d/openssl.sh
echo "export LD_LIBRARY_PATH=/usr/local/openssl/lib64:\$LD_LIBRARY_PATH" | sudo tee -a /etc/profile.d/openssl.sh
source /etc/profile.d/openssl.sh
## 查看安装结果
openssl version

pgbackrest安装步骤

wget https://github.com/pgbackrest/pgbackrest/archive/refs/tags/release/2.54.0.tar.gz 
--解压
yum install libxml2-devel libyaml-devel bzip2-devel -y tar  -zxvf pgbackrest-release-2.54.0.tar.gz cd pgbackrest-release-2.54.0/src
./configure LDFLAGS="-L/usr/local/openssl/lib64" CPPFLAGS="-I/usr/local/openssl/include"
make && make install
## 指令包 复制到/usr/bin目录下
sudo scp  pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest创建相关存储路径
sudo mkdir -p -m 770 /var/log/pgbackrest   ## 日志文件路径
sudo chown postgres:postgres /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf  ## 配置文件路径
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf## 测试pgbackrest是否可用
su - postgres 
pgbackrest

定义pgbackrest.conf配置，
在v2.02中，pgBackRest配置文件的默认位置已从/etc/pgbackrest.conf更改为/etc/pgbackrest/pgbackrest.conf。会优先加载/etc/pgbackrest.conf，若不存在才会加载/etc/pgbackrest/pgbackrest.conf文件。

备份端配置如下
# vim /etc/pgbackrest/pgbackrest.conf
[test]
pg1-path=/home/postgres/pg/data
pg1-port=5432
pg1-socket-path=/tmp
pg1-user=postgres
pg1-host-config-path=/etc/pgbackrest
pg1-host=10.0.0.138
pg1-host-port=22
pg1-host-user=postgres[global]
process-max=3
start-fast=y
repo1-path=/home/postgres/backup
repo1-retention-full=2
backup-user=postgres
log-level-console=info
log-level-file=debug
buffer-size=16MiB
compress-type=gz
backup-standby=n[global:archive-push]
compress-level=3备份源配置如下
## vim /etc/pgbackrest/pgbackrest.conf[test]
pg1-path=/home/postgres/pg/data
[global]
log-level-file=detail
repo1-host=10.0.0.107
repo1-host-user=postgres
log-path=/var/log/pgbackrest

在数据源端和备份端，需要创建两个机器的免密登录信息

 ssh-keygen -t rsassh-copy-id postgres@10.0.0.138ssh-keygen -t rsassh-copy-id postgres@10.0.0.107

在备份源端，
注意$PGHOME/lib 需要配置到 /home/postgres/.bashrc 下

echo "
export PGHOME=/home/postgres/pg
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH  " >> /home/postgres/.bashrc
source /home/postgres/.bashrc

调整archive_command参数

archive_command = 'pgbackrest --stanza=test archive-push %p'
archive_mode = on
##重启数据库

备份端
执行创建备份

## 必须使用stanza-create  进行初始化节点。[postgres@vm107 ~]$  pgbackrest --stanza=test --log-level-console=info stanza-create
2024-10-29 20:46:32.293 P00   INFO: stanza-create command begin 2.54.0: --buffer-size=16MiB --exec-id=4578-cca3ce09 --log-level-console=info --log-level-file=debug --pg1-host=10.0.0.138 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=postgres --pg1-path=/home/postgres/pg/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=postgres --repo1-path=/home/postgres/backup --stanza=test
2024-10-29 20:46:32.550 P00   INFO: stanza-create for stanza 'test' on repo1
2024-10-29 20:46:32.664 P00   INFO: stanza-create command end: completed successfully (374ms)## 执行效验
[postgres@vm107 ~]$ pgbackrest --stanza=test check
2024-10-29 20:47:00.590 P00   INFO: check command begin 2.54.0: --backup-standby=y --buffer-size=16MiB --exec-id=4658-99926db1 --log-level-console=info --log-level-file=debug --pg1-host=10.0.0.138 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=postgres --pg1-path=/home/postgres/pg/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=postgres --repo1-path=/home/postgres/backup --stanza=test
2024-10-29 20:47:00.980 P00   WARN: option 'backup-standby' is enabled but standby is not properly configured
2024-10-29 20:47:00.980 P00   INFO: check repo1 configuration (primary)
2024-10-29 20:47:01.027 P00   INFO: check repo1 archive for WAL (primary)
2024-10-29 20:47:01.648 P00   INFO: WAL segment 000000010000000000000022 successfully archived to '/home/postgres/backup/archive/test/16-1/0000000100000000/000000010000000000000022-8dcdaed28bccf22a0ba8424d449053abcd7f5fe9.gz' on repo1
2024-10-29 20:47:01.754 P00   INFO: check command end: completed successfully (1166ms)## 执行全备份
[postgres@vm107 ~]$ pgbackrest --stanza=test --log-level-console=info backup
2024-10-29 20:59:28.674 P00   INFO: backup command begin 2.54.0: --backup-standby=n --buffer-size=16MiB --compress-type=gz --exec-id=4846-9c94c63f --log-level-console=info --log-level-file=debug --pg1-host=10.0.0.138 --pg1-host-config-path=/etc/pgbackrest --pg1-host-port=22 --pg1-host-user=postgres --pg1-path=/home/postgres/pg/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=postgres --process-max=3 --repo1-path=/home/postgres/backup --repo1-retention-full=2 --stanza=test --start-fast
2024-10-29 20:59:29.044 P00   WARN: no prior backup exists, incr backup has been changed to full
2024-10-29 20:59:29.044 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2024-10-29 20:59:29.145 P00   INFO: backup start archive = 000000010000000000000024, lsn = 0/24000028
2024-10-29 20:59:29.145 P00   INFO: check archive for prior segment 000000010000000000000023
2024-10-29 20:59:35.318 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-10-29 20:59:35.352 P00   INFO: backup stop archive = 000000010000000000000024, lsn = 0/24000138
2024-10-29 20:59:35.374 P00   INFO: check archive for segment(s) 000000010000000000000024:000000010000000000000024
2024-10-29 20:59:36.233 P00   INFO: new backup label = 20241029-205929F
2024-10-29 20:59:36.276 P00   INFO: full backup size = 42.7MB, file total = 988
2024-10-29 20:59:36.276 P00   INFO: backup command end: completed successfully (7604ms)
2024-10-29 20:59:36.276 P00   INFO: expire command begin 2.54.0: --buffer-size=16MiB --exec-id=4846-9c94c63f --log-level-console=info --log-level-file=debug --repo1-path=/home/postgres/backup --repo1-retention-full=2 --stanza=test
2024-10-29 20:59:36.277 P00   INFO: expire command end: completed successfully (1ms)

查看示例脚本

在其解压包有配置查看备份信息的脚本pgsql-pgbackrest-info.sql、pgsql-pgbackrest-query.sql

cd /build/pgbackrest-release-2.54.0/doc/example
## 如果备份端 有安装样例数据库的情况下，可以使用psql -f 可以进行加载
psql -f /build/pgbackrest-release-2.54.0/doc/example/pgsql-pgbackrest-info.sql  
psql -f /build/pgbackrest-release-2.54.0/doc/example/pgsql-pgbackrest-query.sql

执行增量备份

pgbackrest --stanza=test --log-level-console=info --type=incr backup