PG备份之pg_probackup工具

相比与postgresql自带的pg_basebackup备份工具，pg_probackup可以实现增量备份，当然也拥有很多其他优秀的功能，如备份保留策略，备份有效性检测，并行，压缩等。本文抛砖引玉，并通过一个误删小实验来展示pg_probackup工具的使用

1.工具准备

###下载网址###
https://github.com/postgrespro/pg_probackup/tags
###选择合适版本###

2.工具安装

2.1 解压
[postgres@postgresql soft]$ unzip pg_probackup-2.5.5.zip 
[postgres@postgresql pg_probackup-2.5.5]$ ls
doc  gen_probackup_project.pl  LICENSE  Makefile  packaging  README.md  src  tests  travis

2.2 安装
# PG_CONFIG是我们pg_config程序所在路径，top_srcdir为postgres源码所在路径
[postgres@postgresql pg_probackup-2.5.5]$ make USE_PGXS=1 PG_CONFIG=/data/pg13.6/bin/pg_config  top_srcdir=/data/pg13.6

[postgres@postgresql pg_probackup-2.5.5]$ ll
total 548
drwxrwxr-x. 2 postgres postgres    111 Feb 17 20:37 doc
-rw-rw-r--. 1 postgres postgres   5005 Feb 17 20:37 gen_probackup_project.pl
-rw-rw-r--. 1 postgres postgres   1200 Feb 17 20:37 LICENSE
-rw-rw-r--. 1 postgres postgres   3396 Feb 17 20:37 Makefile
drwxrwxr-x. 6 postgres postgres    139 Feb 17 20:37 packaging
-rwxrwxr-x. 1 postgres postgres 516896 Jul 20 09:32 pg_probackup ###安装后产生
-rw-rw-r--. 1 postgres postgres  14579 Feb 17 20:37 README.md
drwxrwxr-x. 3 postgres postgres   4096 Jul 20 09:32 src
drwxrwxr-x. 4 postgres postgres   4096 Feb 17 20:37 tests
drwxrwxr-x. 2 postgres postgres    124 Feb 17 20:37 travis
2.3 将工具移动至/data/pg13.6/bin目录下
[postgres@postgresql pg_probackup-2.5.5]$ cp pg_probackup /data/pg13.6/bin/pg_probackup
[postgres@postgresql pg_probackup-2.5.5]$ pg_probackup version
pg_probackup 2.5.5 (PostgreSQL 13.6)

3.配置归档参数

wal_level = replica
archive_mode=on
archive_command='pg_probackup archive-push -B /pgback --instance instance_local --wal-file-path=%p --wal-file-name=%f'

不能使用开启归档时的参数配置(archive_command=‘test ! -f /data/archive/%f && cp %p /data/archive/%f’),否则会出现如下错误

[postgres@postgresql ~]$ pg_probackup backup -B /data/pgback/ --instance instance_local -b full
INFO: Backup start, pg_probackup version: 2.5.5, instance: instance_local, backup ID: RFIPO8, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
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/pgback/wal/instance_local/000000020000000000000068 to be archived
WARNING: By default pg_probackup assume WAL delivery method to be ARCHIVE. If continuous archiving is not set up, use '--stream' option to make autonomous backup. Otherwise check that continuous archiving works correctly.
ERROR: WAL segment 000000020000000000000068 could not be archived in 300 seconds
WARNING: backup in progress, stop backup
WARNING: Backup RFIPO8 is running, setting its status to ERROR

使用单独归档目录，即(archive_command=‘test ! -f /data/archive/%f && cp %p /data/archive/%f’)方式，在wal日志删除前会copy一份到归档目录下（日志编码会是顺序的),所以只要有一个pg_basebackup备份集，只要归档目录下有之后的所有的归档文件，是可以实现(PITR)恢复的；使用pg_probackup备份的话，所有的归档日志都存储在pg_wal目录下，旧的段在被归档之前不能被不能删除或回收，直到它们被归档。但并一定所有的日志编码都存在（再多次切换日志后，日志编码顺序存在断层），笔者猜测是在检查点之后，wal文件进行了合并。

4.初始化备份目录

[postgres@postgresql data]$ pg_probackup init -B /data/pgback/
INFO: Backup catalog '/data/pgback' successfully inited
[postgres@postgresql data]$ cd /data/pgbuckup/
[postgres@postgresql data]$ cd pgback/
[postgres@postgresql pgback]$ tree
.
├── backups
└── wal
2 directories, 0 files

可以看到在pgback下生成了backups和wal两个空的子目录

5.添加本地实例

[postgres@postgresql pgback]$ pg_probackup add-instance -B /data/pgback/ -D /data/pg13.6/data/ --instance instance_local
INFO: Instance 'instance_local' successfully inited
[postgres@postgresql pgback]$ tree
.
├── backups
│   └── instance_local
│       └── pg_probackup.conf
└── wal
    └── instance_local

4 directories, 1 file

在初始化目录生成的两个子目录下分别生成实例 instance_local目录，用以存放备份文件，并且backups/instance_local/多出一个文件pg_probackup.conf，是备份参数的配置，可使用pg_probackup set-config进行定制配置，这里不做阐述

6.进行备份

[postgres@postgresql pgback]$  pg_probackup backup -B /data/pgback/ --instance instance_local -b full
INFO: Backup start, pg_probackup version: 2.5.5, instance: instance_local, backup ID: RFHB0T, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
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/pgback/wal/instance_local/000000010000000000000064 to be archived
WARNING: Skip hidden file: '/data/pg13.6/data/.postgresql.conf.swp'
INFO: PGDATA size: 23MB
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 RFHB0T
INFO: Backup RFHB0T data files are valid
INFO: Backup RFHB0T resident size: 23MB
INFO: Backup RFHB0T completed

7.查看备份情况

[postgres@postgresql pgback]$  pg_probackup show -B /data/pgback/ --instance instance_local
========================================================================================================================================
 Instance        Version  ID      Recovery Time           Mode  WAL Mode  TLI  Time  Data   WAL  Zratio  Start LSN   Stop LSN    Status 
========================================================================================================================================
 instance_local  13       RFHB0T  2022-07-23 23:00:30+08  FULL  ARCHIVE   1/0    2s  23MB  16MB    1.00  0/64000028  0/650000B8  OK     
 instance_local  13       RFHAZS  ----                    FULL  ARCHIVE   1/0    3s     0     0    1.00  0/60000028  0/0         ERROR  
 instance_local  13       RFHAYB  ----                    FULL  ARCHIVE   1/0   51s     0     0    1.00  0/5E000028  0/0         ERROR  

8.模拟误删情景

如下在2022-07-23 23:09:03后，不小心误删了数据，想要恢复到误删前的数据状态

postgres=# create table cs (id int);
CREATE TABLE
postgres=# select now();
              now              
-------------------------------
 2022-07-23 23:08:31.131677+08
(1 row)

postgres=# insert into cs values (1);
INSERT 0 1
postgres=# insert into cs values (2);
INSERT 0 1
postgres=# select now();
              now              
-------------------------------
 2022-07-23 23:09:03.123938+08
(1 row)

postgres=# delete from cs where id =1;
DELETE 1
postgres=# select * from cs;
 id 
----
  2
(1 row)

9.恢复前备份$PGDATA

[postgres@postgresql data]$ pg_ctl stop
[postgres@postgresql data]$ pwd
/data/pg13.6/data
[postgres@postgresql data]$ cd ..
[postgres@postgresql pg13.6]$ mv data databak

10.恢复

[postgres@postgresql ~]$ pg_probackup restore -B /data/pgback/ --instance instance_local --recovery-target-time='2022-07-23 23:09:03+08'
INFO: Validating backup RFHB0T
INFO: Backup RFHB0T data files are valid
INFO: Backup validation completed successfully on time 2022-07-23 23:09:31+08, xid 490 and LSN 0/66013EA8
INFO: Backup RFHB0T is valid.
INFO: Restoring the database from backup at 2022-07-23 23:00:29+08
INFO: Start restoring backup files. PGDATA size: 23MB
INFO: Backup files are restored. Transfered bytes: 23MB, time elapsed: 0
INFO: Restore incremental ratio (less is better): 100% (23MB/23MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 0
INFO: Restore of backup RFHB0T completed.
[postgres@postgresql ~]$ 
[postgres@postgresql ~]$ 
[postgres@postgresql ~]$ 
[postgres@postgresql ~]$ 
[postgres@postgresql ~]$ pg_ctl start
waiting for server to start....2022-07-23 23:25:02.358 CST [8686] LOG:  starting PostgreSQL 13.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-07-23 23:25:02.360 CST [8686] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-07-23 23:25:02.360 CST [8686] LOG:  listening on IPv6 address "::", port 5432
2022-07-23 23:25:02.361 CST [8686] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-07-23 23:25:02.364 CST [8687] LOG:  database system was interrupted; last known up at 2022-07-23 23:00:29 CST
2022-07-23 23:25:02.365 CST [8687] LOG:  creating missing WAL directory "pg_wal/archive_status"
2022-07-23 23:25:02.371 CST [8687] LOG:  starting point-in-time recovery to 2022-07-23 23:09:03+08
INFO: pg_probackup archive-get WAL file: 000000010000000000000064, remote: none, threads: 1/1, batch: 1
INFO: pg_probackup archive-get copied WAL file 000000010000000000000064
INFO: pg_probackup archive-get completed successfully, fetched: 1/1, time elapsed: 85ms
2022-07-23 23:25:02.462 CST [8687] LOG:  restored log file "000000010000000000000064" from archive
2022-07-23 23:25:02.474 CST [8687] LOG:  redo starts at 0/64000028
INFO: pg_probackup archive-get WAL file: 000000010000000000000065, remote: none, threads: 1/1, batch: 1
INFO: pg_probackup archive-get copied WAL file 000000010000000000000065
INFO: pg_probackup archive-get completed successfully, fetched: 1/1, time elapsed: 61ms
2022-07-23 23:25:02.543 CST [8687] LOG:  restored log file "000000010000000000000065" from archive
2022-07-23 23:25:02.554 CST [8687] LOG:  consistent recovery state reached at 0/650000B8
2022-07-23 23:25:02.554 CST [8686] LOG:  database system is ready to accept read only connections
INFO: pg_probackup archive-get WAL file: 000000010000000000000066, remote: none, threads: 1/1, batch: 1
INFO: pg_probackup archive-get copied WAL file 000000010000000000000066
INFO: pg_probackup archive-get completed successfully, fetched: 1/1, time elapsed: 43ms
2022-07-23 23:25:02.602 CST [8687] LOG:  restored log file "000000010000000000000066" from archive
2022-07-23 23:25:02.614 CST [8687] LOG:  recovery stopping before commit of transaction 490, time 2022-07-23 23:09:31.34119+08
2022-07-23 23:25:02.614 CST [8687] LOG:  pausing at the end of recovery
2022-07-23 23:25:02.614 CST [8687] HINT:  Execute pg_wal_replay_resume() to promote.
 done
server started
[postgres@postgresql ~]$ 
[postgres@postgresql ~]$ 
[postgres@postgresql ~]$ 
[postgres@postgresql ~]$ psql
psql (13.6)
Type "help" for help.

postgres=# select * from cs;
 id 
----
  1
  2
(2 rows)