乐趣区

关于数据库:工具-常用-PostgreSQL-预防数据丢失方案

jiezi

作者：张连壮 PostgreSQL 研发负责人

从事多年 PostgreSQL 数据库内核开发，对 Citus 有十分深刻的钻研。

PostgreSQL 自身不具备数据闪回和数据误删除爱护性能，但在不同场景下也有对应的解决方案。

本文由作者在 2021 PCC 大会的演讲主题《PostgreSQL 数据找回》整顿而来，上一篇《盘点 | 罕用 PG 数据恢复计划概览》介绍了 PostgreSQL 常见的 数据恢复计划 。本篇将介绍 预防数据失落计划 的实现原理及应用示例。

预防数据失落计划

前文提到数据失落的次要操作为 DDL 和 DML。

本篇次要介绍对于 DDL 和 DML 操作，如何预防数据失落的计划。

DDL 操作

事件触发器

当事件以其定义的形式在数据库中相干的产生时，触发事件触发器。次要可预防以下四种 DDL 事件。

事件 阐明
ddl_command_start DDL 执行前执行
ddl_command_end DDL 执行后执行，通过 pg_event_trigger_ddl_commands() 能够获取操作的对象
sql_drop DDL 执行后执行，通过 pg_event_trigger_dropped_objects() 能够获取所有被删除的对象
table_rewrite DDL 执行前执行，例如 ALTER TABLE、ALTER TYPE 等

当表被删除后，能够通过 ddl_command_start 事件组织删除操作。

CREATE OR REPLACE FUNCTION disable_drops()
    RETURNS event_trigger LANGUAGE plpgsql AS $$
BEGIN
     RAISE EXCEPTION 'drop table denied';
END
$$; -- 创立事件触发器函数
 
CREATE EVENT TRIGGER event_trigger_disable_drops
    ON ddl_command_start WHEN TAG in('drop table')
    EXECUTE PROCEDURE disable_drops(); -- 创立事件触发器，禁止 drop table 操作

事件触发器，无奈批改 drop 的任何行为，因而只能回绝，来确保数据不被删除，由其余领有更高权限的数据库管理员删除。

test=# \dy
                                        事件触发器列表
            名称             |       Event       | 拥有者  | 使能 |     函数      |    标签    
-----------------------------+-------------------+---------+------+---------------+------------
 event_trigger_disable_drops | ddl_command_start | lzzhang | 启用 | disable_drops | DROP TABLE
(1 行记录)
 
test=# drop table lzzhang;
ERROR:  drop table denied
CONTEXT:  PL/pgSQL function disable_drops() line 3 at RAISE

删除表的操作由领有更高级权限的数据库管理员操作。

BEGIN;
ALTER EVENT TRIGGER event_trigger_disable_drops DISABLE;
DROP TABLE lzzhang;
ALTER EVENT TRIGGER event_trigger_disable_drops ENABLE;
COMMIT;

回收站

DDL 会将文件从操作系统中齐全删除，因而惟一的方法是将 删除 改为 换一个 ” 地位 ”，相似 Windows 中回收站。

pgtanshscan[1] 便是一种回收站工具，并且只能通过插件采纳 hook 的形式来实现。

if (nodeTag(parsetree) == T_DropStmt)
{if (stmt->removeType == OBJECT_TABLE)
{AlterObjectSchemaStmt *newstmt = makeNode(AlterObjectSchemaStmt);
newstmt->newschema = pstrdup(trashcan_nspname);

通过其代码示例能够看出，DROP TABLE 操作被转换成了 ALTER 操作。

因为 pgtrashcan 代码古老，曾经有 8 年未更新，不适配新版本 PG。且仅反对挪动性能，并不反对彻底清除性能。由此，pgtrashcan 做了很多优化。

  • 反对新版本 PG 14/13/12
  • 通过插件的 depend 性能，依赖 pg_cron
  • 主动设置 pg_cron 将其回收站中超过 1 天的数据革除

DML 操作

通过参数 vacuum_defer_cleanup_age 来调整 Dead 元组在数据库中的量，以便复原误操作的数据。接下来将依据 流复制提早复原 和 备份复原 两种设计方案来具体介绍：

流复制提早复原

PostgreSQL 流复制时能够通过 recovery_min_apply_delay 设置相应的延迟时间。例如设置 5 小时，备库能够提早利用最近 5 小时的日志，提供最多 5 小时的数据恢复窗口，提早的利用日志的同时并不影响日志的承受，源库的日志依然是实时的被提早复原节点承受。

找回数据的具体操作步骤如下:

  1. 暂停提早复原 pg_wal_replay_pause()；
  2. 通过 pg_dump 或 copy 操作将其须要的数据找进去；
  3. 通过 psql、copy、pg_restore 等操作将数据导入源库中；
  4. 持续提早 pg_wal_replay_resume()。

备份复原

从备份模式的角度来说，备份次要包含以下两种：

  • 逻辑备份
    不能进行实时备份，因而不太实用于数据找回，会失落很多数据。
  • 物理备份
    物理备份领有与源集群完全一致的数据，因而能够继续应用源集群的 WAL 日志，达到数据找回的指标，原理上也是提早复原。

物理备份与 PITR 联合，可复原数据到任意工夫点。可选用工具有很多，如下几种是罕用的复原工具。

  • pg_basebackup[2]
  • pg_probackup[3]
  • pgbackrest[4]
  • barman[5]
  • pg_rman[6]

总结

  1. 留神权限划分。危险操作或是 DDL 等影响大的操作，肯定要由 第二个数据库管理员 操作。
  2. 提前做好数据找回和数据安全的计划布局。
  3. 流复制提早复原，同样须要设置 recovery_target_xid、recovery_target_time 或 recovery_target_lsn 来精准的定位到残缺的数据集。
  4. pg_waldump 是数据找回必备的一个性能。
  5. 如果计划是重型的，轻型的插件有时会是更好的抉择。
  6. 若无任何筹备，且不能装置任何插件，可 第一工夫将数据库关机！！！避免 Dead 元组被清理，拷贝整个集群，应用拷贝后的集群用 pg_resetwal 进行数据恢复。

参考援用

[1]：pgtrashcan：https://github.com/petere/pgt…

[2]：pg_basebackup：https://www.postgresql.org/do…

[3]：pg_probackup：https://github.com/postgrespr…

[4]：pgbackrest：https://github.com/pgbackrest…

[5]：barman：https://github.com/Enterprise…

[6]：pg_rman：https://github.com/ossc-db/pg…

退出移动版