备份恢复新体验！pgBackRest与IvorySQL的完美融合-每日运维

pgBackRest作为PostgreSQL数据库的备份和还原工具，在软件架构上深度融合了功能与性能的双重考量。它不仅确保了数据的完整性与一致性，还提供了严密的安全防护，同时支持数据的高效压缩以及异步并行处理。今天，我们将聚焦pgBackRest与最新版本的IvorySQL3.1的简单应用，共同展示IvorySQL与PG生态工具的出色兼容性与实用效能。

创建用户及目录

首先，我们需要创建一个名为“ivorysql”的普通用户，该用户将专门用于编译和使用IvorySQL以及pgBackRest，确保系统资源的使用与权限分配得到妥善管理。

useradd ivorysql passwd ivorysql

接下来，我们需要创建备份目录以及仓库、日志、配置等专用目录。

在本实例中，我们将采用自定义配置文件路径的方式来使用pgBackRest。

mkdir -p /home/ivorysql/backup mkdir -p /home/ivorysql/backup/repo mkdir -p /home/ivorysql/backup/log mkdir -p /home/ivorysql/backup/conf

编译IvorySQL

注意事项：在执行./configure命令时，若系统提示需要安装ICU库，请确保执行以下命令以安装所需依赖，确保编译过程顺利进行。

yum install -y libicu-devel

首先，下载IvorySQL的源码文件包，随后执行编译和安装流程，确保IvorySQL能够顺利集成至您的系统中。

tar -zxvf IvorySQL-IvorySQL_3.1.tar.gz ./configure --prefix=`pwd`/install make && make install

编译完成后，为确保IvorySQL能够正确运行，我们需要设置相应的环境变量，以便系统能够正确识别并加载相关的配置和库文件。

vi ~/.bashrc export PATH=$PATH:/home/ivorysql/IvorySQL-IvorySQL_3.1/install/bin export LD_LIBRARY_PATH=/home/ivorysql/IvorySQL-IvorySQL_3.1/install/lib source ~/.bashrc

接下来，我们将编译pgBackRest。

wget https://github.com/pgbackrest/pgbackrest/archive/release/2.50.tar.gz tar -zxvf 2.50.tar.gz cd src && ./configure && make

随后，我们需要将编译完成的pgBackRest可执行文件复制到/usr/bin目录下。

cp /home/ivorysql/backup/pgbackrest /usr/bin/

配置pgBackRest

创建pgBackRest的配置文件pgbackrest.conf

cd /home/ivorysql/backup/conf touch pgbackrest.conf vi pgbackrest.conf

配置文件内容如下：

[ivorysql] pg1-path=/home/ivorysql/IvorySQL-IvorySQL_3.1/install/data pg1-user=ivorysql pg1-port=5432


[global]
repo1-block=y
repo1-bundle=y
repo1-path=/home/ivorysql/backup/repo
repo1-cipher-pass=L8yY5t2uaAf6/+uHb0rEWBDZ+n5ZA9yIk3BxrtCTwEns7znjwpLb0ReIxcSHnqxg
repo1-cipher-type=aes-256-cbc
repo1-retention-full=2
repo1-retention-diff=1
log-path=/home/ivorysql/backup/log
start-fast=y
[global:archive-push]
compress-level=3

其中主要参数如下：

pg1-path为当前数据库数据文件夹绝对路径
pg1-user为数据库用户，此处使用默认用户
pg1-port为数据库端口
repo-path为pgbackrest备份仓库路径
log-path为pgbackrest的日志路径

配置数据库

（一）初始化数据库

initdb -D data

接下来，我们需要对ivorysql.conf配置文件进行修改。

#增加如下内容 archive_command = 'pgbackrest --pg-version-force=16 --config=/home/ivorysql/backup/conf/pgbackrest.conf --stanza=ivorysql archive-push %p' archive_mode = on hot_standby = on log_filename = 'postgresql.log' max_wal_senders = 3 wal_level = replica

请注意，由于IvorySQL对原有的pg_control文件进行了修改，因此我们需要在使用pgBackRest时添加--pg-version-force=16参数，以便绕过版本验证步骤，确保备份和恢复过程的顺利进行。

（二）启动数据库

pg_ctl -D data start

备份操作

首先，我们需要创建存储节点，以存储IvorySQL的备份数据。在执行相关命令时，请务必添加--pg-version-force=16参数，以兼容IvorySQL对pg_control文件的修改，确保备份过程的正确性和顺利性。

pgbackrest --stanza=ivorysql stanza-create --config=/home/ivorysql/backup/conf/pgbackrest.conf --pg-version-force=16

（一）测试归档

首先，我们需要创建数据表并填充测试数据，随后执行切换日志文件的操作，以模拟数据库的正常使用场景，为后续备份和恢复操作提供必要的测试环境。

[ivorysql@localhost install]$ psql psql (16.1) Type "help" for help.


ivorysql=# create table test(id int);
CREATE TABLE
ivorysql=# insert into test select generate_series(1,10000);
INSERT 0 10000
ivorysql=# select pg_switch_wal();
 pg_switch_wal
 0/20BA9E0
(1 row)

（二）查看归档文件

[ivorysql@localhost ~]$ cd /home/ivorysql/backup/repo/archive/ivorysql/16-1/0000000100000000 [ivorysql@localhost 0000000100000000]$ ll total 3864 -rw-r-----. 1 ivorysql ivorysql 3694848 Mar 8 04:26 000000010000000000000001-bdac3cc50aa5da3dc4309046f40623fed9a2b379.gz -rw-r-----. 1 ivorysql ivorysql 254848 Mar 8 04:27 000000010000000000000002-62d76ca6d4d749b7d885621aafeee498775fde14.gz

（三）创建全量备份

[ivorysql@localhost ~]$ pgbackrest --stanza=ivorysql --type=full backup --config=/home/ivorysql/backup/conf/pgbackrest.conf --pg-version-force=16 --log-level-console=info 2024-03-08 04:32:12.989 P00 INFO: backup command begin 2.50: --config=/home/ivorysql/backup/conf/pgbackrest.conf --exec-id=4644-5c559805 --log-level-console=info --log-path=/home/ivorysql/backup/log --pg1-path=/home/ivorysql/IvorySQL-IvorySQL_3.1/install/data --pg1-port=5432 --pg1-user=ivorysql --pg-version-force=16 --repo1-block --repo1-bundle --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo1-path=/home/ivorysql/backup/repo --repo1-retention-diff=1 --repo1-retention-full=2 --stanza=ivorysql --start-fast --type=full 2024-03-08 04:32:13.697 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes 2024-03-08 04:32:14.403 P00 INFO: backup start archive = 000000010000000000000006, lsn = 0/6000028 2024-03-08 04:32:14.403 P00 INFO: check archive for prior segment 000000010000000000000005 2024-03-08 04:32:16.500 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2024-03-08 04:32:16.700 P00 INFO: backup stop archive = 000000010000000000000006, lsn = 0/6000100 2024-03-08 04:32:16.703 P00 INFO: check archive for segment(s) 000000010000000000000006:000000010000000000000006 2024-03-08 04:32:16.715 P00 INFO: new backup label = 20240308-043213F 2024-03-08 04:32:16.751 P00 INFO: full backup size = 35.6MB, file total = 1292 2024-03-08 04:32:16.751 P00 INFO: backup command end: completed successfully (3763ms) 2024-03-08 04:32:16.751 P00 INFO: expire command begin 2.50: --config=/home/ivorysql/backup/conf/pgbackrest.conf --exec-id=4644-5c559805 --log-level-console=info --log-path=/home/ivorysql/backup/log --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo1-path=/home/ivorysql/backup/repo --repo1-retention-diff=1 --repo1-retention-full=2 --stanza=ivorysql 2024-03-08 04:32:16.756 P00 INFO: repo1: 16-1 remove archive, start = 000000010000000000000001, stop = 000000010000000000000003 2024-03-08 04:32:16.756 P00 INFO: expire command end: completed successfully (5ms)

（四）创建差异备份

[ivorysql@localhost ~]$ pgbackrest --stanza=ivorysql --type=diff backup --config=/home/ivorysql/backup/conf/pgbackrest.conf --pg-version-force=16 --log-level-console=info 2024-03-08 04:32:58.113 P00 INFO: backup command begin 2.50: --config=/home/ivorysql/backup/conf/pgbackrest.conf --exec-id=4653-761b3d49 --log-level-console=info --log-path=/home/ivorysql/backup/log --pg1-path=/home/ivorysql/IvorySQL-IvorySQL_3.1/install/data --pg1-port=5432 --pg1-user=ivorysql --pg-version-force=16 --repo1-block --repo1-bundle --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo1-path=/home/ivorysql/backup/repo --repo1-retention-diff=1 --repo1-retention-full=2 --stanza=ivorysql --start-fast --type=diff 2024-03-08 04:32:58.831 P00 INFO: last backup label = 20240308-043213F, version = 2.50 2024-03-08 04:32:58.831 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes 2024-03-08 04:32:59.337 P00 INFO: backup start archive = 000000010000000000000008, lsn = 0/8000028 2024-03-08 04:32:59.337 P00 INFO: check archive for prior segment 000000010000000000000007 2024-03-08 04:33:00.520 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2024-03-08 04:33:00.721 P00 INFO: backup stop archive = 000000010000000000000008, lsn = 0/8000100 2024-03-08 04:33:00.723 P00 INFO: check archive for segment(s) 000000010000000000000008:000000010000000000000008 2024-03-08 04:33:00.734 P00 INFO: new backup label = 20240308-043213F_20240308-043258D 2024-03-08 04:33:00.764 P00 INFO: diff backup size = 8.3KB, file total = 1292 2024-03-08 04:33:00.764 P00 INFO: backup command end: completed successfully (2652ms) 2024-03-08 04:33:00.765 P00 INFO: expire command begin 2.50: --config=/home/ivorysql/backup/conf/pgbackrest.conf --exec-id=4653-761b3d49 --log-level-console=info --log-path=/home/ivorysql/backup/log --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo1-path=/home/ivorysql/backup/repo --repo1-retention-diff=1 --repo1-retention-full=2 --stanza=ivorysql 2024-03-08 04:33:00.768 P00 INFO: repo1: 16-1 no archive to remove 2024-03-08 04:33:00.768 P00 INFO: expire command end: completed successfully (3ms)

（五）增量备份

[ivorysql@localhost ~]$ pgbackrest --stanza=ivorysql --type=incr backup --config=/home/ivorysql/backup/conf/pgbackrest.conf --pg-version-force=16 --log-level-console=info 2024-03-08 04:33:24.955 P00 INFO: backup command begin 2.50: --config=/home/ivorysql/backup/conf/pgbackrest.conf --exec-id=4668-1454bb89 --log-level-console=info --log-path=/home/ivorysql/backup/log --pg1-path=/home/ivorysql/IvorySQL-IvorySQL_3.1/install/data --pg1-port=5432 --pg1-user=ivorysql --pg-version-force=16 --repo1-block --repo1-bundle --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo1-path=/home/ivorysql/backup/repo --repo1-retention-diff=1 --repo1-retention-full=2 --stanza=ivorysql --start-fast --type=incr 2024-03-08 04:33:25.673 P00 INFO: last backup label = 20240308-043213F_20240308-043258D, version = 2.50 2024-03-08 04:33:25.673 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes 2024-03-08 04:33:26.379 P00 INFO: backup start archive = 00000001000000000000000A, lsn = 0/A000028 2024-03-08 04:33:26.379 P00 INFO: check archive for prior segment 000000010000000000000009 2024-03-08 04:33:27.519 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive 2024-03-08 04:33:27.726 P00 INFO: backup stop archive = 00000001000000000000000A, lsn = 0/A000100 2024-03-08 04:33:27.728 P00 INFO: check archive for segment(s) 00000001000000000000000A:00000001000000000000000A 2024-03-08 04:33:27.739 P00 INFO: new backup label = 20240308-043213F_20240308-043325I 2024-03-08 04:33:27.772 P00 INFO: incr backup size = 8.3KB, file total = 1292 2024-03-08 04:33:27.772 P00 INFO: backup command end: completed successfully (2818ms) 2024-03-08 04:33:27.772 P00 INFO: expire command begin 2.50: --config=/home/ivorysql/backup/conf/pgbackrest.conf --exec-id=4668-1454bb89 --log-level-console=info --log-path=/home/ivorysql/backup/log --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo1-path=/home/ivorysql/backup/repo --repo1-retention-diff=1 --repo1-retention-full=2 --stanza=ivorysql 2024-03-08 04:33:27.775 P00 INFO: repo1: 16-1 no archive to remove 2024-03-08 04:33:27.776 P00 INFO: expire command end: completed successfully (4ms)

（六）查看备份信息

[ivorysql@localhost ~]$ pgbackrest --stanza=ivorysql info --config=/home/ivorysql/backup/conf/pgbackrest.conf --log-level-console=info stanza: ivorysql status: ok cipher: aes-256-cbc db (current) wal archive min/max (16): 000000010000000000000004/00000001000000000000000A


    full backup: 20240308-043137F
        timestamp start/stop: 2024-03-08 04:31:37+08 / 2024-03-08 04:31:41+08
        wal start/stop: 000000010000000000000004 / 000000010000000000000004
        database size: 35.6MB, database backup size: 35.6MB
        repo1: backup size: 4.8MB
    full backup: 20240308-043213F
        timestamp start/stop: 2024-03-08 04:32:13+08 / 2024-03-08 04:32:16+08
        wal start/stop: 000000010000000000000006 / 000000010000000000000006
        database size: 35.6MB, database backup size: 35.6MB
        repo1: backup size: 4.8MB
    diff backup: 20240308-043213F_20240308-043258D
        timestamp start/stop: 2024-03-08 04:32:58+08 / 2024-03-08 04:33:00+08
        wal start/stop: 000000010000000000000008 / 000000010000000000000008
        database size: 35.6MB, database backup size: 8.3KB
        repo1: backup size: 480B
        backup reference list: 20240308-043213F
    incr backup: 20240308-043213F_20240308-043325I
        timestamp start/stop: 2024-03-08 04:33:25+08 / 2024-03-08 04:33:27+08
        wal start/stop: 00000001000000000000000A / 00000001000000000000000A
        database size: 35.6MB, database backup size: 8.3KB
        repo1: backup size: 480B
        backup reference list: 20240308-043213F, 20240308-043213F_20240308-043258D

（七）验证

[ivorysql@localhost ~]$ pgbackrest --stanza=ivorysql verify --config=/home/ivorysql/backup/conf/pgbackrest.conf --log-level-console=info --verbose=y 2024-03-08 04:35:58.862 P00 INFO: verify command begin 2.50: --config=/home/ivorysql/backup/conf/pgbackrest.conf --exec-id=4719-18757686 --log-level-console=info --log-path=/home/ivorysql/backup/log --repo1-cipher-pass= --repo1-cipher-type=aes-256-cbc --repo1-path=/home/ivorysql/backup/repo --stanza=ivorysql --verbose 2024-03-08 04:35:59.441 P00 INFO: stanza: ivorysql status: ok archiveId: 16-1, total WAL checked: 7, total valid WAL: 7 missing: 0, checksum invalid: 0, size invalid: 0, other: 0 backup: 20240308-043137F, status: valid, total files checked: 1292, total valid files: 1292 missing: 0, checksum invalid: 0, size invalid: 0, other: 0 backup: 20240308-043213F, status: valid, total files checked: 1292, total valid files: 1292 missing: 0, checksum invalid: 0, size invalid: 0, other: 0 backup: 20240308-043213F_20240308-043258D, status: valid, total files checked: 1292, total valid files: 1292 missing: 0, checksum invalid: 0, size invalid: 0, other: 0 backup: 20240308-043213F_20240308-043325I, status: valid, total files checked: 1292, total valid files: 1292 missing: 0, checksum invalid: 0, size invalid: 0, other: 0 2024-03-08 04:35:59.441 P00 INFO: verify command end: completed successfully (580ms)

总结

上述内容简要介绍了pgBackRest与IvorySQL的基础应用。由于IvorySQL对PostgreSQL原生的控制文件进行了修改，因此在执行各类备份操作时，我们需格外注意并采取相应的措施，以确保备份的完整性和准确性。

社区仓库：https://github.com/IvorySQL/IvorySQLIvorySQL社区欢迎并赞赏所有类型的贡献，期待您的加入！

备份恢复新体验！pgBackRest与IvorySQL的完美融合

相关文章

发布评论取消回复

剑圣无痕

百度搜索：蓝易云 – centos7系统手动安装ceph教程

出行365：用国产数据库为国人出行“护航”

tidb8.1的磁盘选择，关于网络ssd，和本地ssd的选择对性能影响很大，差距60倍。