一文了解 PG PITR 即时恢复

字数 3989阅读 832评论 0赞 0

在数据库系统中，数据是一切的基础，数据的安全更是重中之重。但可能因为各种原因导致数据丢失或损坏，因此数据的备份和恢复便显得尤为重要。

PostgreSQL 是一个强大的开源对象关系数据库系统，经过 30 多年的积极开发，在可靠性、功能稳健性和性能方面赢得了良好的声誉。Point-In-Time Recovery（简称 PITR）是 PostgreSQL 的基于时间点的数据恢复技术，在开启 WAL 日志归档及基础备份后，可以即时恢复用户误操作丢失的数据，为您的数据库加上一重"保险"。

本文将演示 PostgreSQL 如何配置 PITR 数据备份，以及当数据被误删除后如何进行数据即时恢复。

环境准备

准备数据库

准备 PostgreSQL 11 版数据库环境。

  # echo "$PGHOME" 查看

  PGHOME=/usr/lib/postgresql/11

  # echo "$PGDATA" 查看，PostgreSQL 的数据目录

  PGDATA=/data/pgsql/main

  # pg 命令行执行 show config_file; 命令查看，PostgreSQL 的配置文件位置

  PGCONFIG_FILE=/etc/postgresql/11/main/postgresql.conf

  # PostgreSQL 的启动日志位置，当启动失败时，可以通过日志快速查看报错内容

  PG_LOG=/data/pglog/start.log

PITR 数据备份

当用户误删除数据之后，可以通过 PITR 恢复指定的时间戳的数据。下面介绍开启备份及恢复数据的详细步骤。

开启 WAL 日志归档

通过修改配置文件，开启 WAL 日志归档功能。

  # 创建保存wal日志归档的目录，并修改目录拥有者

  $ mkdir -p /data/wallog_back

  $ chown -R postgres:postgres /data/wallog_back

  # 修改 PostgreSQL 数据库配置文件 postgresql.conf

  $ vim /etc/postgresql/11/main/postgresql.conf

  # 修改如下配置项：

  wal_level = archive

  archive_mode = on 

  archive_command = 'test ! -f /data/wallog_back/%f && cp %p /data/wallog_back/%f' # 配置归档命令，拷贝 wal 文件到指定目录，%p 为实际的 wal 文件目录，%f 为 wal 文件名称

  # 保存后重启数据库

  root:~# su postgres

  # stop postgresql server

  postgres:~$ /usr/lib/postgresql/11/bin/pg_ctl stop

  # start postgresql server

  postgres:~$ /usr/lib/postgresql/11/bin/pg_ctl -D /data/pgsql/main/ start 

  -o '-c config_file=/etc/postgresql/11/main/postgresql.conf' -l /data/pglog/start.log

成功保存配置修改，即打开了 WAL 日志归档功能。

准备数据

  # 创建测试表

  CREATE TABLE test_table(

  ID INT NOT NULL,

  NAME TEXT NOT NULL,

  AGE INT NOT NULL

  );

  # 插入测试数据。通过以下命令，快速插入 200001 条测试数据

  insert into test_table(id, name, age) 

  values(

  generate_series(0,200000), 

  substr(md5(random()::text), 0, 25), 

  generate_series(0,200000)

  );

执行基础备份

PostgreSQL 查看当前时间，指定备份时的 lable。

    postgres=# select now();

    now 

    -------------------------------

    2021-11-04 14:50:42.482253+08

    (1 row)

开始备份

# PostgreSQL 命令行执行 

    postgres=# select pg_start_backup('backup_2021-11-04 14:50:42');

    pg_start_backup 

    -----------------

    0/9000060

    (1 row)

# 切换到 linux 命令行执行

    cd /data

    $ sudo tar -cvzf pgsql.tar pgsql

    postgres=# select pg_stop_backup();

    NOTICE: pg_stop_backup complete, all required WAL segments have been archived

    pg_stop_backup 

    ----------------

    0/9000168

    (1 row)

执行一次 WAL 切换，使得 WAL 日志归档

    postgres=# select pg_switch_wal();

    pg_switch_wal 

    ---------------

    0/A000078

    (1 row)

查看归档状态，如果 archived_count 为 0 代表异常，需要查看配置文件是否修改成功或尝试重启 PostgreSQL 服务
```
    postgres=# select * from pg_stat_archiver;
```

注意：

备份完成后，/data/wallog_back/ 目录下应该有相应的归档文件。

select * from pg_stat_archiver;命令执行完成后，如果 archived_count 为 0，代表归档设置开启失败，请重新检查配置文件或重启服务。

PITR 数据恢复

模拟误删数据

  # 删除 test_table 表中 id 大于 10000 的数据，模拟误删除数据的操作

  postgres=# delete from test_table where id>10000;

  DELETE 190000

  # 查询出当前时间，根据此时间确定大概的恢复时间点

  postgres=# select now();

  now 

  -------------------------------

  2021-11-04 14:56:17.452967+08

  (1 row)

即时恢复数据

  # 停止数据库运行

  $ /usr/lib/postgresql/11/bin/pg_ctl stop

  # 重命名 pgsql 目录

  cd /data

  mv pgsql pgsql_back

  # 恢复备份包到 data 目录

  sudo tar -zxvf pgsql.tar

  # 配置 data 目录下的recovery.done文件，如果没有则创建。

  restore_command='cp /data/wallog_back/%f %p' # wal归档的目录

  recovery_target_time='2021-11-04 14:56:17.452967' # 恢复到此时刻，根据之前的 select now()判断恢复时刻。

  recovery_target_timeline='latest'

## 重新启动数据库

  $ /usr/lib/postgresql/11/bin/pg_ctl -D /data/pgsql/main/ start -o '-c config_file=/etc/postgresql/11/main/postgresql.conf' -l /data/pglog/start.log

# 数据恢复验证

  postgres=# select * from test_table;

  postgres=# select count(*) from test_table;

  count 

  --------

  200001

  (1 row)

如图所示，数据恢复到了 200001 条。