练习PG两年半,手搓回收站插件

今天的案例来自于我的同事楚帝（不是花名，让我称呼他楚弟，但他太牛了，我们私下叫他楚帝），一名非常优秀的PostgreSQL DBA。

我们在测试研究PDU时，聊到了误删保护的话题，关于PG回收站的功能本来还有些小争论，在浏览器搜索PG回收站时，看到了德哥2014年写的博客^[1]，介绍了一个插件，我们立马达成一致，去测试这个插件。不过年代久远，并不适配高版本。所以他就参考这个插件的思路，2.5小时手搓了一个插件，老杨直呼牛X！！！

现在的年轻人不得了，长得帅，学历高，能力强，猛。

以下均为我们个人愚见，各位读者大大多多包涵。

不可预见的悲剧

没有人想故意搞出故障。常言道“常在河边走哪有不湿鞋”，哪怕是经验再丰富的DBA老鸟，也有可能走背字误操作了表。

• 脚本问题，连错环境了！！！

• 没戴老花镜，清错表了！！！

• 一时激动，手抖了！！！

这样的悲剧屡见不鲜，由于PG原生并没有回收站能力，一般误删数据，就得依赖物理备份PITR，通常RTO会比较久。

当然我们现在有PDU和WALminer，可以加快恢复，功能都比较强大，但我希望大家永远都用不到这两个工具…

从某些视角来看，误操作也是流程管理层面的漏洞。“就不应该直接操作数据库！！！要有审批流程，凡是操作数据库统一走审批单，相关干系人审批通过，才可以下发执行”。这就要求我们要建设变更流程系统，白屏化操作数据库。严格执行，定能降低误操作的概率。

除了流程层面的建设，数据库本身能不能做些保护机制呢？我们不能总是等到出问题再去恢复呀，往往出问题后，恢复只是开始，后续还有一轮又一轮的复盘，汇报，整改。

所以，为什么不做点预防手段？对关键表做些风险操作保护呢？

可以尝试做些“SQL防火墙”或者“回收站”的能力。

回收站思路

PDU确实牛，还是忍不住要吹下。 在测完PDU后楚帝问我为什么PG不做回收站？

老杨又开始瞎讲：”要从PG没有undo的多版本并发架构说起…”，我们争论起来了。我看过一些扩展，有些可以在误操作后，vacuum之前将dead tuple还原回来。 当然还有基于支持undo的存储引擎提供的闪回方案，相对就比较复杂了。

他说为什么要简单问题复杂化，对于表，最危险的就是drop和truncate直接把数据搞没了。

想想RDS回收站的功能，是把实例放入回收站，这个阶段并不是真正销毁了实例，只是无法正常访问了。可以确认后手动销毁或等到一定周期后自动销毁，销毁之前可以随时恢复回去，我们为什么不能借鉴这个思路呢。

老杨感觉很有道理，有的时候觉得自己也一把年纪了不愿意消耗脑细胞去思考，但是有这样的同事，不思考不行呐，容易被拍死在沙滩上。

所以我们打算调研下，目标是实现drop和truncate的保护机制。

手搓回收站

我首先想到的是创建DDL event trigger，简单省事。 当执行drop/truncate table时触发其他命令，比如将表rename或者切换到其他schema，做一层保护，再次check后再做删除。

楚帝觉得PG最牛的就是扩展能力，想尝试通过扩展来实现，刚好学习下extension的原理。老杨猜测肯定有这样的扩展，就去浏览器搜索了，没错，还是那个男人，德哥2014年的博客里^[1]介绍了pgtrashcan这个扩展，我们在新版本尝试编译报错了。我还想着改巴改巴试试，他说不然参考这个扩展的思路自己写吧。 我刚好去开会了，过了2.5小时，给我发消息，写好了，一起测试一下。这你受得了吗。

代码比较简单，我就描述下大致的逻辑：

扩展名称：pg_safe_recycle

利用ProcessUtility_hook，注册hook函数，对UtilityStmt做处理：

1、语句为drop table时，生成alterStmt，并替换原本的dropStmt，目标将表schema替换到“回收站”schema；

2、语句为truncate table时，生成alterStmt，并替换原本的TruncateStmt，目标将表schema替换到“回收站”schema，同时创建一个结构完全相同的空表。

并给插件设置了两个guc，一个决定是否打开回收站，对于业务核心表我们就可以表级别打开； 另一个是指定“回收站”schema的名称。

当然对于不同schema表名重复问题也做了些处理，回收站里通过时间戳后缀来区分。当做危险操作时，表实际上放入了“回收站”，想恢复随时可以恢复。

测试验证

1、truncate

postgres@postgres=# create extension pg_safe_recycle;CREATE EXTENSIONTime: 2.921 mspostgres@postgres=# \dnListof schemasName  |       Owner       --------+-------------------public | pg_database_owner(1row)postgres@postgres=# \dListof relationsSchema |          Name           | Type |  Owner   --------+-------------------------+------+----------public | pg_stat_statements      | view | postgrespublic | pg_stat_statements_info | view | postgres(2rows)postgres@postgres=# create table test(id int);CREATETABLETime: 2.086 mspostgres@postgres=# insert into test values (1),(2),(3);INSERT03Time: 0.950 mspostgres@postgres=# truncate table test;NOTICE:  pg_safe_recycle: table public.test moved to recycled_tables.test_truncate_20250905_120240 and recreated empty (TRUNCATE operation)TRUNCATETABLETime: 1.592 mspostgres@postgres=# \dt recycled_tables.*Listof relationsSchema      |             Name              | Type  |  Owner   -----------------+-------------------------------+-------+---------- recycled_tables | test_truncate_20250905_120240 | table | postgres(1row)postgres@postgres=# select * from recycled_tables.test_truncate_20250905_120240;id----123(3rows)Time: 0.288 ms

2、drop table

postgres@postgres=# create table test1(id int);CREATETABLETime: 1.219 mspostgres@postgres=# insert into test1 values (1),(2),(3);INSERT03Time: 0.921 mspostgres@postgres=# drop table test1;NOTICE:  pg_safe_recycle: table public.test1 moved to recycled_tables.test1_drop_20250905_120333 (DROP operation)DROPTABLETime: 1.372 mspostgres@postgres=# \dt recycled_tables.*Listof relationsSchema      |             Name              | Type  |  Owner   -----------------+-------------------------------+-------+---------- recycled_tables | test1_drop_20250905_120333    | table | postgres recycled_tables | test_truncate_20250905_120240 | table | postgres(2rows)postgres@postgres=# select * from  recycled_tables.test1_drop_20250905_120333;id----123(3rows)

3、多表truncate

postgres@postgres=# create table test1(id int);CREATETABLETime: 2.031 mspostgres@postgres=# create table test2(id int);CREATETABLETime: 2.249 mspostgres@postgres=# create table test3(id int);CREATETABLETime: 1.231 mspostgres@postgres=# truncate table test1,test2,test3;NOTICE:  pg_safe_recycle: table public.test1 moved to recycled_tables.test1_truncate_20250905_120533 and recreated empty (TRUNCATE operation)NOTICE:  pg_safe_recycle: table public.test2 moved to recycled_tables.test2_truncate_20250905_120533 and recreated empty (TRUNCATE operation)NOTICE:  pg_safe_recycle: table public.test3 moved to recycled_tables.test3_truncate_20250905_120533 and recreated empty (TRUNCATE operation)TRUNCATETABLETime: 2.782 mspostgres@postgres=# \dListof relationsSchema |          Name           | Type  |  Owner   --------+-------------------------+-------+----------public | pg_stat_statements      | view  | postgrespublic | pg_stat_statements_info | view  | postgrespublic | test                    | table | postgrespublic | test1                   | table | postgrespublic | test2                   | table | postgrespublic | test3                   | table | postgres(6rows)postgres@postgres=# \dt recycled_tables.*Listof relationsSchema      |              Name              | Type  |  Owner   -----------------+--------------------------------+-------+---------- recycled_tables | test1_drop_20250905_120333     | table | postgres recycled_tables | test1_drop_20250905_120437     | table | postgres recycled_tables | test1_truncate_20250905_120533 | table | postgres recycled_tables | test2_drop_20250905_120502     | table | postgres recycled_tables | test2_truncate_20250905_120533 | table | postgres recycled_tables | test3_drop_20250905_120502     | table | postgres recycled_tables | test3_truncate_20250905_120533 | table | postgres recycled_tables | test_truncate_20250905_120240  | table | postgres(8rows)

总结与感悟

楚帝写了一篇设计文档，其中介绍了扩展设计的逻辑，并总结了他从中学习到了PG Hook的原理。

理解了_PG_Init中自定义参数的设置初始化，hook函数的初始化。以及扩展so加载，_PG_Init注册的过程。掌握了UtilityStmt的执行逻辑，和parsetree的处理逻辑，也算收获满满了。

其实所有的插件原理都可以抽象为：

if(Hook)  Hook_func();else：  standard_func();

这就印证了老杨之前的观点，DBA搞内核，不用注重专业性，是否可实现，Just do it！！！最大的收益是透过现象看本质，对原理的深入理解掌握。