PostgreSQL备份和恢复实战操作一、逻辑备份与恢复PostgreSQL提供了pg_dump以及pg_dumpall的命令来实现逻辑备份。
这两命令差不多，看名字猜的。
pg_dump这种备份，不会造成用户对数据的操作出现阻塞。
查看命令：
这个命令从三点去看：
连接的信息，指定连接哪个库，用哪个用户。
option的信息有就点多，查看官网。
备份的数据库。
操作一波备份数据库中的全部数据
删除当前laozheng库中的表等信息，然后恢复数据。
除此之外，也可以通过图形化界面备份，在库的位置点击备份就成，导出一个文本文件。
二、物理备份（归档+物理）这里需要基于前面的文件系统的备份和归档备份实现最终的操作
单独使用文件系统的方式，不推荐毕竟数据会丢失。
这里直接上PostgreSQL提供的pg_basebackup命令来实现。
pg_basebackup会做两个事情、
会将内存中的脏数据落到磁盘中，然后将数据全部备份
会将wal日志直接做归档，然后将归档也备走。
查看pg_basebackup命令
代码语言：javascript复制# -D 指定备份文件的存储位置
# -Ft 备份文件打个包
# -Pv 输出备份的详细信息
# -U 用户名（要拥有备份的权限）
# -h ip地址 -p 端口号
# -R 复制写配置文件
pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.11.32 -p 5432 -R
准备测试
提前准备出/pg_basebackup目录，记得将拥有者赋予postgres用户。
代码语言：javascript复制mkdir /pg_basebackup
chown -R postgres. /pg_basebackup/
给postgres用户提供replication的权限，修改pg_hba.conf，记得重启生效。
执行备份代码语言：javascript复制pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.11.32 -p 5432 -R
需要输入postgres的密码，这里可以设置，重新备份。
执行备份
三、物理恢复（归档+物理）模拟数据库崩盘，先停止postgresql服务，然后直接删掉data目录下的全部内容
将之前备份的两个文件准备好，一个base.tar，一个pg_wal.tar
第一步：将base.tar中的内容，全部解压到 12/data 目录下
第二步：将pg_wal.tar中的内容，全部解压到 /archive 目录下
第三步：在postgresql.auto.conf文件中，指定归档文件的存储位置，以及恢复数据的方式
第四步：启动postgresql服务
代码语言：javascript复制systemctl start postgresql-12第五步：启动后，发现查询没问题，但是执行写操作时，出错，不让写。需要执行一个函数，取消这种恢复数据后的状态，才允许正常的执行写操作。
代码语言：javascript复制select pg_wal_replay_resume();
四、物理备份&恢复（PITR-Point in time Recovery）1、模拟场景 场景：每天凌晨02:00，开始做全备（PBK），到了第二天，如果有人14:00分将数据做了误删，希望将数据恢复到14:00分误删之前的状态？
恢复全备数据，使用PBK的全备数据恢复到凌晨02:00的数据。（数据会丢失很多）归档恢复：备份中的归档，有02:00~14:00之间的额数据信息，可以基于归档日志将数据恢复到指定的事务id或者是指定时间点，从而实现数据的完整恢复。2、准备场景和具体操作2.1、构建一张t3表查询一些数据代码语言：javascript复制-- 构建一张表
create table t3 (id int);
insert into t3 values (1);
insert into t3 values (11);
2.2、模拟凌晨2点开始做全备操作代码语言：javascript复制pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.11.32 -p 5432 -R
2.3、再次做一些写操作，然后误删数据代码语言：javascript复制-- 凌晨2点已经全备完毕
-- 模拟第二天操作
insert into t3 values (111);
insert into t3 values (1111);
-- 误删操作 2023年3月20日20:13:26
delete from t3;
2.4、恢复数据（确认有归档日志）将当前服务的数据全部干掉，按照之前的全备恢复的套路先走着
然后将全备的内容中的base.tar扔data目录下，归档日志也扔到/archive位置。
2.5、查看归档日志，找到指定的事务id查看归档日志，需要基于postgresql提供的一个命令
代码语言：javascript复制# 如果命令未找到，说明两种情况，要么没有这个可执行文件，要么是文件在，没设置环境变量
# 咱们这是后者
pg_waldump
# 也可以采用全路径的方式
/usr/pgsql-12/bin/pg_waldump
2.6、修改data目录下的恢复数据的方式修改postgresql.auto.conf文件
将之前的最大恢复，更换为指定的事务id恢复
基于提供的配置例子，如何指定事务id
修改postgresql.auto.conf文件指定好事务ID
2.7、启动postgreSQL服务，查看是否恢复到指定事务ID
2.8、记得执行会后的函数，避免无法执行写操作 代码语言：javascript复制select pg_wal_replay_resume();