pg_drop_events

记录删表删列删视图的事务号，辅助PITR确定时间点

概览

扩展包名	版本	分类	许可证	语言
`pg_drop_events`	`0.1.0`	ADMIN	PostgreSQL	SQL

ID	扩展名	Bin	Lib	Load	Create	Trust	Reloc	模式
5850	`pg_drop_events`	否	否	否	是	否	否	`public`

相关扩展	`plpgsql` `table_log` `safeupdate` `pg_strict` `pg_savior` `pgmemento` `pgaudit` `pgelog` `block_copy_command` `pg_kpart` `data_historization`

版本

类型	仓库	版本	PG 大版本	包名	依赖
EXT	PIGSTY	`0.1.0`	1817161514	`pg_drop_events`	`plpgsql`
RPM	PIGSTY	`0.1.0`	1817161514	`pg_drop_events_$v`	-
DEB	PIGSTY	`0.1.0`	1817161514	`postgresql-$v-pg-drop-events`	-

OS / PG	PG18	PG17	PG16	PG15	PG14
el8.x86_64	PIGSTY 0.1.0 el8.x86_64.pg18 : pg_drop_events_18 pg_drop_events_18-0.1.0-1PIGSTY.el8.x86_64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el8.x86_64.pg17 : pg_drop_events_17 pg_drop_events_17-0.1.0-1PIGSTY.el8.x86_64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el8.x86_64.pg16 : pg_drop_events_16 pg_drop_events_16-0.1.0-1PIGSTY.el8.x86_64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el8.x86_64.pg15 : pg_drop_events_15 pg_drop_events_15-0.1.0-1PIGSTY.el8.x86_64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el8.x86_64.pg14 : pg_drop_events_14 pg_drop_events_14-0.1.0-1PIGSTY.el8.x86_64.rpm PIGSTY · 0.1.0 · 10.9KiB
el8.aarch64	PIGSTY 0.1.0 el8.aarch64.pg18 : pg_drop_events_18 pg_drop_events_18-0.1.0-1PIGSTY.el8.aarch64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el8.aarch64.pg17 : pg_drop_events_17 pg_drop_events_17-0.1.0-1PIGSTY.el8.aarch64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el8.aarch64.pg16 : pg_drop_events_16 pg_drop_events_16-0.1.0-1PIGSTY.el8.aarch64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el8.aarch64.pg15 : pg_drop_events_15 pg_drop_events_15-0.1.0-1PIGSTY.el8.aarch64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el8.aarch64.pg14 : pg_drop_events_14 pg_drop_events_14-0.1.0-1PIGSTY.el8.aarch64.rpm PIGSTY · 0.1.0 · 10.9KiB
el9.x86_64	PIGSTY 0.1.0 el9.x86_64.pg18 : pg_drop_events_18 pg_drop_events_18-0.1.0-1PIGSTY.el9.x86_64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el9.x86_64.pg17 : pg_drop_events_17 pg_drop_events_17-0.1.0-1PIGSTY.el9.x86_64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el9.x86_64.pg16 : pg_drop_events_16 pg_drop_events_16-0.1.0-1PIGSTY.el9.x86_64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el9.x86_64.pg15 : pg_drop_events_15 pg_drop_events_15-0.1.0-1PIGSTY.el9.x86_64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el9.x86_64.pg14 : pg_drop_events_14 pg_drop_events_14-0.1.0-1PIGSTY.el9.x86_64.rpm PIGSTY · 0.1.0 · 10.9KiB
el9.aarch64	PIGSTY 0.1.0 el9.aarch64.pg18 : pg_drop_events_18 pg_drop_events_18-0.1.0-1PIGSTY.el9.aarch64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el9.aarch64.pg17 : pg_drop_events_17 pg_drop_events_17-0.1.0-1PIGSTY.el9.aarch64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el9.aarch64.pg16 : pg_drop_events_16 pg_drop_events_16-0.1.0-1PIGSTY.el9.aarch64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el9.aarch64.pg15 : pg_drop_events_15 pg_drop_events_15-0.1.0-1PIGSTY.el9.aarch64.rpm PIGSTY · 0.1.0 · 10.9KiB	PIGSTY 0.1.0 el9.aarch64.pg14 : pg_drop_events_14 pg_drop_events_14-0.1.0-1PIGSTY.el9.aarch64.rpm PIGSTY · 0.1.0 · 10.9KiB
el10.x86_64	PIGSTY 0.1.0 el10.x86_64.pg18 : pg_drop_events_18 pg_drop_events_18-0.1.0-1PIGSTY.el10.x86_64.rpm PIGSTY · 0.1.0 · 11.0KiB	PIGSTY 0.1.0 el10.x86_64.pg17 : pg_drop_events_17 pg_drop_events_17-0.1.0-1PIGSTY.el10.x86_64.rpm PIGSTY · 0.1.0 · 11.0KiB	PIGSTY 0.1.0 el10.x86_64.pg16 : pg_drop_events_16 pg_drop_events_16-0.1.0-1PIGSTY.el10.x86_64.rpm PIGSTY · 0.1.0 · 11.0KiB	PIGSTY 0.1.0 el10.x86_64.pg15 : pg_drop_events_15 pg_drop_events_15-0.1.0-1PIGSTY.el10.x86_64.rpm PIGSTY · 0.1.0 · 11.0KiB	PIGSTY 0.1.0 el10.x86_64.pg14 : pg_drop_events_14 pg_drop_events_14-0.1.0-1PIGSTY.el10.x86_64.rpm PIGSTY · 0.1.0 · 11.0KiB
el10.aarch64	PIGSTY 0.1.0 el10.aarch64.pg18 : pg_drop_events_18 pg_drop_events_18-0.1.0-1PIGSTY.el10.aarch64.rpm PIGSTY · 0.1.0 · 11.0KiB	PIGSTY 0.1.0 el10.aarch64.pg17 : pg_drop_events_17 pg_drop_events_17-0.1.0-1PIGSTY.el10.aarch64.rpm PIGSTY · 0.1.0 · 11.0KiB	PIGSTY 0.1.0 el10.aarch64.pg16 : pg_drop_events_16 pg_drop_events_16-0.1.0-1PIGSTY.el10.aarch64.rpm PIGSTY · 0.1.0 · 11.0KiB	PIGSTY 0.1.0 el10.aarch64.pg15 : pg_drop_events_15 pg_drop_events_15-0.1.0-1PIGSTY.el10.aarch64.rpm PIGSTY · 0.1.0 · 11.0KiB	PIGSTY 0.1.0 el10.aarch64.pg14 : pg_drop_events_14 pg_drop_events_14-0.1.0-1PIGSTY.el10.aarch64.rpm PIGSTY · 0.1.0 · 11.0KiB
d12.x86_64	PIGSTY 0.1.0 d12.x86_64.pg18 : postgresql-18-pg-drop-events postgresql-18-pg-drop-events_0.1.0-1PIGSTY~bookworm_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d12.x86_64.pg17 : postgresql-17-pg-drop-events postgresql-17-pg-drop-events_0.1.0-1PIGSTY~bookworm_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d12.x86_64.pg16 : postgresql-16-pg-drop-events postgresql-16-pg-drop-events_0.1.0-1PIGSTY~bookworm_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d12.x86_64.pg15 : postgresql-15-pg-drop-events postgresql-15-pg-drop-events_0.1.0-1PIGSTY~bookworm_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d12.x86_64.pg14 : postgresql-14-pg-drop-events postgresql-14-pg-drop-events_0.1.0-1PIGSTY~bookworm_amd64.deb PIGSTY · 0.1.0 · 7.8KiB
d12.aarch64	PIGSTY 0.1.0 d12.aarch64.pg18 : postgresql-18-pg-drop-events postgresql-18-pg-drop-events_0.1.0-1PIGSTY~bookworm_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d12.aarch64.pg17 : postgresql-17-pg-drop-events postgresql-17-pg-drop-events_0.1.0-1PIGSTY~bookworm_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d12.aarch64.pg16 : postgresql-16-pg-drop-events postgresql-16-pg-drop-events_0.1.0-1PIGSTY~bookworm_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d12.aarch64.pg15 : postgresql-15-pg-drop-events postgresql-15-pg-drop-events_0.1.0-1PIGSTY~bookworm_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d12.aarch64.pg14 : postgresql-14-pg-drop-events postgresql-14-pg-drop-events_0.1.0-1PIGSTY~bookworm_arm64.deb PIGSTY · 0.1.0 · 7.8KiB
d13.x86_64	PIGSTY 0.1.0 d13.x86_64.pg18 : postgresql-18-pg-drop-events postgresql-18-pg-drop-events_0.1.0-1PIGSTY~trixie_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d13.x86_64.pg17 : postgresql-17-pg-drop-events postgresql-17-pg-drop-events_0.1.0-1PIGSTY~trixie_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d13.x86_64.pg16 : postgresql-16-pg-drop-events postgresql-16-pg-drop-events_0.1.0-1PIGSTY~trixie_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d13.x86_64.pg15 : postgresql-15-pg-drop-events postgresql-15-pg-drop-events_0.1.0-1PIGSTY~trixie_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d13.x86_64.pg14 : postgresql-14-pg-drop-events postgresql-14-pg-drop-events_0.1.0-1PIGSTY~trixie_amd64.deb PIGSTY · 0.1.0 · 7.8KiB
d13.aarch64	PIGSTY 0.1.0 d13.aarch64.pg18 : postgresql-18-pg-drop-events postgresql-18-pg-drop-events_0.1.0-1PIGSTY~trixie_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d13.aarch64.pg17 : postgresql-17-pg-drop-events postgresql-17-pg-drop-events_0.1.0-1PIGSTY~trixie_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d13.aarch64.pg16 : postgresql-16-pg-drop-events postgresql-16-pg-drop-events_0.1.0-1PIGSTY~trixie_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d13.aarch64.pg15 : postgresql-15-pg-drop-events postgresql-15-pg-drop-events_0.1.0-1PIGSTY~trixie_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 d13.aarch64.pg14 : postgresql-14-pg-drop-events postgresql-14-pg-drop-events_0.1.0-1PIGSTY~trixie_arm64.deb PIGSTY · 0.1.0 · 7.8KiB
u22.x86_64	PIGSTY 0.1.0 u22.x86_64.pg18 : postgresql-18-pg-drop-events postgresql-18-pg-drop-events_0.1.0-1PIGSTY~jammy_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u22.x86_64.pg17 : postgresql-17-pg-drop-events postgresql-17-pg-drop-events_0.1.0-1PIGSTY~jammy_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u22.x86_64.pg16 : postgresql-16-pg-drop-events postgresql-16-pg-drop-events_0.1.0-1PIGSTY~jammy_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u22.x86_64.pg15 : postgresql-15-pg-drop-events postgresql-15-pg-drop-events_0.1.0-1PIGSTY~jammy_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u22.x86_64.pg14 : postgresql-14-pg-drop-events postgresql-14-pg-drop-events_0.1.0-1PIGSTY~jammy_amd64.deb PIGSTY · 0.1.0 · 7.8KiB
u22.aarch64	PIGSTY 0.1.0 u22.aarch64.pg18 : postgresql-18-pg-drop-events postgresql-18-pg-drop-events_0.1.0-1PIGSTY~jammy_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u22.aarch64.pg17 : postgresql-17-pg-drop-events postgresql-17-pg-drop-events_0.1.0-1PIGSTY~jammy_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u22.aarch64.pg16 : postgresql-16-pg-drop-events postgresql-16-pg-drop-events_0.1.0-1PIGSTY~jammy_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u22.aarch64.pg15 : postgresql-15-pg-drop-events postgresql-15-pg-drop-events_0.1.0-1PIGSTY~jammy_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u22.aarch64.pg14 : postgresql-14-pg-drop-events postgresql-14-pg-drop-events_0.1.0-1PIGSTY~jammy_arm64.deb PIGSTY · 0.1.0 · 7.8KiB
u24.x86_64	PIGSTY 0.1.0 u24.x86_64.pg18 : postgresql-18-pg-drop-events postgresql-18-pg-drop-events_0.1.0-1PIGSTY~noble_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u24.x86_64.pg17 : postgresql-17-pg-drop-events postgresql-17-pg-drop-events_0.1.0-1PIGSTY~noble_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u24.x86_64.pg16 : postgresql-16-pg-drop-events postgresql-16-pg-drop-events_0.1.0-1PIGSTY~noble_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u24.x86_64.pg15 : postgresql-15-pg-drop-events postgresql-15-pg-drop-events_0.1.0-1PIGSTY~noble_amd64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u24.x86_64.pg14 : postgresql-14-pg-drop-events postgresql-14-pg-drop-events_0.1.0-1PIGSTY~noble_amd64.deb PIGSTY · 0.1.0 · 7.8KiB
u24.aarch64	PIGSTY 0.1.0 u24.aarch64.pg18 : postgresql-18-pg-drop-events postgresql-18-pg-drop-events_0.1.0-1PIGSTY~noble_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u24.aarch64.pg17 : postgresql-17-pg-drop-events postgresql-17-pg-drop-events_0.1.0-1PIGSTY~noble_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u24.aarch64.pg16 : postgresql-16-pg-drop-events postgresql-16-pg-drop-events_0.1.0-1PIGSTY~noble_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u24.aarch64.pg15 : postgresql-15-pg-drop-events postgresql-15-pg-drop-events_0.1.0-1PIGSTY~noble_arm64.deb PIGSTY · 0.1.0 · 7.8KiB	PIGSTY 0.1.0 u24.aarch64.pg14 : postgresql-14-pg-drop-events postgresql-14-pg-drop-events_0.1.0-1PIGSTY~noble_arm64.deb PIGSTY · 0.1.0 · 7.8KiB
u26.x86_64	PIGSTY 0.1.0 u26.x86_64.pg18 : postgresql-18-pg-drop-events postgresql-18-pg-drop-events_0.1.0-1PIGSTY~resolute_amd64.deb PIGSTY · 0.1.0 · 5.6KiB	PIGSTY 0.1.0 u26.x86_64.pg17 : postgresql-17-pg-drop-events postgresql-17-pg-drop-events_0.1.0-1PIGSTY~resolute_amd64.deb PIGSTY · 0.1.0 · 5.6KiB	PIGSTY 0.1.0 u26.x86_64.pg16 : postgresql-16-pg-drop-events postgresql-16-pg-drop-events_0.1.0-1PIGSTY~resolute_amd64.deb PIGSTY · 0.1.0 · 5.6KiB	PIGSTY 0.1.0 u26.x86_64.pg15 : postgresql-15-pg-drop-events postgresql-15-pg-drop-events_0.1.0-1PIGSTY~resolute_amd64.deb PIGSTY · 0.1.0 · 5.6KiB	PIGSTY 0.1.0 u26.x86_64.pg14 : postgresql-14-pg-drop-events postgresql-14-pg-drop-events_0.1.0-1PIGSTY~resolute_amd64.deb PIGSTY · 0.1.0 · 5.6KiB
u26.aarch64	PIGSTY 0.1.0 u26.aarch64.pg18 : postgresql-18-pg-drop-events postgresql-18-pg-drop-events_0.1.0-1PIGSTY~resolute_arm64.deb PIGSTY · 0.1.0 · 5.6KiB	PIGSTY 0.1.0 u26.aarch64.pg17 : postgresql-17-pg-drop-events postgresql-17-pg-drop-events_0.1.0-1PIGSTY~resolute_arm64.deb PIGSTY · 0.1.0 · 5.6KiB	PIGSTY 0.1.0 u26.aarch64.pg16 : postgresql-16-pg-drop-events postgresql-16-pg-drop-events_0.1.0-1PIGSTY~resolute_arm64.deb PIGSTY · 0.1.0 · 5.6KiB	PIGSTY 0.1.0 u26.aarch64.pg15 : postgresql-15-pg-drop-events postgresql-15-pg-drop-events_0.1.0-1PIGSTY~resolute_arm64.deb PIGSTY · 0.1.0 · 5.6KiB	PIGSTY 0.1.0 u26.aarch64.pg14 : postgresql-14-pg-drop-events postgresql-14-pg-drop-events_0.1.0-1PIGSTY~resolute_arm64.deb PIGSTY · 0.1.0 · 5.6KiB

构建

您可以使用 pig build 命令构建 pg_drop_events 扩展的 RPM / DEB 包：

BASH

pig build pkg pg_drop_events         # 构建 RPM / DEB 包

安装

您可以直接安装 pg_drop_events 扩展包的预置二进制包，首先确保 PGDG 和 PIGSTY 仓库已经添加并启用：

BASH

pig repo add pgsql -u          # 添加仓库并更新缓存

使用 pig 或者是 apt/yum/dnf 安装扩展：

安装

BASH

pig install pg_drop_events;          # 当前活跃 PG 版本安装

pig

BASH

pig ext install -y pg_drop_events -v 18  # PG 18
pig ext install -y pg_drop_events -v 17  # PG 17
pig ext install -y pg_drop_events -v 16  # PG 16
pig ext install -y pg_drop_events -v 15  # PG 15
pig ext install -y pg_drop_events -v 14  # PG 14

dnf

BASH

dnf install -y pg_drop_events_18       # PG 18
dnf install -y pg_drop_events_17       # PG 17
dnf install -y pg_drop_events_16       # PG 16
dnf install -y pg_drop_events_15       # PG 15
dnf install -y pg_drop_events_14       # PG 14

apt

BASH

apt install -y postgresql-18-pg-drop-events   # PG 18
apt install -y postgresql-17-pg-drop-events   # PG 17
apt install -y postgresql-16-pg-drop-events   # PG 16
apt install -y postgresql-15-pg-drop-events   # PG 15
apt install -y postgresql-14-pg-drop-events   # PG 14

创建扩展：

SQL

CREATE EXTENSION pg_drop_events CASCADE;  -- 依赖: plpgsql

用法

pg_drop_events: 记录 DROP TABLE、DROP COLUMN、DROP MATERIALIZED VIEW 语句的事务 ID

pg_drop_events 扩展使用事件触发器自动记录对表、列和物化视图的 DROP 操作详情。记录的信息可用于意外删除后的时间点恢复（PITR）。

跟踪的操作

DROP TABLE
DROP COLUMN（通过 ALTER TABLE）
DROP MATERIALIZED VIEW

记录的信息

列	描述
`pid`	进程标识符
`usename`	执行命令的数据库用户
`query`	SQL 语句
`xact_id`	事务标识符
`wal_position`	预写日志位置
`objid`	对象标识符
`object_name`	被删除对象的完全限定名称
`object_type`	对象分类（表、表列等）
`xact_time`	事务时间戳

示例

SQL

CREATE EXTENSION pg_drop_events;

-- 删除一个表
DROP TABLE t.t3;
-- NOTICE: table t.t3 dropped by transaction 1085.

-- 查询事件日志
SELECT * FROM pg_drop_events;

时间点恢复

记录的数据直接映射到 PostgreSQL 恢复参数：

pg_drop_events 列	恢复参数
`xact_id`	`recovery_target_xid`
`xact_time`	`recovery_target_time`
`wal_position`	`recovery_target_lsn`

在 postgresql.conf 或 recovery.conf 中使用这些值将数据库恢复到意外删除之前的时间点。

意见反馈

这个页面对您有帮助吗？

感谢反馈！请告诉我们如何改进。

抱歉给您带来不便。请告诉我们如何改进。