Lightdb 增量备份工具lt_probackup

与postgresql数据库相比，lightdb的lt_probackup工具是安装完就自带的，无需额外的安装配置。当然postgresql自身的pg_dump和pg_basebackup，在lightdb上同样适用，只是在用法上有细小的差别，可通过help指令帮助。本文抛砖引玉，并通过一个误删小实验来展示pg_probackup工具的使用

如读者想了解pg_dump和pg_basebackup，可参见：
pg_dump

pg_basebackup

1.创建备份目录

[root@lightdb ~]# mkdir /backupsets
[root@lightdb ~]# chown lightdb.lightdb /backupsets/

2.配置归档参数

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

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

[lightdb@lightdb backupsets]$  lt_probackup backup -B /backupsets/ --instance instance_local -b full
INFO: Backup start, lt_probackup version: 2.4.15, instance: instance_local, backup ID: RFIXGS, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
WARNING: This LightDB instance was initialized without data block checksums. lt_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.
WARNING: Current LightDB role is superuser. It is not recommended to run backup or checkdb as superuser.
INFO: wait for pg_start_backup()
INFO: Wait for WAL segment /backupsets/wal/instance_local/000000010000000200000007 to be archived
WARNING: By default lt_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 000000010000000200000007 could not be archived in 300 seconds
WARNING: backup in progress, stop backup
WARNING: Backup RFIXGS is running, setting its status to ERROR
[lightdb@lightdb backupsets]$ 

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

4.初始化备份目录

[lightdb@lightdb ~]$ lt_probackup init -B /backupsets/
INFO: Backup catalog '/backupsets' successfully inited
[lightdb@lightdb ~]$ cd /backupsets/
[lightdb@lightdb backupsets]$ 
[lightdb@lightdb backupsets]$ tree
.
├── backups
└── wal

2 directories, 0 files
[lightdb@lightdb backupsets]$ 

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

5.添加本地实例

[lightdb@lightdb backupsets]$ lt_probackup add-instance -B /backupsets/ -D /data/light13.3/data/ --instance instance_local
INFO: Instance 'instance_local' successfully inited
[lightdb@lightdb backupsets]$ tree
.
├── backups
│   └── instance_local
│       └── pg_probackup.conf
└── wal
    └── instance_local

4 directories, 1 file
[lightdb@lightdb backupsets]$ 

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

6.进行备份

[lightdb@lightdb backupsets]$  lt_probackup backup -B /backupsets/ --instance instance_local -b full
INFO: Backup start, lt_probackup version: 2.4.15, instance: instance_local, backup ID: RFIXWG, backup mode: FULL, wal mode: ARCHIVE, remote: false, compress-algorithm: none, compress-level: 1
WARNING: This LightDB instance was initialized without data block checksums. lt_probackup have no way to detect data block corruption without them. Reinitialize PGDATA with option '--data-checksums'.
WARNING: Current LightDB role is superuser. It is not recommended to run backup or checkdb as superuser.
INFO: wait for pg_start_backup()
INFO: Wait for WAL segment /backupsets/wal/instance_local/000000010000000300000007 to be archived
INFO: PGDATA size: 66MB
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: Wait for LSN 4/5A98 in archived WAL segment /backupsets/wal/instance_local/000000010000000400000000
INFO: Syncing backup files to disk
INFO: Backup files are synced, time elapsed: 0
INFO: Validating backup RFIXWG
INFO: Backup RFIXWG data files are valid
INFO: Backup RFIXWG resident size: 66MB
INFO: Backup RFIXWG completed

7.查看备份情况

[lightdb@lightdb backupsets]$  lt_probackup show -B /backupsets/ --instance instance_local 
=========================================================================================================================================
 Instance        Version  ID      Recovery Time           Mode  WAL Mode  TLI    Time  Data    WAL  Zratio  Start LSN   Stop LSN  Status 
=========================================================================================================================================
 instance_local  13       RFIXWG  2022-07-24 20:12:24+08  FULL  ARCHIVE   1/0     11s  66MB  512MB    1.00  3/E0000120  4/5A98    OK     
 instance_local  13       RFIXT4  ----                    FULL  ARCHIVE   1/0  1m:49s     0      0    1.00  3/60000120  0/0       ERROR  
 instance_local  13       RFIXGS  ----                    FULL  ARCHIVE   1/0   5m:8s     0      0    1.00  2/E0000120  0/0       ERROR  
 instance_local  ----     RFIXGA  ----                    FULL  ARCHIVE   0/0       0     0      0    1.00  0/0         0/0       ERROR 

8.模拟误删情景

如下在2022-07-24 20:14:59后，不小心误删了数据，想要恢复到误删前的数据状态

lightdb@postgres=# create table cs (id int);
CREATE TABLE
lightdb@postgres=# insert into cs values(11);
INSERT 0 1
lightdb@postgres=# insert into cs values(22);
INSERT 0 1
lightdb@postgres=# insert into cs values(33);
INSERT 0 1
lightdb@postgres=# select now();
              now              
-------------------------------
 2022-07-24 20:14:59.047315+08
(1 row)

lightdb@postgres=# delete from cs where id=33;
DELETE 1
lightdb@postgres=# 

9.恢复前先备份$PGDATA

[lightdb@lightdb backupsets]$ cd /data/light13.3/
[lightdb@lightdb light13.3]$ ls
data
[lightdb@lightdb light13.3]$ mv data/ databak

10.恢复

[lightdb@lightdb light13.3]$  lt_probackup restore -B /backupsets/ --instance instance_local --recovery-target-time='2022-07-24 20:14:59+08'
INFO: Validating backup RFIXWG
INFO: Backup RFIXWG data files are valid
INFO: Backup validation completed successfully on time 2022-07-24 20:14:59+08, xid 581603 and LSN 4/20053D60
INFO: Backup RFIXWG is valid.
INFO: Restoring the database from backup at 2022-07-24 20:12:16+08
INFO: Start restoring backup files. PGDATA size: 66MB
INFO: Backup files are restored. Transfered bytes: 66MB, time elapsed: 0
INFO: Restore incremental ratio (less is better): 100% (66MB/66MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 1s
INFO: Restore of backup RFIXWG completed.
[lightdb@lightdb light13.3]$ 
[lightdb@lightdb light13.3]$ lt_ctl start
waiting for server to start....2022-07-24 20:17:50.815148T  @  postmaster  00000[2022-07-24 20:17:50 CST] 0 [40726] LOG:  LightDB autoprewarm: prewarm dbnum=0
2022-07-24 20:17:50.816683T  @  postmaster  00000[2022-07-24 20:17:50 CST] 0 [40726] LOG:  pgaudit extension initialized
2022-07-24 20:17:50.946461T  @  postmaster  00000[2022-07-24 20:17:50 CST] 0 [40726] LOG:  redirecting log output to logging collector process
2022-07-24 20:17:50.946461T  @  postmaster  00000[2022-07-24 20:17:50 CST] 0 [40726] HINT:  Future log output will appear in directory "log".
.. done
server started
[lightdb@lightdb light13.3]$ lt_sql
bash: lt_sql: command not found...
[lightdb@lightdb light13.3]$ ltsql
ltsql (13.3-22.1)
Type "help" for help.

lightdb@postgres=# select * from cs;
 id 
----
 11
 22
 33
(3 rows)