杭州行业网站建设公司,wordpress怎么新建模块,外贸自己做网站,建设网站建设网站文章目录 PostgreSQL 备份方式SQL备份#xff08;逻辑备份#xff09;文件系统备份#xff08;物理备份#xff09;归档备份#xff08;物理备份#xff09; 逻辑备份恢复物理备份恢复#xff08;全量#xff09;备份恢复 物理备份恢复#xff08;某个… 文章目录 PostgreSQL 备份方式SQL备份逻辑备份文件系统备份物理备份归档备份物理备份 逻辑备份恢复物理备份恢复全量备份恢复 物理备份恢复某个时间点场景具体操作 PostgreSQL 备份方式
防止数据丢失的第一道防线就是备份。数据丢失有的是硬件损坏还有人为的误删之类的也有BUG的原因导致误删数据。在PostgreSQL中有三种备份方式。
SQL备份逻辑备份
SQL备份逻辑备份 利用数据库自带的类似dump的命令或者是用图形化界面执行导入导出时底层就是基于这个dump命令实现的。
优点简单方便操作有手就行还挺可靠。缺点数据数据量比较大这种方式巨慢可能导出一天都无法导出完所有数据。
文件系统备份物理备份
文件系统备份物理备份 找到当前数据库数据文件在磁盘存储的位置将数据文件直接复制一份或多份存储在不同的物理机上。
优点相比逻辑备份恢复的速度快。缺点在备份数据时可能数据还正在写入一定程度上会丢失数据。 在恢复数据时也需要注意数据库的版本和环境必须保持高度的一致。如果是线上正在运行的数据库这种复制的方式无法在生产环境实现。
如果说要做数据的迁移这种方式还不错滴。
归档备份物理备份
先了解几个概念在PostgreSQL有多个子进程来辅助一些操作 BgWriter进程BgWriter是将内存中的数据写到磁盘中的一个辅助进程。当向数据库中执行写操作后数据不会马上持久化到磁盘里。这个主要是为了提升性能。BgWriter会周期性的将内存中的数据写入到磁盘。但是这个周期时间长了不行短了也不行。如果快了IO操作频繁效率慢。如果慢了有查询操作需要内存中的数据时需要BgWriter现把数据从内存写到磁盘中再提供给查询操作作为返回结果。会导致查询操作效率变低。 考虑一个问题 事务提交了数据没落到磁盘这时服务器宕机了怎么办 WalWriter进程WAL就是write ahead log的缩写对应MYSQL的redo log。数据还在内存中时其实已经写入到WAL日志中一份这样一来即便BgWriter进程没写入到磁盘中时数据也不会存在丢失的问题。 PgArch进程WAL日志会循环使用数据会丢失。没关系还有一个归档的进程会在切换wal日志前将WAL日志备份出来。PostgreSQL也提供了一个全量备份的操作。可以根据WAL日志选择一个事件点进行恢复。
查看WAL日志 这些就是归档日志 wal日志的名称是三块内容组成 每8个字符分成一组用16进制标识的 00000001 00000000 0000000A 时间线 逻辑id 物理id
查询当前库用的是哪个wal日志
-- 查看当前使用的wal日志 查询到的lsn0/47233270
select pg_current_wal_lsn();
-- 基于lsn查询具体的wal日志名称 000000010000000000000047
select pg_walfile_name(0/47233270);归档默认不是开启的需要手动开启归档操作才能保证wal日志的完整性
修改postgresql.conf文件
# 开启wal日志的内容注释去掉即可
wal_level replica
fsync on# 开启归档操作
archive_mode on
# 修改一小下命令修改存放归档日志的路径
archive_command test ! -f /archive/%f cp %p /archive/%f修改完上述配置文件后记得重启postgreSQL进程才会生效
归档操作执行时需要保证/archive存在并且postgres用户有权限进行w操作
构建/archive路径
# postgres没有权限在/目录下构建目录
# 切换到root构建目录将目录的拥有者更改为postgres
mkdir /archive
chown -R postgres. archive在当前库中做大量写操作接入到wal日志重置切换wal日志再查看归档情况
发现将当前的正在使用的wal日志和最新的上一个wal日志归档过来了但是之前的没归档不要慌后期备份时会执行命令这个命令会直接要求wal日志立即归档然后最全量备份。
逻辑备份恢复
PostgreSQL提供了pg_dump以及pg_dumpall的命令来实现逻辑备份。 pg_dump这种备份不会造成用户对数据的操作出现阻塞。 连接的信息指定连接哪个库用哪个用户。option的信息有就点多查看官网。备份的数据库名称。
恢复直接导入或者执行SQL就行。
物理备份恢复全量
备份
需要基于前面的文件系统的备份和归档备份实现最终的操作不推荐单独使用文件系统的方式毕竟数据会丢失。
通过PostgreSQL提供的pg_basebackup命令来实现pg_basebackup会做两个事情
会将内存中的脏数据落到磁盘中然后将数据全部备份。会将wal日志直接做归档然后将归档也备走。 一个pg_basebackup的备份命令
# -D 指定备份文件的存储位置
# -Ft 备份文件打个包
# -Pv 输出备份的详细信息
# -U 用户名要拥有备份的权限
# -h ip地址 -p 端口号
# -R 复制写配置文件
pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.11.32 -p 5432 -Rpg_basebackup命令执行前准备
创建/pg_basebackup目录并赋予postgres用户权限。mkdir /pg_basebackup
chown -R postgres. /pg_basebackup/给postgres用户提供replication的权限修改pg_hba.conf记得重启生效。执行备份 备份结果
恢复
模拟数据库崩盘先停止postgresql服务然后直接删掉data目录下的全部内容 将之前备份的两个文件准备好一个base.tar一个pg_wal.tar。
第一步将base.tar中的内容全部解压到 12/data 目录下
第二步将pg_wal.tar中的内容全部解压到 /archive 目录下 第三步在postgresql.auto.conf文件中指定归档文件的存储位置以及恢复数据的方式
第四步启动postgresql服务
systemctl start postgresql-12第五步启动后发现查询没问题但是执行写操作时出错不让写。需要执行一个函数取消这种恢复数据后的状态才允许正常的执行写操作。
select pg_wal_replay_resume();物理备份恢复某个时间点
场景 场景每天凌晨02:00开始做全备到了第二天如果有人14:00分将数据做了误删希望将数据恢复到14:00分误删之前的状态 恢复全备数据使用全备数据恢复到凌晨02:00的数据。数据会丢失很多归档恢复备份中的归档有02:00~14:00之间的额数据信息可以基于归档日志将数据恢复到指定的事务id或者是指定时间点从而实现数据的完整恢复。
具体操作
1、构建一张t3表查询一些数据
-- 构建一张表
create table t3 (id int);
insert into t3 values (1);
insert into t3 values (11);2、模拟凌晨2点开始做全备操作
pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.11.32 -p 5432 -R3、再次做一些写操作然后误删数据
-- 凌晨2点已经全备完毕
-- 模拟第二天操作
insert into t3 values (111);
insert into t3 values (1111);
-- 误删操作 2023年3月20日20:13:26
delete from t3;4、恢复数据确认有归档日志
将当前服务的数据全部干掉按照之前的全备恢复的套路先走着 然后将全备的内容中的base.tar扔data目录下归档日志也扔到/archive位置。
5、查看归档日志找到指定的事务id
查看归档日志需要基于postgresql提供的一个命令
# 如果命令未找到说明两种情况要么没有这个可执行文件要么是文件在没设置环境变量
# 咱们这是后者
pg_waldump
# 也可以采用全路径的方式
/usr/pgsql-12/bin/pg_waldump6、修改data目录下的恢复数据的方式
修改postgresql.auto.conf文件将之前的最大恢复更换为指定的事务id恢复 修改postgresql.auto.conf文件指定好事务ID 7、启动postgreSQL服务查看是否恢复到指定事务ID 8、记得执行会后的函数避免无法执行写操作
select pg_wal_replay_resume();
