【一文搞懂PGSQL】4.逻辑备份和物理备份 pg_dump/ pg_basebackup

备份恢复

需要备份什么？

数据

归档日志

备份方式

逻辑导出工具

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:正在等待后台进程完成流式处理。。。
pg_basebackup:正在将数据同步到磁盘。。。
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'