备份恢复PG_RMAN

二  物理备份恢复PG_RMAN

1. 需要开启归档
cat >> /postgresql/pgdata/postgresql.conf <<"EOF"
wal_level='replica'
archive_mode='on'
archive_command='test ! -f /4T/postgresql/pg12/archive/%f && cp %p /4T/postgresql/pg12/archive/%f'
restore_command='cp /4T/postgresql/pg12/archive/%f %p'
EOF

which pg_dump
%p 表示xlog文件名$PGDAT的相对路径，如pg_xlog/000000000007D     
%f表示xlog文件名，如0000000000007D

pg_ctl restart

select * from pg_settings where name in ('wal_level','archive_mode','archive_command');


pg_basebackup -h192.168.60.22 -Upostgres -p5432  -Ft -Pv -Xf -z -Z5  -D /4T/postgresql/pg12/backup    --压缩备份
pg_basebackup -h192.168.60.22 -Upostgres -p5432  -Ft -Pv -Xf         -D /4T/postgresql/pg12/backup

-- 清理pg_wal日志
$which pg_resetwal
$ pg_ctl stop 
pg_resetwal /4T/postgresql/pg12/pgdata/                                
$ pg_ctl start


--pg_rman
su - pgsql
wget https://github.com/ossc-db/pg_rman/releases/download/V1.3.11/pg_rman-1.3.11-pg13.tar.gz
tar -zxvf pg_rman-1.3.11-pg13.tar.gz
cd pg_rman-1.3.11-pg13/
make && make install
[pgsql@lhrpg pg_rman-1.3.11-pg13]$ which pg_rman
/4T/postgresql/pg12/pg12/bin/pg_rman


备份
1.初始化
pg_rman init -B /4T/postgresql/pg12/backup

2.全备
pg_rman backup --backup-mode=full    -B /4T/postgresql/pg12/backup
pg_rman validate -B /4T/postgresql/pg12/backup                                  --校验
pg_rman show -B /4T/postgresql/pg12/backup                                      --查看

3.增量备份
create table mmp11 as select * from sbtest1;
pg_rman backup --backup-mode=incremental -B /4T/postgresql/pg12/backup
pg_rman validate -B /4T/postgresql/pg12/backup                                  --校验
pg_rman show -B /4T/postgresql/pg12/backup                                      --查看

4.还原
pg_ctl stop
rm -rf /postgresql/pgdata/
pg_rman restore -B /4T/postgresql/pg12/backup                                      （原始备份，增量备份一次性全部还原）
pg_rman restore -B /4T/postgresql/pg12/backup    --recovery-target-time   "2023-03-21 14:59:47"     --基于时间点恢复


4.1 pg_ctl: could not start server                                  （还原出现问题）
/postgresql/pg13/bin/pg_resetwal -f /postgresql/pgdata                  --修复命令
    Write-ahead log reset                                               --代表修复成功                     
如果不能成功
pg_ctl stop
rm -rf /postgresql/pgdata/
pg_rman restore -B /4T/postgresql/pg12/backup 


5.删除备份集
pg_rman delete "2023-03-21 14:59:47" -B /4T/postgresql/pg12/backup                 --需要指定目录
pg_rman purge  "2023-03-24 14:59:47" -B /4T/postgresql/pg12/backup                 --备份文件夹一起删除


6.根据备份策略来删除备份集（自己在pg_rman.ini文件中添加）
KEEP_DATA_GENERATIONS = 1   -- 备份集冗余度是1
KEEP_DATA_DAYS = 10         -- 备份集保留日期是10d




==========================================================================================
备份脚本

--创建备份脚本
vim bak0.sh
#!/bin/bash
rm -rf /4T/postgresql/pg12/backup/ *

DATE=`date "+%Y%m%d%H%M%S"`
pg_rman init -B /4T/postgresql/pg12/backup

sh /home/pg12/src_bak/bak.sh  2> /home/pg12/src_bak/pg_rman_${DATE}.log 1>>/home/pg12/src_bak/pg_rman_${DATE}.log  



vim bak.sh

source /home/pg12/.bash_profile
#DATE=`date +%Y%m%d`;
#DATE=`date "+%Y-%m-%d %H:%M:%S"`
DATE=$(date -d '6 day ago' +"%F %T")

#执行备份命令
pg_rman backup -b full      -B /4T/postgresql/pg12/backup
#备份集校验
pg_rman validate            -B /4T/postgresql/pg12/backup
#清理无效备份集
pg_rman delete "$DATE"      -B /4T/postgresql/pg12/backup
pg_rman purge  "$DATE"      -B /4T/postgresql/pg12/backup
#查看备份集
pg_rman show                -B  /4T/postgresql/pg12/backup


--创建删除日志脚本
vim /home/pg12/src_bak/dellog.sh
--删除指定文件夹里的文件 
vim   dellog.sh
#find：linux的查找命令，用户查找指定条件的文件；
#/opt/soft/log/：想要进行清理的任意目录；
#-mtime：标准语句写法；
#+3：查找3天前的文件，这里用数字代表天数；
#"*.log"：希望查找的数据类型，"*.jpg"表示查找扩展名为jpg的所有文件，"*"表示查找所有文件，这个可以灵活运用，举一反三；
#-exec：固定写法；
#rm -rf：强制删除文件，包括目录；
# {} ; ：固定写法，一对大括号+空格++; 

find /4T/postgresql/pg12/archive/   -mtime +0 -name "*" -exec rm -rf {} \;
find /4T/postgresql/pg12/backup/ *   -mtime +0 -name "*" -exec rm -rf {} \;


--创建时间同步脚本
vim /home/pg12/src_bak/date.sh

ntpdate ntp.aliyun.com

============================================================================================================================

crontab -e
		
30 0 * * *  su - pg12 -c  /home/pg12/src_bak/bak0.sh
20 0 * * *  su - pg12 -c  /home/pg12/src_bak/dellog.sh


================================================================

测试：