首页 > 数据库 >PostgreSQL pgBackRest 是最好的PG备份工具 ? (小试牛刀 1)

PostgreSQL pgBackRest 是最好的PG备份工具 ? (小试牛刀 1)

时间:2023-10-30 17:01:47浏览次数:40  
标签:INFO 10 PostgreSQL 23 -- 09 PG 2023 小试牛刀


PostgreSQL  pgBackRest 是最好的PG备份工具 ? (小试牛刀  1)_hive


之前备份的工具一直在使用PGRMAN,潮流变化了,现在最新最推崇的PG备份软件是pgBackRest,今天来探究一下到底为什么他是目前最推崇的备份软件。

根据GITHUB的介绍,pgbackrest 目标是一个可信赖的容易使用的备份和恢复工具和一体化的解决方案,针对大型的数据库和多负载的情况下的选择,目前写文时的版本是2.48,其中功能特点如下

1 并行备份和恢复

2 支持本地和远程的备份方式

3 多种备份的留存方式可选择

4 全备,差异,以及增量备份的方式都可以支持

5  针对数据备份归档的一体化的留存设置

6  备份的完整性验证

7  多种数据的备份的设备支持,数据备份的加密

8  多种不同版本的PG的数据库支持

同时crunchy data 对于软件的开发进行背书,相信熟悉postgresql数据库的同学对于crunchy data并不会太陌生。

先需要将依赖库都安装上 

sudo yum install postgresql-libs libssh2

sudo yum install libyaml-devel

sudo yum install bzip2-devel

进入到下载后的pgbackrest的 src目录中并加载PG的变量后,进行安装.configure

PostgreSQL  pgBackRest 是最好的PG备份工具 ? (小试牛刀  1)_postgresql_02

PostgreSQL  pgBackRest 是最好的PG备份工具 ? (小试牛刀  1)_postgresql_03

编译后,相关的执行文件存在与/usr/local/bin 中,安装不成功的大部分是没有加载相关的PG变量环境。

PostgreSQL  pgBackRest 是最好的PG备份工具 ? (小试牛刀  1)_hive_04

在安装完毕后,后面就是配置相关的pgbackrest需要的配置信息,这里需要进行设置一个供pgbackrest放置配置文件和日志的目录,这里在磁盘目录上建立一个目录 pgbackrest 并且需要postgres 账号在此目录有绝对权限。

这里需要注意,严格按照配置文档中的信息填写,基于编译后,默认文件夹和配置文件读取的位置暂时不能变化。

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 的配置文件进行简单的配置

[test]

pg1-path=/pgdata/data

pg1-port=5432

pg1-socket-path=/tmp

[global]

repo1-path=/pgbackrest/backup

repo1-retention-full=2

log-level-console=info

log-level-file=debug

[global:archive-push]

compress-level=3

同时这里还需要针对PG数据库进行相关的设置,这里先略过,后面会在写一篇分析这个备份软件的时候在提到。

然后先针对我们的配置进行检测

pgbackrest --stanza=test check
2023-10-09 22:44:01.156 P00   INFO: check command begin 2.48: --exec-id=20685-efcf99cd --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --pg1-port=5432 --pg1-socket-path=/tmp --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 22:44:01.869 P00   INFO: check repo1 configuration (primary)
2023-10-09 22:44:03.115 P00   INFO: check repo1 archive for WAL (primary)
2023-10-09 22:44:03.440 P00   INFO: WAL segment 000000010000000700000019 successfully archived to '/pgbackrest/backup/archive/test/13-1/0000000100000007/000000010000000700000019-c8fc91fc730963a2e453fe3f735da51a3fc773c5.gz' on repo1
2023-10-09 22:44:03.440 P00   INFO: check command end: completed successfully (2286ms)

 然后我们针对这个备份配置基础上创建备份的基础信息在本地数据库上。    

pgbackrest --stanza=test stanza-create
2023-10-09 22:58:00.959 P00   INFO: stanza-create command begin 2.48: --exec-id=20723-2d7e77fa --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --pg1-port=5432 --pg1-socket-path=/tmp --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 22:58:01.674 P00   INFO: stanza-create for stanza 'test' on repo1
2023-10-09 22:58:01.678 P00   INFO: stanza 'test' already exists on repo1 and is valid
2023-10-09 22:58:01.678 P00   INFO: stanza-create command end: completed successfully (720ms)

先针对数据库进行全备,然后在进行增量备份等

[postgres@postgresql13 ~]$ pgbackrest --stanza=test --type=full backup
2023-10-09 23:02:06.959 P00   INFO: backup command begin 2.48: --exec-id=20749-40619772 --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --pg1-port=5432 --pg1-socket-path=/tmp --repo1-path=/pgbackrest/backup --repo1-retention-full=2 --stanza=test --type=full
2023-10-09 23:02:07.737 P00   INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2023-10-09 23:02:08.785 P00   INFO: backup start archive = 00000001000000070000001B, lsn = 7/6C000060
2023-10-09 23:02:08.785 P00   INFO: check archive for prior segment 00000001000000070000001A
ERROR: [082]: WAL segment 00000001000000070000001A was not archived before the 60000ms timeout
       HINT: check the archive_command to ensure that all options are correct (especially --stanza).
       HINT: check the PostgreSQL server log for errors.
       HINT: run the 'start' command if the stanza was previously stopped.
2023-10-09 23:03:08.806 P00   INFO: backup command end: aborted with exception [082]

可以进行差异备份

pgbackrest --stanza=test --type=diff --log-level-console=info backup
2023-10-09 23:15:41.673 P00   INFO: backup command begin 2.48: --exec-id=20854-2678592e --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --pg1-port=5432 --pg1-socket-path=/tmp --repo1-path=/pgbackrest/backup --repo1-retention-full=2 --stanza=test --type=diff
WARN: no prior backup exists, diff backup has been changed to full
2023-10-09 23:15:42.480 P00   INFO: execute non-exclusive backup start: backup begins after the next regular checkpoint completes
2023-10-09 23:15:44.874 P00   INFO: backup start archive = 00000001000000070000001C, lsn = 7/70000028
2023-10-09 23:15:44.874 P00   INFO: check archive for prior segment 00000001000000070000001B
ERROR: [082]: WAL segment 00000001000000070000001B was not archived before the 60000ms timeout
       HINT: check the archive_command to ensure that all options are correct (especially --stanza).
       HINT: check the PostgreSQL server log for errors.
       HINT: run the 'start' command if the stanza was previously stopped.
2023-10-09 23:16:44.897 P00   INFO: backup command end: aborted with exception [082]
[postgres@postgresql13 ~]$

在备份后对数据库进行恢复

pg_ctl -D /pgdata/data/ stop
waiting for server to shut down....2023-10-09 23:29:40.778 EDT [20962] LOG:  received fast shutdown request
2023-10-09 23:29:40.778 EDT [20962] LOG:  aborting any active transactions
2023-10-09 23:29:40.780 EDT [20962] LOG:  background worker "logical replication launcher" (PID 20970) exited with exit code 1
2023-10-09 23:29:40.780 EDT [20964] LOG:  shutting down
2023-10-09 23:29:41.510 P00   INFO: archive-push command begin 2.48: [pg_wal/00000001000000070000001F] --compress-level=3 --exec-id=20992-0d418f8e --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
.2023-10-09 23:29:41.893 P00   INFO: pushed WAL file '00000001000000070000001F' to the archive
2023-10-09 23:29:41.893 P00   INFO: archive-push command end: completed successfully (384ms)
2023-10-09 23:29:41.897 EDT [20962] LOG:  database system is shut down
 done
server stopped
[postgres@postgresql13 ~]$  pgbackrest --stanza=test --log-level-console=info restore
2023-10-09 23:29:52.031 P00   INFO: restore command begin 2.48: --exec-id=20994-5e8a8ad2 --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 23:29:52.044 P00   INFO: repo1: restore backup set 20231009-232636F, recovery will start at 2023-10-09 23:26:36
ERROR: [040]: unable to restore to path '/pgdata/data' because it contains files
       HINT: try using --delta if this is what you intended.
2023-10-09 23:29:52.046 P00   INFO: restore command end: aborted with exception [040]
[postgres@postgresql13 ~]$ cd /pgdata/data/
[postgres@postgresql13 data]$ rm -rf *
[postgres@postgresql13 data]$  pgbackrest --stanza=test --log-level-console=info restore
2023-10-09 23:30:12.311 P00   INFO: restore command begin 2.48: --exec-id=20997-6facbc94 --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 23:30:12.332 P00   INFO: repo1: restore backup set 20231009-232636F, recovery will start at 2023-10-09 23:26:36
2023-10-09 23:30:25.081 P00   INFO: write updated /pgdata/data/postgresql.auto.conf
2023-10-09 23:30:25.083 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-09 23:30:25.084 P00   INFO: restore size = 1.6GB, file total = 1259
2023-10-09 23:30:25.084 P00   INFO: restore command end: completed successfully (12775ms)
[postgres@postgresql13 data]$ pg_ctl -D /pgdata/data/ start
waiting for server to start....2023-10-09 23:30:32.112 EDT [21001] LOG:  starting PostgreSQL 13.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-10-09 23:30:32.112 EDT [21001] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-10-09 23:30:32.112 EDT [21001] LOG:  listening on IPv6 address "::", port 5432
2023-10-09 23:30:32.115 EDT [21001] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-10-09 23:30:32.119 EDT [21002] LOG:  database system was interrupted; last known up at 2023-10-09 23:26:37 EDT
2023-10-09 23:30:32.141 P00   INFO: archive-get command begin 2.48: [00000002.history, pg_wal/RECOVERYHISTORY] --exec-id=21003-eeb7a5b0 --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 23:30:32.144 P00   INFO: unable to find 00000002.history in the archive
2023-10-09 23:30:32.144 P00   INFO: archive-get command end: completed successfully (4ms)
2023-10-09 23:30:32.144 EDT [21002] LOG:  starting archive recovery
2023-10-09 23:30:32.151 P00   INFO: archive-get command begin 2.48: [00000001000000070000001E, pg_wal/RECOVERYXLOG] --exec-id=21004-fa63ee19 --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 23:30:32.358 P00   INFO: found 00000001000000070000001E in the repo1: 13-1 archive
2023-10-09 23:30:32.358 P00   INFO: archive-get command end: completed successfully (208ms)
2023-10-09 23:30:32.359 EDT [21002] LOG:  restored log file "00000001000000070000001E" from archive
2023-10-09 23:30:32.733 EDT [21002] LOG:  redo starts at 7/78000060
2023-10-09 23:30:32.734 EDT [21002] LOG:  consistent recovery state reached at 7/78000170
2023-10-09 23:30:32.735 EDT [21001] LOG:  database system is ready to accept read only connections
2023-10-09 23:30:32.741 P00   INFO: archive-get command begin 2.48: [00000001000000070000001F, pg_wal/RECOVERYXLOG] --exec-id=21007-ee1cf9df --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
 done
server started
[postgres@postgresql13 data]$ 2023-10-09 23:30:32.953 P00   INFO: found 00000001000000070000001F in the repo1: 13-1 archive
2023-10-09 23:30:32.953 P00   INFO: archive-get command end: completed successfully (213ms)
2023-10-09 23:30:32.954 EDT [21002] LOG:  restored log file "00000001000000070000001F" from archive
2023-10-09 23:30:33.288 P00   INFO: archive-get command begin 2.48: [000000010000000700000020, pg_wal/RECOVERYXLOG] --exec-id=21009-37902dce --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 23:30:33.291 P00   INFO: unable to find 000000010000000700000020 in the archive
2023-10-09 23:30:33.291 P00   INFO: archive-get command end: completed successfully (4ms)
2023-10-09 23:30:33.292 EDT [21002] LOG:  redo done at 7/7C000060
2023-10-09 23:30:33.301 P00   INFO: archive-get command begin 2.48: [00000001000000070000001F, pg_wal/RECOVERYXLOG] --exec-id=21010-8e086c39 --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 23:30:33.500 P00   INFO: found 00000001000000070000001F in the repo1: 13-1 archive
2023-10-09 23:30:33.500 P00   INFO: archive-get command end: completed successfully (201ms)
2023-10-09 23:30:33.501 EDT [21002] LOG:  restored log file "00000001000000070000001F" from archive
2023-10-09 23:30:33.825 P00   INFO: archive-get command begin 2.48: [00000002.history, pg_wal/RECOVERYHISTORY] --exec-id=21011-f1336823 --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 23:30:33.826 P00   INFO: unable to find 00000002.history in the archive
2023-10-09 23:30:33.826 P00   INFO: archive-get command end: completed successfully (2ms)
2023-10-09 23:30:33.827 EDT [21002] LOG:  selected new timeline ID: 2
2023-10-09 23:30:34.195 EDT [21002] LOG:  archive recovery complete
2023-10-09 23:30:34.206 P00   INFO: archive-get command begin 2.48: [00000001.history, pg_wal/RECOVERYHISTORY] --exec-id=21012-340144e2 --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 23:30:34.209 P00   INFO: unable to find 00000001.history in the archive
2023-10-09 23:30:34.209 P00   INFO: archive-get command end: completed successfully (5ms)
2023-10-09 23:30:34.220 EDT [21001] LOG:  database system is ready to accept connections
2023-10-09 23:30:34.229 P00   INFO: archive-push command begin 2.48: [pg_wal/00000002.history] --compress-level=3 --exec-id=21017-0b370cc3 --log-level-console=info --log-level-file=debug --pg1-path=/pgdata/data --repo1-path=/pgbackrest/backup --stanza=test
2023-10-09 23:30:34.234 P00   INFO: pushed WAL file '00000002.history' to the archive
2023-10-09 23:30:34.234 P00   INFO: archive-push command end: completed successfully (6ms)

[postgres@postgresql13 data]$ psql
psql (13.8)
Type "help" for help.

postgres=# exit 
[postgres@postgresql13 data]$

小结:在初步使用pgbackrest 备份软件中,有以下一些感受

1  与之前使用的 pgrman 软件比较,配置较困难,官方文档中一些 quick start 的内容并未写清晰。

2  安装后pgbackrest的执行文件,日志,配置文件都是固定的,目前在编译环节并未进行灵活的设置,后续可能会发现 --prefix 

3  备份的展示和留存等,不如pgrman 清晰,pgrman 有一个相对的展示界面和调用展示界面的命令,可能是pgbackrest目前并未深入,为发现类似pgrman的经典的备份文件显示界面。

4   文档方面在pgbackrest 中一些部分的配置还希望有更醒目的位置进行展示,方便修改配置文件。

PostgreSQL  pgBackRest 是最好的PG备份工具 ? (小试牛刀  1)_postgresql_05

PostgreSQL  pgBackRest 是最好的PG备份工具 ? (小试牛刀  1)_postgresql_06

标签:INFO,10,PostgreSQL,23,--,09,PG,2023,小试牛刀
From: https://blog.51cto.com/u_14150796/8095479

相关文章

  • 卡普空计划明年3月前发布大型新作,引发PG-SOFT 电子游戏玩家猜测
    卡普空发布的财报在上周,其中提到了一项备受关注的消息,即他们计划在下半财政年度(截止到2024年3月)推出一款大型新作。这一消息在PG-SOFT电子游戏界引发了猜测,宝石侠游戏试玩家们纷纷猜测这个新作是庆祝《怪物猎人》20年的力作,尤其是在《怪物猎人世界》之后,还是《生化危机8》之后的新......
  • PostgreSQL(kingbaseES) 中,可以使用 unnest 函数将一个包含多个值的字符串分割成多行
    在PostgreSQL中,您可以使用unnest函数将一个包含多个值的字符串分割成多行。unnest函数将一个数组(或者像我们的情况下是由STRING_TO_ARRAY函数生成的数组)展开为多行数据。假设您有一个表my_table,其中包含一个名为my_column的字符串列,其内容如下:my_column-----------......
  • 微软Xbox主机将推出韧体更新,为PG用户供便捷的电子游戏设置功能
    微软即将发布一次重要的韧体更新,特别面向Xbox主机,其中的主要功能包括控.制器键盘配置、Clipchamp集成以及用户偏好的诊断数据共享。首先,这次更新将引入新的键盘配置功能,允许玩家将XboxElite无线控.制器Series2和Xbox自适应控.制器的按键映射至键盘上。在XboxAccessories应用程序......
  • PostgreSQL数据库toast表数据损坏处理
    一、pg_statistic的toast表数据损坏问题现象在安装插件的时候使用\dx元命令的时候,突然发现报了一个错误:postgres=#\dxERROR:missingchunknumber0fortoastvalue32789inpg_toast_2619根据提示来看,主表字段还留存着ToastPointer,但Toast表中已经没有对应的Chunk条目,怀疑t......
  • postgresql数据库经纬度转geometry
    postgresql数据库经纬度转geometry1、在postgresql数据库中,如果字段类型是geometry,更新该字段为经纬度(坐标),可以尝试采取以下脚本:注意:108.658463代表经度34.1437代表纬度中间没有逗号updatetablesetgeom=ST_GeomFromText('POINT(108.65846334.1437)',4490)wh......
  • Git 提交时提示 GPG 签名错误
    本来应该一切都是正常的,但今天提交的时候提示GPG签名错误。错误的信息就是GPG签名失败。 gpg:skipped"942395299055675C":Nosecretkeygpg:signingfailed:Nosecretkeyerror:gpgfailedtosignthedatafatal:failedtowritecommitobject  解决方案开始认......
  • [ME]Backup, upgrade & installation
    Backup,upgrade&installationServiceDeskPlus>Support>FAQ>Backup,upgrade&installationSeeforCloudGeneralModuleHowdoImanuallybackupdatainServiceDeskPlus?HowdoIbackuponlythedatabasewithoutthefileattachmentsinServ......
  • szfpga 详细:高云1N1开发板高云gowin软件使用教程
     1.概述  国产FPGA是最近几年起来的产品,具有性价比高特点。高云FPGA,大多用在LED,电机控制,PLC设备上。 高云1N1开发板采用GW1N-LV1QN48C6/I5FPGA器件。具有低功耗,瞬时启动,高安全性,低成本,方便扩展等特点。价格便宜,易扩张,帮助用户快速进入国产FPGA学习。开发板集成多......
  • PostgreSQL内存管理-内存上下文
    PostgreSQL8.4.1内存管理共享内存中存储着所有进程的公共数据,例如锁变量、进程通信状态、缓冲区等。而本地内存为每个后台进程所专有,是它们的工作区域,存储了该进程的Cache(高速缓存)、事务管理信息、进程信息等。为了防止多个进程并发访问共享内存中数据时产生冲突,PostgreSOL......
  • python.exe -m pip install --upgrade pip什么问题
    python.exe-mpipinstall--upgradepip命令的目的是升级Python包管理工具pip到最新版本。这通常是一个有用的操作,以确保你的pip版本是最新的,以便更好地管理Python包和依赖关系。但是,这个命令可能会遇到一些问题,具体取决于你的系统和安装环境。以下是一些可能的问题和......