PostgreSQL：如何在CREATE/DROP TABLE操作时捕获表名？

一、在DROP TABLE执行前获取表名 #

你可以利用ddl_command_start阶段的事件触发器，结合pg_event_trigger_ddl_commands()内置函数，在DDL操作执行前捕获目标表名，这个方法能覆盖CREATE TABLE和DROP TABLE所有场景。

实现步骤 #

1. 先创建用于记录DDL事件的日志表（如果没有的话）：

CREATE SCHEMA IF NOT EXISTS logs;
CREATE TABLE IF NOT EXISTS logs.ddl_events (
    id SERIAL PRIMARY KEY,
    event_time TIMESTAMP WITH TIME ZONE DEFAULT now(),
    command_tag TEXT NOT NULL,
    object_name TEXT NOT NULL
);

2. 创建事件触发器函数：

CREATE OR REPLACE FUNCTION capture_ddl_details()
RETURNS event_trigger AS $$
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        -- 只捕获CREATE TABLE和DROP TABLE事件
        IF rec.command_tag IN ('CREATE TABLE', 'DROP TABLE') THEN
            INSERT INTO logs.ddl_events (command_tag, object_name)
            VALUES (rec.command_tag, rec.object_identity);
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

3. 创建绑定到ddl_command_start阶段的事件触发器：

CREATE EVENT TRIGGER capture_ddl_trigger
ON ddl_command_start
EXECUTE FUNCTION capture_ddl_details();

二、获取已删除表的数据 #

如果DROP TABLE已经执行，无法直接从当前数据库恢复数据，只能依赖预先的备份机制：

• 定时逻辑备份：用pg_dump定期导出指定表或整个数据库，DROP后从备份文件恢复数据；
• WAL日志归档：确保wal_level设置为replica或更高，开启WAL归档，通过pg_waldump解析归档日志，定位DROP操作前的事务，结合工具恢复数据；
• 实时备份触发器：提前在ddl_command_start触发器中加入逻辑，检测到DROP TABLE时自动复制表数据到备份表（示例如下）：

CREATE OR REPLACE FUNCTION auto_backup_before_drop()
RETURNS event_trigger AS $$
DECLARE
    rec record;
    backup_table_name TEXT;
BEGIN
    FOR rec IN SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        IF rec.command_tag = 'DROP TABLE' THEN
            -- 生成带时间戳的备份表名
            backup_table_name := rec.object_identity || '_backup_' || to_char(now(), 'YYYYMMDDHH24MISS');
            -- 复制原表的结构和数据
            EXECUTE format('CREATE TABLE %I AS TABLE %I', backup_table_name, rec.object_identity);
            RAISE NOTICE 'Auto-created backup table: %', backup_table_name;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER auto_backup_trigger
ON ddl_command_start
EXECUTE FUNCTION auto_backup_before_drop();

内容的提问来源于stack exchange，提问作者Molarix