提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
操作系统:centos 7
平台:x86_64
数据库: 已安装lightdb数据库
准备:对数据库做了全量备份(FULL)与增量备份(DELTA | PAGE)
基本语法:
[lightdb@lightdb ~]$ lt_probackup delete --help
lt_probackup delete -B backup-path --instance=instance_name
[-i backup-id | --delete-expired | --merge-expired] [--delete-wal]
[-j num-threads] [--progress]
[--retention-redundancy=retention-redundancy]
[--retention-window=retention-window]
[--wal-depth=wal-depth]
[--no-validate] [--no-sync]
-B, --backup-path=backup-path location of the backup storage area # 备份目录
--instance=instance_name name of the instance # 备份实例名称
-i, --backup-id=backup-id backup to delete # 已经备份的标识
-j, --threads=NUM number of parallel threads
--progress show progress
--no-validate disable validation during retention merge
--no-sync do not sync merged files to disk
Retention options:
--delete-expired delete backups expired according to current
retention policy # 根据当前保留策略删除过期的备份
--merge-expired merge backups expired according to current
retention policy # 根据当前保留策略删除过期备份
--delete-wal remove redundant files in WAL archive # 基于WAL归档删除
--retention-redundancy=retention-redundancy
number of full backups to keep; 0 disables; (default: 0) # 要保留的全量备份数量
--retention-window=retention-window
number of days of recoverability; 0 disables; (default: 0) # 可以恢复的时间窗口
--wal-depth=wal-depth number of latest valid backups per timeline that must
retain the ability to perform PITR; 0 disables; (default: 0)
--dry-run perform a trial run without any changes # 只是做一个尝试不做出任何不可逆的行为
--status=backup_status delete all backups with specified status
Logging options:
--log-level-console=log-level-console
level for console logging (default: info)
available options: 'off', 'error', 'warning', 'info', 'log', 'verbose'
--log-level-file=log-level-file
level for file logging (default: off)
available options: 'off', 'error', 'warning', 'info', 'log', 'verbose'
--log-filename=log-filename
filename for file logging (default: 'pg_probackup.log')
support strftime format (example: pg_probackup-%Y-%m-%d_%H%M%S.log)
--error-log-filename=error-log-filename
filename for error logging (default: none)
--log-directory=log-directory
directory for file logging (default: BACKUP_PATH/log)
--log-rotation-size=log-rotation-size
rotate logfile if its size exceeds this value; 0 disables; (default: 0)
available units: 'kB', 'MB', 'GB', 'TB' (default: kB)
--log-rotation-age=log-rotation-age
rotate logfile if its age exceeds this value; 0 disables; (default: 0)
available units: 'ms', 's', 'min', 'h', 'd' (default: min)
# 查看备份信息
[lightdb@lightdb ~]$ lt_probackup show -B /home/lightdb/backup --instance instance_192.168.76.128_5432
========================================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
========================================================================================================================================================
instance_192.168.76.128_5432 13 RET37Y 2022-07-10 21:09:52+08 FULL ARCHIVE 1/0 28s 361MB 512MB 1.00 5/120 5/2000A898 OK
instance_192.168.76.128_5432 13 RET2UT 2022-07-10 21:01:59+08 FULL ARCHIVE 1/0 27s 361MB 512MB 1.00 4/A0001690 4/C000A0C8 OK
instance_192.168.76.128_5432 13 RET2R2 2022-07-10 20:59:43+08 FULL ARCHIVE 1/0 26s 360MB 512MB 1.00 4/40000120 4/6000EE18 OK
instance_192.168.76.128_5432 13 RET2MZ 2022-07-10 20:57:14+08 FULL ARCHIVE 1/0 25s 360MB 512MB 1.00 3/E0000120 4/260C8 OK
instance_192.168.76.128_5432 13 RET2M1 2022-07-10 20:56:42+08 FULL ARCHIVE 1/0 25s 360MB 512MB 1.00 3/80000120 3/A000EEA8 OK
instance_192.168.76.128_5432 13 RET2J1 2022-07-10 20:55:01+08 FULL ARCHIVE 1/0 33s 360MB 512MB 1.00 3/20000068 3/40026F58 OK
# 做一次尝试看看是否可以删除某个backup id,这里以backup id RET37Y为例:
[lightdb@lightdb ~]$ lt_probackup delete -B /home/lightdb/backup --instance instance_192.168.76.128_5432 --dry-run -i RET37Y
INFO: Resident data size to free by delete of backup RET37Y : 361MB
# 可以删除backup id RET37Y,释放361MB磁盘空间
# 执行删除操作
[lightdb@lightdb ~]$ lt_probackup delete -B /home/lightdb/backup --instance instance_192.168.76.128_5432 -i RET37Y
INFO: Resident data size to free by delete of backup RET37Y : 361MB
INFO: Delete: RET37Y 2022-07-10 21:09:52+08
# 再次查看备份信息
[lightdb@lightdb ~]$ lt_probackup show -B /home/lightdb/backup --instance instance_192.168.76.128_5432
========================================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
========================================================================================================================================================
instance_192.168.76.128_5432 13 RET2UT 2022-07-10 21:01:59+08 FULL ARCHIVE 1/0 27s 361MB 512MB 1.00 4/A0001690 4/C000A0C8 OK
instance_192.168.76.128_5432 13 RET2R2 2022-07-10 20:59:43+08 FULL ARCHIVE 1/0 26s 360MB 512MB 1.00 4/40000120 4/6000EE18 OK
instance_192.168.76.128_5432 13 RET2MZ 2022-07-10 20:57:14+08 FULL ARCHIVE 1/0 25s 360MB 512MB 1.00 3/E0000120 4/260C8 OK
instance_192.168.76.128_5432 13 RET2M1 2022-07-10 20:56:42+08 FULL ARCHIVE 1/0 25s 360MB 512MB 1.00 3/80000120 3/A000EEA8 OK
instance_192.168.76.128_5432 13 RET2J1 2022-07-10 20:55:01+08 FULL ARCHIVE 1/0 33s 360MB 512MB 1.00 3/20000068 3/40026F58 OK
# 在备份目录下分别查看backup id RET37Y对应的文件夹是否存在
[lightdb@lightdb ~]$ ls /home/lightdb/backup/backups/instance_192.168.76.128_5432/RET37Y
ls: 无法访问/home/lightdb/backup/backups/instance_192.168.76.128_5432/RET37Y: 没有那个文件或目录
[lightdb@lightdb ~]$ ls /home/lightdb/backup/wal/instance_192.168.76.128_5432/RET37Y
ls: 无法访问/home/lightdb/backup/wal/instance_192.168.76.128_5432/RET37Y: 没有那个文件或目录
# RET37Y在备份数据目录和wal文件目录均已被删除
# 查看备份目录下wal目录下实例的WAL文件(WAL文件所在目录为backup_dir/wal/instanceName)。 本地虚拟机环境,备份目录/home/lightdb/backup 备份实例为instance_192.168.76.128_5432
[lightdb@lightdb ~]$ ls /home/lightdb/backup/wal/instance_192.168.76.128_5432/ -rltha
总用量 15G
drwx------ 3 lightdb lightdb 42 7月 10 12:23 ..
-rw------- 1 lightdb lightdb 512M 7月 10 20:51 000000010000000200000007
-rw------- 1 lightdb lightdb 512M 7月 10 20:54 000000010000000300000000
-rw------- 1 lightdb lightdb 512M 7月 10 20:54 000000010000000300000001
-rw------- 1 lightdb lightdb 512M 7月 10 20:55 000000010000000300000002
-rw------- 1 lightdb lightdb 355 7月 10 20:55 000000010000000300000001.00000068.backup
-rw------- 1 lightdb lightdb 512M 7月 10 20:56 000000010000000300000003
-rw------- 1 lightdb lightdb 512M 7月 10 20:56 000000010000000300000004
-rw------- 1 lightdb lightdb 512M 7月 10 20:56 000000010000000300000005
-rw------- 1 lightdb lightdb 355 7月 10 20:56 000000010000000300000004.00000120.backup
-rw------- 1 lightdb lightdb 512M 7月 10 20:57 000000010000000300000006
-rw------- 1 lightdb lightdb 512M 7月 10 20:57 000000010000000300000007
-rw------- 1 lightdb lightdb 352 7月 10 20:57 000000010000000300000007.00000120.backup
-rw------- 1 lightdb lightdb 512M 7月 10 20:57 000000010000000400000000
-rw------- 1 lightdb lightdb 512M 7月 10 20:59 000000010000000400000001
-rw------- 1 lightdb lightdb 512M 7月 10 20:59 000000010000000400000002
-rw------- 1 lightdb lightdb 512M 7月 10 20:59 000000010000000400000003
-rw------- 1 lightdb lightdb 355 7月 10 20:59 000000010000000400000002.00000120.backup
-rw------- 1 lightdb lightdb 512M 7月 10 21:01 000000010000000400000004
-rw------- 1 lightdb lightdb 512M 7月 10 21:01 000000010000000400000005
-rw------- 1 lightdb lightdb 355 7月 10 21:02 000000010000000400000005.00001690.backup
-rw------- 1 lightdb lightdb 512M 7月 10 21:02 000000010000000400000006
-rw------- 1 lightdb lightdb 512M 7月 10 21:09 000000010000000400000007
-rw------- 1 lightdb lightdb 512M 7月 10 21:09 000000010000000500000000
-rw------- 1 lightdb lightdb 512M 7月 10 21:09 000000010000000500000001
-rw------- 1 lightdb lightdb 346 7月 10 21:10 000000010000000500000000.00000120.backup
-rw------- 1 lightdb lightdb 512M 7月 11 12:56 000000010000000500000002
-rw------- 1 lightdb lightdb 512M 7月 11 12:56 000000010000000500000003
-rw------- 1 lightdb lightdb 355 7月 11 12:56 000000010000000500000003.00000120.backup
-rw------- 1 lightdb lightdb 512M 7月 11 12:56 000000010000000500000004
-rw------- 1 lightdb lightdb 512M 7月 11 12:59 000000010000000500000005
-rw------- 1 lightdb lightdb 512M 7月 11 12:59 000000010000000500000006
-rw------- 1 lightdb lightdb 512M 7月 11 12:59 000000010000000500000007
-rw------- 1 lightdb lightdb 355 7月 11 12:59 000000010000000500000006.00000120.backup
-rw------- 1 lightdb lightdb 512M 7月 11 13:07 000000010000000600000000
-rw------- 1 lightdb lightdb 512M 7月 11 13:07 000000010000000600000001
-rw------- 1 lightdb lightdb 512M 7月 11 13:07 000000010000000600000002
-rw------- 1 lightdb lightdb 355 7月 11 13:07 000000010000000600000001.000000F8.backup
-rw------- 1 lightdb lightdb 512M 7月 11 13:10 000000010000000600000003
drwx------ 2 lightdb lightdb 4.0K 7月 11 13:11 .
# delete命令增加选项--dry-run进行尝试不做出真实删除行为
[lightdb@lightdb ~]$ lt_probackup delete -B /home/lightdb/backup --instance instance_192.168.76.128_5432 --delete-wal --dry-run
INFO: On timeline 1 WAL segments between 000000010000000200000007 and 000000010000000400000005 can be removed # 这里是时间线1上的WAL 段可以被删除(当前没有真正执行删除)
INFO: Logical WAL size to remove on timeline 1 : 7168MB
INFO: Resident WAL size to free on timeline 1 : 7168MB
INFO: There are no backups to merge by retention policy # 保留策略,没有备份需要被合并(merge)
INFO: There are no backups to delete by retention policy # 保留策略,没有备份需要被删除
INFO: There is no WAL to purge by retention policy # 保留策略,没有WAL文件需要被清除
# 执行真正的删除动作
[lightdb@lightdb ~]$ lt_probackup delete -B /home/lightdb/backup --instance instance_192.168.76.128_5432 --delete-wal
INFO: On timeline 1 WAL segments between 000000010000000200000007 and 000000010000000400000005 will be removed # 时间线1上的WAL 段将被删除
INFO: Logical WAL size to remove on timeline 1 : 7168MB
INFO: Resident WAL size to free on timeline 1 : 7168MB
INFO: There are no backups to merge by retention policy
INFO: There are no backups to delete by retention policy
# 查看备份目录下wal目录下实例的WAL文件,发现WAL文件段区间000000010000000200000007 and 000000010000000400000005已被删除
[lightdb@lightdb ~]$ ls /home/lightdb/backup/wal/instance_192.168.76.128_5432/ -rltha
总用量 7.6G
drwx------ 3 lightdb lightdb 42 7月 10 12:23 ..
-rw------- 1 lightdb lightdb 512M 7月 10 21:01 000000010000000400000005
-rw------- 1 lightdb lightdb 355 7月 10 21:02 000000010000000400000005.00001690.backup
-rw------- 1 lightdb lightdb 512M 7月 10 21:02 000000010000000400000006
-rw------- 1 lightdb lightdb 512M 7月 10 21:09 000000010000000400000007
-rw------- 1 lightdb lightdb 512M 7月 10 21:09 000000010000000500000000
-rw------- 1 lightdb lightdb 512M 7月 10 21:09 000000010000000500000001
-rw------- 1 lightdb lightdb 346 7月 10 21:10 000000010000000500000000.00000120.backup
-rw------- 1 lightdb lightdb 512M 7月 11 12:56 000000010000000500000002
-rw------- 1 lightdb lightdb 512M 7月 11 12:56 000000010000000500000003
-rw------- 1 lightdb lightdb 355 7月 11 12:56 000000010000000500000003.00000120.backup
-rw------- 1 lightdb lightdb 512M 7月 11 12:56 000000010000000500000004
-rw------- 1 lightdb lightdb 512M 7月 11 12:59 000000010000000500000005
-rw------- 1 lightdb lightdb 512M 7月 11 12:59 000000010000000500000006
-rw------- 1 lightdb lightdb 512M 7月 11 12:59 000000010000000500000007
-rw------- 1 lightdb lightdb 355 7月 11 12:59 000000010000000500000006.00000120.backup
-rw------- 1 lightdb lightdb 512M 7月 11 13:07 000000010000000600000000
-rw------- 1 lightdb lightdb 512M 7月 11 13:07 000000010000000600000001
-rw------- 1 lightdb lightdb 512M 7月 11 13:07 000000010000000600000002
-rw------- 1 lightdb lightdb 355 7月 11 13:07 000000010000000600000001.000000F8.backup
-rw------- 1 lightdb lightdb 512M 7月 11 13:10 000000010000000600000003
drwx------ 2 lightdb lightdb 4.0K 7月 11 17:34 .
# 语法 lt_probackup delete -B backup_dir --instance instance_name --delete-expired
# 执行delete命令 添加选项 --dry-run 尝试执行一下不产生实际效果
[lightdb@lightdb ~]$ lt_probackup delete -B /home/lightdb/backup --instance instance_192.168.76.128_5432 --delete-expired --dry-run
INFO: Evaluate backups by retention
WARNING: Failed to find parent FULL backup for REUB00 # backup id REUB00 的备份状态为ERROR 在备份过程中出现了意外而终止了。
INFO: Backup REUBKR, mode: DELTA, status: OK. Redundancy: 1/2, Time Window: 0d/14d. Active
INFO: Backup REUB6L, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/14d. Active
INFO: Backup REUB0Z, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/14d. Active
INFO: Backup REUB00, mode: PAGE, status: ERROR. Redundancy: 1/2, Time Window: 0d/14d. Active
INFO: Backup RET2UT, mode: FULL, status: OK. Redundancy: 1/2, Time Window: 1d/14d. Active
INFO: There are no backups to merge by retention policy
INFO: There are no backups to delete by retention polic y
INFO: There is no WAL to purge by retention policy
lt_probackup 会搜索满足保留策略的最旧的增量备份,并将此备份与已经过期的底层全量备份和增量备份合并(merge),从而使其成为全量备份。合并完成后,剩余的过期备份将被删除。
# 基本语法:lt_probackup delete -B backup_dir --instance instance_name --delete-expired --merge-expired
[lightdb@lightdb ~]$ lt_probackup delete -B /home/lightdb/backup --instance instance_192.168.76.128_5432 --delete-expired --merge-expired
INFO: Evaluate backups by retention
INFO: Backup REUBKR, mode: DELTA, status: OK. Redundancy: 1/2, Time Window: 0d/14d. Active
INFO: Backup REUB6L, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/14d. Active
INFO: Backup REUB0Z, mode: PAGE, status: OK. Redundancy: 1/2, Time Window: 0d/14d. Active
INFO: Backup RET2UT, mode: FULL, status: OK. Redundancy: 1/2, Time Window: 1d/14d. Active
INFO: Retention merging finished
INFO: There are no backups to merge by retention policy
INFO: There are no backups to delete by retention policy
INFO: There is no WAL to purge by retention policy
**注意:**根据状态(status)来删除备份的时候忽略已经建立的保留策略。
# 以删除状态为ERROR的备份为例,查询状态为ERROR的备份列表
[lightdb@lightdb ~]$ lt_probackup show -B /home/lightdb/backup --instance instance_192.168.76.128_5432
==========================================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
==========================================================================================================================================================
instance_192.168.76.128_5432 13 REUBKR 2022-07-11 13:07:50+08 DELTA ARCHIVE 1/1 18s 4054kB 512MB 1.00 6/200000F8 6/4000A850 OK
instance_192.168.76.128_5432 13 REUB6L 2022-07-11 12:59:23+08 PAGE ARCHIVE 1/1 21s 707kB 512MB 1.00 5/C0000120 5/E000B678 OK
instance_192.168.76.128_5432 13 REUB0Z 2022-07-11 12:56:15+08 PAGE ARCHIVE 1/1 38s 21MB 512MB 1.00 5/60000120 5/800358A8 OK
instance_192.168.76.128_5432 ---- REUB00 ---- PAGE ARCHIVE 0/1 0 0 0 1.00 0/0 0/0 ERROR
instance_192.168.76.128_5432 13 RET2UT 2022-07-10 21:01:59+08 FULL ARCHIVE 1/0 27s 361MB 512MB 1.00 4/A0001690 4/C000A0C8 OK
# backup id REUB00在备份过程因为意外而终止,尝试删除该状态为ERROR的备份
# 查看backup id REUB00 在备份目录是否存在
[lightdb@lightdb ~]$ ls /home/lightdb/backup/backups/instance_192.168.76.128_5432/ -rltha
总用量 4.0K
drwx------ 3 lightdb lightdb 42 7月 10 12:23 ..
-rw-rw-r-- 1 lightdb lightdb 392 7月 10 14:26 pg_probackup.conf
drwx------ 3 lightdb lightdb 97 7月 10 22:06 RET2UT
drwx------ 3 lightdb lightdb 44 7月 11 12:55 REUB00
drwx------ 3 lightdb lightdb 97 7月 11 12:56 REUB0Z
drwx------ 3 lightdb lightdb 97 7月 11 12:59 REUB6L
drwx------ 7 lightdb lightdb 101 7月 11 13:07 .
drwx------ 3 lightdb lightdb 97 7月 11 13:07 REUBKR
# backup id REUB00在备份过程因为意外而终止,删除该状态为ERROR的备份
[lightdb@lightdb ~]$ lt_probackup delete -B /home/lightdb/backup --instance instance_192.168.76.128_5432 --dry-run --status ERROR
INFO: Deleting all backups with status 'ERROR' in dry run mode
INFO: Backup REUB00 with status ERROR can be deleted
INFO: Resident data size to free by delete of 1 backups: 0
[lightdb@lightdb ~]$ lt_probackup delete -B /home/lightdb/backup --instance instance_192.168.76.128_5432 --status ERROR
INFO: Deleting all backups with status 'ERROR'
INFO: Backup REUB00 with status ERROR will be deleted
INFO: Delete: REUB00 2022-07-11 12:55:12+08
INFO: Resident data size to free by delete of 1 backups: 0
INFO: Successfully deleted 1 backup from instance 'instance_192.168.76.128_5432'
Status— 备份状态。可能的值:
OK— 备份完整且有效。
DONE— 备份已完成,但未经验证。
RUNNING— 备份正在进行中。
MERGING— 正在合并备份。
MERGED— 备份数据文件已成功合并,但其元数据正在更新中。只有完整备份才能具有此状态。
DELETING— 正在删除备份文件。
CORRUPT— 一些备份文件已损坏。
ERROR— 备份因意外错误而中止。
ORPHAN— 备份无效,因为其父备份之一已损坏或丢失。
[lightdb@lightdb ~]$ lt_probackup show-config -B /home/lightdb/backup --instance instance_192.168.76.128_5432
# Backup instance information
pgdata = /home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster
system-identifier = 7117222465357338420
xlog-seg-size = 536870912
# Connection parameters
pgdatabase = postgres
pghost = 192.168.76.128
pgport = 5432
pguser = lt_probackup
# Replica parameters
replica-timeout = 5min
# Archive parameters
archive-timeout = 5min
# Logging parameters
log-level-console = INFO
log-level-file = OFF
log-filename = pg_probackup.log
log-rotation-size = 0TB
log-rotation-age = 0d
# Retention parameters 保留参数
retention-redundancy = 2 # 可保持的全量副本的数量
retention-window = 14 # 可恢复的时间窗口
wal-depth = 0
# Compression parameters
compress-algorithm = none
compress-level = 2
# Remote access parameters
remote-proto = ssh
# 使用--dry-run参数做一个尝试不采取真正行为,比如要删除状态为OK的备份
[lightdb@lightdb ~]$ lt_probackup delete -B /home/lightdb/backup --instance instance_192.168.76.128_5432 --dry-run --status=OK
INFO: Deleting all backups with status 'OK' in dry run mode
INFO: Backup RET37Y with status OK can be deleted
INFO: Backup RET2UT with status OK can be deleted
INFO: Backup RET2R2 with status OK can be deleted
INFO: Backup RET2MZ with status OK can be deleted
INFO: Backup RET2M1 with status OK can be deleted
INFO: Backup RET2J1 with status OK can be deleted
INFO: Resident data size to free by delete of 6 backups: 2162MB
# 查看这儿写备份其实都还在
[lightdb@lightdb ~]$ ls /home/lightdb/backup/backups/instance_192.168.76.128_5432/ -rltha
总用量 4.0K
drwx------ 3 lightdb lightdb 42 7月 10 12:23 ..
-rw-rw-r-- 1 lightdb lightdb 392 7月 10 14:26 pg_probackup.conf
drwx------ 8 lightdb lightdb 115 7月 10 21:09 .
drwx------ 3 lightdb lightdb 97 7月 10 22:06 RET37Y
drwx------ 3 lightdb lightdb 97 7月 10 22:06 RET2UT
drwx------ 3 lightdb lightdb 97 7月 10 22:06 RET2R2
drwx------ 3 lightdb lightdb 97 7月 10 22:06 RET2MZ
drwx------ 3 lightdb lightdb 97 7月 10 22:06 RET2M1
drwx------ 3 lightdb lightdb 97 7月 10 22:06 RET2J1
在使用工具lt_probackup执行备份并校验备份有效之后,要从备份中恢复数据库实例需要执行恢复命令(restore)。
语法:
[lightdb@lightdb ~]$ lt_probackup restore --help
lt_probackup restore -B backup-path --instance=instance_name
[-D pgdata-path] [-i backup-id] [-j num-threads]
[--progress] [--force] [--no-sync]
[--no-validate] [--skip-block-validation]
[-T OLDDIR=NEWDIR]
[--external-mapping=OLDDIR=NEWDIR]
[--skip-external-dirs]
[-I | --incremental-mode=none|checksum|lsn]
[--db-include dbname | --db-exclude dbname]
[--recovery-target-time=time|--recovery-target-xid=xid
|--recovery-target-lsn=lsn [--recovery-target-inclusive=boolean]]
[--recovery-target-timeline=timeline]
[--recovery-target=immediate|latest]
[--recovery-target-name=target-name]
[--recovery-target-action=pause|promote|shutdown]
[--restore-command=cmdline]
[-R | --restore-as-replica]
[--primary-conninfo=primary_conninfo]
[-S | --primary-slot-name=slotname]
[--remote-proto] [--remote-host]
[--remote-port] [--remote-path] [--remote-user]
[--ssh-options]
[--archive-host=hostname] [--archive-port=port]
[--archive-user=username]
-B, --backup-path=backup-path location of the backup storage area # 备份目录
--instance=instance_name name of the instance # 备份实例
-D, --pgdata=pgdata-path location of the database storage area # 数据库实例的数据目录
-i, --backup-id=backup-id backup to restore # 待恢复的backup id
-j, --threads=NUM number of parallel threads
--progress show progress
--force ignore invalid status of the restored backup
--no-sync do not sync restored files to disk
--no-validate disable backup validation during restore
--skip-block-validation set to validate only file-level checksum
-T, --tablespace-mapping=OLDDIR=NEWDIR
relocate the tablespace from directory OLDDIR to NEWDIR
--external-mapping=OLDDIR=NEWDIR
relocate the external directory from OLDDIR to NEWDIR
--skip-external-dirs do not restore all external directories
Incremental restore options: # 增量恢复选项
-I, --incremental-mode=none|checksum|lsn
reuse valid pages available in LTDATA if they have not changed
(default: none)
Partial restore options: # 部分恢复
--db-include dbname restore only specified databases # 只恢复指定的数据库
--db-exclude dbname do not restore specified databases # 不恢复指定的数据库
Recovery options: # 恢复参数
--recovery-target-time=time time stamp up to which recovery will proceed
--recovery-target-xid=xid transaction ID up to which recovery will proceed
--recovery-target-lsn=lsn LSN of the write-ahead log location up to which recovery will proceed
--recovery-target-inclusive=boolean
whether we stop just after the recovery target
--recovery-target-timeline=timeline
recovering into a particular timeline
--recovery-target=immediate|latest
end recovery as soon as a consistent state is reached or as late as possible
--recovery-target-name=target-name
the named restore point to which recovery will proceed
--recovery-target-action=pause|promote|shutdown
action the server should take once the recovery target is reached
(default: pause)
--restore-command=cmdline command to use as 'restore_command' in recovery.conf; 'none' disables
Standby options:
-R, --restore-as-replica write a minimal recovery.conf in the output directory
to ease setting up a standby server
--primary-conninfo=primary_conninfo
connection string to be used for establishing connection
with the primary server
-S, --primary-slot-name=slotname replication slot to be used for WAL streaming from the primary server
Logging options:
--log-level-console=log-level-console
level for console logging (default: info)
available options: 'off', 'error', 'warning', 'info', 'log', 'verbose'
--log-level-file=log-level-file
level for file logging (default: off)
available options: 'off', 'error', 'warning', 'info', 'log', 'verbose'
--log-filename=log-filename
filename for file logging (default: 'pg_probackup.log')
support strftime format (example: pg_probackup-%Y-%m-%d_%H%M%S.log)
--error-log-filename=error-log-filename
filename for error logging (default: none)
--log-directory=log-directory
directory for file logging (default: BACKUP_PATH/log)
--log-rotation-size=log-rotation-size
rotate logfile if its size exceeds this value; 0 disables; (default: 0)
available units: 'kB', 'MB', 'GB', 'TB' (default: kB)
--log-rotation-age=log-rotation-age
rotate logfile if its age exceeds this value; 0 disables; (default: 0)
available units: 'ms', 's', 'min', 'h', 'd' (default: min)
Remote options: # 远程参数
--remote-proto=protocol remote protocol to use
available options: 'ssh', 'none' (default: ssh)
--remote-host=destination remote host address or hostname
--remote-port=port remote host port (default: 22)
--remote-path=path path to directory with lt_probackup binary on remote host
(default: current binary path)
--remote-user=username user name for ssh connection (default: current user)
--ssh-options=ssh_options additional ssh options (default: none)
(example: --ssh-options='-c cipher_spec -F configfile')
Remote WAL archive options:
--archive-host=destination address or hostname for ssh connection to archive host
--archive-port=port port for ssh connection to archive host (default: 22)
--archive-user=username user name for ssh connection to archive host (default: LightDB user)
命令完成后restore,启动数据库服务。
基本使用:lt_probackup restore -B backup_dir --instance instance_name -i backup_id
backup_dir是存储所有备份文件和元信息的备份目录。
instance_name是要恢复的集群的备份实例。
backup_id 指定要恢复的备份的标识。如果省略此选项,lt_probackup将使用可用于指定实例的最新有效备份。如果指定要恢复的增量备份,lt_probackup 会自动恢复底层全量备份,然后按顺序应用所有必要的增量。
# 本地备份恢复,备份目录与数据库实例在同一个机器之上,使用可用于指定实例的最新有效备份(若该备份为增量备份,优先全量备份,再依次应用必要的增量备份)
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432
ERROR: Restore destination is not empty: "/home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster"# 待恢复的数据库实例的数据目录($LTDATA)不空则不允许进行恢复(restore)。
# 查看备份列表
[lightdb@lightdb ~]$ lt_probackup show -B /home/lightdb/backup --instance=instance_192.168.76.128_5432
==========================================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
==========================================================================================================================================================
instance_192.168.76.128_5432 13 REUBKR 2022-07-11 13:07:50+08 DELTA ARCHIVE 1/1 18s 4054kB 512MB 1.00 6/200000F8 6/4000A850 OK
instance_192.168.76.128_5432 13 REUB6L 2022-07-11 12:59:23+08 PAGE ARCHIVE 1/1 21s 707kB 512MB 1.00 5/C0000120 5/E000B678 OK
instance_192.168.76.128_5432 13 REUB0Z 2022-07-11 12:56:15+08 PAGE ARCHIVE 1/1 38s 21MB 512MB 1.00 5/60000120 5/800358A8 OK
instance_192.168.76.128_5432 13 RET2UT 2022-07-10 21:01:59+08 FULL ARCHIVE 1/0 27s 361MB 512MB 1.00 4/A0001690 4/C000A0C8 OK
# 清空数据库实例的数据目录($LTDATA)之后再次进行恢复
[lightdb@lightdb data]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432
INFO: Validating parents for backup REUBKR # 最新有效备份REUBKR为增量备份,需要应用全量备份和按顺序应用必要增量
INFO: Validating backup RET2UT # 验证备份是否有效
INFO: Backup RET2UT data files are valid
INFO: Validating backup REUB0Z
INFO: Backup REUB0Z data files are valid
INFO: Validating backup REUB6L
INFO: Backup REUB6L data files are valid
INFO: Validating backup REUBKR
INFO: Backup REUBKR data files are valid
INFO: Backup REUBKR WAL segments are valid
INFO: Backup REUBKR is valid.
INFO: Restoring the database from backup at 2022-07-11 13:07:39+08 # 从备份恢复在时间点"2022-07-11 13:07:39+08"的数据库数据
INFO: Start restoring backup files. LTDATA size: 361MB
INFO: Backup files are restored. Transfered bytes: 361MB, time elapsed: 1s
INFO: Restore incremental ratio (less is better): 100% (361MB/361MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 1s
INFO: Restore of backup REUBKR completed. # 备份标识REUBKR的恢复已完成
# 重启数据库实例(指定数据目录)
[lightdb@lightdb data]$ lt_ctl start -D $LTDATA
2022-07-11 22:27:03.297937T @ postmaster 00000[2022-07-11 22:27:03 CST] 0 [123172] LOG: LightDB autoprewarm: prewarm dbnum=0
waiting for server to start....2022-07-11 22:27:03.302378T @ postmaster 00000[2022-07-11 22:27:03 CST] 0 [123172] LOG: ltaudit extension initialized
....2022-07-11 22:27:07.938711T @ postmaster 00000[2022-07-11 22:27:03 CST] 0 [123172] LOG: redirecting log output to logging collector process
2022-07-11 22:27:07.938711T @ postmaster 00000[2022-07-11 22:27:03 CST] 0 [123172] HINT: Future log output will appear in directory "log".
.................... done
server started
使用“增量恢复选项”仅替换现有Postgres Pro数据目录中的无效和更改页面,可以显着提高从备份恢复的速度。
语法:lt_probackup restore -B backup_dir --instance instance_name -D data_dir -I incremental_mode
-D data_dir # 表示lightdb数据库的数据目录
-I incremental_mode # 增量恢复选项
CHECKSUM — 读取数据目录中的所有数据文件,验证每个页面中的 header 和 checksum,只替换无效页面以及那些 checksum 和 LSN 与备份中相应页面不匹配的页面。这是最简单的增量模式。建议默认使用。
LSN — 读取pg_control数据目录中的数据以获得重做 LSN 和重做 TLI,这允许确定历史中的一个点 (shiftpoint),其中数据目录状态从目标备份链历史转移。如果 shiftpoint 不在备份链历史的范围内,则恢复被中止。如果 shiftpoint 在备份链历史的范围内,则读取数据目录中的所有数据文件,验证每个页面中的 header 和校验和,仅替换无效页面和 LSN 大于 shiftpoint 的页面。与 CHECKSUM 相比,此模式提供了更大的加速,但需要满足两个条件。一、data_checksums必须在数据目录中启用参数(以避免由于提示位而损坏)。此条件将在增量恢复开始时检查,如果禁用校验和,操作将中止。其次,pg_control文件必须与数据目录的状态同步。在恢复开始时无法检查此条件,因此用户有责任确保pg_control包含有效信息。因此,不建议在任何情况下使用 LSN 模式,其中 pg_control 不可信或被篡改:pg_resetxlog执行后,从备份恢复后未运行恢复等。
NONE — 没有任何增量优化的常规恢复。
注意:无论选择哪种增量模式,lt_probackup 都会检查给定目标目录中的 postmaster 是否未运行并且system-identifier与备份中的相同。
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 -I checksum
INFO: Running incremental restore into nonempty directory: "/home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster"
WARNING: Postmaster with pid 1580 is running in destination directory "/home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster"
INFO: Trying to read lt_control file in destination direstory
ERROR: Incremental restore is not allowed # 增量恢复,需要停止当前数据库实例(pid 1580)
# 停止运行的数据库实例
[lightdb@lightdb defaultCluster]$ lt_ctl stop
waiting for server to shut down.......... done
server stopped
# 查看备份列表
[lightdb@lightdb defaultCluster]$ lt_probackup show -B /home/lightdb/backup --instance=instance_192.168.76.128_5432
==========================================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
==========================================================================================================================================================
instance_192.168.76.128_5432 13 REUBKR 2022-07-11 13:07:50+08 DELTA ARCHIVE 1/1 18s 4054kB 512MB 1.00 6/200000F8 6/4000A850 OK
instance_192.168.76.128_5432 13 REUB6L 2022-07-11 12:59:23+08 PAGE ARCHIVE 1/1 21s 707kB 512MB 1.00 5/C0000120 5/E000B678 OK
instance_192.168.76.128_5432 13 REUB0Z 2022-07-11 12:56:15+08 PAGE ARCHIVE 1/1 38s 21MB 512MB 1.00 5/60000120 5/800358A8 OK
instance_192.168.76.128_5432 13 RET2UT 2022-07-10 21:01:59+08 FULL ARCHIVE 1/0 27s 361MB 512MB 1.00 4/A0001690 4/C000A0C8 OK
# 再次进行增量恢复,增量恢复选项使用checksum
[lightdb@lightdb defaultCluster]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 -I checksum
INFO: Running incremental restore into nonempty directory: "/home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster"
INFO: Trying to read lt_control file in destination direstory
INFO: Validating parents for backup REUBKR
INFO: Validating backup RET2UT
INFO: Backup RET2UT data files are valid
INFO: Validating backup REUB0Z
INFO: Backup REUB0Z data files are valid
INFO: Validating backup REUB6L
INFO: Backup REUB6L data files are valid
INFO: Validating backup REUBKR
INFO: Backup REUBKR data files are valid
INFO: Backup REUBKR WAL segments are valid
INFO: Backup REUBKR is valid.
INFO: Restoring the database from backup at 2022-07-11 13:07:39+08 # 恢复在时间点2022-07-11 13:07:39+08 的备份
INFO: Extracting the content of destination directory for incremental restore
INFO: Destination directory content extracted, time elapsed: 0
INFO: Removing redundant files in destination directory
INFO: Redundant files are removed, time elapsed: 0
INFO: Start restoring backup files. LTDATA size: 361MB
INFO: Backup files are restored. Transfered bytes: 6435kB, time elapsed: 1s
INFO: Restore incremental ratio (less is better): 2% (6435kB/361MB) # 恢复增量百分比 2% 越小越好
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 0
INFO: Restore of backup REUBKR completed.
# 再次启动lightdb数据库实例
[lightdb@lightdb defaultCluster]$ lt_ctl start -D $LTDATA
2022-07-12 10:37:45.197079T @ postmaster 00000[2022-07-12 10:37:45 CST] 0 [24478] LOG: LightDB autoprewarm: prewarm dbnum=0
2022-07-12 10:37:45.199313T @ postmaster 00000[2022-07-12 10:37:45 CST] 0 [24478] LOG: ltaudit extension initialized
waiting for server to start........2022-07-12 10:37:50.013407T @ postmaster 00000[2022-07-12 10:37:45 CST] 0 [24478] LOG: redirecting log output to logging collector process
2022-07-12 10:37:50.013407T @ postmaster 00000[2022-07-12 10:37:45 CST] 0 [24478] HINT: Future log output will appear in directory "log".
.......... done
server started
如果在进行备份之前启用了部分还原,则可以使用部分还原选项(partial restore options)和还原命令(restore)仅还原部分数据库。
语法:GRANT SELECT ON TABLE pg_catalog.pg_database TO backup;
为备份用户backup赋予数据库表pg_catalog.pg_database的只读权限
#为备份用户lt_probackup设置部分还原
[lightdb@lightdb ~]$ ltsql
ltsql (13.3-22.2)
Type "help" for help.
lightdb@postgres=# \du
List of roles
Role name | Attributes | Member of
--------------+------------------------------------------------------------+-----------
em | Superuser | {}
lightdb | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
lt_probackup | | {}
lightdb@postgres=# GRANT SELECT ON TABLE pg_catalog.pg_database TO lt_probackup;
GRANT
lightdb@postgres=# \q
# 为数据库的备份用户lt_probackup赋予部分还原的权限。
--db-include dbname 恢复指定的数据库
--db-exclude dbname 排除指定数据库
# 恢复指定数据库em
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --db-include em
ERROR: Restore destination is not empty: "/home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster"
# 需要清空数据库目录之后才能进行恢复指定数据库
# 停止正在运行的数据库实例
[lightdb@lightdb ~]$ lt_ctl stop
waiting for server to shut down......... done
server stopped
# 清空数据目录 /home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster
# 恢复指定数据库em 使用--db-include选项
[lightdb@lightdb data]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --db-include em
INFO: Validating parents for backup REUBKR
INFO: Validating backup RET2UT
INFO: Backup RET2UT data files are valid
INFO: Validating backup REUB0Z
INFO: Backup REUB0Z data files are valid
INFO: Validating backup REUB6L
INFO: Backup REUB6L data files are valid
INFO: Validating backup REUBKR
INFO: Backup REUBKR data files are valid
INFO: Backup REUBKR WAL segments are valid
INFO: Backup REUBKR is valid.
INFO: Restoring the database from backup at 2022-07-11 13:07:39+08
INFO: Start restoring backup files. LTDATA size: 361MB
INFO: Backup files are restored. Transfered bytes: 320MB, time elapsed: 1s
INFO: Restore incremental ratio (less is better): 89% (320MB/361MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 0
INFO: Restore of backup REUBKR completed.
# 重启数据库实例,并进行连接
[lightdb@lightdb ~]$ lt_ctl start -D $LTDATA
2022-07-12 11:15:03.492031T @ postmaster 00000[2022-07-12 11:15:03 CST] 0 [39627] LOG: LightDB autoprewarm: prewarm dbnum=0
waiting for server to start....2022-07-12 11:15:03.496560T @ postmaster 00000[2022-07-12 11:15:03 CST] 0 [39627] LOG: ltaudit extension initialized
...2022-07-12 11:15:06.848920T @ postmaster 00000[2022-07-12 11:15:03 CST] 0 [39627] LOG: redirecting log output to logging collector process
2022-07-12 11:15:06.848920T @ postmaster 00000[2022-07-12 11:15:03 CST] 0 [39627] HINT: Future log output will appear in directory "log".
.... done
server started
[lightdb@lightdb ~]$ ltsql
ltsql: error: FATAL: "base/13592" is not a valid data directory
DETAIL: File "base/13592/LT_VERSION" does not contain valid data.
HINT: You might need to initdb. # 提示需要执行initdb命令
# 要从还原中排除一个或多个数据库,使用--db-exclude选项,部分恢复排除数据库em
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --db-exclude=em
ERROR: Restore destination is not empty: "/home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster"
[lightdb@lightdb ~]$ rm -rf /home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster # 只是本地虚拟机这么暴力删除 需要提前将该数据目录进行备份。
# 再次进行部分恢复,排除数据库em
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --db-exclude=em
INFO: Validating parents for backup REUBKR
INFO: Validating backup RET2UT
INFO: Backup RET2UT data files are valid
INFO: Validating backup REUB0Z
INFO: Backup REUB0Z data files are valid
INFO: Validating backup REUB6L
INFO: Backup REUB6L data files are valid
INFO: Validating backup REUBKR
INFO: Backup REUBKR data files are valid
INFO: Backup REUBKR WAL segments are valid
INFO: Backup REUBKR is valid.
INFO: Restoring the database from backup at 2022-07-11 13:07:39+08
INFO: Start restoring backup files. LTDATA size: 361MB
INFO: Backup files are restored. Transfered bytes: 59MB, time elapsed: 0
INFO: Restore incremental ratio (less is better): 16% (59MB/361MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 0
INFO: Restore of backup REUBKR completed.
# 启动数据库实例
[lightdb@lightdb ~]$ lt_ctl start -D $LTDATA
2022-07-12 11:19:13.184998T @ postmaster 00000[2022-07-12 11:19:13 CST] 0 [41826] LOG: LightDB autoprewarm: prewarm dbnum=0
waiting for server to start....2022-07-12 11:19:13.189054T @ postmaster 00000[2022-07-12 11:19:13 CST] 0 [41826] LOG: ltaudit extension initialized
....2022-07-12 11:19:17.402657T @ postmaster 00000[2022-07-12 11:19:13 CST] 0 [41826] LOG: redirecting log output to logging collector process
2022-07-12 11:19:17.402657T @ postmaster 00000[2022-07-12 11:19:13 CST] 0 [41826] HINT: Future log output will appear in directory "log".
...... done
server started
# 验证部分恢复之后,数据库em是否已经不存在
[lightdb@lightdb ~]$ ltsql em
ltsql: error: FATAL: "base/28848" is not a valid data directory
DETAIL: File "base/28848/LT_VERSION" does not contain valid data.
HINT: You might need to initdb.
如果在进行备份(backup命令)之前启用了连续 WAL 归档,则可以使用restore命令的恢复目标选项(recovery target options)将集群恢复到任意时间点(恢复目标)的状态。
如果配置了连续 WAL 归档,可以将这些选项之一与restore或validate命令一起使用来指定数据库实例必须恢复或验证的时刻。
–recovery-target=immediate|latest
定义何时停止恢复:
该immediate值在达到指定备份的一致状态后停止恢复,或者如果省略-i/--backup-id选项,则停止最新的可用备份。这是 STREAM 备份的默认行为。
该latest值将继续恢复,直到应用了归档中所有可用的 WAL 段。这是 ARCHIVE 备份的默认行为。
–recovery-target-timeline=timeline
指定用于恢复的特定时间线。默认情况下,使用指定备份的时间线。
–recovery-target-lsn=lsn
指定恢复将继续到的预写日志位置的 LSN。只能在恢复主版本 10 或 更高版本的数据库实例时使用。
–recovery-target-name=recovery_target_name
指定将数据库实例恢复到的命名保存点。
–recovery-target-time=time
指定恢复到的时间戳。如果未指定时区偏移量,则使用本地时区。例子:–recovery-target-time=“2020-01-01 00:00:00+03”
–recovery-target-xid=xid
指定恢复到的事务 ID。
–recovery-target-inclusive=boolean
指定是在指定的恢复目标 ( true) 之后停止,还是在恢复目标 ( false) 之前停止。此选项只能与 --recovery-target-name, --recovery-target-time, --recovery-target-lsn or --recovery-target-xid一起使用。默认值取决于recovery_target_inclusive参数。
–recovery-target-action=pause|promote|shutdown
指定达到恢复目标时服务器应采取的 recovery_target_action。默认:pause
可以同时使用 STREAM 和 ARCHIVE 备份进行时间点恢复,只要 WAL 存档至少从进行备份开始的时候可用。如果省略-i/选项, lt_probackup 会自动选择最接近指定恢复目标的备份并启动恢复过程,否则lt_probackup将尝试将指定备份(--backup-id ,-I)恢复到指定恢复目标。
# 查看备份目录列表
[lightdb@lightdb ~]$ lt_probackup show -B /home/lightdb/backup --instance=instance_192.168.76.128_5432
==========================================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
==========================================================================================================================================================
instance_192.168.76.128_5432 13 REUBKR 2022-07-11 13:07:50+08 DELTA ARCHIVE 1/1 18s 4054kB 512MB 1.00 6/200000F8 6/4000A850 OK
instance_192.168.76.128_5432 13 REUB6L 2022-07-11 12:59:23+08 PAGE ARCHIVE 1/1 21s 707kB 512MB 1.00 5/C0000120 5/E000B678 OK
instance_192.168.76.128_5432 13 REUB0Z 2022-07-11 12:56:15+08 PAGE ARCHIVE 1/1 38s 21MB 512MB 1.00 5/60000120 5/800358A8 OK
instance_192.168.76.128_5432 13 RET2UT 2022-07-10 21:01:59+08 FULL ARCHIVE 1/0 27s 361MB 512MB 1.00 4/A0001690 4/C000A0C8 OK
# 在准确时间恢复数据库状态,使用恢复目标选项--recovery-target-time以时间戳格式指定内容。
# 语法 lt_probackup restore -B backup_dir --instance instance_name --recovery-target-time="2017-05-18 14:18:11+03"
# 恢复到时间点 2022-07-11 13:00:50+08
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --recovery-target-time="2022-07-11 13:00:50+08"
ERROR: Restore destination is not empty: "/home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster"
# 清空数据目录,本次只是学习分享,已提前备份该数据库数据目录,清空该目录
[lightdb@lightdb ~]$ rm -rf /home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --recovery-target-time="2022-07-11 13:00:50+08"
INFO: Validating parents for backup REUB6L
INFO: Validating backup RET2UT
INFO: Backup RET2UT data files are valid
INFO: Validating backup REUB0Z
INFO: Backup REUB0Z data files are valid
INFO: Validating backup REUB6L
INFO: Backup REUB6L data files are valid
INFO: Backup validation completed successfully on time 2022-07-11 13:00:50+08, xid 770785 and LSN 6/306EB0 # 恢复之前验证备份在待恢复的时间点"2022-07-11 13:00:50+08"是否有效
INFO: Backup REUB6L is valid.
INFO: Restoring the database from backup at 2022-07-11 12:59:09+08
INFO: Start restoring backup files. LTDATA size: 360MB
INFO: Backup files are restored. Transfered bytes: 360MB, time elapsed: 0
INFO: Restore incremental ratio (less is better): 100% (360MB/360MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 2s
INFO: Restore of backup REUB6L completed.
# 要将集群状态恢复到特定 LSN,使用恢复目标选项--recovery-target-lsn
# 查看备份列表
[lightdb@lightdb ~]$ lt_probackup show -B /home/lightdb/backup --instance=instance_192.168.76.128_5432
==========================================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
==========================================================================================================================================================
instance_192.168.76.128_5432 13 REUBKR 2022-07-11 13:07:50+08 DELTA ARCHIVE 1/1 18s 4054kB 512MB 1.00 6/200000F8 6/4000A850 OK
instance_192.168.76.128_5432 13 REUB6L 2022-07-11 12:59:23+08 PAGE ARCHIVE 1/1 21s 707kB 512MB 1.00 5/C0000120 5/E000B678 OK
instance_192.168.76.128_5432 13 REUB0Z 2022-07-11 12:56:15+08 PAGE ARCHIVE 1/1 38s 21MB 512MB 1.00 5/60000120 5/800358A8 OK
instance_192.168.76.128_5432 13 RET2UT 2022-07-10 21:01:59+08 FULL ARCHIVE 1/0 27s 361MB 512MB 1.00 4/A0001690 4/C000A0C8 OK
# 选项lsn 5/800358A8 对应的bakckup id为 REUB0Z
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --recovery-target-lsn=5/800358A8
ERROR: Restore destination is not empty: "/home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster"
[lightdb@lightdb ~]$ rm -rf /home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster # 仅仅学习分享使用,直接删除了数据目录,测试和生产环境不要这么做
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --recovery-target-lsn=5/800358A8
INFO: Validating parents for backup REUB0Z # 增量备份,先验证其父备份
INFO: Validating backup RET2UT # 全量备份
INFO: Backup RET2UT data files are valid
INFO: Validating backup REUB0Z
INFO: Backup REUB0Z data files are valid
INFO: Backup validation completed successfully on time 2022-07-11 12:56:15+08, xid 769271 and LSN 5/800358A8
INFO: Backup REUB0Z is valid.
INFO: Restoring the database from backup at 2022-07-11 12:55:47+08
INFO: Start restoring backup files. LTDATA size: 360MB
INFO: Backup files are restored. Transfered bytes: 360MB, time elapsed: 0
INFO: Restore incremental ratio (less is better): 100% (360MB/360MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 2s
INFO: Restore of backup REUB0Z completed.
# 将数据库实例恢复到特定的事务 ID,使用恢复目标选项--recovery-target-xid选项,xid 769271 为例
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --recovery-target-xid=769271
ERROR: Restore destination is not empty: "/home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster" # 仅仅为例验证工功能,这里直接暴力删除了数据库数据目录
[lightdb@lightdb ~]$ rm -rf /home/lightdb/database/lightdb-x/13.3-22.2/data/defaultCluster
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --recovery-target-xid=769271
INFO: Validating parents for backup REUB0Z
INFO: Validating backup RET2UT
INFO: Backup RET2UT data files are valid
INFO: Validating backup REUB0Z
INFO: Backup REUB0Z data files are valid
INFO: Backup validation completed successfully on time 2022-07-11 12:56:15+08, xid 769271 and LSN 5/800358A8
INFO: Backup REUB0Z is valid.
INFO: Restoring the database from backup at 2022-07-11 12:55:47+08
INFO: Start restoring backup files. LTDATA size: 360MB
INFO: Backup files are restored. Transfered bytes: 360MB, time elapsed: 0
INFO: Restore incremental ratio (less is better): 100% (360MB/360MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 1s
INFO: Restore of backup REUB0Z completed.
# 查看备份列表
[lightdb@lightdb ~]$ lt_probackup show -B /home/lightdb/backup --instance=instance_192.168.76.128_5432
==========================================================================================================================================================
Instance Version ID Recovery Time Mode WAL Mode TLI Time Data WAL Zratio Start LSN Stop LSN Status
==========================================================================================================================================================
instance_192.168.76.128_5432 13 REUBKR 2022-07-11 13:07:50+08 DELTA ARCHIVE 1/1 18s 4054kB 512MB 1.00 6/200000F8 6/4000A850 OK
instance_192.168.76.128_5432 13 REUB6L 2022-07-11 12:59:23+08 PAGE ARCHIVE 1/1 21s 707kB 512MB 1.00 5/C0000120 5/E000B678 OK
instance_192.168.76.128_5432 13 REUB0Z 2022-07-11 12:56:15+08 PAGE ARCHIVE 1/1 38s 21MB 512MB 1.00 5/60000120 5/800358A8 OK
instance_192.168.76.128_5432 13 RET2UT 2022-07-10 21:01:59+08 FULL ARCHIVE 1/0 27s 361MB 512MB 1.00 4/A0001690 4/C000A0C8 OK
# --recovery-target=latest 直到应用了归档中所有可用的 WAL 段。这是 ARCHIVE 备份的默认行为。
[lightdb@lightdb ~]$ lt_probackup restore -B /home/lightdb/backup --instance=instance_192.168.76.128_5432 --recovery-target=latest
INFO: Validating parents for backup REUBKR
INFO: Validating backup RET2UT
INFO: Backup RET2UT data files are valid
INFO: Validating backup REUB0Z
INFO: Backup REUB0Z data files are valid
INFO: Validating backup REUB6L
INFO: Backup REUB6L data files are valid
INFO: Validating backup REUBKR
INFO: Backup REUBKR data files are valid
INFO: Backup REUBKR WAL segments are valid
INFO: Backup REUBKR is valid.
INFO: Restoring the database from backup at 2022-07-11 13:07:39+08
INFO: Start restoring backup files. LTDATA size: 361MB
INFO: Backup files are restored. Transfered bytes: 361MB, time elapsed: 0
INFO: Restore incremental ratio (less is better): 100% (361MB/361MB)
INFO: Syncing restored files to disk
INFO: Restored backup files are synced, time elapsed: 2s
INFO: Restore of backup REUBKR completed.
配置ssh免密互通基本操作:
[backup@backup_host] ssh-copy-id postgres@db_host
[postgres@db_host] ssh-copy-id backup@backup_host
backup_host 备份服务器
backup 备份服务器上的操作系统用户
db_host 数据库实例所在主机
postgres 数据库实例所在主机上的操作系统用户
# 分别在本地虚拟机192.168.76.129和192.168.76.128执行如下操作完成ssh免密互通
#ssh-keygen -t rsa -P "" -f ~/.ssh/id_rsa
#ssh-copy-id lightdb@192.168.76.129 # 128免密登录129配置
# 测试128环境的lightdb用户可以ssh免密登录129
[lightdb@lightdb_128 scripts]$ ssh 'lightdb@192.168.76.129'
Last login: Wed Jul 13 13:17:18 2022 from 192.168.76.3
/\/\
(_人|人_)
/‥\
ミ(_Y_)ミ
> <
(/ \)
_( )_
(_>―<_)
没有bug
[lightdb@lightdb_backup_129 ~]$
# 测试129环境的lightdb用户可以ssh免密登录128
[lightdb@lightdb_backup_129 ~]$ ssh 'lightdb@192.168.76.128'
Last login: Wed Jul 13 09:37:59 2022 from 192.168.76.3
___
彡 _/ ⌒ ⌒
/)) (●) (●)ヽ
|∩ (_人_) |
/ ノ、_ヽノ_ノ ̄)
/ / /フ_/
L_/\ \(
没有bug
[lightdb@lightdb_128 ~]$
使用ssh在远程模式下运行lt_probackup工作流程如下:
本地备份机器:192.168.76.129
远程机器:192.168.76.128