PostgreSQL数据库 pg_probackup保留策略测试

本文链接：https://blog.csdn.net/dqk1982/article/details/126210550

1结论

PostgreSQL数据库在利用pg_probackup备份并设置了备份集保留策略之后，可以根据保留策略清理备份集和wal归档。在执行清理命令的时候，有如下参数，它们之间的差异如下表所示:
在这里插入图片描述

2准备环境

2.1软件版本

PostgreSQL 11.15
pg_probackup 2.5.6

2.2安装pg_probackup

root用户
unzip pg_probackup-master.zip
cd pg_probackup-master/
make USE_PGXS=1 PG_CONFIG=/opt/pgsql/bin/pg_config top_srcdir=/root/postgresql-11.15
make USE_PGXS=1 PG_CONFIG=/opt/pgsql/bin/pg_config top_srcdir=/root/postgresql-11.15 install

2.3准备测试环境

2.3.1初始化备份目录初始换实例

mkdir /data/backup
chown postgres:postgres /data/backup -R

su - postgres
pg_probackup init -B /data/backup
pg_probackup add-instance -B /data/backup -D /data/pgdata/ --instance mytest

2.3.2修改数据库参数配置

[postgres@du104 ~]$ cat /data/pgdata/postgresql.conf
archive_mode = on
archive_command = ‘/opt/pgsql/bin/pg_probackup archive-push -B /data/backup --instance mytest --wal-file-path=%p --wal-file-name=%f’
full_page_writes = on
wal_log_hints = on
max_wal_senders = 10
max_replication_slots = 10
[postgres@du104 ~]$ pg_ctl restart

2.3.3准备测试表

[postgres@du104 ~]$ psql
psql (11.15)
Type “help” for help.

postgres=# create table t(id int,name varchar(20));
CREATE TABLE
postgres=# insert into t select generate_series(1,100),‘test data’;
INSERT 0 100
postgres=#

3以ARCHIVE模式备份

3.1模拟备份

3.1.1系统时间为7月27日全备和增量备份

查看操作系统时间：
[root@du104 ~]# date
Wed Jul 27 11:29:22 CST 2022
[root@du104 ~]#
执行全量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b full -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RFNTN2, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000002 to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 1s
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 1s
INFO: Validating backup RFNTN2
INFO: Backup RFNTN2 data files are valid
INFO: Backup RFNTN2 resident size: 64MB
INFO: Backup RFNTN2 completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ ================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RFNTN2 2022-07-27 11:28:20+08 FULL ARCHIVE 1/0 8s 64MB 16MB 1.00 0/2000060 0/30000B8 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ===============================================================================================================================
TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000001 000000010000000000000003 3 48MB 1.00 1 OK
[postgres@du104 ~]$

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RFNTOD, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RFNTN2
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000005 to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RFNTOD
INFO: Backup RFNTOD data files are valid
INFO: Backup RFNTOD resident size: 568kB
INFO: Backup RFNTOD completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RFNTOD 2022-07-27 11:29:05+08 PAGE ARCHIVE 1/1 5s 568kB 16MB 1.00 0/5000028 0/60000B8 OK
mytest 11 RFNTN2 2022-07-27 11:28:20+08 FULL ARCHIVE 1/0 8s 64MB 16MB 1.00 0/2000060 0/30000B8 OK
[postgres@du104 ~]$

3.1.2系统时间为7月28日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-07-28 11:29:22’
Thu Jul 28 11:29:22 CST 2022
[root@du104 ~]# date
Thu Jul 28 11:29:23 CST 2022
[root@du104 ~]#
模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#
增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RFPODF, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RFNTOD
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000008 to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RFPODF
INFO: Backup RFPODF data files are valid
INFO: Backup RFPODF resident size: 218kB
INFO: Backup RFPODF completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RFPODF 2022-07-28 11:29:41+08 PAGE ARCHIVE 1/1 3s 218kB 16MB 1.00 0/8000028 0/90000B8 OK
mytest 11 RFNTOD 2022-07-27 11:29:05+08 PAGE ARCHIVE 1/1 5s 568kB 16MB 1.00 0/5000028 0/60000B8 OK
mytest 11 RFNTN2 2022-07-27 11:28:20+08 FULL ARCHIVE 1/0 8s 64MB 16MB 1.00 0/2000060 0/30000B8 OK
[postgres@du104 ~]$

3.1.3系统时间为7月29日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-07-29 11:29:22’
Fri Jul 29 11:29:22 CST 2022
[root@du104 ~]# date
Fri Jul 29 11:29:22 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RFRJ1G, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RFPODF
INFO: Wait for WAL segment /data/backup/wal/mytest/00000001000000000000000B to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RFRJ1G
INFO: Backup RFRJ1G data files are valid
INFO: Backup RFRJ1G resident size: 187kB
INFO: Backup RFRJ1G completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ ================================================================================================================================= Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RFRJ1G 2022-07-29 11:29:43+08 PAGE ARCHIVE 1/1 4s 187kB 16MB 1.00 0/B000028 0/C0000B8 OK
mytest 11 RFPODF 2022-07-28 11:29:41+08 PAGE ARCHIVE 1/1 3s 218kB 16MB 1.00 0/8000028 0/90000B8 OK
mytest 11 RFNTOD 2022-07-27 11:29:05+08 PAGE ARCHIVE 1/1 5s 568kB 16MB 1.00 0/5000028 0/60000B8 OK
mytest 11 RFNTN2 2022-07-27 11:28:20+08 FULL ARCHIVE 1/0 8s 64MB 16MB 1.00 0/2000060 0/30000B8 OK
[postgres@du104 ~]$

3.1.4系统时间为7月30日全量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-07-30 10:28:05’
Sat Jul 30 10:28:05 CST 2022
[root@du104 ~]# date
Sat Jul 30 10:28:06 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

执行全量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b full -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RFTDPK, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Wait for WAL segment /data/backup/wal/mytest/00000001000000000000000E to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 1s
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RFTDPK
INFO: Backup RFTDPK data files are valid
INFO: Backup RFTDPK resident size: 64MB
INFO: Backup RFTDPK completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ ================================================================================================================================= Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RFTDPK 2022-07-30 11:29:47+08 FULL ARCHIVE 1/0 4s 64MB 16MB 1.00 0/E000028 0/F0000B8 OK
mytest 11 RFRJ1G 2022-07-29 11:29:43+08 PAGE ARCHIVE 1/1 4s 187kB 16MB 1.00 0/B000028 0/C0000B8 OK
mytest 11 RFPODF 2022-07-28 11:29:41+08 PAGE ARCHIVE 1/1 3s 218kB 16MB 1.00 0/8000028 0/90000B8 OK
mytest 11 RFNTOD 2022-07-27 11:29:05+08 PAGE ARCHIVE 1/1 5s 568kB 16MB 1.00 0/5000028 0/60000B8 OK
mytest 11 RFNTN2 2022-07-27 11:28:20+08 FULL ARCHIVE 1/0 8s 64MB 16MB 1.00 0/2000060 0/30000B8 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000001 00000001000000000000000F 15 240MB 1.00 5 OK
[postgres@du104 ~]$

3.1.5系统时间为7月31日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-07-31 11:29:22’
Sun Jul 31 11:29:22 CST 2022
[root@du104 ~]# date
Sun Jul 31 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RFV8DH, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RFTDPK
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000011 to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RFV8DH
INFO: Backup RFV8DH data files are valid
INFO: Backup RFV8DH resident size: 184kB
INFO: Backup RFV8DH completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RFV8DH 2022-07-31 11:29:45+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/11000028 0/120000B8 OK
mytest 11 RFTDPK 2022-07-30 11:29:47+08 FULL ARCHIVE 1/0 4s 64MB 16MB 1.00 0/E000028 0/F0000B8 OK
mytest 11 RFRJ1G 2022-07-29 11:29:43+08 PAGE ARCHIVE 1/1 4s 187kB 16MB 1.00 0/B000028 0/C0000B8 OK
mytest 11 RFPODF 2022-07-28 11:29:41+08 PAGE ARCHIVE 1/1 3s 218kB 16MB 1.00 0/8000028 0/90000B8 OK
mytest 11 RFNTOD 2022-07-27 11:29:05+08 PAGE ARCHIVE 1/1 5s 568kB 16MB 1.00 0/5000028 0/60000B8 OK
mytest 11 RFNTN2 2022-07-27 11:28:20+08 FULL ARCHIVE 1/0 8s 64MB 16MB 1.00 0/2000060 0/30000B8 OK
[postgres@du104 ~]$

3.1.6系统时间为8月1日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-01 11:29:22’
Mon Aug 1 11:29:22 CST 2022
[root@du104 ~]# date
Mon Aug 1 11:29:26 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RFX31N, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RFV8DH
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000014 to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RFX31N
INFO: Backup RFX31N data files are valid
INFO: Backup RFX31N resident size: 184kB
INFO: Backup RFX31N completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RFX31N 2022-08-01 11:29:51+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/14000028 0/150000B8 OK
mytest 11 RFV8DH 2022-07-31 11:29:45+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/11000028 0/120000B8 OK
mytest 11 RFTDPK 2022-07-30 11:29:47+08 FULL ARCHIVE 1/0 4s 64MB 16MB 1.00 0/E000028 0/F0000B8 OK
mytest 11 RFRJ1G 2022-07-29 11:29:43+08 PAGE ARCHIVE 1/1 4s 187kB 16MB 1.00 0/B000028 0/C0000B8 OK
mytest 11 RFPODF 2022-07-28 11:29:41+08 PAGE ARCHIVE 1/1 3s 218kB 16MB 1.00 0/8000028 0/90000B8 OK
mytest 11 RFNTOD 2022-07-27 11:29:05+08 PAGE ARCHIVE 1/1 5s 568kB 16MB 1.00 0/5000028 0/60000B8 OK
mytest 11 RFNTN2 2022-07-27 11:28:20+08 FULL ARCHIVE 1/0 8s 64MB 16MB 1.00 0/2000060 0/30000B8 OK
[postgres@du104 ~]$

3.1.7系统时间为8月2日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-02 11:29:22’
Tue Aug 2 11:29:22 CST 2022
[root@du104 ~]# date
Tue Aug 2 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RFYXPC, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RFX31N
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000017 to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RFYXPC
INFO: Backup RFYXPC data files are valid
INFO: Backup RFYXPC resident size: 152kB
INFO: Backup RFYXPC completed
[postgres@du104 ~]$
[postgres@du104 ~]$
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RFYXPC 2022-08-02 11:29:40+08 PAGE ARCHIVE 1/1 5s 152kB 16MB 1.00 0/17000060 0/180000B8 OK
mytest 11 RFX31N 2022-08-01 11:29:51+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/14000028 0/150000B8 OK
mytest 11 RFV8DH 2022-07-31 11:29:45+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/11000028 0/120000B8 OK
mytest 11 RFTDPK 2022-07-30 11:29:47+08 FULL ARCHIVE 1/0 4s 64MB 16MB 1.00 0/E000028 0/F0000B8 OK
mytest 11 RFRJ1G 2022-07-29 11:29:43+08 PAGE ARCHIVE 1/1 4s 187kB 16MB 1.00 0/B000028 0/C0000B8 OK
mytest 11 RFPODF 2022-07-28 11:29:41+08 PAGE ARCHIVE 1/1 3s 218kB 16MB 1.00 0/8000028 0/90000B8 OK
mytest 11 RFNTOD 2022-07-27 11:29:05+08 PAGE ARCHIVE 1/1 5s 568kB 16MB 1.00 0/5000028 0/60000B8 OK
mytest 11 RFNTN2 2022-07-27 11:28:20+08 FULL ARCHIVE 1/0 8s 64MB 16MB 1.00 0/2000060 0/30000B8 OK
[postgres@du104 ~]$

3.1.8系统时间为8月3日全量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-03 11:29:22’
Wed Aug 3 11:29:22 CST 2022
[root@du104 ~]# date
Wed Aug 3 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

全量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b full -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RG0SDJ, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Wait for WAL segment /data/backup/wal/mytest/00000001000000000000001A to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 1s
INFO: Validating backup RG0SDJ
INFO: Backup RG0SDJ data files are valid
INFO: Backup RG0SDJ resident size: 64MB
INFO: Backup RG0SDJ completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RG0SDJ 2022-08-03 11:29:47+08 FULL ARCHIVE 1/0 6s 64MB 16MB 1.00 0/1A000028 0/1B0000B8 OK
mytest 11 RFYXPC 2022-08-02 11:29:40+08 PAGE ARCHIVE 1/1 5s 152kB 16MB 1.00 0/17000060 0/180000B8 OK
mytest 11 RFX31N 2022-08-01 11:29:51+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/14000028 0/150000B8 OK
mytest 11 RFV8DH 2022-07-31 11:29:45+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/11000028 0/120000B8 OK
mytest 11 RFTDPK 2022-07-30 11:29:47+08 FULL ARCHIVE 1/0 4s 64MB 16MB 1.00 0/E000028 0/F0000B8 OK
mytest 11 RFRJ1G 2022-07-29 11:29:43+08 PAGE ARCHIVE 1/1 4s 187kB 16MB 1.00 0/B000028 0/C0000B8 OK
mytest 11 RFPODF 2022-07-28 11:29:41+08 PAGE ARCHIVE 1/1 3s 218kB 16MB 1.00 0/8000028 0/90000B8 OK
mytest 11 RFNTOD 2022-07-27 11:29:05+08 PAGE ARCHIVE 1/1 5s 568kB 16MB 1.00 0/5000028 0/60000B8 OK
mytest 11 RFNTN2 2022-07-27 11:28:20+08 FULL ARCHIVE 1/0 8s 64MB 16MB 1.00 0/2000060 0/30000B8 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000001 00000001000000000000001B 27 432MB 1.00 9 OK
[postgres@du104 ~]$

3.1.9系统时间为8月4日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-04 11:29:22’
Thu Aug 4 11:29:22 CST 2022
[root@du104 ~]# date
Thu Aug 4 11:29:24 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RG2N1M, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RG0SDJ
INFO: Wait for WAL segment /data/backup/wal/mytest/00000001000000000000001D to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RG2N1M
INFO: Backup RG2N1M data files are valid
INFO: Backup RG2N1M resident size: 248kB
INFO: Backup RG2N1M completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RG2N1M 2022-08-04 11:29:49+08 PAGE ARCHIVE 1/1 4s 248kB 16MB 1.00 0/1D000028 0/1E0000F0 OK
mytest 11 RG0SDJ 2022-08-03 11:29:47+08 FULL ARCHIVE 1/0 6s 64MB 16MB 1.00 0/1A000028 0/1B0000B8 OK
mytest 11 RFYXPC 2022-08-02 11:29:40+08 PAGE ARCHIVE 1/1 5s 152kB 16MB 1.00 0/17000060 0/180000B8 OK
mytest 11 RFX31N 2022-08-01 11:29:51+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/14000028 0/150000B8 OK
mytest 11 RFV8DH 2022-07-31 11:29:45+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/11000028 0/120000B8 OK
mytest 11 RFTDPK 2022-07-30 11:29:47+08 FULL ARCHIVE 1/0 4s 64MB 16MB 1.00 0/E000028 0/F0000B8 OK
mytest 11 RFRJ1G 2022-07-29 11:29:43+08 PAGE ARCHIVE 1/1 4s 187kB 16MB 1.00 0/B000028 0/C0000B8 OK
mytest 11 RFPODF 2022-07-28 11:29:41+08 PAGE ARCHIVE 1/1 3s 218kB 16MB 1.00 0/8000028 0/90000B8 OK
mytest 11 RFNTOD 2022-07-27 11:29:05+08 PAGE ARCHIVE 1/1 5s 568kB 16MB 1.00 0/5000028 0/60000B8 OK
mytest 11 RFNTN2 2022-07-27 11:28:20+08 FULL ARCHIVE 1/0 8s 64MB 16MB 1.00 0/2000060 0/30000B8 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000001 00000001000000000000001E 30 480MB 1.00 10 OK
[postgres@du104 ~]$
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/* |head
-rw------- 1 postgres postgres 16777216 Jul 27 11:28 /data/backup/wal/mytest/000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jul 27 11:28 /data/backup/wal/mytest/000000010000000000000002
-rw------- 1 postgres postgres 16777216 Jul 27 11:28 /data/backup/wal/mytest/000000010000000000000003
-rw------- 1 postgres postgres 352 Jul 27 11:28 /data/backup/wal/mytest/000000010000000000000002.00000060.backup
-rw------- 1 postgres postgres 16777216 Jul 27 11:29 /data/backup/wal/mytest/000000010000000000000004
-rw------- 1 postgres postgres 16777216 Jul 27 11:29 /data/backup/wal/mytest/000000010000000000000005
-rw------- 1 postgres postgres 16777216 Jul 27 11:29 /data/backup/wal/mytest/000000010000000000000006
-rw------- 1 postgres postgres 352 Jul 27 11:29 /data/backup/wal/mytest/000000010000000000000005.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 28 11:29 /data/backup/wal/mytest/000000010000000000000007
-rw------- 1 postgres postgres 16777216 Jul 28 11:29 /data/backup/wal/mytest/000000010000000000000008
[postgres@du104 ~]$

3.2删除备份和归档

3.2.1查看现有备份集和归档

[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

3.2.2设置保留策略

[postgres@du104 ~]$ cat /data/backup/backups/mytest/pg_probackup.conf

Backup instance information

pgdata = /data/pgdata
system-identifier = 7080519098597187188
xlog-seg-size = 16777216
[postgres@du104 ~]$ pg_probackup set-config -B /data/backup --instance mytest --retention-redundancy=2 --retention-window=4
[postgres@du104 ~]$ cat /data/backup/backups/mytest/pg_probackup.conf

Backup instance information

pgdata = /data/pgdata
system-identifier = 7080519098597187188
xlog-seg-size = 16777216

Retention parameters

retention-redundancy = 2
retention-window = 4
[postgres@du104 ~]$

官方文档原文：
To configure retention policy, set one or more of the following variables in the pg_probackup.conf file via set-config:
–retention-redundancy=redundancy
Specifies the number of full backup copies to keep in the backup catalog.
–retention-window=window
Defines the earliest point in time for which pg_probackup can complete the recovery. This option is set in the number of days from the current moment. For example, if retention-window=7, pg_probackup must keep at least one backup copy that is older than seven days, with all the corresponding WAL files, and all the backups that follow.
If both --retention-redundancy and --retention-window options are set, both these conditions have to be taken into account when purging the backup catalog.

3.2.3在8月4日删除备份集和wal

[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest --delete-expired --delete-wal
INFO: Evaluate backups by retention
INFO: Backup RG2N1M, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/4d. Active
INFO: Backup RG0SDJ, mode: FULL, status: OK. Redundancy: 1/2, Time Window: 1d/4d. Active
INFO: Backup RFYXPC, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 2d/4d. Active
INFO: Backup RFX31N, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 3d/4d. Active
INFO: Backup RFV8DH, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 4d/4d. Active
INFO: Backup RFTDPK, mode: FULL, status: OK. Redundancy: 2/2, Time Window: 5d/4d. Active
INFO: Backup RFRJ1G, mode: PAGE, status: OK. Redundancy: 3/2, Time Window: 6d/4d. Expired
INFO: Backup RFPODF, mode: PAGE, status: OK. Redundancy: 3/2, Time Window: 7d/4d. Expired
INFO: Backup RFNTOD, mode: PAGE, status: OK. Redundancy: 3/2, Time Window: 8d/4d. Expired
INFO: Backup RFNTN2, mode: FULL, status: OK. Redundancy: 3/2, Time Window: 8d/4d. Expired
INFO: Delete: RFRJ1G 2022-07-29 11:29:43+08
INFO: Delete: RFPODF 2022-07-28 11:29:41+08
INFO: Delete: RFNTOD 2022-07-27 11:29:05+08
INFO: Delete: RFNTN2 2022-07-27 11:28:20+08
INFO: On timeline 1 WAL segments between 000000010000000000000001 and 00000001000000000000000E will be removed
INFO: Logical WAL size to remove on timeline 1 : 208MB
INFO: Resident WAL size to free on timeline 1 : 208MB
INFO: There are no backups to merge by retention policy
INFO: Purging finished
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000000E 00000001000000000000001E 17 272MB 1.00 6 OK
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000F
-rw------- 1 postgres postgres 352 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000010
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011
-rw------- 1 postgres postgres 355 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000012
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000013
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014
-rw------- 1 postgres postgres 355 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000015
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000016
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000018
-rw------- 1 postgres postgres 355 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/000000010000000000000019
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001B
-rw------- 1 postgres postgres 355 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001C
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D
-rw------- 1 postgres postgres 355 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001E
[postgres@du104 ~]$

3.2.4结论

参数–delete-expired --delete-wal运行之后，由于有归档的存在可以恢复到保留备份集之间的任意时间点。

官方文档解释如下：
To clean up the backup catalog in accordance with retention policy, you have to run the delete command with retention flags, as shown below, or use the backup command with these flags to process the outdated backup copies right when the new backup is created.
For example, to remove all backup copies that no longer satisfy the defined retention policy, run the following command with the --delete-expired flag:
pg_probackup delete -B backup_dir --instance instance_name --delete-expired
If you would like to also remove the WAL files that are no longer required for any of the backups, you should also specify the --delete-wal flag:
pg_probackup delete -B backup_dir --instance instance_name --delete-expired --delete-wal

3.3参数wal-depth的使用

3.3.1在8月4日删除归档

3.3.1.1查看wal归档

[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000F
-rw------- 1 postgres postgres 352 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000010
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011
-rw------- 1 postgres postgres 355 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000012
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000013
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014
-rw------- 1 postgres postgres 355 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000015
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000016
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000018
-rw------- 1 postgres postgres 355 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/000000010000000000000019
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001B
-rw------- 1 postgres postgres 355 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001C
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D
-rw------- 1 postgres postgres 355 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001E
[postgres@du104 ~]$

3.3.1.2测试wal-depth参数

[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest --delete-expired --delete-wal --wal-depth=2
INFO: Evaluate backups by retention
INFO: Backup RG2N1M, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/4d. Active
INFO: Backup RG0SDJ, mode: FULL, status: OK. Redundancy: 1/2, Time Window: 1d/4d. Active
INFO: Backup RFYXPC, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 2d/4d. Active
INFO: Backup RFX31N, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 3d/4d. Active
INFO: Backup RFV8DH, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 4d/4d. Active
INFO: Backup RFTDPK, mode: FULL, status: OK. Redundancy: 2/2, Time Window: 5d/4d. Active
INFO: On timeline 1 WAL segments between 00000001000000000000000E and 00000001000000000000001A will be removed
INFO: Logical WAL size to remove on timeline 1 : 192MB
INFO: Resident WAL size to free on timeline 1 : 192MB
INFO: There are no backups to merge by retention policy
INFO: There are no backups to delete by retention policy
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000000E 00000001000000000000001E 13 208MB 1.00 6 DEGRADED
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000F
-rw------- 1 postgres postgres 352 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011
-rw------- 1 postgres postgres 355 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000012
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014
-rw------- 1 postgres postgres 355 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000015
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000018
-rw------- 1 postgres postgres 355 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001B
-rw------- 1 postgres postgres 355 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001C
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D
-rw------- 1 postgres postgres 355 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001E
[postgres@du104 ~]$

添加参数–wal-depth之后，也会保留可根据存在的备份集恢复到任意时间点的归档，但是一些归档也会由于的添加–wal-depth被删除，猜测是不存在数据的归档。此步骤中000000010000000000000010，000000010000000000000013，000000010000000000000016，000000010000000000000019由于添加此参数被删除。

官方文档中的原文为：
To remove WAL segments that do not satisfy the defined WAL archive retention policy, you simply have to run the delete or backup command with the --delete-wal flag. For archive backups, WAL segments between Start LSN and Stop LSN are always kept intact, so such backups remain valid regardless of the --wal-depth setting and can still be restored, if required.

–wal-depth=wal_depth
Number of latest valid backups on every timeline that must retain the ability to perform PITR. Must be a non-negative integer. The zero value disables this setting.
Default: 0

由于wal-depth指定为2，所以只能在最后的2个备份集之间做任意的PITR恢复。保留这2个备份集之外的wal归档，是由于保留侧虐中要求保留这些备份集，要保证备份集的可用，需要每个备份集保留Start LSN and到 Stop LSN 之间的wal归档。

关于DEGRADED状态，官方文档中的解释为：
•DEGRADED — some WAL segments between Min Segno and Max Segno are missing. To find out which files are lost, view this report in the JSON format.

通过json格式查看，丢失的wal归档为：000000010000000000000010，000000010000000000000013，000000010000000000000016，000000010000000000000019

[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive --format=json
[
{
“instance”: “mytest”,
“timelines”: [
{
“tli”: 1,
“parent-tli”: 0,
“switchpoint”: “0/0”,
“min-segno”: “00000001000000000000000E”,
“max-segno”: “00000001000000000000001E”,
“n-segments”: 13,
“size”: 218103808,
“zratio”: 1.00,
“closest-backup-id”: “”,
“status”: “DEGRADED”,
“lost-segments”: [
{
“begin-segno”: “000000010000000000000010”,
“end-segno”: “000000010000000000000010”
},
{
“begin-segno”: “000000010000000000000013”,
“end-segno”: “000000010000000000000013”
},
{
“begin-segno”: “000000010000000000000016”,
“end-segno”: “000000010000000000000016”
},
{
“begin-segno”: “000000010000000000000019”,
“end-segno”: “000000010000000000000019”
}
],
“backups”: [
{
“id”: “RG2N1M”,
“parent-backup-id”: “RG0SDJ”,
“backup-mode”: “PAGE”,
“wal”: “ARCHIVE”,
“compress-alg”: “none”,
“compress-level”: 1,
“from-replica”: “false”,
“block-size”: 32768,
“xlog-block-size”: 8192,
“checksum-version”: 0,
“program-version”: “2.5.6”,
“server-version”: “11”,
“current-tli”: 1,
“parent-tli”: 1,
“start-lsn”: “0/1D000028”,
“stop-lsn”: “0/1E0000F0”,
“start-time”: “2022-08-04 11:29:46+08”,
“end-time”: “2022-08-04 11:29:50+08”,
“recovery-xid”: 582,
“recovery-time”: “2022-08-04 11:29:49+08”,
“data-bytes”: 253847,
“wal-bytes”: 16777216,
“uncompressed-bytes”: 253745,
“pgdata-bytes”: 67023380,
“primary_conninfo”: “user=postgres host=localhost port=1921 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any”,
“status”: “OK”,
“content-crc”: 3909771175
},
{
“id”: “RG0SDJ”,
“backup-mode”: “FULL”,
“wal”: “ARCHIVE”,
“compress-alg”: “none”,
“compress-level”: 1,
“from-replica”: “false”,
“block-size”: 32768,
“xlog-block-size”: 8192,
“checksum-version”: 0,
“program-version”: “2.5.6”,
“server-version”: “11”,
“current-tli”: 1,
“parent-tli”: 0,
“start-lsn”: “0/1A000028”,
“stop-lsn”: “0/1B0000B8”,
“start-time”: “2022-08-03 11:29:43+08”,
“end-time”: “2022-08-03 11:29:49+08”,
“recovery-xid”: 580,
“recovery-time”: “2022-08-03 11:29:47+08”,
“data-bytes”: 67032643,
“wal-bytes”: 16777216,
“uncompressed-bytes”: 67021229,
“pgdata-bytes”: 67020988,
“primary_conninfo”: “user=postgres host=localhost port=1921 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any”,
“status”: “OK”,
“content-crc”: 3379469098
},
{
“id”: “RFYXPC”,
“parent-backup-id”: “RFX31N”,
“backup-mode”: “PAGE”,
“wal”: “ARCHIVE”,
“compress-alg”: “none”,
“compress-level”: 1,
“from-replica”: “false”,
“block-size”: 32768,
“xlog-block-size”: 8192,
“checksum-version”: 0,
“program-version”: “2.5.6”,
“server-version”: “11”,
“current-tli”: 1,
“parent-tli”: 1,
“start-lsn”: “0/17000060”,
“stop-lsn”: “0/180000B8”,
“start-time”: “2022-08-02 11:29:36+08”,
“end-time”: “2022-08-02 11:29:41+08”,
“recovery-xid”: 579,
“recovery-time”: “2022-08-02 11:29:40+08”,
“data-bytes”: 155520,
“wal-bytes”: 16777216,
“uncompressed-bytes”: 155442,
“pgdata-bytes”: 67018597,
“primary_conninfo”: “user=postgres host=localhost port=1921 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any”,
“status”: “OK”,
“content-crc”: 257397189
},
{
“id”: “RFX31N”,
“parent-backup-id”: “RFV8DH”,
“backup-mode”: “PAGE”,
“wal”: “ARCHIVE”,
“compress-alg”: “none”,
“compress-level”: 1,
“from-replica”: “false”,
“block-size”: 32768,
“xlog-block-size”: 8192,
“checksum-version”: 0,
“program-version”: “2.5.6”,
“server-version”: “11”,
“current-tli”: 1,
“parent-tli”: 1,
“start-lsn”: “0/14000028”,
“stop-lsn”: “0/150000B8”,
“start-time”: “2022-08-01 11:29:47+08”,
“end-time”: “2022-08-01 11:29:52+08”,
“recovery-xid”: 579,
“recovery-time”: “2022-08-01 11:29:51+08”,
“data-bytes”: 188294,
“wal-bytes”: 16777216,
“uncompressed-bytes”: 188208,
“pgdata-bytes”: 67016205,
“primary_conninfo”: “user=postgres host=localhost port=1921 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any”,
“status”: “OK”,
“content-crc”: 3600326332
},
{
“id”: “RFV8DH”,
“parent-backup-id”: “RFTDPK”,
“backup-mode”: “PAGE”,
“wal”: “ARCHIVE”,
“compress-alg”: “none”,
“compress-level”: 1,
“from-replica”: “false”,
“block-size”: 32768,
“xlog-block-size”: 8192,
“checksum-version”: 0,
“program-version”: “2.5.6”,
“server-version”: “11”,
“current-tli”: 1,
“parent-tli”: 1,
“start-lsn”: “0/11000028”,
“stop-lsn”: “0/120000B8”,
“start-time”: “2022-07-31 11:29:41+08”,
“end-time”: “2022-07-31 11:29:46+08”,
“recovery-xid”: 578,
“recovery-time”: “2022-07-31 11:29:45+08”,
“data-bytes”: 188289,
“wal-bytes”: 16777216,
“uncompressed-bytes”: 188203,
“pgdata-bytes”: 67013813,
“primary_conninfo”: “user=postgres host=localhost port=1921 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any”,
“status”: “OK”,
“content-crc”: 2224551608
},
{
“id”: “RFTDPK”,
“backup-mode”: “FULL”,
“wal”: “ARCHIVE”,
“compress-alg”: “none”,
“compress-level”: 1,
“from-replica”: “false”,
“block-size”: 32768,
“xlog-block-size”: 8192,
“checksum-version”: 0,
“program-version”: “2.5.6”,
“server-version”: “11”,
“current-tli”: 1,
“parent-tli”: 0,
“start-lsn”: “0/E000028”,
“stop-lsn”: “0/F0000B8”,
“start-time”: “2022-07-30 11:29:44+08”,
“end-time”: “2022-07-30 11:29:48+08”,
“recovery-xid”: 577,
“recovery-time”: “2022-07-30 11:29:47+08”,
“data-bytes”: 67023075,
“wal-bytes”: 16777216,
“uncompressed-bytes”: 67011661,
“pgdata-bytes”: 67011422,
“primary_conninfo”: “user=postgres host=localhost port=1921 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any”,
“status”: “OK”,
“content-crc”: 1091404132
}
]
}
]
}
]
[postgres@du104 ~]$

3.3.2在8月5日删除归档

[root@du104 ~]# date -s ‘2022-08-05 11:29:22’
Fri Aug 5 11:29:22 CST 2022
[root@du104 ~]# date
Fri Aug 5 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RG4HPJ, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RG2N1M
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000020 to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RG4HPJ
INFO: Backup RG4HPJ data files are valid
INFO: Backup RG4HPJ resident size: 184kB
INFO: Backup RG4HPJ completed
[postgres@du104 ~]$
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RG4HPJ 2022-08-05 11:29:47+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/20000028 0/210000B8 OK
mytest 11 RG2N1M 2022-08-04 11:29:49+08 PAGE ARCHIVE 1/1 4s 248kB 16MB 1.00 0/1D000028 0/1E0000F0 OK
mytest 11 RG0SDJ 2022-08-03 11:29:47+08 FULL ARCHIVE 1/0 6s 64MB 16MB 1.00 0/1A000028 0/1B0000B8 OK
mytest 11 RFYXPC 2022-08-02 11:29:40+08 PAGE ARCHIVE 1/1 5s 152kB 16MB 1.00 0/17000060 0/180000B8 OK
mytest 11 RFX31N 2022-08-01 11:29:51+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/14000028 0/150000B8 OK
mytest 11 RFV8DH 2022-07-31 11:29:45+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/11000028 0/120000B8 OK
mytest 11 RFTDPK 2022-07-30 11:29:47+08 FULL ARCHIVE 1/0 4s 64MB 16MB 1.00 0/E000028 0/F0000B8 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000000E 000000010000000000000021 16 256MB 1.00 7 DEGRADED
[postgres@du104 ~]$

删除归档：
[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest --delete-expired --delete-wal --wal-depth=2
INFO: Evaluate backups by retention
INFO: Backup RG4HPJ, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/4d. Active
INFO: Backup RG2N1M, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 1d/4d. Active
INFO: Backup RG0SDJ, mode: FULL, status: OK. Redundancy: 1/2, Time Window: 2d/4d. Active
INFO: Backup RFYXPC, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 3d/4d. Active
INFO: Backup RFX31N, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 4d/4d. Active
INFO: Backup RFV8DH, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 5d/4d. Active
INFO: Backup RFTDPK, mode: FULL, status: OK. Redundancy: 2/2, Time Window: 6d/4d. Active
INFO: On timeline 1 WAL segments between 00000001000000000000000E and 00000001000000000000001D will be removed
INFO: Logical WAL size to remove on timeline 1 : 240MB
INFO: Resident WAL size to free on timeline 1 : 176MB
INFO: There are no backups to merge by retention policy
INFO: There are no backups to delete by retention policy
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000000E 000000010000000000000021 15 240MB 1.00 7 DEGRADED
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000F
-rw------- 1 postgres postgres 352 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011
-rw------- 1 postgres postgres 355 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000012
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014
-rw------- 1 postgres postgres 355 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000015
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000018
-rw------- 1 postgres postgres 355 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001B
-rw------- 1 postgres postgres 355 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D
-rw------- 1 postgres postgres 355 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001E
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/00000001000000000000001F
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000020
-rw------- 1 postgres postgres 355 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000020.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000021
[postgres@du104 ~]$

此步骤中00000001000000000000001C被删除。

3.3.3在8月6日删除归档

[root@du104 ~]# date -s ‘2022-08-06 11:29:22’
Sat Aug 6 11:29:22 CST 2022
[root@du104 ~]# date
Sat Aug 6 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RG6CDS, backup mode: PAGE, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RG4HPJ
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000023 to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RG6CDS
INFO: Backup RG6CDS data files are valid
INFO: Backup RG6CDS resident size: 184kB
INFO: Backup RG6CDS completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RG6CDS 2022-08-06 11:29:55+08 PAGE ARCHIVE 1/1 4s 184kB 16MB 1.00 0/23000060 0/240000B8 OK
mytest 11 RG4HPJ 2022-08-05 11:29:47+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/20000028 0/210000B8 OK
mytest 11 RG2N1M 2022-08-04 11:29:49+08 PAGE ARCHIVE 1/1 4s 248kB 16MB 1.00 0/1D000028 0/1E0000F0 OK
mytest 11 RG0SDJ 2022-08-03 11:29:47+08 FULL ARCHIVE 1/0 6s 64MB 16MB 1.00 0/1A000028 0/1B0000B8 OK
mytest 11 RFYXPC 2022-08-02 11:29:40+08 PAGE ARCHIVE 1/1 5s 152kB 16MB 1.00 0/17000060 0/180000B8 OK
mytest 11 RFX31N 2022-08-01 11:29:51+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/14000028 0/150000B8 OK
mytest 11 RFV8DH 2022-07-31 11:29:45+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/11000028 0/120000B8 OK
mytest 11 RFTDPK 2022-07-30 11:29:47+08 FULL ARCHIVE 1/0 4s 64MB 16MB 1.00 0/E000028 0/F0000B8 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000000E 000000010000000000000024 18 288MB 1.00 8 DEGRADED
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000F
-rw------- 1 postgres postgres 352 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011
-rw------- 1 postgres postgres 355 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000012
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014
-rw------- 1 postgres postgres 355 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000015
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000018
-rw------- 1 postgres postgres 355 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001B
-rw------- 1 postgres postgres 355 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D
-rw------- 1 postgres postgres 355 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001E
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/00000001000000000000001F
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000020
-rw------- 1 postgres postgres 355 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000020.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000021
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000022
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000023
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000024
-rw------- 1 postgres postgres 355 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000023.00000060.backup
[postgres@du104 ~]$

删除归档：
[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest --delete-expired --delete-wal --wal-depth=2
INFO: Evaluate backups by retention
INFO: Backup RG6CDS, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/4d. Active
INFO: Backup RG4HPJ, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 1d/4d. Active
INFO: Backup RG2N1M, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 2d/4d. Active
INFO: Backup RG0SDJ, mode: FULL, status: OK. Redundancy: 1/2, Time Window: 3d/4d. Active
INFO: Backup RFYXPC, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 4d/4d. Active
INFO: Backup RFX31N, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 5d/4d. Active
INFO: Backup RFV8DH, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 6d/4d. Active
INFO: Backup RFTDPK, mode: FULL, status: OK. Redundancy: 2/2, Time Window: 7d/4d. Active
INFO: On timeline 1 WAL segments between 00000001000000000000000E and 000000010000000000000020 will be removed
INFO: Logical WAL size to remove on timeline 1 : 288MB
INFO: Resident WAL size to free on timeline 1 : 208MB
INFO: There are no backups to merge by retention policy
INFO: There are no backups to delete by retention policy
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000000E 000000010000000000000024 17 272MB 1.00 8 DEGRADED
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000F
-rw------- 1 postgres postgres 352 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011
-rw------- 1 postgres postgres 355 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000012
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014
-rw------- 1 postgres postgres 355 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000015
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000018
-rw------- 1 postgres postgres 355 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001B
-rw------- 1 postgres postgres 355 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D
-rw------- 1 postgres postgres 355 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001E
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000020
-rw------- 1 postgres postgres 355 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000020.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000021
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000022
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000023
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000024
-rw------- 1 postgres postgres 355 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000023.00000060.backup
[postgres@du104 ~]$

此步骤中00000001000000000000001F被删除。

3.3.4在8月7日删除备份集和归档

[root@du104 ~]# date -s ‘2022-08-07 11:29:22’
Sun Aug 7 11:29:22 CST 2022
[root@du104 ~]# date
Sun Aug 7 11:29:24 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

全量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest -U postgres -W -b full -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RG871S, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000026 to be archived
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 1s
INFO: Validating backup RG871S
INFO: Backup RG871S data files are valid
INFO: Backup RG871S resident size: 64MB
INFO: Backup RG871S completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RG871S 2022-08-07 11:29:55+08 FULL ARCHIVE 1/0 5s 64MB 16MB 1.00 0/26000028 0/270000B8 OK
mytest 11 RG6CDS 2022-08-06 11:29:55+08 PAGE ARCHIVE 1/1 4s 184kB 16MB 1.00 0/23000060 0/240000B8 OK
mytest 11 RG4HPJ 2022-08-05 11:29:47+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/20000028 0/210000B8 OK
mytest 11 RG2N1M 2022-08-04 11:29:49+08 PAGE ARCHIVE 1/1 4s 248kB 16MB 1.00 0/1D000028 0/1E0000F0 OK
mytest 11 RG0SDJ 2022-08-03 11:29:47+08 FULL ARCHIVE 1/0 6s 64MB 16MB 1.00 0/1A000028 0/1B0000B8 OK
mytest 11 RFYXPC 2022-08-02 11:29:40+08 PAGE ARCHIVE 1/1 5s 152kB 16MB 1.00 0/17000060 0/180000B8 OK
mytest 11 RFX31N 2022-08-01 11:29:51+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/14000028 0/150000B8 OK
mytest 11 RFV8DH 2022-07-31 11:29:45+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/11000028 0/120000B8 OK
mytest 11 RFTDPK 2022-07-30 11:29:47+08 FULL ARCHIVE 1/0 4s 64MB 16MB 1.00 0/E000028 0/F0000B8 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000000E 000000010000000000000027 20 320MB 1.00 9 DEGRADED
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E
-rw------- 1 postgres postgres 16777216 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000F
-rw------- 1 postgres postgres 352 Jul 30 11:29 /data/backup/wal/mytest/00000001000000000000000E.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011
-rw------- 1 postgres postgres 355 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000011.00000028.backup
-rw------- 1 postgres postgres 16777216 Jul 31 11:29 /data/backup/wal/mytest/000000010000000000000012
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014
-rw------- 1 postgres postgres 355 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000014.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 1 11:29 /data/backup/wal/mytest/000000010000000000000015
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017
-rw------- 1 postgres postgres 16777216 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000018
-rw------- 1 postgres postgres 355 Aug 2 11:29 /data/backup/wal/mytest/000000010000000000000017.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001B
-rw------- 1 postgres postgres 355 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D
-rw------- 1 postgres postgres 355 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001E
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000020
-rw------- 1 postgres postgres 355 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000020.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000021
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000022
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000023
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000024
-rw------- 1 postgres postgres 355 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000023.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 7 11:29 /data/backup/wal/mytest/000000010000000000000025
-rw------- 1 postgres postgres 16777216 Aug 7 11:29 /data/backup/wal/mytest/000000010000000000000026
-rw------- 1 postgres postgres 16777216 Aug 7 11:29 /data/backup/wal/mytest/000000010000000000000027
-rw------- 1 postgres postgres 355 Aug 7 11:29 /data/backup/wal/mytest/000000010000000000000026.00000028.backup
[postgres@du104 ~]$
删除备份集和归档
[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest --delete-expired --delete-wal --wal-depth=2
INFO: Evaluate backups by retention
INFO: Backup RG871S, mode: FULL, status: OK. Redundancy: 1/2, Time Window: 0d/4d. Active
INFO: Backup RG6CDS, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 1d/4d. Active
INFO: Backup RG4HPJ, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 2d/4d. Active
INFO: Backup RG2N1M, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 3d/4d. Active
INFO: Backup RG0SDJ, mode: FULL, status: OK. Redundancy: 2/2, Time Window: 4d/4d. Active
INFO: Backup RFYXPC, mode: PAGE, status: OK. Redundancy: 3/2, Time Window: 5d/4d. Expired
INFO: Backup RFX31N, mode: PAGE, status: OK. Redundancy: 3/2, Time Window: 6d/4d. Expired
INFO: Backup RFV8DH, mode: PAGE, status: OK. Redundancy: 3/2, Time Window: 7d/4d. Expired
INFO: Backup RFTDPK, mode: FULL, status: OK. Redundancy: 3/2, Time Window: 8d/4d. Expired
INFO: Delete: RFYXPC 2022-08-02 11:29:40+08
INFO: Delete: RFX31N 2022-08-01 11:29:51+08
INFO: Delete: RFV8DH 2022-07-31 11:29:45+08
INFO: Delete: RFTDPK 2022-07-30 11:29:47+08
INFO: On timeline 1 WAL segments between 00000001000000000000000E and 000000010000000000000023 will be removed
INFO: Logical WAL size to remove on timeline 1 : 336MB
INFO: Resident WAL size to free on timeline 1 : 240MB
INFO: There are no backups to merge by retention policy
INFO: Purging finished
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000001A 000000010000000000000027 11 176MB 1.00 5 DEGRADED
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A
-rw------- 1 postgres postgres 16777216 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001B
-rw------- 1 postgres postgres 355 Aug 3 11:29 /data/backup/wal/mytest/00000001000000000000001A.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D
-rw------- 1 postgres postgres 355 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001D.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 4 11:29 /data/backup/wal/mytest/00000001000000000000001E
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000020
-rw------- 1 postgres postgres 355 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000020.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 5 11:29 /data/backup/wal/mytest/000000010000000000000021
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000023
-rw------- 1 postgres postgres 16777216 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000024
-rw------- 1 postgres postgres 355 Aug 6 11:29 /data/backup/wal/mytest/000000010000000000000023.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 7 11:29 /data/backup/wal/mytest/000000010000000000000025
-rw------- 1 postgres postgres 16777216 Aug 7 11:29 /data/backup/wal/mytest/000000010000000000000026
-rw------- 1 postgres postgres 16777216 Aug 7 11:29 /data/backup/wal/mytest/000000010000000000000027
-rw------- 1 postgres postgres 355 Aug 7 11:29 /data/backup/wal/mytest/000000010000000000000026.00000028.backup
[postgres@du104 ~]$
此步骤中那些expired的备份集对应的归档和000000010000000000000022被删除。

3.3.5结论

1.在archive备份模式下，删除备份集和wal归档的时候，添加wal-depth参数，会根据wal-depth参数具体的值保留恢复到多少个备份集任意时间点之间所需的归档。但除了保留这些归档之外，还会根据保留策略，为了保证备份集的可用，需要每个备份集保留Start LSN and到 Stop LSN 之间的wal归档。
2.wal-depth的值，具体是指保留多少次备份之间的wal归档。

官方文档解释如下：
To configure WAL archive retention policy, you have to run the set-config command with the --wal-depth option that specifies the number of backups that can be used for PITR. This setting applies to all the timelines, so you should be able to perform PITR for the same number of backups on each timeline, if available.

4清理备份数据

清理所有的备份集和相关wal
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RG871S 2022-08-07 11:29:55+08 FULL ARCHIVE 1/0 5s 64MB 16MB 1.00 0/26000028 0/270000B8 OK
mytest 11 RG6CDS 2022-08-06 11:29:55+08 PAGE ARCHIVE 1/1 4s 184kB 16MB 1.00 0/23000060 0/240000B8 OK
mytest 11 RG4HPJ 2022-08-05 11:29:47+08 PAGE ARCHIVE 1/1 5s 184kB 16MB 1.00 0/20000028 0/210000B8 OK
mytest 11 RG2N1M 2022-08-04 11:29:49+08 PAGE ARCHIVE 1/1 4s 248kB 16MB 1.00 0/1D000028 0/1E0000F0 OK
mytest 11 RG0SDJ 2022-08-03 11:29:47+08 FULL ARCHIVE 1/0 6s 64MB 16MB 1.00 0/1A000028 0/1B0000B8 OK
[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest -i RG0SDJ
INFO: Resident data size to free by delete of backup RG0SDJ : 65MB
INFO: Delete: RG0SDJ 2022-08-03 11:29:47+08
INFO: Delete: RG2N1M 2022-08-04 11:29:49+08
INFO: Delete: RG4HPJ 2022-08-05 11:29:47+08
INFO: Delete: RG6CDS 2022-08-06 11:29:55+08
[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest -i RG871S
INFO: Resident data size to free by delete of backup RG871S : 64MB
INFO: Delete: RG871S 2022-08-07 11:29:55+08
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ ================================================================================================================= Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000001A 000000010000000000000027 11 176MB 1.00 0 DEGRADED
[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest --delete-expired --delete-wal --wal-depth=2
WARNING: Backup list is empty, retention purge and merge are problematic
INFO: On timeline 1 all files will be removed
INFO: On timeline 1 WAL segments between 00000001000000000000001A and 000000010000000000000027 will be removed
INFO: Logical WAL size to remove on timeline 1 : 224MB
INFO: Resident WAL size to free on timeline 1 : 176MB
INFO: There are no backups to merge by retention policy
INFO: There are no backups to delete by retention policy
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================= TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
ls: cannot access /data/backup/wal/mytest/*: No such file or directory
[postgres@du104 ~]$

5以STREAM模式备份

5.1模拟备份

5.1.1系统时间为8月7日全量备份

[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b full -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RG87HB, backup mode: FULL, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Wait for WAL segment /data/backup/backups/mytest/RG87HB/database/pg_wal/000000010000000000000029 to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 1s
INFO: Validating backup RG87HB
INFO: Backup RG87HB data files are valid
INFO: Backup RG87HB resident size: 80MB
INFO: Backup RG87HB completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ ================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RG87HB 2022-08-07 11:39:16+08 FULL STREAM 1/0 15s 64MB 16MB 1.00 0/29000060 0/29000198 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ =============================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000028 000000010000000000000029 2 32MB 1.00 1 OK
[postgres@du104 ~]$

5.1.2系统时间为8月8日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-08 11:29:22’
Mon Aug 8 11:29:22 CST 2022
[root@du104 ~]# date
Mon Aug 8 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：

[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RGA1Q9, backup mode: PAGE, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RG87HB
INFO: Wait for WAL segment /data/backup/wal/mytest/00000001000000000000002B to be archived
INFO: Wait for WAL segment /data/backup/backups/mytest/RGA1Q9/database/pg_wal/00000001000000000000002B to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RGA1Q9
INFO: Backup RGA1Q9 data files are valid
INFO: Backup RGA1Q9 resident size: 32MB
INFO: Backup RGA1Q9 completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RGA1Q9 2022-08-08 11:30:14+08 PAGE STREAM 1/1 14s 186kB 32MB 1.00 0/2B000060 0/2C0000B8 OK
mytest 11 RG87HB 2022-08-07 11:39:16+08 FULL STREAM 1/0 15s 64MB 16MB 1.00 0/29000060 0/29000198 OK
[postgres@du104 ~]$

5.1.3系统时间为8月9日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-09 11:29:22’
Tue Aug 9 11:29:22 CST 2022
[root@du104 ~]# date
Tue Aug 9 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RGBWDH, backup mode: PAGE, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RGA1Q9
INFO: Wait for WAL segment /data/backup/backups/mytest/RGBWDH/database/pg_wal/00000001000000000000002E to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RGBWDH
INFO: Backup RGBWDH data files are valid
INFO: Backup RGBWDH resident size: 48MB
INFO: Backup RGBWDH completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RGBWDH 2022-08-09 11:29:45+08 PAGE STREAM 1/1 9s 188kB 48MB 1.00 0/2E000028 0/2F0000B8 OK
mytest 11 RGA1Q9 2022-08-08 11:30:14+08 PAGE STREAM 1/1 14s 186kB 32MB 1.00 0/2B000060 0/2C0000B8 OK
mytest 11 RG87HB 2022-08-07 11:39:16+08 FULL STREAM 1/0 15s 64MB 16MB 1.00 0/29000060 0/29000198 OK
[postgres@du104 ~]$

5.1.4系统时间为8月10日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-10 11:29:22’
Wed Aug 10 11:29:22 CST 2022
[root@du104 ~]# date
Wed Aug 10 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RGDR1Z, backup mode: PAGE, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RGBWDH
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000031 to be archived
INFO: Wait for WAL segment /data/backup/backups/mytest/RGDR1Z/database/pg_wal/000000010000000000000031 to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RGDR1Z
INFO: Backup RGDR1Z data files are valid
INFO: Backup RGDR1Z resident size: 32MB
INFO: Backup RGDR1Z completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RGDR1Z 2022-08-10 11:30:21+08 PAGE STREAM 1/1 31s 185kB 32MB 1.00 0/31000060 0/320000B8 OK
mytest 11 RGBWDH 2022-08-09 11:29:45+08 PAGE STREAM 1/1 9s 188kB 48MB 1.00 0/2E000028 0/2F0000B8 OK
mytest 11 RGA1Q9 2022-08-08 11:30:14+08 PAGE STREAM 1/1 14s 186kB 32MB 1.00 0/2B000060 0/2C0000B8 OK
mytest 11 RG87HB 2022-08-07 11:39:16+08 FULL STREAM 1/0 15s 64MB 16MB 1.00 0/29000060 0/29000198 OK
[postgres@du104 ~]$

5.1.5系统时间为8月11日全量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-11 11:29:22’
Thu Aug 11 11:29:22 CST 2022
[root@du104 ~]# date
Thu Aug 11 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

全量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b full -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RGFLPS, backup mode: FULL, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Wait for WAL segment /data/backup/backups/mytest/RGFLPS/database/pg_wal/000000010000000000000034 to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RGFLPS
INFO: Backup RGFLPS data files are valid
INFO: Backup RGFLPS resident size: 80MB
INFO: Backup RGFLPS completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RGFLPS 2022-08-11 11:29:56+08 FULL STREAM 1/0 13s 64MB 16MB 1.00 0/34000060 0/34000198 OK
mytest 11 RGDR1Z 2022-08-10 11:30:21+08 PAGE STREAM 1/1 31s 185kB 32MB 1.00 0/31000060 0/320000B8 OK
mytest 11 RGBWDH 2022-08-09 11:29:45+08 PAGE STREAM 1/1 9s 188kB 48MB 1.00 0/2E000028 0/2F0000B8 OK
mytest 11 RGA1Q9 2022-08-08 11:30:14+08 PAGE STREAM 1/1 14s 186kB 32MB 1.00 0/2B000060 0/2C0000B8 OK
mytest 11 RG87HB 2022-08-07 11:39:16+08 FULL STREAM 1/0 15s 64MB 16MB 1.00 0/29000060 0/29000198 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000028 000000010000000000000034 13 208MB 1.00 5 OK
[postgres@du104 ~]$

5.1.6系统时间为8月12日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-12 11:29:22’
Fri Aug 12 11:29:22 CST 2022
[root@du104 ~]# date
Fri Aug 12 11:29:26 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RGHGEA, backup mode: PAGE, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RGFLPS
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000036 to be archived
INFO: Wait for WAL segment /data/backup/backups/mytest/RGHGEA/database/pg_wal/000000010000000000000036 to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RGHGEA
INFO: Backup RGHGEA data files are valid
INFO: Backup RGHGEA resident size: 32MB
INFO: Backup RGHGEA completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RGHGEA 2022-08-12 11:30:14+08 PAGE STREAM 1/1 13s 185kB 32MB 1.00 0/36000060 0/370000B8 OK
mytest 11 RGFLPS 2022-08-11 11:29:56+08 FULL STREAM 1/0 13s 64MB 16MB 1.00 0/34000060 0/34000198 OK
mytest 11 RGDR1Z 2022-08-10 11:30:21+08 PAGE STREAM 1/1 31s 185kB 32MB 1.00 0/31000060 0/320000B8 OK
mytest 11 RGBWDH 2022-08-09 11:29:45+08 PAGE STREAM 1/1 9s 188kB 48MB 1.00 0/2E000028 0/2F0000B8 OK
mytest 11 RGA1Q9 2022-08-08 11:30:14+08 PAGE STREAM 1/1 14s 186kB 32MB 1.00 0/2B000060 0/2C0000B8 OK
mytest 11 RG87HB 2022-08-07 11:39:16+08 FULL STREAM 1/0 15s 64MB 16MB 1.00 0/29000060 0/29000198 OK
[postgres@du104 ~]$

5.1.7系统时间为8月13日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-13 11:29:22’
Sat Aug 13 11:29:22 CST 2022
[root@du104 ~]# date
Sat Aug 13 11:29:22 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RGJB1G, backup mode: PAGE, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RGHGEA
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000039 to be archived
INFO: Wait for WAL segment /data/backup/backups/mytest/RGJB1G/database/pg_wal/000000010000000000000039 to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RGJB1G
INFO: Backup RGJB1G data files are valid
INFO: Backup RGJB1G resident size: 48MB
INFO: Backup RGJB1G completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RGJB1G 2022-08-13 11:29:46+08 PAGE STREAM 1/1 8s 186kB 48MB 1.00 0/39000028 0/3A0000B8 OK
mytest 11 RGHGEA 2022-08-12 11:30:14+08 PAGE STREAM 1/1 13s 185kB 32MB 1.00 0/36000060 0/370000B8 OK
mytest 11 RGFLPS 2022-08-11 11:29:56+08 FULL STREAM 1/0 13s 64MB 16MB 1.00 0/34000060 0/34000198 OK
mytest 11 RGDR1Z 2022-08-10 11:30:21+08 PAGE STREAM 1/1 31s 185kB 32MB 1.00 0/31000060 0/320000B8 OK
mytest 11 RGBWDH 2022-08-09 11:29:45+08 PAGE STREAM 1/1 9s 188kB 48MB 1.00 0/2E000028 0/2F0000B8 OK
mytest 11 RGA1Q9 2022-08-08 11:30:14+08 PAGE STREAM 1/1 14s 186kB 32MB 1.00 0/2B000060 0/2C0000B8 OK
mytest 11 RG87HB 2022-08-07 11:39:16+08 FULL STREAM 1/0 15s 64MB 16MB 1.00 0/29000060 0/29000198 OK
[postgres@du104 ~]$

5.1.8系统时间为8月14日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-14 11:29:22’
Sun Aug 14 11:29:22 CST 2022
[root@du104 ~]# date
Sun Aug 14 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

增量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RGL5PY, backup mode: PAGE, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RGJB1G
INFO: Wait for WAL segment /data/backup/wal/mytest/00000001000000000000003C to be archived
INFO: Wait for WAL segment /data/backup/backups/mytest/RGL5PY/database/pg_wal/00000001000000000000003C to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RGL5PY
INFO: Backup RGL5PY data files are valid
INFO: Backup RGL5PY resident size: 32MB
INFO: Backup RGL5PY completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RGL5PY 2022-08-14 11:30:04+08 PAGE STREAM 1/1 7s 186kB 32MB 1.00 0/3C000060 0/3D0000B8 OK
mytest 11 RGJB1G 2022-08-13 11:29:46+08 PAGE STREAM 1/1 8s 186kB 48MB 1.00 0/39000028 0/3A0000B8 OK
mytest 11 RGHGEA 2022-08-12 11:30:14+08 PAGE STREAM 1/1 13s 185kB 32MB 1.00 0/36000060 0/370000B8 OK
mytest 11 RGFLPS 2022-08-11 11:29:56+08 FULL STREAM 1/0 13s 64MB 16MB 1.00 0/34000060 0/34000198 OK
mytest 11 RGDR1Z 2022-08-10 11:30:21+08 PAGE STREAM 1/1 31s 185kB 32MB 1.00 0/31000060 0/320000B8 OK
mytest 11 RGBWDH 2022-08-09 11:29:45+08 PAGE STREAM 1/1 9s 188kB 48MB 1.00 0/2E000028 0/2F0000B8 OK
mytest 11 RGA1Q9 2022-08-08 11:30:14+08 PAGE STREAM 1/1 14s 186kB 32MB 1.00 0/2B000060 0/2C0000B8 OK
mytest 11 RG87HB 2022-08-07 11:39:16+08 FULL STREAM 1/0 15s 64MB 16MB 1.00 0/29000060 0/29000198 OK
[postgres@du104 ~]$

5.1.9系统时间为8月15日全量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-15 11:29:22’
Mon Aug 15 11:29:22 CST 2022
[root@du104 ~]# date
Mon Aug 15 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#

全量备份：
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b full -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RGN0DO, backup mode: FULL, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Wait for WAL segment /data/backup/backups/mytest/RGN0DO/database/pg_wal/00000001000000000000003F to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 2s
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 1s
INFO: Validating backup RGN0DO
INFO: Backup RGN0DO data files are valid
INFO: Backup RGN0DO resident size: 80MB
INFO: Backup RGN0DO completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RGN0DO 2022-08-15 11:29:54+08 FULL STREAM 1/0 14s 64MB 16MB 1.00 0/3F000060 0/3F000198 OK
mytest 11 RGL5PY 2022-08-14 11:30:04+08 PAGE STREAM 1/1 7s 186kB 32MB 1.00 0/3C000060 0/3D0000B8 OK
mytest 11 RGJB1G 2022-08-13 11:29:46+08 PAGE STREAM 1/1 8s 186kB 48MB 1.00 0/39000028 0/3A0000B8 OK
mytest 11 RGHGEA 2022-08-12 11:30:14+08 PAGE STREAM 1/1 13s 185kB 32MB 1.00 0/36000060 0/370000B8 OK
mytest 11 RGFLPS 2022-08-11 11:29:56+08 FULL STREAM 1/0 13s 64MB 16MB 1.00 0/34000060 0/34000198 OK
mytest 11 RGDR1Z 2022-08-10 11:30:21+08 PAGE STREAM 1/1 31s 185kB 32MB 1.00 0/31000060 0/320000B8 OK
mytest 11 RGBWDH 2022-08-09 11:29:45+08 PAGE STREAM 1/1 9s 188kB 48MB 1.00 0/2E000028 0/2F0000B8 OK
mytest 11 RGA1Q9 2022-08-08 11:30:14+08 PAGE STREAM 1/1 14s 186kB 32MB 1.00 0/2B000060 0/2C0000B8 OK
mytest 11 RG87HB 2022-08-07 11:39:16+08 FULL STREAM 1/0 15s 64MB 16MB 1.00 0/29000060 0/29000198 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000028 00000001000000000000003F 24 384MB 1.00 9 OK
[postgres@du104 ~]$

5.1.10系统时间为8月16日增量备份

修改操作系统时间：
[root@du104 ~]# date -s ‘2022-08-16 11:29:22’
Tue Aug 16 11:29:22 CST 2022
[root@du104 ~]# date
Tue Aug 16 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入：
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=#
增量备份：

[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RGOV1J, backup mode: PAGE, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RGN0DO
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000041 to be archived
INFO: Wait for WAL segment /data/backup/backups/mytest/RGOV1J/database/pg_wal/000000010000000000000041 to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RGOV1J
INFO: Backup RGOV1J data files are valid
INFO: Backup RGOV1J resident size: 48MB
INFO: Backup RGOV1J completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RGOV1J 2022-08-16 11:29:48+08 PAGE STREAM 1/1 6s 185kB 48MB 1.00 0/41000028 0/420000B8 OK
mytest 11 RGN0DO 2022-08-15 11:29:54+08 FULL STREAM 1/0 14s 64MB 16MB 1.00 0/3F000060 0/3F000198 OK
mytest 11 RGL5PY 2022-08-14 11:30:04+08 PAGE STREAM 1/1 7s 186kB 32MB 1.00 0/3C000060 0/3D0000B8 OK
mytest 11 RGJB1G 2022-08-13 11:29:46+08 PAGE STREAM 1/1 8s 186kB 48MB 1.00 0/39000028 0/3A0000B8 OK
mytest 11 RGHGEA 2022-08-12 11:30:14+08 PAGE STREAM 1/1 13s 185kB 32MB 1.00 0/36000060 0/370000B8 OK
mytest 11 RGFLPS 2022-08-11 11:29:56+08 FULL STREAM 1/0 13s 64MB 16MB 1.00 0/34000060 0/34000198 OK
mytest 11 RGDR1Z 2022-08-10 11:30:21+08 PAGE STREAM 1/1 31s 185kB 32MB 1.00 0/31000060 0/320000B8 OK
mytest 11 RGBWDH 2022-08-09 11:29:45+08 PAGE STREAM 1/1 9s 188kB 48MB 1.00 0/2E000028 0/2F0000B8 OK
mytest 11 RGA1Q9 2022-08-08 11:30:14+08 PAGE STREAM 1/1 14s 186kB 32MB 1.00 0/2B000060 0/2C0000B8 OK
mytest 11 RG87HB 2022-08-07 11:39:16+08 FULL STREAM 1/0 15s 64MB 16MB 1.00 0/29000060 0/29000198 OK
[postgres@du104 ~]$

5.2删除备份和归档

5.2.1查看备份集和wal归档

[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000028 000000010000000000000042 27 432MB 1.00 10 OK
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Aug 7 11:39 /data/backup/wal/mytest/000000010000000000000028
-rw------- 1 postgres postgres 16777216 Aug 7 11:39 /data/backup/wal/mytest/000000010000000000000029
-rw------- 1 postgres postgres 355 Aug 7 11:39 /data/backup/wal/mytest/000000010000000000000029.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 8 11:30 /data/backup/wal/mytest/00000001000000000000002A
-rw------- 1 postgres postgres 16777216 Aug 8 11:30 /data/backup/wal/mytest/00000001000000000000002B
-rw------- 1 postgres postgres 355 Aug 8 11:30 /data/backup/wal/mytest/00000001000000000000002B.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 8 11:30 /data/backup/wal/mytest/00000001000000000000002C
-rw------- 1 postgres postgres 16777216 Aug 9 11:29 /data/backup/wal/mytest/00000001000000000000002D
-rw------- 1 postgres postgres 16777216 Aug 9 11:29 /data/backup/wal/mytest/00000001000000000000002E
-rw------- 1 postgres postgres 355 Aug 9 11:29 /data/backup/wal/mytest/00000001000000000000002E.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 9 11:29 /data/backup/wal/mytest/00000001000000000000002F
-rw------- 1 postgres postgres 16777216 Aug 10 11:30 /data/backup/wal/mytest/000000010000000000000030
-rw------- 1 postgres postgres 16777216 Aug 10 11:30 /data/backup/wal/mytest/000000010000000000000031
-rw------- 1 postgres postgres 355 Aug 10 11:30 /data/backup/wal/mytest/000000010000000000000031.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 10 11:30 /data/backup/wal/mytest/000000010000000000000032
-rw------- 1 postgres postgres 16777216 Aug 11 11:29 /data/backup/wal/mytest/000000010000000000000033
-rw------- 1 postgres postgres 16777216 Aug 11 11:29 /data/backup/wal/mytest/000000010000000000000034
-rw------- 1 postgres postgres 355 Aug 11 11:29 /data/backup/wal/mytest/000000010000000000000034.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000035
-rw------- 1 postgres postgres 16777216 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000036
-rw------- 1 postgres postgres 355 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000036.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000037
-rw------- 1 postgres postgres 16777216 Aug 13 11:29 /data/backup/wal/mytest/000000010000000000000038
-rw------- 1 postgres postgres 16777216 Aug 13 11:29 /data/backup/wal/mytest/000000010000000000000039
-rw------- 1 postgres postgres 355 Aug 13 11:29 /data/backup/wal/mytest/000000010000000000000039.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 13 11:29 /data/backup/wal/mytest/00000001000000000000003A
-rw------- 1 postgres postgres 16777216 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003B
-rw------- 1 postgres postgres 16777216 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003C
-rw------- 1 postgres postgres 355 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003C.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003D
-rw------- 1 postgres postgres 16777216 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003E
-rw------- 1 postgres postgres 16777216 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003F
-rw------- 1 postgres postgres 355 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003F.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000040
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041
-rw------- 1 postgres postgres 355 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000042
[postgres@du104 ~]$

5.2.2查看保留策略

[postgres@du104 ~]$ cat /data/backup/backups/mytest/pg_probackup.conf

Backup instance information

pgdata = /data/pgdata
system-identifier = 7080519098597187188
xlog-seg-size = 16777216

Retention parameters

retention-redundancy = 2
retention-window = 4
[postgres@du104 ~]$

5.2.3删除备份集和wal

[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest --delete-expired --delete-wal
INFO: Evaluate backups by retention
INFO: Backup RGOV1J, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/4d. Active
INFO: Backup RGN0DO, mode: FULL, status: OK. Redundancy: 1/2, Time Window: 1d/4d. Active
INFO: Backup RGL5PY, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 2d/4d. Active
INFO: Backup RGJB1G, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 3d/4d. Active
INFO: Backup RGHGEA, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 4d/4d. Active
INFO: Backup RGFLPS, mode: FULL, status: OK. Redundancy: 2/2, Time Window: 5d/4d. Active
INFO: Backup RGDR1Z, mode: PAGE, status: OK. Redundancy: 3/2, Time Window: 6d/4d. Expired
INFO: Backup RGBWDH, mode: PAGE, status: OK. Redundancy: 3/2, Time Window: 7d/4d. Expired
INFO: Backup RGA1Q9, mode: PAGE, status: OK. Redundancy: 3/2, Time Window: 8d/4d. Expired
INFO: Backup RG87HB, mode: FULL, status: OK. Redundancy: 3/2, Time Window: 8d/4d. Expired
INFO: Delete: RGDR1Z 2022-08-10 11:30:21+08
INFO: Delete: RGBWDH 2022-08-09 11:29:45+08
INFO: Delete: RGA1Q9 2022-08-08 11:30:14+08
INFO: Delete: RG87HB 2022-08-07 11:39:16+08
INFO: On timeline 1 WAL segments between 000000010000000000000028 and 000000010000000000000034 will be removed
INFO: Logical WAL size to remove on timeline 1 : 192MB
INFO: Resident WAL size to free on timeline 1 : 192MB
INFO: There are no backups to merge by retention policy
INFO: Purging finished
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000034 000000010000000000000042 15 240MB 1.00 6 OK
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Aug 11 11:29 /data/backup/wal/mytest/000000010000000000000034
-rw------- 1 postgres postgres 355 Aug 11 11:29 /data/backup/wal/mytest/000000010000000000000034.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000035
-rw------- 1 postgres postgres 16777216 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000036
-rw------- 1 postgres postgres 355 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000036.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000037
-rw------- 1 postgres postgres 16777216 Aug 13 11:29 /data/backup/wal/mytest/000000010000000000000038
-rw------- 1 postgres postgres 16777216 Aug 13 11:29 /data/backup/wal/mytest/000000010000000000000039
-rw------- 1 postgres postgres 355 Aug 13 11:29 /data/backup/wal/mytest/000000010000000000000039.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 13 11:29 /data/backup/wal/mytest/00000001000000000000003A
-rw------- 1 postgres postgres 16777216 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003B
-rw------- 1 postgres postgres 16777216 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003C
-rw------- 1 postgres postgres 355 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003C.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003D
-rw------- 1 postgres postgres 16777216 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003E
-rw------- 1 postgres postgres 16777216 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003F
-rw------- 1 postgres postgres 355 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003F.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000040
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041
-rw------- 1 postgres postgres 355 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000042
[postgres@du104 ~]$

5.2.4结论

参数–delete-expired --delete-wal运行之后，由于有归档的存在可以恢复到保留备份集之间的任意时间点。

5.3参数wal-depth的使用

5.3.1在8月16日删除归档

查看归档
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Aug 11 11:29 /data/backup/wal/mytest/000000010000000000000034
-rw------- 1 postgres postgres 355 Aug 11 11:29 /data/backup/wal/mytest/000000010000000000000034.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000035
-rw------- 1 postgres postgres 16777216 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000036
-rw------- 1 postgres postgres 355 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000036.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 12 11:30 /data/backup/wal/mytest/000000010000000000000037
-rw------- 1 postgres postgres 16777216 Aug 13 11:29 /data/backup/wal/mytest/000000010000000000000038
-rw------- 1 postgres postgres 16777216 Aug 13 11:29 /data/backup/wal/mytest/000000010000000000000039
-rw------- 1 postgres postgres 355 Aug 13 11:29 /data/backup/wal/mytest/000000010000000000000039.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 13 11:29 /data/backup/wal/mytest/00000001000000000000003A
-rw------- 1 postgres postgres 16777216 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003B
-rw------- 1 postgres postgres 16777216 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003C
-rw------- 1 postgres postgres 355 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003C.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 14 11:30 /data/backup/wal/mytest/00000001000000000000003D
-rw------- 1 postgres postgres 16777216 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003E
-rw------- 1 postgres postgres 16777216 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003F
-rw------- 1 postgres postgres 355 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003F.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000040
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041
-rw------- 1 postgres postgres 355 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000042
[postgres@du104 ~]$

通过参数wal-depth删除归档

[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest --delete-expired --delete-wal --wal-depth=2
INFO: Evaluate backups by retention
INFO: Backup RGOV1J, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/4d. Active
INFO: Backup RGN0DO, mode: FULL, status: OK. Redundancy: 1/2, Time Window: 1d/4d. Active
INFO: Backup RGL5PY, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 2d/4d. Active
INFO: Backup RGJB1G, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 3d/4d. Active
INFO: Backup RGHGEA, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 4d/4d. Active
INFO: Backup RGFLPS, mode: FULL, status: OK. Redundancy: 2/2, Time Window: 5d/4d. Active
INFO: On timeline 1 WAL segments between 000000010000000000000034 and 00000001000000000000003F will be removed
INFO: Logical WAL size to remove on timeline 1 : 176MB
INFO: Resident WAL size to free on timeline 1 : 176MB
INFO: There are no backups to merge by retention policy
INFO: There are no backups to delete by retention policy
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

ARCHIVE INSTANCE ‘mytest’ =============================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000003F 000000010000000000000042 4 64MB 1.00 6 OK
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003F
-rw------- 1 postgres postgres 355 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003F.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000040
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041
-rw------- 1 postgres postgres 355 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000042
[postgres@du104 ~]$

在stream备份模式下，删除备份集和wal归档的时候，添加wal-depth参数，会根据wal-depth参数具体的值保留恢复到多少个备份集任意时间点之间所需的归档。

5.3.2在8月17日删除归档

修改操作系统时间
[root@du104 ~]# date -s ‘2022-08-17 11:29:22’
Wed Aug 17 11:29:22 CST 2022
[root@du104 ~]# date
Wed Aug 17 11:29:23 CST 2022
[root@du104 ~]#

模拟数据插入
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/43003948
(1 row)
postgres=# select pg_switch_wal();
pg_switch_wal
0/44000000
(1 row)
postgres=# select pg_switch_wal();
pg_switch_wal
0/44000000
(1 row)
postgres=#

增量备份
[postgres@du104 ~]$ pg_probackup backup -B /data/backup --instance mytest --stream -U postgres -W -b PAGE -j 4
INFO: Backup start, pg_probackup version: 2.5.6, instance: mytest, backup ID: RGQPRE, backup mode: PAGE, wal mode: STREAM, remote: false, compress-algorithm: none, compress-level: 1
Password for user postgres:
WARNING: This PostgreSQL instance was initialized without data block checksums. pg_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option ‘–data-checksums’.
WARNING: Current PostgreSQL role is superuser. It is not recommended to run pg_probackup under superuser.
INFO: wait for pg_start_backup()
INFO: Parent backup: RGOV1J
INFO: Wait for WAL segment /data/backup/wal/mytest/000000010000000000000045 to be archived
INFO: Wait for WAL segment /data/backup/backups/mytest/RGQPRE/database/pg_wal/000000010000000000000045 to be streamed
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921.lock’
WARNING: Skip hidden file: ‘/data/pgdata/.s.PGSQL.1921’
INFO: PGDATA size: 64MB
INFO: Extracting pagemap of changed blocks
INFO: Pagemap successfully extracted, time elapsed: 0 sec
INFO: Start transferring data files
INFO: Data files are transferred, time elapsed: 0
INFO: wait for pg_stop_backup()
INFO: pg_stop backup() successfully executed
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RGQPRE
INFO: Backup RGQPRE data files are valid
INFO: Backup RGQPRE resident size: 32MB
INFO: Backup RGQPRE completed
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

mytest 11 RGQPRE 2022-08-17 11:30:57+08 PAGE STREAM 1/1 16s 187kB 32MB 1.00 0/45000060 0/460000B8 OK
mytest 11 RGOV1J 2022-08-16 11:29:48+08 PAGE STREAM 1/1 6s 185kB 48MB 1.00 0/41000028 0/420000B8 OK
mytest 11 RGN0DO 2022-08-15 11:29:54+08 FULL STREAM 1/0 14s 64MB 16MB 1.00 0/3F000060 0/3F000198 OK
mytest 11 RGL5PY 2022-08-14 11:30:04+08 PAGE STREAM 1/1 7s 186kB 32MB 1.00 0/3C000060 0/3D0000B8 OK
mytest 11 RGJB1G 2022-08-13 11:29:46+08 PAGE STREAM 1/1 8s 186kB 48MB 1.00 0/39000028 0/3A0000B8 OK
mytest 11 RGHGEA 2022-08-12 11:30:14+08 PAGE STREAM 1/1 13s 185kB 32MB 1.00 0/36000060 0/370000B8 OK
mytest 11 RGFLPS 2022-08-11 11:29:56+08 FULL STREAM 1/0 13s 64MB 16MB 1.00 0/34000060 0/34000198 OK
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 00000001000000000000003F 000000010000000000000046 8 128MB 1.00 7 OK
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003F
-rw------- 1 postgres postgres 355 Aug 15 11:29 /data/backup/wal/mytest/00000001000000000000003F.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000040
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041
-rw------- 1 postgres postgres 355 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000042
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000043
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000044
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000045
-rw------- 1 postgres postgres 355 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000045.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000046
[postgres@du104 ~]$

删除归档
[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest --delete-expired --delete-wal --wal-depth=2
INFO: Evaluate backups by retention
INFO: Backup RGQPRE, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/4d. Active
INFO: Backup RGOV1J, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 1d/4d. Active
INFO: Backup RGN0DO, mode: FULL, status: OK. Redundancy: 1/2, Time Window: 2d/4d. Active
INFO: Backup RGL5PY, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 3d/4d. Active
INFO: Backup RGJB1G, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 4d/4d. Active
INFO: Backup RGHGEA, mode: PAGE, status: OK. Redundancy: 2/2, Time Window: 5d/4d. Active
INFO: Backup RGFLPS, mode: FULL, status: OK. Redundancy: 2/2, Time Window: 6d/4d. Active
INFO: On timeline 1 WAL segments between 00000001000000000000003F and 000000010000000000000041 will be removed
INFO: Logical WAL size to remove on timeline 1 : 32MB
INFO: Resident WAL size to free on timeline 1 : 32MB
INFO: There are no backups to merge by retention policy
INFO: There are no backups to delete by retention policy
[postgres@du104 ~]$ pg_probackup show -B /data/backup

BACKUP INSTANCE ‘mytest’ =================================================================================================================================== Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status

ARCHIVE INSTANCE ‘mytest’ =============================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000041 000000010000000000000046 6 96MB 1.00 7 OK
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041
-rw------- 1 postgres postgres 355 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000041.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug 16 11:29 /data/backup/wal/mytest/000000010000000000000042
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000043
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000044
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000045
-rw------- 1 postgres postgres 355 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000045.00000060.backup
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000046
[postgres@du104 ~]$

5.3.3结论

1.在stream备份模式下，删除备份集和wal归档的时候，添加wal-depth参数，会根据wal-depth参数具体的值保留恢复到多少个备份集任意时间点之间所需的归档。
2.wal-depth的值，具体是指保留多少次备份之间的wal归档。

6没有备份

清理备份和归档之后操作

6.1模拟归档

6.1.1系统时间为8月17日归档

postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/50000158
(1 row)

postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/51000120
(1 row)

postgres=# checkpoint;
CHECKPOINT
postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/520042D0
(1 row)

postgres=# select pg_switch_wal();
pg_switch_wal
0/53000000
(1 row)

postgres=#

查看归档
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ =============================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000051 000000010000000000000052 2 32MB 1.00 0 OK
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Aug 17 11:29 /data/backup/wal/mytest/000000010000000000000051
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000052
[postgres@du104 ~]$

6.1.2系统时间为8月18日归档

[root@du104 ~]# date -s ‘2022-08-18 11:29:22’
Thu Aug 18 11:29:22 CST 2022
[root@du104 ~]# date
Thu Aug 18 11:29:23 CST 2022
[root@du104 ~]#

postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/530043E0
(1 row)

postgres=# select pg_switch_wal();
pg_switch_wal
0/54000000
(1 row)

postgres=#
查看归档
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ =============================================================================================================================== TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000051 000000010000000000000053 3 48MB 1.00 0 OK
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Aug 17 11:29 /data/backup/wal/mytest/000000010000000000000051
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000052
-rw------- 1 postgres postgres 16777216 Aug 18 11:29 /data/backup/wal/mytest/000000010000000000000053
[postgres@du104 ~]$

6.1.3系统时间为8月19日归档

[root@du104 ~]# date -s ‘2022-08-19 11:29:22’
Fri Aug 19 11:29:22 CST 2022
[root@du104 ~]# date
Fri Aug 19 11:29:22 CST 2022
[root@du104 ~]#

postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=# checkpoint;
CHECKPOINT
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/54004640
(1 row)

postgres=# select pg_switch_wal();
pg_switch_wal
0/55000000
(1 row)

postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/550000E8
(1 row)

postgres=#

6.1.4系统时间为8月20日归档

[root@du104 ~]# date -s ‘2022-08-20 11:29:22’
Sat Aug 20 11:29:22 CST 2022
[root@du104 ~]# date
Sat Aug 20 11:29:23 CST 2022
[root@du104 ~]#

postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/56004750
(1 row)

postgres=# select pg_switch_wal();
pg_switch_wal
0/57000000
(1 row)

postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/570000E8
(1 row)

postgres=#

6.1.5系统时间为8月21日归档

[root@du104 ~]# date -s ‘2022-08-21 11:29:22’
Sun Aug 21 11:29:22 CST 2022
[root@du104 ~]# date
Sun Aug 21 11:29:23 CST 2022
[root@du104 ~]#

postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/58004908
(1 row)

postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/590000E8
(1 row)

postgres=# insert into t select generate_series(1,10),‘test data’;
INSERT 0 10
postgres=# checkpoint;
CHECKPOINT
postgres=# select pg_switch_wal();
pg_switch_wal
0/5A004AC0
(1 row)

postgres=#

查看归档
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

1 0 0/0 000000010000000000000051 00000001000000000000005A 10 160MB 1.00 0 OK
[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
-rw------- 1 postgres postgres 16777216 Aug 17 11:29 /data/backup/wal/mytest/000000010000000000000051
-rw------- 1 postgres postgres 16777216 Aug 17 11:30 /data/backup/wal/mytest/000000010000000000000052
-rw------- 1 postgres postgres 16777216 Aug 18 11:29 /data/backup/wal/mytest/000000010000000000000053
-rw------- 1 postgres postgres 16777216 Aug 19 11:29 /data/backup/wal/mytest/000000010000000000000054
-rw------- 1 postgres postgres 16777216 Aug 19 11:29 /data/backup/wal/mytest/000000010000000000000055
-rw------- 1 postgres postgres 16777216 Aug 20 11:29 /data/backup/wal/mytest/000000010000000000000056
-rw------- 1 postgres postgres 16777216 Aug 20 11:29 /data/backup/wal/mytest/000000010000000000000057
-rw------- 1 postgres postgres 16777216 Aug 21 11:29 /data/backup/wal/mytest/000000010000000000000058
-rw------- 1 postgres postgres 16777216 Aug 21 11:29 /data/backup/wal/mytest/000000010000000000000059
-rw------- 1 postgres postgres 16777216 Aug 21 11:29 /data/backup/wal/mytest/00000001000000000000005A
[postgres@du104 ~]$

6.2清理归档

6.2.1查看现有wal归档

[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

ARCHIVE INSTANCE ‘mytest’ ================================================================================================================================ TLI Parent TLI Switchpoint Min Segno Max Segno N segments Size Zratio N backups Status

6.2.2清除wal归档

[postgres@du104 ~]$ pg_probackup delete -B /data/backup --instance mytest --delete-wal --wal-depth=2
INFO: On timeline 1 all files will be removed
INFO: On timeline 1 WAL segments between 000000010000000000000051 and 00000001000000000000005A will be removed
INFO: Logical WAL size to remove on timeline 1 : 160MB
INFO: Resident WAL size to free on timeline 1 : 160MB
INFO: There are no backups to merge by retention policy
INFO: There are no backups to delete by retention policy
[postgres@du104 ~]$ pg_probackup show -B /data/backup --archive

[postgres@du104 ~]$ ls -trl /data/backup/wal/mytest/*
ls: cannot access /data/backup/wal/mytest/*: No such file or directory
[postgres@du104 ~]$