备份还原"/>
利用pgbackrest实现postgresql备份还原
一、pgbackRest简介
pgBackRest旨在提供一个简单可靠,容易纵向扩展的PostgreSQL备份恢复系统。pgBackRest并不依赖像tar和rsync这样的传统备份工具,而是通过在内部实现所有备份功能,并使用自定义协议来与远程系统进行通信。 消除对tar和rsync的依赖可以更好地解决特定于数据库的备份问题。 自定义远程协议提供了更多的灵活性,并限制执行备份所需的连接类型,从而提高安全性。
相关网站
参考:/
pgBackRest主页:
手册:.html#introduction
pgBackRest Github主页:
二、pgbackRest特征
1.并行备份和还原
2.本地或远程操作
3.完整,增量和差异备份
4.备份轮换和存档到期
5.备份完整性
6.页面校验和
7.备份恢复
8.流压缩和校验和
9.增量还原
10.并行,异步WAL Push&Get
11.表空间和链接支持
12.S3、Azure和GCS兼容对象存储支持
13.加密
13.与PostgreSQL > = 8.3的兼容性
三、pgbackRest安装
基本实验环境
DB: PostgreSQL 14.7
pgbackrest:pgBackRest2.48
OS: CentOS 7.9
安装pgbackRest
root用户:
yum -y install libxml2 libxml2-devel lz4 lz4-devel make gcc openssl openssl-devel libyaml libyaml-devel
source /home/postgres/.bash_profile
unzip pgbackrest-release-2.48.zip
cd pgbackrest-release-2.48/src/
./configure
make
make install
安装完成后,可以查看pgbackrest相关命令
[postgres@du101 ~]$ pgbackrest
pgBackRest 2.48 - General helpUsage:pgbackrest [options] [command]Commands:annotate Add or modify backup annotation.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.repo-get Get a file from a repository.repo-ls List files in a repository.restore Restore a database cluster.server pgBackRest server.server-ping Ping pgBackRest server.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.verify Verify contents of the repository.version Get version.Use 'pgbackrest help [command]' for more information.
[postgres@du101 ~]$
四、pgbackRest配置
创建所需目录和配置文件
备份目录:
mkdir /data/backup
chown postgres:postgres /data/backup
chmod 750 /data/backup配置文件目录:
mkdir -p /etc/pgbackrest
mkdir -p /etc/pgbackrest/conf.d
touch /etc/pgbackrest/pgbackrest.conf
chmod 640 /etc/pgbackrest/pgbackrest.conf
chown postgres:postgres /etc/pgbackrest/pgbackrest.confpgbackrest日志目录:
mkdir -p -m 770 /var/log/pgbackrest
chown postgres:postgres /var/log/pgbackrest
配值文件内容
[postgres@du101 ~]$ cat /etc/pgbackrest/pgbackrest.conf
[pgsql]
pg1-path=/data/pgdata
pg1-port=1921
pg1-host-user=postgres
pg1-user=postgres[global]
process-max=2
start-fast=y
repo1-path=/data/backup
repo1-retention-full=2[global:archive-push]
compress-level=2
process-max=3[global:archive-get]
process-max=2
[postgres@du101 ~]$
说明:
start-fast:By default pgBackRest will wait for the next regularly scheduled checkpoint before starting a backup. Depending on the checkpoint_timeout and checkpoint_segments settings in PostgreSQL it may be quite some time before a checkpoint completes and the backup can begin. Generally, it is best to set start-fast=y so that the backup starts immediately. This forces a checkpoint, but since backups are usually run once a day an additional checkpoint should not have a noticeable impact on performance. However, on very busy clusters it may be best to pass --start-fast on the command-line as needed.
process-max:pgBackRest offers parallel processing to improve performance of compression and transfer. The number of processes to be used for this feature is set using the --process-max option.
配置文件其他设置:
以下设置都是在配置文件中的global部分:
集中化备份
-pg-host在备份中有些配置并不是在本地PG数据库上部署的,而是进行集中化备份实现而设置的,所以配置也分管理节点和本地数据库节点。
1 本地数据库节点,不能带有pg-host 这是为远程控制节点而设置的,不是本地的配置
2 本地配置了错误的远程控制的配置后,就会出现上面的错误。
3 本地备份的账号尽力单独设置,在pg_hba.conf 对这个账号访问数据库尽力trust 。
连接数据库选项
–pg-host
设置pgbackrest 命令执行的位置
–pg1-host-cmd
设置配置文件的位置
–pg1-host-config
设置数据库的访问端口
–pg1-port
数据目录位置
–pg1-path
本地数据库socket端口
–pg-socket-path
数据库用户
–pg-user
buffer-size
buffer-size=16MiB
这部分值不能进行随意的设置值在最大不能超过16MB
最小在16kb,默认这部分主要使用在文件的copy 压缩,加密,以及其他的操作中使用的缓冲
Allowed values are 16KiB, 32KiB, 64KiB, 128KiB, 256KiB, 512KiB, 1MiB, 2MiB, 4MiB, 8MiB, and 16MiB
repo1-path
repo1-path=/pgbackrest/backup
这部分是备份文件的存储位置,这里我们统一认为是本地的文件位置
compress-type
compress-type=gz
备份文件的压缩中份不同的等级,其中支持的压缩 bz2 9 个等级 gz 6个等级 LZ4 默认等级 zst 3个等级,这里我们选择常用的gz 并且选择了3这个等级
config-path
config-path=/etc/pgbackrestpgbackrest.conf
配置文件位置
delta
备份和恢复文件采用checksums
**dry-run **
是否打开命令行中运行–dry-run的开关
io-timeout
这里由于pgbackrest里面的备份存储设备可以是S3 或其他设备,则这里需要针对IO读取设置超时的部分范围在 0.1 - 3600 秒
process-max
是否采用更多的线程来对数据库的工作进行操作,这里默认是1 ,不要设置太大,太大会在备份是影响数据库的运行。
repo1-retention-full
这个repo1-retention-full=2是必须要进行设置的,不设置的情况下会软件会给出警告,在设置后相关的repo1-retention-full 中的与之相关的其他的增量和差异备份,也会在过期后被清理出去。
pgbackrest日志设置:
log-path=/pgbackrest
log-level-console=info
log-level-file=debug
检查配置
数据库需要利用pgbackrest配置归档
vi /data/pgdata/postgresql.conf
增加
archive_command = 'pgbackrest --stanza=pgsql archive-push %p'
archive_mode = on
重启数据库后生效
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info check
2023-10-24 08:58:33.782 P00 INFO: check command begin 2.48: --exec-id=2836-f2a672fb --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --repo1-path=/data/backup --stanza=pgsql
2023-10-24 08:58:34.482 P00 INFO: check repo1 configuration (primary)
2023-10-24 08:58:34.897 P00 INFO: check repo1 archive for WAL (primary)
2023-10-24 08:58:35.200 P00 INFO: WAL segment 000000010000000000000001 successfully archived to '/data/backup/archive/pgsql/14-1/0000000100000000/000000010000000000000001-ff4f9f7bcf35eb908754f7e54cbd0d9a072b8a4a.gz' on repo1
2023-10-24 08:58:35.201 P00 INFO: check command end: completed successfully (1419ms)
[postgres@du101 ~]$
创建 Stanza
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info stanza-create
2023-10-24 08:57:34.334 P00 INFO: stanza-create command begin 2.48: --exec-id=2807-77b81216 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --repo1-path=/data/backup --stanza=pgsql
2023-10-24 08:57:35.288 P00 INFO: stanza-create for stanza 'pgsql' on repo1
2023-10-24 08:57:35.345 P00 INFO: stanza-create command end: completed successfully (1011ms)
[postgres@du101 ~]$ tree /data/backup/
/data/backup/
|-- archive
| `-- pgsql
| |-- archive.info
| `-- archive.info.copy
`-- backup`-- pgsql|-- backup.info`-- backup.info.copy4 directories, 4 files
[postgres@du101 ~]$
五、pgbackRest备份
备份是数据库集群的一致副本,可以对其进行还原,以便从硬件故障中恢复、执行时间点恢复或启动新的备用服务器。
全量备份: pgBackRest将整个数据库集群的内容拷贝到备份中。数据库集群的第一次备份始终是完全备份。pgBackRest始终能够直接恢复全量备份。为了保持一致性,完全备份不依赖于完全备份之外的任何文件。
差异备份: pgBackRest只复制自上次全量备份以来发生变化的数据库集群文件。pgBackRest通过复制所选差异备份中的所有文件和先前完整备份中适当的未更改文件来恢复差异备份。差异备份的优点是它比完全备份需要更少的磁盘空间,但是,要恢复差异备份,差异备份和完全备份必须都是有效的。
增量备份: pgBackRest只复制自上次备份(可以是另一次增量备份、差异备份或完全备份)以来发生变化的数据库集群文件。由于增量备份只包括自上次备份以来更改的文件,因此它们通常比完整备份或差异备份小得多。与差异备份一样,增量备份依赖于其他备份才能有效地恢复增量备份。由于增量备份只包括上次备份以来的那些文件,因此,要执行增量备份的恢复,必须对返回到先前差异备份、先前差异备份和先前完全备份的所有先前增量备份都有效。如果不存在差异备份,则所有先前的增量备份都将返回到先前的完整备份(必须存在),并且完整备份本身必须有效才能恢复增量备份。
全量备份
[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.postgres=# create table t(id int);
CREATE TABLE
postgres=# insert into t select generate_series(1,100000);
INSERT 0 100000
postgres=# checkpoint;
CHECKPOINT
postgres=# \q
[postgres@du101 ~]$ [postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=full backup
2023-10-24 08:59:15.046 P00 INFO: backup command begin 2.48: --exec-id=2841-44c88dc2 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=full
2023-10-24 08:59:15.969 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 08:59:16.471 P00 INFO: backup start archive = 000000010000000000000003, lsn = 0/3000028
2023-10-24 08:59:16.471 P00 INFO: check archive for prior segment 000000010000000000000002
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 08:59:22.315 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 08:59:22.722 P00 INFO: backup stop archive = 000000010000000000000003, lsn = 0/3000138
2023-10-24 08:59:22.731 P00 INFO: check archive for segment(s) 000000010000000000000003:000000010000000000000003
2023-10-24 08:59:22.797 P00 INFO: new backup label = 20231024-085915F
2023-10-24 08:59:22.873 P00 INFO: full backup size = 28.7MB, file total = 957
2023-10-24 08:59:22.873 P00 INFO: backup command end: completed successfully (7828ms)
2023-10-24 08:59:22.873 P00 INFO: expire command begin 2.48: --exec-id=2841-44c88dc2 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 08:59:22.883 P00 INFO: expire command end: completed successfully (10ms)
[postgres@du101 ~]$[postgres@du101 ~]$ ll /data/backup/backup/pgsql/
total 8.0K
drwxr-x--- 3 postgres postgres 72 Oct 24 08:59 20231024-085915F
drwxr-x--- 3 postgres postgres 18 Oct 24 08:59 backup.history
-rw-r----- 1 postgres postgres 1.1K Oct 24 08:59 backup.info
-rw-r----- 1 postgres postgres 1.1K Oct 24 08:59 backup.info.copy
lrwxrwxrwx 1 postgres postgres 16 Oct 24 08:59 latest -> 20231024-085915F
[postgres@du101 ~]$
差异备份
[postgres@du101 ~]$ psql -c "insert into t select generate_series(1,100);checkpoint;"
CHECKPOINT
[postgres@du101 ~]$ psql -c "select count(*) from t;" count
--------100100
(1 row)[postgres@du101 ~]$
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=diff backup
2023-10-24 09:01:21.166 P00 INFO: backup command begin 2.48: --exec-id=2876-e5c4d427 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=diff
2023-10-24 09:01:21.965 P00 INFO: last backup label = 20231024-085915F, version = 2.48
2023-10-24 09:01:21.966 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 09:01:23.144 P00 INFO: backup start archive = 000000010000000000000005, lsn = 0/5000028
2023-10-24 09:01:23.144 P00 INFO: check archive for prior segment 000000010000000000000004
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 09:01:24.818 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 09:01:25.020 P00 INFO: backup stop archive = 000000010000000000000005, lsn = 0/5000138
2023-10-24 09:01:25.026 P00 INFO: check archive for segment(s) 000000010000000000000005:000000010000000000000005
2023-10-24 09:01:25.061 P00 INFO: new backup label = 20231024-085915F_20231024-090121D
2023-10-24 09:01:25.136 P00 INFO: diff backup size = 3.8MB, file total = 958
2023-10-24 09:01:25.136 P00 INFO: backup command end: completed successfully (3971ms)
2023-10-24 09:01:25.136 P00 INFO: expire command begin 2.48: --exec-id=2876-e5c4d427 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 09:01:25.145 P00 INFO: expire command end: completed successfully (9ms)
[postgres@du101 ~]$ ll /data/backup/backup/pgsql/
total 8.0K
drwxr-x--- 3 postgres postgres 72 Oct 24 08:59 20231024-085915F
drwxr-x--- 3 postgres postgres 72 Oct 24 09:01 20231024-085915F_20231024-090121D
drwxr-x--- 3 postgres postgres 18 Oct 24 08:59 backup.history
-rw-r----- 1 postgres postgres 1.8K Oct 24 09:01 backup.info
-rw-r----- 1 postgres postgres 1.8K Oct 24 09:01 backup.info.copy
lrwxrwxrwx 1 postgres postgres 33 Oct 24 09:01 latest -> 20231024-085915F_20231024-090121D
[postgres@du101 ~]$
增量备份
postgres@du101 ~]$ psql -c "insert into t select generate_series(1,100);checkpoint;"
CHECKPOINT
[postgres@du101 ~]$ psql -c "select count(*) from t;" count
--------100200
(1 row)[postgres@du101 ~]$ --第一次增量备份
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=incr backup
2023-10-24 09:04:49.582 P00 INFO: backup command begin 2.48: --exec-id=2896-3eb7685d --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=incr
2023-10-24 09:04:50.404 P00 INFO: last backup label = 20231024-085915F_20231024-090121D, version = 2.48
2023-10-24 09:04:50.404 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 09:04:51.508 P00 INFO: backup start archive = 000000010000000000000007, lsn = 0/7000028
2023-10-24 09:04:51.508 P00 INFO: check archive for prior segment 000000010000000000000006
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 09:04:53.159 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 09:04:53.374 P00 INFO: backup stop archive = 000000010000000000000007, lsn = 0/7000100
2023-10-24 09:04:53.380 P00 INFO: check archive for segment(s) 000000010000000000000007:000000010000000000000007
2023-10-24 09:04:53.406 P00 INFO: new backup label = 20231024-085915F_20231024-090450I
2023-10-24 09:04:53.466 P00 INFO: incr backup size = 3.5MB, file total = 958
2023-10-24 09:04:53.466 P00 INFO: backup command end: completed successfully (3885ms)
2023-10-24 09:04:53.466 P00 INFO: expire command begin 2.48: --exec-id=2896-3eb7685d --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 09:04:53.474 P00 INFO: expire command end: completed successfully (8ms)
[postgres@du101 ~]$ ll /data/backup/backup/pgsql/
total 8.0K
drwxr-x--- 3 postgres postgres 72 Oct 24 08:59 20231024-085915F
drwxr-x--- 3 postgres postgres 72 Oct 24 09:01 20231024-085915F_20231024-090121D
drwxr-x--- 3 postgres postgres 72 Oct 24 09:04 20231024-085915F_20231024-090450I
drwxr-x--- 3 postgres postgres 18 Oct 24 08:59 backup.history
-rw-r----- 1 postgres postgres 2.6K Oct 24 09:04 backup.info
-rw-r----- 1 postgres postgres 2.6K Oct 24 09:04 backup.info.copy
lrwxrwxrwx 1 postgres postgres 33 Oct 24 09:04 latest -> 20231024-085915F_20231024-090450I
[postgres@du101 ~]$ [postgres@du101 ~]$ psql -c "insert into t select generate_series(1,100);checkpoint;"
CHECKPOINT
[postgres@du101 ~]$ psql -c "select count(*) from t;" count
--------100300
(1 ro
[postgres@du101 ~]$ --第二次增量备份
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=incr backup
2023-10-24 09:05:38.510 P00 INFO: backup command begin 2.48: --exec-id=2909-47344759 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=incr
2023-10-24 09:05:39.293 P00 INFO: last backup label = 20231024-085915F_20231024-090450I, version = 2.48
2023-10-24 09:05:39.293 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 09:05:40.019 P00 INFO: backup start archive = 000000010000000000000009, lsn = 0/9000028
2023-10-24 09:05:40.020 P00 INFO: check archive for prior segment 000000010000000000000008
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 09:05:41.963 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 09:05:42.490 P00 INFO: backup stop archive = 000000010000000000000009, lsn = 0/9000100
2023-10-24 09:05:42.500 P00 INFO: check archive for segment(s) 000000010000000000000009:000000010000000000000009
2023-10-24 09:05:42.534 P00 INFO: new backup label = 20231024-085915F_20231024-090539I
2023-10-24 09:05:42.593 P00 INFO: incr backup size = 3.5MB, file total = 958
2023-10-24 09:05:42.593 P00 INFO: backup command end: completed successfully (4083ms)
2023-10-24 09:05:42.593 P00 INFO: expire command begin 2.48: --exec-id=2909-47344759 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 09:05:42.601 P00 INFO: expire command end: completed successfully (8ms)
[postgres@du101 ~]$ ll /data/backup/backup/pgsql/
total 8.0K
drwxr-x--- 3 postgres postgres 72 Oct 24 08:59 20231024-085915F
drwxr-x--- 3 postgres postgres 72 Oct 24 09:01 20231024-085915F_20231024-090121D
drwxr-x--- 3 postgres postgres 72 Oct 24 09:04 20231024-085915F_20231024-090450I
drwxr-x--- 3 postgres postgres 72 Oct 24 09:05 20231024-085915F_20231024-090539I
drwxr-x--- 3 postgres postgres 18 Oct 24 08:59 backup.history
-rw-r----- 1 postgres postgres 3.4K Oct 24 09:05 backup.info
-rw-r----- 1 postgres postgres 3.4K Oct 24 09:05 backup.info.copy
lrwxrwxrwx 1 postgres postgres 33 Oct 24 09:05 latest -> 20231024-085915F_20231024-090539I
[postgres@du101 ~]$
注意 last backup label = 20231024-085915F_20231024-090450I表明第二次增量备份是基于第一个增量的。
差异备份对比
[postgres@du101 ~]$ psql -c "insert into t select generate_series(1,100);checkpoint;"
CHECKPOINT
[postgres@du101 ~]$ psql -c "select count(*) from t;" count
--------100400
(1 row)[postgres@du101 ~]$ [postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=diff backup
2023-10-24 09:06:51.910 P00 INFO: backup command begin 2.48: --exec-id=2923-bfbee114 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=diff
2023-10-24 09:06:52.645 P00 INFO: last backup label = 20231024-085915F, version = 2.48
2023-10-24 09:06:52.645 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 09:06:53.718 P00 INFO: backup start archive = 00000001000000000000000B, lsn = 0/B000060
2023-10-24 09:06:53.718 P00 INFO: check archive for prior segment 00000001000000000000000A
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 09:06:55.158 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 09:06:55.361 P00 INFO: backup stop archive = 00000001000000000000000B, lsn = 0/B000138
2023-10-24 09:06:55.370 P00 INFO: check archive for segment(s) 00000001000000000000000B:00000001000000000000000B
2023-10-24 09:06:55.391 P00 INFO: new backup label = 20231024-085915F_20231024-090652D
2023-10-24 09:06:55.450 P00 INFO: diff backup size = 3.8MB, file total = 958
2023-10-24 09:06:55.450 P00 INFO: backup command end: completed successfully (3540ms)
2023-10-24 09:06:55.450 P00 INFO: expire command begin 2.48: --exec-id=2923-bfbee114 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 09:06:55.462 P00 INFO: expire command end: completed successfully (12ms)
[postgres@du101 ~]$ ll /data/backup/backup/pgsql/
total 16K
drwxr-x--- 3 postgres postgres 72 Oct 24 08:59 20231024-085915F
drwxr-x--- 3 postgres postgres 72 Oct 24 09:01 20231024-085915F_20231024-090121D
drwxr-x--- 3 postgres postgres 72 Oct 24 09:04 20231024-085915F_20231024-090450I
drwxr-x--- 3 postgres postgres 72 Oct 24 09:05 20231024-085915F_20231024-090539I
drwxr-x--- 3 postgres postgres 72 Oct 24 09:06 20231024-085915F_20231024-090652D
drwxr-x--- 3 postgres postgres 18 Oct 24 08:59 backup.history
-rw-r----- 1 postgres postgres 4.2K Oct 24 09:06 backup.info
-rw-r----- 1 postgres postgres 4.2K Oct 24 09:06 backup.info.copy
lrwxrwxrwx 1 postgres postgres 33 Oct 24 09:06 latest -> 20231024-085915F_20231024-090652D
[postgres@du101 ~]$
注意last backup label = 20231024-085915F表明此次的差异备份是基于全量备份的;
六、查看备份信息
[postgres@du101 ~]$ cat /data/backup/backup/pgsql/backup.info
[backrest]
backrest-format=5
backrest-version="2.48"[backup:current]
20231024-085915F={"backrest-format":5,"backrest-version":"2.48","backup-archive-start":"000000010000000000000003","backup-archive-stop":"000000010000000000000003","backup-error":false,"backup-info-repo-size":3695955,"backup-info-repo-size-delta":3695955,"backup-info-size":30043421,"backup-info-size-delta":30043421,"backup-lsn-start":"0/3000028","backup-lsn-stop":"0/3000138","backup-timestamp-start":1698109155,"backup-timestamp-stop":1698109162,"backup-type":"full","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}
20231024-085915F_20231024-090121D={"backrest-format":5,"backrest-version":"2.48","backup-archive-start":"000000010000000000000005","backup-archive-stop":"000000010000000000000005","backup-error":false,"backup-info-repo-size":3697409,"backup-info-repo-size-delta":480504,"backup-info-size":30058636,"backup-info-size-delta":3978051,"backup-lsn-start":"0/5000028","backup-lsn-stop":"0/5000138","backup-prior":"20231024-085915F","backup-reference":["20231024-085915F"],"backup-timestamp-start":1698109281,"backup-timestamp-stop":1698109284,"backup-type":"diff","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}
20231024-085915F_20231024-090450I={"backrest-format":5,"backrest-version":"2.48","backup-archive-start":"000000010000000000000007","backup-archive-stop":"000000010000000000000007","backup-error":false,"backup-info-repo-size":3698077,"backup-info-repo-size-delta":416484,"backup-info-size":30070343,"backup-info-size-delta":3694846,"backup-lsn-start":"0/7000028","backup-lsn-stop":"0/7000100","backup-prior":"20231024-085915F_20231024-090121D","backup-reference":["20231024-085915F","20231024-085915F_20231024-090121D"],"backup-timestamp-start":1698109490,"backup-timestamp-stop":1698109493,"backup-type":"incr","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}
20231024-085915F_20231024-090539I={"backrest-format":5,"backrest-version":"2.48","backup-archive-start":"000000010000000000000009","backup-archive-stop":"000000010000000000000009","backup-error":false,"backup-info-repo-size":3698765,"backup-info-repo-size-delta":417087,"backup-info-size":30073862,"backup-info-size-delta":3673789,"backup-lsn-start":"0/9000028","backup-lsn-stop":"0/9000100","backup-prior":"20231024-085915F_20231024-090450I","backup-reference":["20231024-085915F","20231024-085915F_20231024-090121D","20231024-085915F_20231024-090450I"],"backup-timestamp-start":1698109539,"backup-timestamp-stop":1698109542,"backup-type":"incr","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}
20231024-085915F_20231024-090652D={"backrest-format":5,"backrest-version":"2.48","backup-archive-start":"00000001000000000000000B","backup-archive-stop":"00000001000000000000000B","backup-error":false,"backup-info-repo-size":3699509,"backup-info-repo-size-delta":482604,"backup-info-size":30085573,"backup-info-size-delta":4004988,"backup-lsn-start":"0/B000060","backup-lsn-stop":"0/B000138","backup-prior":"20231024-085915F","backup-reference":["20231024-085915F"],"backup-timestamp-start":1698109612,"backup-timestamp-stop":1698109615,"backup-type":"diff","db-id":1,"option-archive-check":true,"option-archive-copy":false,"option-backup-standby":false,"option-checksum-page":false,"option-compress":true,"option-hardlink":false,"option-online":true}[db]
db-catalog-version=202107181
db-control-version=1300
db-id=1
db-system-id=7209488936723521799
db-version="14"[db:history]
1={"db-catalog-version":202107181,"db-control-version":1300,"db-system-id":7209488936723521799,"db-version":"14"}[backrest]
backrest-checksum="8961821af01cc0e1dd5ff47b8a3fb1a1b3fab371"
[postgres@du101 ~]$---查看所有的备份信息
[postgres@du101 ~]$ pgbackrest info
stanza: pgsqlstatus: okcipher: nonedb (current)wal archive min/max (14): 000000010000000000000001/00000001000000000000000Bfull backup: 20231024-085915Ftimestamp start/stop: 2023-10-24 08:59:15+08 / 2023-10-24 08:59:22+08wal start/stop: 000000010000000000000003 / 000000010000000000000003database size: 28.7MB, database backup size: 28.7MBrepo1: backup set size: 3.5MB, backup size: 3.5MBdiff backup: 20231024-085915F_20231024-090121Dtimestamp start/stop: 2023-10-24 09:01:21+08 / 2023-10-24 09:01:24+08wal start/stop: 000000010000000000000005 / 000000010000000000000005database size: 28.7MB, database backup size: 3.8MBrepo1: backup set size: 3.5MB, backup size: 469.2KBbackup reference list: 20231024-085915Fincr backup: 20231024-085915F_20231024-090450Itimestamp start/stop: 2023-10-24 09:04:50+08 / 2023-10-24 09:04:53+08wal start/stop: 000000010000000000000007 / 000000010000000000000007database size: 28.7MB, database backup size: 3.5MBrepo1: backup set size: 3.5MB, backup size: 406.7KBbackup reference list: 20231024-085915F, 20231024-085915F_20231024-090121Dincr backup: 20231024-085915F_20231024-090539Itimestamp start/stop: 2023-10-24 09:05:39+08 / 2023-10-24 09:05:42+08wal start/stop: 000000010000000000000009 / 000000010000000000000009database size: 28.7MB, database backup size: 3.5MBrepo1: backup set size: 3.5MB, backup size: 407.3KBbackup reference list: 20231024-085915F, 20231024-085915F_20231024-090121D, 20231024-085915F_20231024-090450Idiff backup: 20231024-085915F_20231024-090652Dtimestamp start/stop: 2023-10-24 09:06:52+08 / 2023-10-24 09:06:55+08wal start/stop: 00000001000000000000000B / 00000001000000000000000Bdatabase size: 28.7MB, database backup size: 3.8MBrepo1: backup set size: 3.5MB, backup size: 471.3KBbackup reference list: 20231024-085915F
[postgres@du101 ~]$ --查看某一个备份集的详细信息
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --set=20231024-085915F_20231024-090652D info
stanza: pgsqlstatus: okcipher: nonedb (current)wal archive min/max (14): 000000010000000000000001/00000001000000000000000Bdiff backup: 20231024-085915F_20231024-090652Dtimestamp start/stop: 2023-10-24 09:06:52+08 / 2023-10-24 09:06:55+08wal start/stop: 00000001000000000000000B / 00000001000000000000000Blsn start/stop: 0/B000060 / 0/B000138database size: 28.7MB, database backup size: 3.8MBrepo1: backup set size: 3.5MB, backup size: 471.3KBbackup reference list: 20231024-085915Fdatabase list: postgres (13892)
[postgres@du101 ~]$
七、pgbackRest还原
全量还原
在还原的时候不指定备份集,默认寻找最新的备份集进行还原。
[postgres@du101 ~]$ psql -c "select count(*) from t"count
--------100400
(1 row)[postgres@du101 ~]$ [postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --log-level-console=info
2023-10-24 09:17:29.741 P00 INFO: restore command begin 2.48: --exec-id=3022-1520edf9 --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --repo1-path=/data/backup --stanza=pgsql
2023-10-24 09:17:29.759 P00 INFO: repo1: restore backup set 20231024-085915F_20231024-090652D, recovery will start at 2023-10-24 09:06:52
2023-10-24 09:17:32.706 P00 INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 09:17:32.715 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 09:17:32.717 P00 INFO: restore size = 28.7MB, file total = 958
2023-10-24 09:17:32.718 P00 INFO: restore command end: completed successfully (2978ms)
[postgres@du101 ~]$ ll /data/pgdata/
total 64K
-rw------- 1 postgres postgres 258 Oct 24 09:06 backup_label
drwx------ 5 postgres postgres 41 Oct 24 09:17 base
-rw------- 1 postgres postgres 40 Oct 24 08:57 current_logfiles
drwx------ 2 postgres postgres 4.0K Oct 24 09:17 global
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_commit_ts
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_dynshmem
-rw------- 1 postgres postgres 4.8K Mar 12 2023 pg_hba.conf
-rw------- 1 postgres postgres 1.6K Mar 12 2023 pg_ident.conf
drwx------ 2 postgres postgres 138 Oct 24 09:17 pg_log
drwx------ 4 postgres postgres 68 Oct 24 09:17 pg_logical
drwx------ 4 postgres postgres 36 Oct 24 09:17 pg_multixact
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_notify
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_replslot
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_serial
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_snapshots
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_stat
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_stat_tmp
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_subtrans
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_tblspc
drwx------ 2 postgres postgres 6 Oct 24 09:17 pg_twophase
-rw------- 1 postgres postgres 3 Mar 12 2023 PG_VERSION
drwx------ 3 postgres postgres 28 Oct 24 09:17 pg_wal
drwx------ 2 postgres postgres 18 Oct 24 09:17 pg_xact
-rw------- 1 postgres postgres 230 Oct 24 09:17 postgresql.auto.conf
-rw------- 1 postgres postgres 29K Oct 24 08:57 postgresql.conf
-rw------- 1 postgres postgres 0 Oct 24 09:17 recovery.signal
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.# Recovery settings generated by pgBackRest restore on 2023-10-24 09:17:32
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 09:17:55.110 CST [3029] LOG: 00000: redirecting log output to logging collector process
2023-10-24 09:17:55.110 CST [3029] HINT: Future log output will appear in directory "pg_log".
2023-10-24 09:17:55.110 CST [3029] LOCATION: SysLogger_Start, syslogger.c:674done
server started
[postgres@du101 ~]$ psql -c "select count(*) from t" count
--------100400
(1 row)[postgres@du101 ~]$
还原之后会生成postgresql.auto.conf和recovery.signal。postgresql.auto.conf中只有restore_command 会恢复所有的归档日志。
PITR还原
PITR还原可以基于时间点,lsn和xid,命令如下:
pgbackrest restore --stanza=pgsql --type=xid "--target=837" --log-level-console=info
pgbackrest restore --stanza=pgsql --type=lsn --target="0/7000028" --log-level-console=info
pgbackrest restore --stanza=pgsql --type=time --target="2023-10-24 09:01:21" --log-level-console=info
本次演示基于时间点和lsn的PITR恢复。
基于lsn的恢复
[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --set=20231024-085915F_20231024-090450I info
stanza: pgsqlstatus: okcipher: nonedb (current)wal archive min/max (14): 000000010000000000000001/00000002000000000000000Dincr backup: 20231024-085915F_20231024-090450Itimestamp start/stop: 2023-10-24 09:04:50+08 / 2023-10-24 09:04:53+08wal start/stop: 000000010000000000000007 / 000000010000000000000007lsn start/stop: 0/7000028 / 0/7000100database size: 28.7MB, database backup size: 3.5MBrepo1: backup set size: 3.5MB, backup size: 406.7KBbackup reference list: 20231024-085915F, 20231024-085915F_20231024-090121Ddatabase list: postgres (13892)
[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --type=lsn --target="0/7000028" --log-level-console=info
2023-10-24 09:21:16.443 P00 INFO: restore command begin 2.48: --exec-id=3055-f059583f --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --repo1-path=/data/backup --stanza=pgsql --target=0/7000028 --type=lsn
2023-10-24 09:21:16.464 P00 INFO: repo1: restore backup set 20231024-085915F_20231024-090121D, recovery will start at 2023-10-24 09:01:21
2023-10-24 09:21:20.073 P00 INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 09:21:20.087 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 09:21:20.089 P00 INFO: restore size = 28.7MB, file total = 958
2023-10-24 09:21:20.090 P00 INFO: restore command end: completed successfully (3648ms)
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.# Recovery settings generated by pgBackRest restore on 2023-10-24 09:21:20
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
recovery_target_lsn = '0/7000028'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 09:21:39.020 CST [3061] LOG: 00000: redirecting log output to logging collector process
2023-10-24 09:21:39.020 CST [3061] HINT: Future log output will appear in directory "pg_log".
2023-10-24 09:21:39.020 CST [3061] LOCATION: SysLogger_Start, syslogger.c:674done
server started
[postgres@du101 ~]$ psql -c "select count(*) from t"count
--------100200
(1 row)[postgres@du101 ~]$
基于时间点的恢复
[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --set=20231024-085915F_20231024-090121D info
stanza: pgsqlstatus: okcipher: nonedb (current)wal archive min/max (14): 000000010000000000000001/00000002000000000000000Ddiff backup: 20231024-085915F_20231024-090121Dtimestamp start/stop: 2023-10-24 09:01:21+08 / 2023-10-24 09:01:24+08wal start/stop: 000000010000000000000005 / 000000010000000000000005lsn start/stop: 0/5000028 / 0/5000138database size: 28.7MB, database backup size: 3.8MBrepo1: backup set size: 3.5MB, backup size: 469.2KBbackup reference list: 20231024-085915Fdatabase list: postgres (13892)
[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --type=time --target="2023-10-24 09:01:21" --log-level-console=info
2023-10-24 09:23:15.035 P00 INFO: restore command begin 2.48: --exec-id=3080-369cb7f1 --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --repo1-path=/data/backup --stanza=pgsql --target="2023-10-24 09:01:21" --type=time
2023-10-24 09:23:15.056 P00 INFO: repo1: restore backup set 20231024-085915F, recovery will start at 2023-10-24 08:59:15
2023-10-24 09:23:18.161 P00 INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 09:23:18.169 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 09:23:18.171 P00 INFO: restore size = 28.7MB, file total = 957
2023-10-24 09:23:18.172 P00 INFO: restore command end: completed successfully (3138ms)
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.# Recovery settings generated by pgBackRest restore on 2023-10-24 09:23:18
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
recovery_target_time = '2023-10-24 09:01:21'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 09:23:37.431 CST [3086] LOG: 00000: redirecting log output to logging collector process
2023-10-24 09:23:37.431 CST [3086] HINT: Future log output will appear in directory "pg_log".
2023-10-24 09:23:37.431 CST [3086] LOCATION: SysLogger_Start, syslogger.c:674done
server started
[postgres@du101 ~]$ psql -c "select count(*) from t"count
--------100100
(1 row)[postgres@du101 ~]$
指定备份集的恢复
[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*
[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --set=20231024-085915F_20231024-090539I --log-level-console=info
2023-10-24 09:37:48.612 P00 INFO: restore command begin 2.48: --exec-id=3319-975accb2 --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --repo1-path=/data/backup --set=20231024-085915F_20231024-090539I --stanza=pgsql
2023-10-24 09:37:48.631 P00 INFO: repo1: restore backup set 20231024-085915F_20231024-090539I, recovery will start at 2023-10-24 09:05:39
2023-10-24 09:37:51.347 P00 INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 09:37:51.355 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 09:37:51.370 P00 INFO: restore size = 28.7MB, file total = 958
2023-10-24 09:37:51.371 P00 INFO: restore command end: completed successfully (2760ms)
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.# Recovery settings generated by pgBackRest restore on 2023-10-24 09:37:51
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 09:38:01.577 CST [3325] LOG: 00000: redirecting log output to logging collector process
2023-10-24 09:38:01.577 CST [3325] HINT: Future log output will appear in directory "pg_log".
2023-10-24 09:38:01.577 CST [3325] LOCATION: SysLogger_Start, syslogger.c:674done
server started
[postgres@du101 ~]$ psql -c "select count(*) from t"count
--------100400
(1 row)[postgres@du101 ~]$
虽然指定了备份集,但是在postgresql.auto.conf只有restore_command,会恢复所有的归档,所以最后的结果为所有的数据。
在恢复的时候,手工编辑postgresql.auto.conf:
[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*
[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --set=20231024-085915F_20231024-090539I --log-level-console=info
2023-10-24 09:30:59.132 P00 INFO: restore command begin 2.48: --exec-id=3286-f12503ef --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --repo1-path=/data/backup --set=20231024-085915F_20231024-090539I --stanza=pgsql
2023-10-24 09:30:59.149 P00 INFO: repo1: restore backup set 20231024-085915F_20231024-090539I, recovery will start at 2023-10-24 09:05:39
2023-10-24 09:31:02.158 P00 INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 09:31:02.166 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 09:31:02.168 P00 INFO: restore size = 28.7MB, file total = 958
2023-10-24 09:31:02.169 P00 INFO: restore command end: completed successfully (3038ms)[postgres@du101 ~]$ ll /data/pgdata/
total 64K
-rw------- 1 postgres postgres 258 Oct 24 09:05 backup_label
drwx------ 5 postgres postgres 41 Oct 24 09:30 base
-rw------- 1 postgres postgres 40 Oct 24 08:57 current_logfiles
drwx------ 2 postgres postgres 4.0K Oct 24 09:31 global
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_commit_ts
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_dynshmem
-rw------- 1 postgres postgres 4.8K Mar 12 2023 pg_hba.conf
-rw------- 1 postgres postgres 1.6K Mar 12 2023 pg_ident.conf
drwx------ 2 postgres postgres 138 Oct 24 09:31 pg_log
drwx------ 4 postgres postgres 68 Oct 24 09:31 pg_logical
drwx------ 4 postgres postgres 36 Oct 24 09:30 pg_multixact
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_notify
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_replslot
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_serial
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_snapshots
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_stat
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_stat_tmp
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_subtrans
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_tblspc
drwx------ 2 postgres postgres 6 Oct 24 09:30 pg_twophase
-rw------- 1 postgres postgres 3 Mar 12 2023 PG_VERSION
drwx------ 3 postgres postgres 28 Oct 24 09:30 pg_wal
drwx------ 2 postgres postgres 18 Oct 24 09:31 pg_xact
-rw------- 1 postgres postgres 230 Oct 24 09:31 postgresql.auto.conf
-rw------- 1 postgres postgres 29K Oct 24 08:57 postgresql.conf
-rw------- 1 postgres postgres 0 Oct 24 09:31 recovery.signal
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.# Recovery settings generated by pgBackRest restore on 2023-10-24 09:31:02
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
[postgres@du101 ~]$ vi /data/pgdata/postgresql.auto.conf
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.# Recovery settings generated by pgBackRest restore on 2023-10-24 09:31:02
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
recovery_target = 'immediate'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 09:33:39.647 CST [3300] LOG: 00000: redirecting log output to logging collector process
2023-10-24 09:33:39.647 CST [3300] HINT: Future log output will appear in directory "pg_log".
2023-10-24 09:33:39.647 CST [3300] LOCATION: SysLogger_Start, syslogger.c:674done
server started
[postgres@du101 ~]$ psql -c "select count(*) from t"count
--------100300
(1 row)[postgres@du101 ~]$
手工编辑postgresql.auto.conf,添加recovery_target = 'immediate’表示只要恢复到数据一致的状态后停止。则可以恢复到指定备份集的数据。
recovery-option在恢复的时候指定要写入postgresql.auto.conf的选项
[postgres@du101 ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[postgres@du101 ~]$ rm -fr /data/pgdata/*
[postgres@du101 ~]$ pgbackrest restore --stanza=pgsql --set=20231024-085915F_20231024-090539I --log-level-console=info --recovery-option="recovery_target=immediate"
2023-10-24 10:04:36.556 P00 INFO: restore command begin 2.48: --exec-id=3472-15075067 --log-level-console=info --pg1-path=/data/pgdata --process-max=2 --recovery-option=recovery_target=immediate --repo1-path=/data/backup --set=20231024-085915F_20231024-090539I --stanza=pgsql
2023-10-24 10:04:36.574 P00 INFO: repo1: restore backup set 20231024-085915F_20231024-090539I, recovery will start at 2023-10-24 09:05:39
2023-10-24 10:04:39.906 P00 INFO: write updated /data/pgdata/postgresql.auto.conf
2023-10-24 10:04:39.914 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2023-10-24 10:04:39.916 P00 INFO: restore size = 28.7MB, file total = 958
2023-10-24 10:04:39.917 P00 INFO: restore command end: completed successfully (3362ms)
[postgres@du101 ~]$ cat /data/pgdata/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.# Recovery settings generated by pgBackRest restore on 2023-10-24 10:04:39
recovery_target = 'immediate'
restore_command = 'pgbackrest --stanza=pgsql archive-get %f "%p"'
[postgres@du101 ~]$ pg_ctl start
waiting for server to start....2023-10-24 10:04:52.037 CST [3478] LOG: 00000: redirecting log output to logging collector process
2023-10-24 10:04:52.037 CST [3478] HINT: Future log output will appear in directory "pg_log".
2023-10-24 10:04:52.037 CST [3478] LOCATION: SysLogger_Start, syslogger.c:674done
server started[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.postgres=# select count(*) from t;count
--------100300
(1 row)postgres=#
通过–recovery-option="recovery_target=immediate"指定recovery_target=immediate到postgresql.auto.conf 也可以达到恢复到指定备份集的效果。
其他恢复选项
可以通过pgbackrest help restore查看所有的恢复选项:
--db-exclude restore excluding the specified databases--db-include restore only specified databases--delta restore or backup using checksums
终止recovery状态
数据库在基于PITR的恢复(或者会到指定备份集数据)之后,处于read-only transaction中,不能写,需要手工执行命令select pg_wal_replay_resume();终止recovery状态。
[postgres@du101 ~]$ psql
psql (14.7)
Type "help" for help.postgres=# select count(*) from t;count
--------100300
(1 row)postgres=# checkpoint;
CHECKPOINT
postgres=# create database testdb;
ERROR: cannot execute CREATE DATABASE in a read-only transaction
postgres=# select pg_wal_replay_resume();pg_wal_replay_resume
----------------------(1 row)postgres=# create database testdb;
CREATE DATABASE
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# create table t(id int);
CREATE TABLE
testdb=# insert into t select generate_series(1,100);
INSERT 0 100
testdb=# checkpoint;
CHECKPOINT
testdb=# \q
[postgres@du101 ~]$
八、保留策略
全量冗余策略
** repo1-retention-full=2**
差异备份冗余策略
** repo1-retention-diff=1**
归档策略
** repo1-retention-diff=2**
** repo1-retention-full=2**
在配置文件中已经指定repo1-retention-full=2,会保留2个全备的备份集,目前只有一个全备备份集,需要增加全备备份集数量:
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=full backup
2023-10-24 10:22:47.411 P00 INFO: backup command begin 2.48: --exec-id=3596-dafacc38 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=full
2023-10-24 10:22:48.376 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 10:22:49.132 P00 INFO: backup start archive = 000000050000000000000008, lsn = 0/8000028
2023-10-24 10:22:49.132 P00 INFO: check archive for prior segment 000000050000000000000007
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 10:22:53.905 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 10:22:54.107 P00 INFO: backup stop archive = 000000050000000000000008, lsn = 0/8000138
2023-10-24 10:22:54.115 P00 INFO: check archive for segment(s) 000000050000000000000008:000000050000000000000008
2023-10-24 10:22:54.137 P00 INFO: new backup label = 20231024-102248F
2023-10-24 10:22:54.200 P00 INFO: full backup size = 36.9MB, file total = 1253
2023-10-24 10:22:54.200 P00 INFO: backup command end: completed successfully (6790ms)
2023-10-24 10:22:54.200 P00 INFO: expire command begin 2.48: --exec-id=3596-dafacc38 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 10:22:54.214 P00 INFO: repo1: 14-1 remove archive, start = 000000010000000000000001, stop = 000000010000000000000002
2023-10-24 10:22:54.214 P00 INFO: expire command end: completed successfully (14ms)
[postgres@du101 ~]$ pgbackrest info
stanza: pgsqlstatus: okcipher: nonedb (current)wal archive min/max (14): 000000010000000000000003/000000050000000000000008full backup: 20231024-085915Ftimestamp start/stop: 2023-10-24 08:59:15+08 / 2023-10-24 08:59:22+08wal start/stop: 000000010000000000000003 / 000000010000000000000003database size: 28.7MB, database backup size: 28.7MBrepo1: backup set size: 3.5MB, backup size: 3.5MBdiff backup: 20231024-085915F_20231024-090121Dtimestamp start/stop: 2023-10-24 09:01:21+08 / 2023-10-24 09:01:24+08wal start/stop: 000000010000000000000005 / 000000010000000000000005database size: 28.7MB, database backup size: 3.8MBrepo1: backup set size: 3.5MB, backup size: 469.2KBbackup reference list: 20231024-085915Fincr backup: 20231024-085915F_20231024-090450Itimestamp start/stop: 2023-10-24 09:04:50+08 / 2023-10-24 09:04:53+08wal start/stop: 000000010000000000000007 / 000000010000000000000007database size: 28.7MB, database backup size: 3.5MBrepo1: backup set size: 3.5MB, backup size: 406.7KBbackup reference list: 20231024-085915F, 20231024-085915F_20231024-090121Dincr backup: 20231024-085915F_20231024-090539Itimestamp start/stop: 2023-10-24 09:05:39+08 / 2023-10-24 09:05:42+08wal start/stop: 000000010000000000000009 / 000000010000000000000009database size: 28.7MB, database backup size: 3.5MBrepo1: backup set size: 3.5MB, backup size: 407.3KBbackup reference list: 20231024-085915F, 20231024-085915F_20231024-090121D, 20231024-085915F_20231024-090450Idiff backup: 20231024-085915F_20231024-090652Dtimestamp start/stop: 2023-10-24 09:06:52+08 / 2023-10-24 09:06:55+08wal start/stop: 00000001000000000000000B / 00000001000000000000000Bdatabase size: 28.7MB, database backup size: 3.8MBrepo1: backup set size: 3.5MB, backup size: 471.3KBbackup reference list: 20231024-085915Ffull backup: 20231024-102248Ftimestamp start/stop: 2023-10-24 10:22:48+08 / 2023-10-24 10:22:54+08wal start/stop: 000000050000000000000008 / 000000050000000000000008database size: 36.9MB, database backup size: 36.9MBrepo1: backup set size: 4.6MB, backup size: 4.6MB
[postgres@du101 ~]$
可以看出,已经达到2个全备,如果再次进行全量备份,则保留策略会生效:
[postgres@du101 ~]$ psql testdb -c "insert into t select generate_series(1,100);checkpoint;"
CHECKPOINT
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=full backup
2023-10-24 10:23:31.237 P00 INFO: backup command begin 2.48: --exec-id=3609-61586be5 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=full
2023-10-24 10:23:31.957 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 10:23:32.688 P00 INFO: backup start archive = 00000005000000000000000A, lsn = 0/A000028
2023-10-24 10:23:32.688 P00 INFO: check archive for prior segment 000000050000000000000009
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 10:23:37.067 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 10:23:37.679 P00 INFO: backup stop archive = 00000005000000000000000A, lsn = 0/A000100
2023-10-24 10:23:37.687 P00 INFO: check archive for segment(s) 00000005000000000000000A:00000005000000000000000A
2023-10-24 10:23:37.707 P00 INFO: new backup label = 20231024-102331F
2023-10-24 10:23:37.770 P00 INFO: full backup size = 36.9MB, file total = 1253
2023-10-24 10:23:37.770 P00 INFO: backup command end: completed successfully (6534ms)
2023-10-24 10:23:37.770 P00 INFO: expire command begin 2.48: --exec-id=3609-61586be5 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 10:23:37.771 P00 INFO: repo1: expire full backup set 20231024-085915F, 20231024-085915F_20231024-090121D, 20231024-085915F_20231024-090450I, 20231024-085915F_20231024-090539I, 20231024-085915F_20231024-090652D
2023-10-24 10:23:37.783 P00 INFO: repo1: remove expired backup 20231024-085915F_20231024-090652D
2023-10-24 10:23:37.783 P00 INFO: repo1: remove expired backup 20231024-085915F_20231024-090539I
2023-10-24 10:23:37.784 P00 INFO: repo1: remove expired backup 20231024-085915F_20231024-090450I
2023-10-24 10:23:37.784 P00 INFO: repo1: remove expired backup 20231024-085915F_20231024-090121D
2023-10-24 10:23:37.785 P00 INFO: repo1: remove expired backup 20231024-085915F
2023-10-24 10:23:37.814 P00 INFO: repo1: 14-1 remove archive, start = 0000000400000000, stop = 000000050000000000000007
2023-10-24 10:23:37.814 P00 INFO: repo1: 14-1 remove history file 00000002.history
2023-10-24 10:23:37.814 P00 INFO: repo1: 14-1 remove history file 00000003.history
2023-10-24 10:23:37.814 P00 INFO: repo1: 14-1 remove history file 00000004.history
2023-10-24 10:23:37.814 P00 INFO: expire command end: completed successfully (44ms)
[postgres@du101 ~]$
[postgres@du101 ~]$ pgbackrest info
stanza: pgsqlstatus: okcipher: nonedb (current)wal archive min/max (14): 000000050000000000000008/00000005000000000000000Afull backup: 20231024-102248Ftimestamp start/stop: 2023-10-24 10:22:48+08 / 2023-10-24 10:22:54+08wal start/stop: 000000050000000000000008 / 000000050000000000000008database size: 36.9MB, database backup size: 36.9MBrepo1: backup set size: 4.6MB, backup size: 4.6MBfull backup: 20231024-102331Ftimestamp start/stop: 2023-10-24 10:23:31+08 / 2023-10-24 10:23:37+08wal start/stop: 00000005000000000000000A / 00000005000000000000000Adatabase size: 36.9MB, database backup size: 36.9MBrepo1: backup set size: 4.6MB, backup size: 4.6MB
[postgres@du101 ~]$
可以看出之前的备份集都被删除。检查归档,也被删除:
[postgres@du101 ~]$ ll /data/backup/archive/pgsql/14-1/0000000500000000/
total 228K
-rw-r----- 1 postgres postgres 371 Oct 24 10:22 000000050000000000000008.00000028.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:22 000000050000000000000008-572204415f43334380af6e1c5b5bd239645e6c95.gz
-rw-r----- 1 postgres postgres 75K Oct 24 10:23 000000050000000000000009-92fd7508875b62821a86c82af171749d90a9cb3b.gz
-rw-r----- 1 postgres postgres 370 Oct 24 10:23 00000005000000000000000A.00000028.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:23 00000005000000000000000A-5091dff675fb4950c2fbf71e0e31e4c31b356276.gz
[postgres@du101 ~]$
手工指定某个备份集expire之后,对应的备份集也会被删除:
[postgres@du101 ~]$ pgbackrest expire --set=20231024-102248F --stanza=pgsql
[postgres@du101 ~]$ pgbackrest info
stanza: pgsqlstatus: okcipher: nonedb (current)wal archive min/max (14): 000000050000000000000008/00000005000000000000000Afull backup: 20231024-102331Ftimestamp start/stop: 2023-10-24 10:23:31+08 / 2023-10-24 10:23:37+08wal start/stop: 00000005000000000000000A / 00000005000000000000000Adatabase size: 36.9MB, database backup size: 36.9MBrepo1: backup set size: 4.6MB, backup size: 4.6MB
[postgres@du101 ~]$ ll /data/backup/archive/pgsql/14-1/0000000500000000/
total 228K
-rw-r----- 1 postgres postgres 371 Oct 24 10:22 000000050000000000000008.00000028.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:22 000000050000000000000008-572204415f43334380af6e1c5b5bd239645e6c95.gz
-rw-r----- 1 postgres postgres 75K Oct 24 10:23 000000050000000000000009-92fd7508875b62821a86c82af171749d90a9cb3b.gz
-rw-r----- 1 postgres postgres 370 Oct 24 10:23 00000005000000000000000A.00000028.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:23 00000005000000000000000A-5091dff675fb4950c2fbf71e0e31e4c31b356276.gz
[postgres@du101 ~]$
[postgres@du101 ~]$
[postgres@du101 ~]$
[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=full backup
2023-10-24 10:30:55.978 P00 INFO: backup command begin 2.48: --exec-id=3650-1fb9a7d8 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=full
2023-10-24 10:30:57.363 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 10:30:58.409 P00 INFO: backup start archive = 00000005000000000000000C, lsn = 0/C000060
2023-10-24 10:30:58.409 P00 INFO: check archive for prior segment 00000005000000000000000B
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 10:31:02.517 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 10:31:02.717 P00 INFO: backup stop archive = 00000005000000000000000C, lsn = 0/C000138
2023-10-24 10:31:02.723 P00 INFO: check archive for segment(s) 00000005000000000000000C:00000005000000000000000C
2023-10-24 10:31:02.742 P00 INFO: new backup label = 20231024-103057F
2023-10-24 10:31:02.809 P00 INFO: full backup size = 36.9MB, file total = 1253
2023-10-24 10:31:02.809 P00 INFO: backup command end: completed successfully (6832ms)
2023-10-24 10:31:02.809 P00 INFO: expire command begin 2.48: --exec-id=3650-1fb9a7d8 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 10:31:02.819 P00 INFO: repo1: 14-1 remove archive, start = 000000050000000000000008, stop = 000000050000000000000009
2023-10-24 10:31:02.819 P00 INFO: expire command end: completed successfully (10ms)
[postgres@du101 ~]$ pgbackrest info
stanza: pgsqlstatus: okcipher: nonedb (current)wal archive min/max (14): 00000005000000000000000A/00000005000000000000000Cfull backup: 20231024-102331Ftimestamp start/stop: 2023-10-24 10:23:31+08 / 2023-10-24 10:23:37+08wal start/stop: 00000005000000000000000A / 00000005000000000000000Adatabase size: 36.9MB, database backup size: 36.9MBrepo1: backup set size: 4.6MB, backup size: 4.6MBfull backup: 20231024-103057Ftimestamp start/stop: 2023-10-24 10:30:57+08 / 2023-10-24 10:31:02+08wal start/stop: 00000005000000000000000C / 00000005000000000000000Cdatabase size: 36.9MB, database backup size: 36.9MBrepo1: backup set size: 4.6MB, backup size: 4.6MB
[postgres@du101 ~]$ ll /data/backup/archive/pgsql/14-1/0000000500000000/
total 232K
-rw-r----- 1 postgres postgres 370 Oct 24 10:23 00000005000000000000000A.00000028.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:23 00000005000000000000000A-5091dff675fb4950c2fbf71e0e31e4c31b356276.gz
-rw-r----- 1 postgres postgres 79K Oct 24 10:30 00000005000000000000000B-e2f5215afc918424292aa47f98d41a2d414ae4c8.gz
-rw-r----- 1 postgres postgres 371 Oct 24 10:31 00000005000000000000000C.00000060.backup
-rw-r----- 1 postgres postgres 72K Oct 24 10:31 00000005000000000000000C-ee2a1baa8b64ec95ee3928759a77fc718e9ee94f.gz
[postgres@du101 ~]$
九、监控
The PostgreSQL allows pgBackRest info to be loaded into a table.
[root@du101 ~]# cp pgbackrest-release-2.48/doc/example/* /home/postgres/
[root@du101 ~]# chown postgres:postgres /home/postgres/pgsql-pgbackrest*sql[postgres@du101 ~]$ psql -f pgsql-pgbackrest-info.sql
CREATE SCHEMA
CREATE FUNCTION
[postgres@du101 ~]$ psql -f pgsql-pgbackrest-query.sql name | last_successful_backup | last_archived_wal
---------+------------------------+--------------------------"pgsql" | 2023-10-24 10:31:02+08 | 00000006000000000000000E
(1 row)[postgres@du101 ~]$ pgbackrest --stanza=pgsql --log-level-console=info --type=full backup
2023-10-24 10:59:01.824 P00 INFO: backup command begin 2.48: --exec-id=3738-4ad48e39 --log-level-console=info --pg1-path=/data/pgdata --pg1-port=1921 --pg1-user=postgres --process-max=2 --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql --start-fast --type=full
2023-10-24 10:59:02.609 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-10-24 10:59:03.186 P00 INFO: backup start archive = 000000060000000000000010, lsn = 0/10000028
2023-10-24 10:59:03.186 P00 INFO: check archive for prior segment 00000006000000000000000F
WARN: exclude special file '/data/pgdata/.s.PGSQL.1921' from backup
2023-10-24 10:59:07.780 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-10-24 10:59:07.981 P00 INFO: backup stop archive = 000000060000000000000010, lsn = 0/10000100
2023-10-24 10:59:07.992 P00 INFO: check archive for segment(s) 000000060000000000000010:000000060000000000000010
2023-10-24 10:59:08.014 P00 INFO: new backup label = 20231024-105902F
2023-10-24 10:59:08.080 P00 INFO: full backup size = 36.9MB, file total = 1253
2023-10-24 10:59:08.080 P00 INFO: backup command end: completed successfully (6257ms)
2023-10-24 10:59:08.080 P00 INFO: expire command begin 2.48: --exec-id=3738-4ad48e39 --log-level-console=info --repo1-path=/data/backup --repo1-retention-full=2 --stanza=pgsql
2023-10-24 10:59:08.081 P00 INFO: repo1: expire full backup 20231024-102331F
2023-10-24 10:59:08.092 P00 INFO: repo1: remove expired backup 20231024-102331F
2023-10-24 10:59:08.135 P00 INFO: repo1: 14-1 remove archive, start = 00000005000000000000000A, stop = 00000005000000000000000B
2023-10-24 10:59:08.135 P00 INFO: expire command end: completed successfully (55ms)
[postgres@du101 ~]$ psql -f pgsql-pgbackrest-query.sql name | last_successful_backup | last_archived_wal
---------+------------------------+--------------------------"pgsql" | 2023-10-24 10:59:07+08 | 000000060000000000000010
(1 row)[postgres@du101 ~]$ pgbackrest info
stanza: pgsqlstatus: okcipher: nonedb (current)wal archive min/max (14): 00000005000000000000000C/000000060000000000000010full backup: 20231024-103057Ftimestamp start/stop: 2023-10-24 10:30:57+08 / 2023-10-24 10:31:02+08wal start/stop: 00000005000000000000000C / 00000005000000000000000Cdatabase size: 36.9MB, database backup size: 36.9MBrepo1: backup set size: 4.6MB, backup size: 4.6MBfull backup: 20231024-105902Ftimestamp start/stop: 2023-10-24 10:59:02+08 / 2023-10-24 10:59:07+08wal start/stop: 000000060000000000000010 / 000000060000000000000010database size: 36.9MB, database backup size: 36.9MBrepo1: backup set size: 4.6MB, backup size: 4.6MB
[postgres@du101 ~]$
更多推荐
利用pgbackrest实现postgresql备份还原
发布评论