PostgreSQL 数据备份&恢复

深碍√TFBOYSˉ_ 2023-10-15 22:52 56阅读 0赞

#### 文章目录 ####

*  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日志：  
![image.png][]  
这些就是归档日志  
> wal日志的名称，是三块内容组成，  
>  
> 每8个字符分成一组，用16进制标识的  
>  
> 00000001 00000000 0000000A  
>  
> 时间线 逻辑id 物理id

**查询当前库用的是哪个wal日志**

-- 查看当前使用的wal日志  查询到的lsn：0/47233270
    select pg_current_wal_lsn();
    -- 基于lsn查询具体的wal日志名称  000000010000000000000047
    select pg_walfile_name('0/47233270');

归档默认不是开启的，需要手动开启归档操作，才能保证wal日志的完整性

修改postgresql.conf文件

# 开启wal日志的内容，注释去掉即可
    wal_level = replica
    fsync = on

![image.png][image.png 1]

# 开启归档操作
    archive_mode = on
    # 修改一小下命令，修改存放归档日志的路径
    archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

![image.png][image.png 2]

**修改完上述配置文件后，记得重启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这种备份，不会造成用户对数据的操作出现阻塞。

![image.png][image.png 3]

*  连接的信息，指定连接哪个库，用哪个用户。
 *  option的信息有就点多，查看[官网][Link 1]。
 *  备份的数据库名称。

恢复直接导入或者执行SQL就行。

## 物理备份&恢复（全量） ##

### 备份 ###

需要基于前面的文件系统的备份和归档备份实现最终的操作，不推荐单独使用文件系统的方式，毕竟数据会丢失。

通过PostgreSQL提供的pg\_basebackup命令来实现，pg\_basebackup会做两个事情：

*  会将内存中的脏数据落到磁盘中，然后将数据全部备份。
 *  会将wal日志直接做归档，然后将归档也备走。

![image.png][image.png 4]

一个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 -R

pg\_basebackup命令执行前准备

*  创建/pg\_basebackup目录并赋予postgres用户权限。
    
        mkdir /pg_basebackup
        chown -R postgres. /pg_basebackup/
 *  给postgres用户提供replication的权限，修改pg\_hba.conf，记得重启生效。![image.png][image.png 5]
 *  执行备份![image.png][image.png 6]  
    备份结果  
    ![image.png][image.png 7]

### 恢复 ###

模拟数据库崩盘，先停止postgresql服务，然后直接删掉data目录下的全部内容

![image.png][image.png 8]

将之前备份的两个文件准备好，一个base.tar，一个pg\_wal.tar。

第一步：将base.tar中的内容，全部解压到 **12/data** 目录下

第二步：将pg\_wal.tar中的内容，全部解压到 **/archive** 目录下

![image.png][image.png 9]

第三步：在postgresql.auto.conf文件中，指定归档文件的存储位置，以及恢复数据的方式![image.png][image.png 10]

第四步：启动postgresql服务

systemctl start postgresql-12

第五步：启动后，发现查询没问题，但是执行写操作时出错，不让写。需要执行一个函数，取消这种恢复数据后的状态，才允许正常的执行写操作。

select pg_wal_replay_resume();

## 物理备份&恢复（某个时间点） ##

### 场景 ###

> 场景：每天凌晨02:00，开始做全备，到了第二天，如果有人14:00分将数据做了误删，希望将数据恢复到14:00分误删之前的状态

1.  恢复全备数据，使用全备数据恢复到凌晨02:00的数据。（数据会丢失很多）
2.  归档恢复：备份中的归档，有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 -R

3、再次做一些写操作，然后误删数据

-- 凌晨2点已经全备完毕
    -- 模拟第二天操作
    insert into t3 values (111);
    insert into t3 values (1111);
    -- 误删操作  2023年3月20日20:13:26
    delete from t3;

4、恢复数据（确认有归档日志）

将当前服务的数据全部干掉，按照之前的全备恢复的套路先走着

![image.png][image.png 11]

然后将全备的内容中的base.tar扔data目录下，归档日志也扔到/archive位置。

5、查看归档日志，找到指定的事务id

查看归档日志，需要基于postgresql提供的一个命令

# 如果命令未找到，说明两种情况，要么没有这个可执行文件，要么是文件在，没设置环境变量
    # 咱们这是后者
    pg_waldump
    # 也可以采用全路径的方式
    /usr/pgsql-12/bin/pg_waldump

![image.png][image.png 12]

![image.png][image.png 13]

6、修改data目录下的恢复数据的方式

修改postgresql.auto.conf文件，将之前的最大恢复，更换为指定的事务id恢复

![image.png][image.png 14]

修改postgresql.auto.conf文件指定好事务ID

![image.png][image.png 15]

7、启动postgreSQL服务，查看是否恢复到指定事务ID

![image.png][image.png 16]

8、记得执行会后的函数，避免无法执行写操作

select pg_wal_replay_resume();

[image.png]: https://img-blog.csdnimg.cn/img_convert/63b7c2c523b11a0d59a222062808bd0b.png
[image.png 1]: https://img-blog.csdnimg.cn/img_convert/aa5acc1b81c02de21d578265b6c16a6c.png
[image.png 2]: https://img-blog.csdnimg.cn/img_convert/4367dc1da01f06aadf11b3b720aaeb8d.png
[image.png 3]: https://img-blog.csdnimg.cn/img_convert/b343adae2deb85c1bc86aa23fe6a8900.png
[Link 1]: http://postgres.cn/docs/12/app-pgdump.html
[image.png 4]: https://img-blog.csdnimg.cn/img_convert/fce03c0304cedeb514a95c15b79cdb86.png
[image.png 5]: https://img-blog.csdnimg.cn/img_convert/e593ec1db5645a970200ec472089ba2c.png
[image.png 6]: https://img-blog.csdnimg.cn/img_convert/5f725c92569769f4d8bb1ba33fbf0a2c.png
[image.png 7]: https://img-blog.csdnimg.cn/img_convert/913623f931e77265a809a00861ae4a86.png
[image.png 8]: https://img-blog.csdnimg.cn/img_convert/76bf2b7ad46c8c3badad1e256c742d9f.png
[image.png 9]: https://img-blog.csdnimg.cn/img_convert/e21fd95016337b4756a8f687048504af.png
[image.png 10]: https://img-blog.csdnimg.cn/img_convert/b71402ea2de6b8e3c1f82d3c14912801.png
[image.png 11]: https://img-blog.csdnimg.cn/img_convert/2ac50f207353cd091c077ed89b10302e.png
[image.png 12]: https://img-blog.csdnimg.cn/img_convert/38163a0bd5e87aa218c019517449e3ef.png
[image.png 13]: https://img-blog.csdnimg.cn/img_convert/dd72e7cee730c10da4e84a51b4536a1b.png
[image.png 14]: https://img-blog.csdnimg.cn/img_convert/7c43a8178ab71e67cdc7d7502756cb24.png
[image.png 15]: https://img-blog.csdnimg.cn/img_convert/48570510c990cd6c8ae4ee04560ca84d.png
[image.png 16]: https://img-blog.csdnimg.cn/img_convert/fc1afd55fcc0d1014226697ca01763c9.png