作者：张连壮 PostgreSQL 研发负责人
从事多年 PostgreSQL 数据库内核开发，对 Citus 有非常深入的研究。
PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系型数据库管理系统。POSTGRES的许多领先概念只是在比较迟的时候才出现在商业网站数据库中。PostgreSQL支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。同样，PostgreSQL也可以用许多方法扩展，例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外，因为许可证的灵活，任何人都可以以任何目的免费使用、修改和分发PostgreSQL。下面看下常用 PostgreSQL 预防数据丢失解决方案。
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 小时的数据恢复窗口，延迟的应用日志的同时并不影响日志的接受，源库的日志仍然是实时的被延迟恢复节点接受。
找回数据的具体操作步骤如下:
- 暂停延迟恢复
pg_wal_replay_pause()； - 通过 pg_dump 或 copy 操作将其需要的数据找出来；
- 通过 psql、copy、pg_restore 等操作将数据导入源库中；
- 继续延迟
pg_wal_replay_resume()。
备份恢复
从备份模式的角度来说，备份主要包括以下两种：
- 逻辑备份
不能进行实时备份，因此不太适用于数据找回，会丢失很多数据。
- 物理备份
物理备份拥有与源集群完全一致的数据，因此可以持续使用源集群的 WAL 日志，达到数据找回的目标，原理上也是延迟恢复。
物理备份与 PITR 结合，可恢复数据到任意时间点。可选用工具有很多，如下几种是常用的恢复工具。
- pg_basebackup[2]
- pg_probackup[3]
- pgbackrest[4]
- barman[5]
- pg_rman[6]
总结
- 注意权限划分。危险操作或是 DDL 等影响大的操作，一定要由第二个数据库管理员操作。
- 提前做好数据找回和数据安全的方案规划。
- 流复制延迟恢复，同样需要设置 recovery_target_xid 、recovery_target_time 或recovery_target_lsn 来精准的定位到完整的数据集。
- pg_waldump 是数据找回必备的一个功能。
- 如果方案是重型的，轻型的插件有时会是更好的选择。
- 若无任何准备，且不能安装任何插件，可第一时间将数据库关机！！！防止 Dead 元组被清理，拷贝整个集群，使用拷贝后的集群用 pg_resetwal 进行数据恢复。
参考引用
[1] ：pgtrashcan：https://github.com/petere/pgtrashcan
[2]：pg_basebackup：https://www.postgresql.org/docs/10/app-pgbasebackup.html
[3]：pg_probackup：https://github.com/postgrespro/pg_probackup
[4]：pgbackrest：https://github.com/pgbackrest/pgbackrest
[5]：barman：https://github.com/EnterpriseDB/barman
[6]：pg_rman：https://github.com/ossc-db/pg_rman
到此这篇关于常用 PostgreSQL 预防数据丢失方案的文章就介绍到这了,更多相关PostgreSQL数据丢失内容请搜索好代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持好代码网！
