一主两从高可用

1.备份、恢复

sed -i "s/#archive_mode = off/archive_mode = on/" /app/halo/postgresql/pgdata/postgresql.base.conf
sed -i "s/#archive_command = ''/archive_command = 'test ! -f \/backup\/halo\/archive_wals\/%f \&\& cp %p \/backup\/halo\/archive_wals\/%f'/" /app/halo/postgresql/pgdata/postgresql.base.conf
sed -i "s/#restore_command = ''/restore_command = 'cp \/backup\/halo\/archive_wals\/%f %p'/" /app/halo/postgresql/pgdata/postgresql.base.conf

1.1逻辑备份恢复

--启动数据库，建表，插入数据，新建用户
halotest

create database halotest;
\c halotest
CREATE TABLE RCT_BFHF_TJ (C1 INT);
INSERT INTO RCT_BFHF_TJ VALUES(3);
SELECT * FROM RCT_BFHF_TJ;

-进行逻辑导出
pg_dump -Fc -f halotest.dump halotest

--导出指定的表
pg_dump -Fp -d upbs -t upbs.test -f test.sql

psql -d halo1 -f test.sql

--导出指定的数据库
pg_dump -Fc upbs -f upbs_test.dump

pg_restore -d upbs -c --if-exists upbs_test.dump


--删除表
psql -d halotest
DROP TABLE RCT_BFHF_TJ;
SELECT * FROM RCT_BFHF_TJ;

--进行逻辑导入
pg_restore -C -d halotest halotest.dump

2物理备份恢复

模式主要涉及 2 个系统参数：
-- archive_mode = on
-- archive_command

创建归档日志目录
mkdir /data/halo/archivedir
chown halo:halo /backup/halo/archive_wals/
mkdir -p /data/halo_backup
chown halo:halo /data/halo_backup

杭州银行用的：
mkdir -p /backup/halo/archive_wals/
chown halo:halo /backup/halo/archive_wals/
mkdir -p /backup/halo/db_backup/
chown halo:halo /backup/halo/db_backup/

--配置环境变量
vi /home/halo/.bash_profile
export BACKUP_PATH=/backup/halo/db_backup/

source /home/halo/.bash_profile

--初始化
rman init


standby-host=HOSTNAME | 从备用主机进行备份时，选择备用主机 |
standby-port=PORT | 从备用进行备份时的备用端口
rman backup -b full -d halotest -A /backup/halo/archive_wals --standby-host=192.168.65.120 --standby-port=1921
rman validate
rman show detail
rman init
rman backup -b full -A /backup/halo/archive_wals --host=192.168.65.122 --standby-host=192.168.65.122 --standby-port=1921
rman validate
rman show detail



-- 建表，并插入数据
psql -d halotest
CREATE TABLE TEST(a int);
INSERT INTO TEST VALUES(3);

2.1全量备份

rman backup -b full -d halotest -A /backup/halo/archive_wals/
rman validate
rman show detail

$ rman backup -b full -d halotest -A /backup/halo/archive_wals/
ERROR: please specify both standby host and port

$ rman backup -b full -U dbadmin -A /backup/halo/archive_wals --host=192.168.65.200 --standby-host=192.168.65.201 --standby-port=1921
Password for user dbadmin:
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'rman validate' to verify the files are correctly copied.

$ rman validate
INFO: validate: "2024-11-11 14:47:53" backup and archive log files by SIZE
INFO: backup "2024-11-11 14:47:53" is valid
$ rman show detail
======================================================================================================================
StartTime EndTime Mode Data ArcLog SrvLog Total Compressed CurTLI ParentTLI Status
======================================================================================================================
2024-11-11 14:47:53 2024-11-11 14:49:32 FULL 195MB 0B ---- 180MB false 17 0 OK

2.2增量备份恢复

--增量备份
psql -d halotest
INSERT INTO TEST VALUES(4);
rman backup -b incremental -d halotest -A /backup/halo/archive_wals/
rman backup -b incremental -U dbadmin -d halotest -A /backup/halo/archive_wals/ --host=192.168.65.200 --standby-host=192.168.65.201 --standby-port=1921
###########
Password for user dbadmin:
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'rman validate' to verify the files are correctly copied.
###########

rman delete backup <backup_id>;
请替换 <backup_id> 为实际的备份ID或时间戳。
#eg:rman delete backup '2024-11-11 15:30:56'
#INFO: delete the backup with start time: "2024-11-11 15:30:56"

rman validate
rman show detail
======================================================================================================================
StartTime EndTime Mode Data ArcLog SrvLog Total Compressed CurTLI ParentTLI Status
======================================================================================================================
2024-11-11 15:11:01 2024-11-11 15:11:43 INCR 1009kB 0B ---- 1001kB false 17 0 OK
2024-11-11 14:47:53 2024-11-11 14:49:32 FULL 195MB 0B ---- 180MB false 17 0 OK

2.3归档备份

误删 DELETE FROM TEST;
--归档备份
#rman backup -b archive -d halo0root -A /tpdata/data/halo/archivedir
rman backup -b archive -U dbadmin -d halotest -A /backup/halo/archive_wals/ --host=192.168.65.200 -p 1921 --standby-host=192.168.65.201 --standby-port=1921
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'rman validate' to verify the files are correctly copied.
rman validate

rman show detail
======================================================================================================================
StartTime EndTime Mode Data ArcLog SrvLog Total Compressed CurTLI ParentTLI Status
======================================================================================================================
2024-11-11 16:36:16 2024-11-11 16:36:28 ARCH ---- 0B ---- 0B false 17 0 OK
2024-11-11 15:11:01 2024-11-11 15:11:43 INCR 1009kB 0B ---- 1001kB false 17 0 OK
2024-11-11 14:47:53 2024-11-11 14:49:32 FULL 195MB 0B ---- 180MB false 17 0 OK

2.4全量恢复

pg_ctl stop
#rman restore -A /backup/halo/archive_wals/
[halo201][halo][/home/halo]$ rman restore -A /backup/halo/archive_wals/
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 17 #使用当前数据库集群的时间线ID（17）作为恢复目标。
INFO: calculating timeline branches to be used to recovery target point #计算将用于恢复目标点的时间线分支。
INFO: searching latest full backup which can be used as restore start point #搜索最新的完整备份，该备份可以用作恢复的起点
INFO: found the full backup can be used as base in recovery: "2024-11-11 14:47:53" #找到了可以用作恢复基础的最新完整备份：。
INFO: copying online WAL files and server log files #复制在线WAL文件和服务器日志文件
INFO: clearing restore destination #清理恢复目标位置
INFO: validate: "2024-11-11 14:47:53" backup and archive log files by SIZE #通过大小验证“time”备份和归档日志文件
INFO: backup "2024-11-11 14:47:53" is valid #备份 有效
INFO: restoring database files from the full mode backup "2024-11-11 14:47:53" #从全量模式备份 恢复数据库文件
INFO: searching incremental backup to be restored #搜索要恢复的增量备份
INFO: validate: "2024-11-11 15:11:01" backup and archive log files by SIZE #通过大小验证备份和归档日志文件
INFO: backup "2024-11-11 15:11:01" is valid
INFO: restoring database files from the incremental mode backup "2024-11-11 15:11:01" #从增量模式备份恢复数据文件
INFO: searching backup which contained archived WAL files to be restored #搜索包含要恢复的归档WAL文件的备份
INFO: backup "2024-11-11 15:11:01" is valid
INFO: restoring WAL files from backup "2024-11-11 15:11:01"
INFO: validate: "2024-11-11 16:36:16" archive log files by SIZE
INFO: backup "2024-11-11 16:36:16" is valid
INFO: restoring WAL files from backup "2024-11-11 16:36:16"
INFO: restoring online WAL files and server log files #恢复在线WAL文件和服务器日志文件
INFO: create rman_recovery.conf for recovery-related parameters. #创建rman_recovery.conf以包含恢复相关的参数
INFO: remove an 'include' directive added by rman in postgresql.conf if exists #如果存在，移除rman在conf中添加的include指令。
INFO: append an 'include' directive in postgresql.conf for rman_recovery.conf #在postgresql.conf中追加一个 include指令以包含 rman_recovery.conf。
INFO: generating recovery.signal #生成recovery.signal
INFO: removing standby.signal if exists to restore as primary #移除standby.signal以恢复为主数据库
INFO: removed standby.signal
HINT: if you want to start as standby, additional manualsetups to make standby.signal and so on are required #您想作为备用服务器启动，需要额外手动设置以制作standby.signal等
INFO: restore complete
HINT: Recovery will start automatically when the Halo server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by rman. #恢复将在Halo服务器启动时自动开始。恢复完成后，我们建议移除由rman 配置的恢复相关参数。


pg_ctl start

2.5基于时间点恢复

[halo201][halo][/home/halo]$ rman show detail
======================================================================================================================
StartTime EndTime Mode Data ArcLog SrvLog Total Compressed CurTLI ParentTLI Status
======================================================================================================================
2024-11-11 16:36:16 2024-11-11 16:36:28 ARCH ---- 0B ---- 0B false 17 0 OK
2024-11-11 15:11:01 2024-11-11 15:11:43 INCR 1009kB 0B ---- 1001kB false 17 0 OK
2024-11-11 14:47:53 2024-11-11 14:49:32 FULL 195MB 0B ---- 180MB false 17 0 OK


rman restore --recovery-target-time '2024-11-11 15:11:01' --hard-copy -A /backup/halo/archive_wals/
pg_ctl start