备份恢复
需要备份什么？
数据
归档日志
备份方式
逻辑导出工具
pg_dump
pg_dumpall
单库备份
pg_dump testdatabase -h 10.10.8.176 -U test -W -C > /pgdata/pg_backup/test_$(date +"%Y_%m_%d").sql
-C # 备份的时候备份创建库的指令
单表备份
pg_dump testdatabase -h 10.10.8.176 -U test -W -C -t t1 > /pgdata/pg_backup/test_$(date +"%Y_%m_%d").sql
单库恢复 // 必须先创建库
# 必须加 -d 参数指定库 不然会导入默认库 如果想修改导入的库 需要修改 备份的 dump 文件
psql -h 10.10.8.176 -U test -p 5432 -d test -f /pgdata/pg_backup/test_$(date +"%Y_%m_%d").sql
单表恢复
# 必须加 -d 参数指定库 不然会导入默认库
psql -h 10.10.8.176 -U test -p 5432 -d test -f /pgdata/pg_backup/t1_$(date +"%Y_%m_%d").sql
pg_basebackup 物理备份工具的使用
必须是同版本才能使用
备份的流程
pg_basebackup：检查点已完成
pg_basebackup：提前写入日志起点：0/B000028 时间线 1
pg_basebackup：启动后台WAL接收器
pg_basebackup：已创建临时复制插槽“pg_basebackup_39298”
40533/40533 kB（100%），1/1表空间
pg_basebackup:预写日志结束点：0/B000138
pg_basebackup:正在等待后台进程完成流式处理。。。
pgbasebackup:正在将数据同步到磁盘。。。
pg基本备份：基本备份已完成
备份
# 创建备份目录
# 必须是空目录才能备份成功
mkdir -p /pgdata/pg_backup
# 执行备份命令
pg_basebackup -D /pgdata/pg_backup/ -F t -Pv -U postgres -h 127.0.0.1 -p 5432 -R -Z 3
-D # 指定备份路径
-F t # 备份的方式 tar包
-Pv # 显示执行的详细信息
-Z 3 # 压缩备份 并选择压缩等级 （1-9级）
报错：
2022-04-22 16:20:42.046 CST [27858] FATAL: no pg_hba.conf entry for replication connection from host "10.10.8.176", user "postgres", SSL off
pg_basebackup: error: FATAL: no pg_hba.conf entry for replication connection from host "10.10.8.176", user "postgres", SSL off
# 添加防火墙规则
vim /pgdata/12/data/pg_hba.conf
host replication all 10.10.8.0/24 md5
# 重载 pg 配置
# pg_ctl reload
select pg_reload_conf();
模拟损坏修复
# 强制关闭所有进程
pkill postgres
# 删除数据目录
rm -fr /pgdata/12/data/*
# 删除归档日志目录
rm -fr /archive/*
# 将备份解压到数据目录
tar xf base.tar.gz -C /pgdata/12/data/
# 将归档日志解压到归档目录下
tar xf pg_wal.tar.gz -C /archive/
# 配置修复位置
cat >/pgdata/12/data/postgresql.auto.conf<<'EOF'
# 将归档日志拷贝到重做日志目录
restore_command = 'cp /archive/%f %p'
# 指定恢复位置
recovery_target = 'immediate'
EOF
# 启动服务 // 注意现在的pgsql是恢复状态的无法使用！！！
pg_ctl start
# 查看当前PostgreSQL状态
pg_controldata |grep 'Database cluster state:'
Database cluster state: in archive recovery # 此状态pg不可用
# 重置恢复状态
select pg_wal_replay_resume();
# 检查状态
pg_controldata |grep 'Database cluster state:'
Database cluster state: in production
# 完成恢复
PITR恢复演示
场景介绍
每天 0 点进行一次全备 ， 下午两点误删除了数据，如何恢复？
恢复逻辑
1、恢复全备数据
2、归档恢复： 备份归档 + 0点到下午两点前删除前一个sql ，在线redo
模拟场景
-- 创建新库
create database pitdb;
\c pitdb;
-- 创建新表插入数据
create table t1(id int);
insert into t1 values(1),(2),(3),(4);
# 执行全备
pg_basebackup -D /pgdata/pg_backup/ -F t -Pv -U postgres -h 10.10.8.176 -p 5432 -R -Z 3
-- 创建新表插入数据
create table t2(id int);
insert into t2 values(1),(2),(111);
-- 模拟删库
\c postgres;
drop database pitdb;
-- 查看当前重做文件
select pg_walfile_name(pg_current_wal_lsn());
-- 切换重做日志
select pg_switch_wal();
# 找一台新机器启动相同版本的实例，删除数据目录将备份解压到数据目录
rm -rf /pgdata/12/data/*
# 将备份解压到数据目录
tar xf base.tar.gz -C /pgdata/12/data/
# 将备份的重做文件解压到归档目录
tar xf pg_wal.tar.gz -C /archive/
# 将重做文件目录里新产生的文件拷贝到新机器的归档目录
scp /archive/* 10.10.8.177:/archive/
# 查找重做日志中的删除语句的位置点
pg_waldump /archive/00000002000000000000000D|grep DROP
rmgr: Database len (rec/tot): 34/ 34, tx: 580, lsn: 0/0D0126E8, prev 0/0D012670, desc: DROP dir 1663/16462
# 编辑配置文件
vim /pgdata/12/data/postgresql.auto.conf
# 将归档日志拷贝到重做日志目录
restore_command = 'cp /archive/%f %p'
# 指定恢复的位置点 //这里也可以指定lsn号进行恢复
recovery_target_xid = '579'
# 查看数据库状态
[postgres@pg-01 archive]$ pg_controldata |grep 'Database cluster state:'
Database cluster state: in archive recovery
-- 重置恢复状态
select pg_wal_replay_resume();
# 查看数据库状态
[postgres@pg-01 archive]$ pg_controldata |grep 'Database cluster state:'
Database cluster state: in production
# 查看数据
postgres=# \c pitdb
You are now connected to database "pitdb" as user "postgres".
pitdb=# select * from t1;
id
----
1
2
3
4
(4 rows)
pitdb=# select * from t2;
id
-----
1
2
111
(3 rows)
# 将实例里的pitdb库进行备份，导入到生产库即可
# 完成恢复
# 还可以再执行危险操作前生成保存点，在恢复时直接指定保存点进行恢复
select pg_create_restore_point('test-t1-index'); # recovery_target_name = 'test-t1-index'
