目录
2、远程备份与恢复(额外服务器作为备份服务器,远程备份数据库部署服务器数据)
一、lt_probackup概述
lt_probackup 是一个用于管理LightDB数据库集群的备份和恢复工具。
备份方式有:
- 全量备份:FULL - 创建一个完整备份,其中包含要恢复的集群的所有数据文件。
- 增量备份:
- DELTA - 读取数据目录中的所有数据文件,并为自上次备份以来已更改的页面创建增量备份。
- PAGE - 根据自上次完整或增量备份以来生成的 WAL 文件创建增量备份。仅从数据文件中读取更改的块。
- PTRACK - 动态创建增量备份跟踪页面更改。
二、安装
LihtDB现已集成lt_probackup,官网地址:LightDB:更快、更稳、更懂金融的分布式关系型数据库https://www.hs.net/lightdb
以x86为例安装请参考官方安装手册。
lt_probackup路径:
$LTHOME/bin
版本检查:
$LTHOME/bin/lt_probackup version
三、使用示例
1、本地实例备份与恢复
- 初始化备份目录
$LTHOME/bin/lt_probackup init -B ${backup_dir}
初始化成功提示如下:
- 添加本地实例
# 本地实例
# backup_dir - 备份目录 LTDATA - 数据目录 instance_name - 示例名称
./lt_probackup add-instance -B ${backup_dir} -D ${LTDATA} --instance ${instance_name}
添加本地实例如下:
- 配置参数
数据库 lt_hba.conf 配置文件中对 connection 和 replication 的放通
local all all 127.0.0.1/32 md5
host all all xxx(备份机) trust
调整lightdb.conf 中archive_command配置
cd $LTDATA
vi postgresql.conf# 添加以下参数信息
archive_mode = on
archive_command = 'lt_probackup archive-push -B ${backup_dir} --instance=${instance_name} --wal-file-path=%p --wal-file-name=%f
restore_command = 'lt_probackup archive-get -B ${backup_dir} --instance=${instance_name} --wal-file-path=%p --wal-file-name=%f
添加参数如下:
- 创建备份用户
BEGIN;
CREATE ROLE lt_probackup WITH LOGIN REPLICATION;
GRANT USAGE ON SCHEMA pg_catalog TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.set_config(text, text, boolean) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean, boolean) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn() TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO lt_probackup;
COMMIT;
- 添加实例配置
./lt_probackup set-config -B ${backup_dir}
--instance=${instance_name}
--retention-window=window #备份的可供恢复时长天数
--retention-redundancy=redundancy #完整备份副本留存数量
--compress-algorithm=zlib #压缩函数: 可选值zlib, pglz(不支持多线程备份),none,默认为none
--compress-level=1 #压缩级别: 取值范围0-9(0-无压缩,9-最高压缩级别)
--merge-expired #将满足保留策略要求的最旧增量备份与其已过期的父备份合并
-h ${remote-dbHost}-p ${remote-dbPort}
-U ${remote-dbUsername}
添加配置示例:
- 备份
cd $LTHOME/bin
#全量备份
./lt_probackup backup -B ${backup_dir} --instance ${instance_name} -b FULL
#增量备份
./lt_probackup backup -B ${backup_dir} --instance ${instance_name} -b PAGE|DELTA|PTRACK
# 备份时也可以使用 -j 参数进行并发备份
./lt_probackup backup -B ${backup_dir} --instance ${instance_name} -b FULL -j ${num_threads}
备份示例如下:
全量
增量
- 查看备份记录
# 查看实例下所有记录
./lt_probackup show -B ${backup_dir} --instance ${instance_name}
# 根据备份ID查看详情
./lt_probackup show -B ${backup_dir} --instance ${instance_name} -i ${backup_id}
查看如下:
- 恢复
# 根据备份ID恢复
./lt_probackup restore -B ${backup_dir} --instance ${instance_name} -i ${backup_id}
# 不完整恢复,恢复部分database
./lt_probackup restore -B ${backup_dir} --instance ${instance_name} --db-include=${database_name1} --db-include=${database_name2}
# 按时间点恢复
./lt_probackup restore -B ${backup_dir} --instance ${instance_name} --recovery-target-time='2022-06-22 19:26:53+08'# 恢复时也可以使用 -j 参数进行并发恢复
./lt_probackup restore -B ${backup_dir} --instance ${instance_name} -i ${backup_id} -j ${num_threads}
恢复案例如下:
先停止数据库实例,备份原先的数据目录
根据备份ID恢复
恢复postgres
按时间点恢复
- 删除备份
# 按照备份ID删除(同时删除过期WAL)
./lt_probackup delete -B ${backup_dir} --instance ${instance_name} --delete-wal -i ${backup_id}
# 按照配置中的过期策略删除
./lt_probackup delete -B ${backup_dir} --instance ${instance_name} --delete-expired --delete-wal
# 按照新策略删除
./lt_probackup delete -B ${backup_dir} --instance ${instance_name} --delete-expired --delete-wal --retention-window=${window} --retention-redundancy=${redundancy}
删除案例如下:
2、远程备份与恢复(额外服务器作为备份服务器,远程备份数据库部署服务器数据)
- 在备份服务器上初始化备份目录
$LTHOME/bin/lt_probackup init -B ${backup_dir}
初始化成功提示如下:
- 添加远程实例
# 添加远程实例
# backup_dir - 备份目录 LTDATA - 远程数据目录 instance_name - 示例名称 remote_ip - 远程主机实例IP remote_ssh_port - 远程实例主机端口 remote_ssh_user - 远程用户 lt_probackup_dir - 远程实例主机lt_probackup路径
./lt_probackup add-instance -B ${backup_dir} -D ${LTDATA} --instance ${instance_name} --remote-prot=ssh --remote-host=${remote_ip} --remote-port=${remote_ssh_port} --remote-user=${remote_ssh_user} --remote-path=${lt_probackup_dir}
添加远程实例如下:
- 修改参数
数据库 lt_hba.conf 配置文件中对 connection 和 replication 的放通
local all all 127.0.0.1/32 md5
host all all xxx(备份机) trust
互信配置
# 在远程备份实例主机上执行
ssh-keygen -t rsa -P "" -f ~/.ssh/id_rsa
ssh-copy-id lightdb@${备份服务器主机_ip}
# 备份机子上执行
ssh-keygen -t rsa -P "" -f ~/.ssh/id_rsa
ssh-copy-id lightdb@${备份实例主机_ip}
执行备份并行数受SSH 连接限制,因为SSH连接数默认上限为10,当备份并行数超过10时,需要修改SSH 连接数限制。参考如下:
sudo vim /etc/ssh/sshd_config
修改 MaxStartup、MaxSessions参数,将最小连接数设置为 80(建议值为最大并行数+10)。保存后退出。
重启 sshd 服务:systemctl restart sshd 或者 service sshd reload。
MaxSessions 80
MaxStartup 80
调整postgresql.conf 中archive_command配置
cd $LTDATA
vi lightdb.conf# 添加以下参数信息
archive_mode = on
archive_command = 'lt_probackup archive-push -B ${backup_dir} --instance=${instance_name} --wal-file-path=%p --wal-file-name=%f --remote-path=/backup_sever_lthome/bin --remote-user=lightdb --remote-host=backup_sever_ip'
restore_command = 'lt_probackup archive-get -B ${backup_dir} --instance=${instance_name} --wal-file-path=%p --wal-file-name=%f --local-path=/backup_sever_lthome/bin --remote-user=lightdb --remote-host= backup_sever_ip'
archive_timeout =300
添加参数如下:
- 创建备份用户
BEGIN;
CREATE ROLE lt_probackup WITH LOGIN REPLICATION;
GRANT USAGE ON SCHEMA pg_catalog TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.set_config(text, text, boolean) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean, boolean) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn() TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO lt_probackup;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO lt_probackup;
COMMIT;
- 添加实例配置
./lt_probackup set-config -B ${backup_dir}
--instance=${instance_name}
--retention-window=window #备份的可供恢复时长天数
--retention-redundancy=redundancy #完整备份副本留存数量
--compress-algorithm=zlib #压缩函数: 可选值zlib, pglz(不支持多线程备份),none,默认为none
--compress-level=1 #压缩级别: 取值范围0-9(0-无压缩,9-最高压缩级别)
--merge-expired #将满足保留策略要求的最旧增量备份与其已过期的父备份合并
-h ${remote-dbHost}-p ${remote-dbPort}
-U ${remote-dbUsername}
--remote-host=${remote-Host}
--remote-port=${remote-Port}
--remote-user=${remote-username}
--remote-path=${lt_probackup_dir} #远程实例主机lt_probackup路径
添加配置示例:
- 备份(远程参数在实例配置中已配置,执行命令时可不带远程参数)
cd $LTHOME/bin
#全量备份
./lt_probackup backup -B ${backup_dir} --instance ${instance_name} -b FULL
#增量备份
./lt_probackup backup -B ${backup_dir} --instance ${instance_name} -b PAGE|DELTA|PTRACK
# 备份时也可以使用 -j 参数进行并发备份
./lt_probackup backup -B ${backup_dir} --instance ${instance_name} -b FULL -j ${num_threads}
备份示例如下:
全量
增量
- 查看备份记录
# 查看实例下所有记录
./lt_probackup show -B ${backup_dir} --instance ${instance_name}
# 根据备份ID查看详情
./lt_probackup show -B ${backup_dir} --instance ${instance_name} -i ${backup_id}
查看如下:
- 恢复
# 根据备份ID恢复
./lt_probackup restore -B ${backup_dir} --instance ${instance_name} -i ${backup_id}
# 不完整恢复,恢复部分database
./lt_probackup restore -B ${backup_dir} --instance ${instance_name} --db-include=${database_name1} --db-include=${database_name2}
# 按时间点恢复
./lt_probackup restore -B ${backup_dir} --instance ${instance_name} --recovery-target-time='2022-06-22 19:26:53+08'# 恢复时也可以使用 -j 参数进行并发恢复
./lt_probackup restore -B ${backup_dir} --instance ${instance_name} -i ${backup_id} -j ${num_threads}
恢复案例如下:
先停止数据库实例,备份原先的数据目录
根据备份ID恢复
恢复postgres
按时间点恢复
- 删除备份
# 按照备份ID删除(同时删除过期WAL)
./lt_probackup delete -B ${backup_dir} --instance ${instance_name} --delete-wal -i ${backup_id}
# 按照配置中的过期策略删除
./lt_probackup delete -B ${backup_dir} --instance ${instance_name} --delete-expired --delete-wal
# 按照新策略删除
./lt_probackup delete -B ${backup_dir} --instance ${instance_name} --delete-expired --delete-wal --retention-window=${window} --retention-redundancy=${redundancy}
删除案例如下: