pgBackRest作为PostgreSQL数据库的备份和还原工具,在软件架构上深度融合了功能与性能的双重考量。它不仅确保了数据的完整性与一致性,还提供了严密的安全防护,同时支持数据的高效压缩以及异步并行处理。今天,我们将聚焦pgBackRest与最新版本的IvorySQL3.1的简单应用,共同展示IvorySQL与PG生态工具的出色兼容性与实用效能。
#1 创建用户及目录
首先,我们需要创建一个名为“ivorysql”的普通用户,该用户将专门用于编译和使用IvorySQL以及pgBackRest,确保系统资源的使用与权限分配得到妥善管理。
useraddivorysql
passwdivorysql
接下来,我们需要创建备份目录以及仓库、日志、配置等专用目录。
在本实例中,我们将采用自定义配置文件路径的方式来使用pgBackRest。
mkdir-p/home/ivorysql/backup
mkdir-p/home/ivorysql/backup/repo
mkdir-p/home/ivorysql/backup/log
mkdir-p/home/ivorysql/backup/conf
#2编译IvorySQL
注意事项:在执行./configure命令时,若系统提示需要安装ICU库,请确保执行以下命令以安装所需依赖,确保编译过程顺利进行。
yuminstall-ylibicu-devel
首先,下载IvorySQL的源码文件包,随后执行编译和安装流程,确保IvorySQL能够顺利集成至您的系统中。
tar-zxvfIvorySQL-IvorySQL_3.1.tar.gz
./configure--prefix=`pwd`/install
make&&makeinstall
编译完成后,为确保IvorySQL能够正确运行,我们需要设置相应的环境变量,以便系统能够正确识别并加载相关的配置和库文件。
vi~/.bashrc
exportPATH=$PATH:/home/ivorysql/IvorySQL-IvorySQL_3.1/install/bin
exportLD_LIBRARY_PATH=/home/ivorysql/IvorySQL-IvorySQL_3.1/install/lib
source~/.bashrc
接下来,我们将编译pgBackRest。
wgethttps://github.com/pgbackrest/pgbackrest/archive/release/2.50.tar.gz
tar-zxvf2.50.tar.gz
cdsrc&&./configure&&make
随后,我们需要将编译完成的pgBackRest可执行文件复制到/usr/bin目录下。
cp/home/ivorysql/backup/pgbackrest/usr/bin/
#3 配置pgBackRest
创建pgBackRest的配置文件pgbackrest.conf
cd/home/ivorysql/backup/conf
touchpgbackrest.conf
vipgbackrest.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的日志路径
#4 配置数据库
(一)初始化数据库
initdb-Ddata
接下来,我们需要对ivorysql.conf配置文件进行修改。
#增加如下内容
archive_command='pgbackrest--pg-version-force=16--config=/home/ivorysql/backup/conf/pgbackrest.conf--stanza=ivorysqlarchive-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-Ddatastart
#5 备份操作
首先,我们需要创建存储节点,以存储IvorySQL的备份数据。在执行相关命令时,请务必添加–pg-version-force=16参数,以兼容IvorySQL对pg_control文件的修改,确保备份过程的正确性和顺利性。
pgbackrest--stanza=ivorysqlstanza-create--config=/home/ivorysql/backup/conf/pgbackrest.conf--pg-version-force=16
(一)测试归档
首先,我们需要创建数据表并填充测试数据,随后执行切换日志文件的操作,以模拟数据库的正常使用场景,为后续备份和恢复操作提供必要的测试环境。
[ivorysql@localhostinstall]$psql
psql(16.1)
Type"help"forhelp.
ivorysql=#createtabletest(idint);
CREATETABLE
ivorysql=#insertintotestselectgenerate_series(1,10000);
INSERT010000
ivorysql=#selectpg_switch_wal();
pg_switch_wal
0/20BA9E0
(1row)
(二)查看归档文件
[ivorysql@localhost~]$cd/home/ivorysql/backup/repo/archive/ivorysql/16-1/0000000100000000
[ivorysql@localhost0000000100000000]$ll
total3864
-rw-r-----.1ivorysqlivorysql3694848Mar804:26000000010000000000000001-bdac3cc50aa5da3dc4309046f40623fed9a2b379.gz
-rw-r-----.1ivorysqlivorysql254848Mar804:27000000010000000000000002-62d76ca6d4d749b7d885621aafeee498775fde14.gz
(三)创建全量备份
[ivorysql@localhost~]$pgbackrest--stanza=ivorysql--type=fullbackup--config=/home/ivorysql/backup/conf/pgbackrest.conf--pg-version-force=16--log-level-console=info
2024-03-0804:32:12.989P00INFO:backupcommandbegin2.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-0804:32:13.697P00INFO:executenon-exclusivebackupstart:backupbeginsaftertherequestedimmediatecheckpointcompletes
2024-03-0804:32:14.403P00INFO:backupstartarchive=000000010000000000000006,lsn=0/6000028
2024-03-0804:32:14.403P00INFO:checkarchiveforpriorsegment000000010000000000000005
2024-03-0804:32:16.500P00INFO:executenon-exclusivebackupstopandwaitforallWALsegmentstoarchive
2024-03-0804:32:16.700P00INFO:backupstoparchive=000000010000000000000006,lsn=0/6000100
2024-03-0804:32:16.703P00INFO:checkarchiveforsegment(s)000000010000000000000006:000000010000000000000006
2024-03-0804:32:16.715P00INFO:newbackuplabel=20240308-043213F
2024-03-0804:32:16.751P00INFO:fullbackupsize=35.6MB,filetotal=1292
2024-03-0804:32:16.751P00INFO:backupcommandend:completedsuccessfully(3763ms)
2024-03-0804:32:16.751P00INFO:expirecommandbegin2.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-0804:32:16.756P00INFO:repo1:16-1removearchive,start=000000010000000000000001,stop=000000010000000000000003
2024-03-0804:32:16.756P00INFO:expirecommandend:completedsuccessfully(5ms)
(四)创建差异备份
[ivorysql@localhost~]$pgbackrest--stanza=ivorysql--type=diffbackup--config=/home/ivorysql/backup/conf/pgbackrest.conf--pg-version-force=16--log-level-console=info
2024-03-0804:32:58.113P00INFO:backupcommandbegin2.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-0804:32:58.831P00INFO:lastbackuplabel=20240308-043213F,version=2.50
2024-03-0804:32:58.831P00INFO:executenon-exclusivebackupstart:backupbeginsaftertherequestedimmediatecheckpointcompletes
2024-03-0804:32:59.337P00INFO:backupstartarchive=000000010000000000000008,lsn=0/8000028
2024-03-0804:32:59.337P00INFO:checkarchiveforpriorsegment000000010000000000000007
2024-03-0804:33:00.520P00INFO:executenon-exclusivebackupstopandwaitforallWALsegmentstoarchive
2024-03-0804:33:00.721P00INFO:backupstoparchive=000000010000000000000008,lsn=0/8000100
2024-03-0804:33:00.723P00INFO:checkarchiveforsegment(s)000000010000000000000008:000000010000000000000008
2024-03-0804:33:00.734P00INFO:newbackuplabel=20240308-043213F_20240308-043258D
2024-03-0804:33:00.764P00INFO:diffbackupsize=8.3KB,filetotal=1292
2024-03-0804:33:00.764P00INFO:backupcommandend:completedsuccessfully(2652ms)
2024-03-0804:33:00.765P00INFO:expirecommandbegin2.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-0804:33:00.768P00INFO:repo1:16-1noarchivetoremove
2024-03-0804:33:00.768P00INFO:expirecommandend:completedsuccessfully(3ms)
(五)增量备份
[ivorysql@localhost~]$pgbackrest--stanza=ivorysql--type=incrbackup--config=/home/ivorysql/backup/conf/pgbackrest.conf--pg-version-force=16--log-level-console=info
2024-03-0804:33:24.955P00INFO:backupcommandbegin2.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-0804:33:25.673P00INFO:lastbackuplabel=20240308-043213F_20240308-043258D,version=2.50
2024-03-0804:33:25.673P00INFO:executenon-exclusivebackupstart:backupbeginsaftertherequestedimmediatecheckpointcompletes
2024-03-0804:33:26.379P00INFO:backupstartarchive=00000001000000000000000A,lsn=0/A000028
2024-03-0804:33:26.379P00INFO:checkarchiveforpriorsegment000000010000000000000009
2024-03-0804:33:27.519P00INFO:executenon-exclusivebackupstopandwaitforallWALsegmentstoarchive
2024-03-0804:33:27.726P00INFO:backupstoparchive=00000001000000000000000A,lsn=0/A000100
2024-03-0804:33:27.728P00INFO:checkarchiveforsegment(s)00000001000000000000000A:00000001000000000000000A
2024-03-0804:33:27.739P00INFO:newbackuplabel=20240308-043213F_20240308-043325I
2024-03-0804:33:27.772P00INFO:incrbackupsize=8.3KB,filetotal=1292
2024-03-0804:33:27.772P00INFO:backupcommandend:completedsuccessfully(2818ms)
2024-03-0804:33:27.772P00INFO:expirecommandbegin2.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-0804:33:27.775P00INFO:repo1:16-1noarchivetoremove
2024-03-0804:33:27.776P00INFO:expirecommandend:completedsuccessfully(4ms)
(六)查看备份信息
[ivorysql@localhost~]$pgbackrest--stanza=ivorysqlinfo--config=/home/ivorysql/backup/conf/pgbackrest.conf--log-level-console=info
stanza:ivorysql
status:ok
cipher:aes-256-cbc
db(current)
walarchivemin/max(16):000000010000000000000004/00000001000000000000000A
fullbackup:20240308-043137F
timestampstart/stop:2024-03-0804:31:37+08/2024-03-0804:31:41+08
walstart/stop:000000010000000000000004/000000010000000000000004
databasesize:35.6MB,databasebackupsize:35.6MB
repo1:backupsize:4.8MB
fullbackup:20240308-043213F
timestampstart/stop:2024-03-0804:32:13+08/2024-03-0804:32:16+08
walstart/stop:000000010000000000000006/000000010000000000000006
databasesize:35.6MB,databasebackupsize:35.6MB
repo1:backupsize:4.8MB
diffbackup:20240308-043213F_20240308-043258D
timestampstart/stop:2024-03-0804:32:58+08/2024-03-0804:33:00+08
walstart/stop:000000010000000000000008/000000010000000000000008
databasesize:35.6MB,databasebackupsize:8.3KB
repo1:backupsize:480B
backupreferencelist:20240308-043213F
incrbackup:20240308-043213F_20240308-043325I
timestampstart/stop:2024-03-0804:33:25+08/2024-03-0804:33:27+08
walstart/stop:00000001000000000000000A/00000001000000000000000A
databasesize:35.6MB,databasebackupsize:8.3KB
repo1:backupsize:480B
backupreferencelist:20240308-043213F,20240308-043213F_20240308-043258D
(七)验证
[ivorysql@localhost~]$pgbackrest--stanza=ivorysqlverify--config=/home/ivorysql/backup/conf/pgbackrest.conf--log-level-console=info--verbose=y
2024-03-0804:35:58.862P00INFO:verifycommandbegin2.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-0804:35:59.441P00INFO:stanza:ivorysql
status:ok
archiveId:16-1,totalWALchecked:7,totalvalidWAL:7
mi服务器托管网ssing:0,checksuminvalid:0,sizeinvalid:0,other:0
backup:20240308-043137F,status:valid,totalfileschecked:1292,totalvalidfiles:1292
missing:0,checksuminvalid:0,sizeinvalid:0,other:0
backup:20240308-043213F,status:valid,totalfileschecked:1292,totalvalidfiles:1292
missing:0,checksuminvalid:0,sizeinvalid:0,other:0
backup:20240308-043213F_20240308-043258D,status:valid,totalfileschecked:1292,totalvalidfiles:1292
missing:0,checksuminvalid:0,sizeinvalid:0,other:0
backup:20240308-043213F_20240308-043325I,status:valid,totalfileschecked:1292,totalvalidfiles:1292
missing:0,checksuminvalid:0,sizeinvalid:0,other:0
2024-03-0804:35:59.441P00INFO:verifycommandend:completedsuccessfully(580ms)
#6 总结
上述内容简要介绍了pgBackRest与IvorySQL的基础应用。由于IvorySQL对PostgreSQL原生的控制文件进行了修改,因此在执行各类备份操作时,我们需格外注意并采取相应的措施,以确保备份的完整性和准确性。
社区仓库:
https://github.com/IvorySQL/IvorySQL
IvorySQL社区欢迎并赞赏所有类型的贡献,期待您的加入!
服务器托管,北京服务器托管,服务器租用 http://www.fwqtg.net
CSS定义 层叠样式表 (Cascading Style Sheets,缩写为 CSS),是一种 样式表 语言,用来描述 HTML 文档的呈现(美化内容) 书写位置:title 标签下方添加 style 双标签,style 标签里面书写 CSS 代码。 示例:…