The pg_drop_events is a PostgreSQL extension that logs transaction ids of drop table, drop column, drop materialized view statements to aid point in time recovery: To perform point in time recovery in case of a disaster whereby a table or a table column was mistakenly dropped, you simply specify the xact_id you get from the table pg_drop_events as the recovery_target_xid. See below user guide.
pg_drop_events uses event trigger to track what statement, what transaction and which user drops a table, a table column or a materialized view.
The pg_drop_events should work on the latest version of PostgreSQL but is only tested with these PostgreSQL versions:
| Distribution | Version | Supported |
|---|---|---|
| PostgreSQL | Version 9.5 | ✔️ |
| PostgreSQL | Version 9.6 | ✔️ |
| PostgreSQL | Version 10 | ✔️ |
| PostgreSQL | Version 11 | ✔️ |
| PostgreSQL | Version 12 | ✔️ |
| PostgreSQL | Version 13 | ✔️ |
| PostgreSQL | Version 14 | ✔️ |
You can download the source code of pg_drop_events from this GitHub page or using git:
git clone git@github.com:bolajiwahab/pg_drop_events.gitCompile and install the extension. Depending on your distribution, you might need to add sudo.
cd pg_drop_events
make clean && make installCreate the extension using the CREATE EXTENSION command.
CREATE EXTENSION pg_drop_events;
CREATE EXTENSIONThis document describes the configuration, key features and usage of pg_drop_events extension.
For how to install and set up pg_drop_events, see README.
After you've installed, create the pg_drop_events extension using the CREATE EXTENSION command.
CREATE EXTENSION pg_drop_events;
CREATE EXTENSIONpostgres=# CREATE SCHEMA t;
CREATE SCHEMA
postgres=# CREATE TABLE t.t1(a int);
CREATE TABLE
postgres=# CREATE TABLE t.t2();
CREATE TABLE
postgres=# CREATE TABLE t.t3();
CREATE TABLE
postgres=# DROP TABLE t.t3;
NOTICE: table t.t3 dropped by transaction 1085.
DROP TABLE
postgres=# ALTER TABLE t.t1 DROP COLUMN a;
NOTICE: table column t.t1.a dropped by transaction 1088.
ALTER TABLE
postgres=# DROP SCHEMA t CASCADE;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to table t.t2
drop cascades to table t.t1
NOTICE: table t.t2 dropped by transaction 1089.
NOTICE: table t.t1 dropped by transaction 1089.
DROP SCHEMA
postgres=# SELECT pid, usename, query, xact_id, wal_position, objid, object_name, object_type, xact_time FROM pg_drop_events;
pid | usename | query | xact_id | wal_position | objid | object_name | object_type | xact_time
-------+-----------+--------------------------------+---------+--------------+-------+-------------+--------------+-------------------------------
54630 | bolaji | DROP TABLE t.t3 | 25184 | 1/A266B090 | 51293 | t.t3 | table | 2022-05-04 17:16:32.913969+00
54633 | bolaji | ALTER TABLE t.t1 DROP COLUMN a | 25185 | 1/A266BBF8 | 51287 | t.t1.a | table column | 2022-05-04 17:16:39.033796+00
54638 | postgres | DROP SCHEMA t CASCADE | 25186 | 1/A266BEC0 | 51287 | t.t1 | table | 2022-05-04 17:16:56.094366+00
54639 | postgres | DROP SCHEMA t CASCADE | 25186 | 1/A266BEC0 | 51290 | t.t2 | table | 2022-05-04 17:16:56.094366+00
To perform point in time recovery, you need access to pg_drop_events data.
We have this mapping of options and the respective PostgreSQL recovery options:
pg_drop_events.xact_id => recovery_target_xid
pg_drop_events.time => recovery_target_time
pg_drop_events.wal_position => recovery_target_lsn
For reference, see https://www.postgresql.org/docs/13/runtime-config-wal.html
[Bolaji K. Wahab @bolajiwahab]
Copyright (c) 2021 Bolaji Wahab.
This module is free software; you can redistribute it and/or modify it under the PostgreSQL License.
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
In no event shall Bolaji K. Wahab be liable to any party for direct, indirect, special, incidental, or consequential damages, including lost profits, arising out of the use of this software and its documentation, even if Bolaji K. Wahab has been advised of the possibility of such damage.
Bolaji K. Wahab specifically disclaims any warranties, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The software provided hereunder is on an "as is" basis, and Bolaji K. Wahab has no obligations to provide maintenance, support, updates, enhancements, or modifications.