PostgreSQL备份工具 pgBackRest使用详解

数据库 2024/11/23 佚名

3 2 1

前言

pgBackRest是一款开源的备份还原工具，目标旨在为备份和还原提供可靠易用的备份。

特性

并行备份和还原

备份操作期间压缩通常是其瓶颈所在。pgBackRest通过并行处理解决了备份期间压缩出现的瓶颈问题。

本地远程操作

自定义协议允许 pgBackRest以最小化配置通过SSH在本地或者远程执行备份、还原和归档。并且该程序也通过协议层提供了PostgreSQL查询接口，以便于必须要再远程访问PostgreSQL，从而保证了其安全性能。

全量，增量和差异备份

支持全量，增量和差异备份。pgBackRest不受异步时间影响，因此差异和增量备份完全安全。

备份保留策略和和归档过期

支持保留策略设置可以在任意时间创建全备和差异备份的覆盖。

备份完整性

可以计算备份中每个文件的校验和，并在还原期间重新检查。备份完成文件复制后，将等待直到每个WAL段所需的备份保持一致然后存储到备份仓库中。

块校验和断点备份流压缩和校验和增量还原并行异步WAL Push和Get表空间重新映射和Link支持S3存储支持和Azure兼容对象存储支持加密

使用

安装解压

[postgres@pgserver12 tools]$ tar -zxf pgbackrest-release-2.31.tar.gz
[postgres@pgserver12 tools]$ ls

创建必要目录

ostgres@sungsasong ~]$ sudo mkdir -p -m 770 /var/log/pgbackrest
[postgres@sungsasong ~]$ sudo chown postgres.postgres /var/log/pgbackrest/
[postgres@sungsasong ~]$ sudo mkdir -p /etc/pgbackrest
[postgres@sungsasong ~]$ sudo mkdir -p /etc/pgbackrest/conf.d
[postgres@sungsasong ~]$ sudo touch /etc/pgbackrest/pgbackrest.conf
[postgres@sungsasong ~]$ sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
[postgres@sungsasong ~]$ sudo chown postgres.postgres -R /etc/pgbackrest/
[postgres@sungsasong ~]$ sudo mkdir /usr/bin/pgbackrest
[postgres@sungsasong ~]$ sudo chown postgres.postgres /usr/bin/pgbackrest/
[postgres@sungsasong ~]$ sudo chmod 755 /usr/bin/pgbackrest/

编译安装

[postgres@sungsasong src]$ ./configure --prefix=/usr/bin/pgbackrest/
[postgres@sungsasong src]$ make -j24
[postgres@sungsasong src]$ make install -j24
install -d /usr/bin/pgbackrest/bin
install -m 755 pgbackrest /usr/bin/pgbackrest/bin

命令测试

[postgres@sungsasong src]$ /usr/bin/pgbackrest/bin/pgbackrest
pgBackRest 2.31 - General help
 
Usage:
 pgbackrest [options] [command]
 
Commands:
 archive-get Get a WAL segment from the archive.
 archive-push Push a WAL segment to the archive.
 backup Backup a database cluster.
 check Check the configuration.
 expire Expire backups that exceed retention.
 help Get help.
 info Retrieve information about backups.
 restore Restore a database cluster.
 stanza-create Create the required stanza data.
 stanza-delete Delete a stanza.
 stanza-upgrade Upgrade a stanza.
 start Allow pgBackRest processes to run.
 stop Stop pgBackRest processes from running.
 version Get version.
 
Use 'pgbackrest help [command]' for more information.

配置数据库监听和访问及日志（可选）

[postgres@sungsasong pgbackrest-release-2.31]$ egrep "10.10" $PGDATA/pg_hba.conf
host all all 10.10.20.0/24 trust
[postgres@sungsasong pgbackrest-release-2.31]$ egrep -v "^#" $PGDATA/postgresql.auto.conf
logging_collector = 'on'
listen_addresses = '*'

配置PostgreSQL数据库数据存储目录

[postgres@sungsasong src]$ cat /etc/pgbackrest/pgbackrest.conf <<EOF
> [demo]
> pgl-path=/data/pg10/pgdata
> EOF

配置环境变量

[postgres@sungsasong ~]$ echo "export PATH=/usr/bin/pgbackrest/bin:\$PATH"  .bashrc

创建备份和归档仓库

[postgres@sungsasong ~]$ sudo mkdir -p /pgbackrest/repos
[postgres@sungsasong ~]$ sudo chmod 750 /pgbackrest/ -R
[postgres@sungsasong ~]$ sudo chown postgres.postgres /pgbackrest/ -R

将仓库路径加载在pgBackRest配置文件中

[postgres@sungsasong ~]$ cat /etc/pgbackrest/pgbackrest.conf
[demo]
pgl-path=/data/pg10/pgdata
 
[global]
repol-path=/pgbackrest/repos

配置数据库归档

[postgres@sungsasong ~]$ egrep -v "^#" $PGDATA/postgresql.auto.conf
logging_collector = 'on'
archive_mode = 'on'
archive_command = 'pgbackrest --stanza=demo archive-push %p'
listen_addresses = '*'
log_filename = 'postgresql.log'
log_line_prefix = ''
max_wal_senders = '3'
wal_level = 'replica'

重新启动数据库

[postgres@sungsasong ~]$ pg_ctl restart -D $PGDATA -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

可选配置归档压缩

[postgres@sungsasong ~]$ cat /etc/pgbackrest/pgbackrest.conf
[demo]
pgl-path=/data/pg10/pgdata
 
[global]
repol-path=/pgbackrest/repos
 
[global:archive_push]
compress-level=3

配置基于保留策略的归档

[postgres@sungsasong ~]$ cat /etc/pgbackrest/pgbackrest.conf
[demo]
pg1-path=/data/pg10/pgdata
pg1-host-config-path=/data/pg10/pgbackrest
pg1-host-port=22
pg1-host-user=postgres
pg1-host=sungsasong
pg1-port=10001
pg1-user=postgres
 
[global]
repo1-path=/pgbackrest/repos
repo1-retention-full=2
 
[global:archive_push]
compress-level=3

创建存储空间并检查配置

[postgres@sungsasong ~]$ pgbackrest --stanza=demo --log-level-console=info stanza-create
2021-01-08 20:42:58.887 P00 INFO: stanza-create command begin 2.31: --exec-id=9283-e9744c3e --log-level-console=info --pg1-host=sungsasong --pg1-host-config-path=/data/pg10/pgbackrest --pg1-host-port=22 --pg1-host-user=postgres --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --stanza=demo
2021-01-08 20:42:59.835 P00 INFO: stanza-create command end: completed successfully (948ms)

检查配置

[postgres@sungsasong ~]$ pgbackrest --stanza=demo --log-level-console=info check
2021-01-08 21:41:29.851 P00 INFO: check command begin 2.31: --exec-id=21648-1862ac0d --log-level-console=info --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --stanza=demo
2021-01-08 21:41:32.826 P00 INFO: WAL segment 000000010000000000000006 successfully archived to '/pgbackrest/repos/archive/demo/10-1/0000000100000000/000000010000000000000006-2f027934f4f35cd3983ca4b1b7b43c32ab089448.gz'
2021-01-08 21:41:32.826 P00 INFO: check command end: completed successfully (2975ms)

执行备份

默认为增量备份，增量备份将会请求一个基础全备，如果没有基础全备，增量备份将会变更到全备。

pgbackrest --stanza=demo --log-level-console=info backup
2021-01-08 21:43:55.014 P00 INFO: backup command begin 2.31: --exec-id=21661-13c54272 --log-level-console=info --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo
WARN: no prior backup exists, incr backup has been changed to full
...
此处省略很多输出
...
2021-01-08 21:44:00.951 P00 INFO: full backup size = 23.3MB
2021-01-08 21:44:00.951 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-01-08 21:44:01.153 P00 INFO: backup stop archive = 000000010000000000000008, lsn = 0/8000130
2021-01-08 21:44:01.154 P00 INFO: check archive for segment(s) 000000010000000000000008:000000010000000000000008
2021-01-08 21:44:01.273 P00 INFO: new backup label = 20210108-214355F
2021-01-08 21:44:01.301 P00 INFO: backup command end: completed successfully (6288ms)
2021-01-08 21:44:01.301 P00 INFO: expire command begin 2.31: --exec-id=21661-13c54272 --log-level-console=info --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo
2021-01-08 21:44:01.304 P00 INFO: expire command end: completed successfully (3ms)

可以定义备份的类型为增量还是差异备份

使用—type参数指定

[postgres@sungsasong ~]$ pgbackrest --stanza=demo --log-level-console=info --type=diff backup
2021-01-08 21:47:00.964 P00 INFO: backup command begin 2.31: --exec-id=21671-d3f8f8bf --log-level-console=info --pg1-path=/data/pg10/pgdata --pg1-port=10001 --pg1-user=postgres --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo --type=diff
2021-01-08 21:47:01.770 P00 INFO: last backup label = 20210108-214355F, version = 2.31
2021-01-08 21:47:01.770 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the next regular checkpoint completes
2021-01-08 21:47:02.309 P00 INFO: backup start archive = 00000001000000000000000A, lsn = 0/A000028
2021-01-08 21:47:03.617 P01 INFO: backup file /data/pg10/pgdata/global/pg_control (8KB, 99%) checksum 5f020e7df484269ea245041be3228673560184ef
2021-01-08 21:47:03.721 P01 INFO: backup file /data/pg10/pgdata/pg_logical/replorigin_checkpoint (8B, 100%) checksum 347fc8f2df71bd4436e38bd1516ccd7ea0d46532
2021-01-08 21:47:03.722 P00 INFO: diff backup size = 8KB
2021-01-08 21:47:03.722 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-01-08 21:47:03.942 P00 INFO: backup stop archive = 00000001000000000000000A, lsn = 0/A0000F8
2021-01-08 21:47:03.943 P00 INFO: check archive for segment(s) 00000001000000000000000A:00000001000000000000000A
2021-01-08 21:47:04.062 P00 INFO: new backup label = 20210108-214355F_20210108-214701D
2021-01-08 21:47:04.092 P00 INFO: backup command end: completed successfully (3129ms)
2021-01-08 21:47:04.092 P00 INFO: expire command begin 2.31: --exec-id=21671-d3f8f8bf --log-level-console=info --repo1-path=/pgbackrest/repos --repo1-retention-full=2 --stanza=demo
2021-01-08 21:47:04.095 P00 INFO: expire command end: completed successfully (3ms)

备份信息查看

[postgres@sungsasong ~]$ pgbackrest info
stanza: demo
 status: ok
 cipher: none
 
 db (current)
 wal archive min/max (10-1): 000000010000000000000001/00000001000000000000000A
 
 full backup: 20210108-214355F
 timestamp start/stop: 2021-01-08 21:43:55 / 2021-01-08 21:44:01
 wal start/stop: 000000010000000000000008 / 000000010000000000000008
 database size: 23.3MB, backup size: 23.3MB
 repository size: 2.7MB, repository backup size: 2.7MB
 
 diff backup: 20210108-214355F_20210108-214701D
 timestamp start/stop: 2021-01-08 21:47:01 / 2021-01-08 21:47:03
 wal start/stop: 00000001000000000000000A / 00000001000000000000000A
 database size: 23.3MB, backup size: 8.2KB
 repository size: 2.7MB, repository backup size: 425B
 backup reference list: 20210108-214355F

还原一个备份

模拟数据库损坏

[postgres@sungsasong ~]$ cd $PGDATA/
[postgres@sungsasong pgdata]$ rm -rf *
#100分警告：千万不要拿生产库执行

执行还原

[postgres@sungsasong pgdata]$ pgbackrest --stanza=demo restore

重新启动数据库

[postgres@sungsasong pgdata]$ pg_ctl start -D $PGDATA -l /tmp/logfile
waiting for server to start.... done
server started
[postgres@sungsasong pgdata]$ psql
psql (10.13)
Type "help" for help.

以上就是pgbackrest介绍及使用。当前使用的最新版本已经可以支持PostgreSQL13版本，仅仅是文档上标注支持最新到PostgreSQL11版本。感兴趣的同学可以下去试一下。

希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

PostgreSQL,备份,pgBackRest

标签：

PostgreSQL,备份,pgBackRest

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

评论“PostgreSQL备份工具 pgBackRest使用详解”

PostgreSQL备份工具 pgBackRest使用详解

暂无“PostgreSQL备份工具 pgBackRest使用详解”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

PostgreSQL备份工具 pgBackRest使用详解

前言

特性

使用

重新启动数据库

PostgreSQL,备份,pgBackRest

PostgreSQL 如何查找需要收集的vacuum 表信息

postgresql数据合并,多条数据合并成1条的操作

评论“PostgreSQL备份工具 pgBackRest使用详解”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

PostgreSQL备份工具 pgBackRest使用详解

前言

特性

使用

重新启动数据库

PostgreSQL,备份,pgBackRest

PostgreSQL 如何查找需要收集的vacuum 表信息

postgresql数据合并,多条数据合并成1条的操作

评论“PostgreSQL备份工具 pgBackRest使用详解”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存