DEV Community

Cover image for Tracking dropped database objects in PostgreSQL
Bolaji Wahab
Bolaji Wahab

Posted on • Updated on


Tracking dropped database objects in PostgreSQL

Ever been in the situation where you need to perform a point-in-time recovery to recover a dropped database object and you need to find the exact time the object was dropped?.
Finding the exact time is not always easy, most times it is all about guesses, we have all been there.
This is why I wrote a simple extension pg_drop_events some times back.

pg_drop_events is a wrapper which utilises PostgreSQL's event triggers, wraps around the dropping statement and logs the info into pg_drop_events table.

A sample data looks like below:

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

Enter fullscreen mode Exit fullscreen mode

The info from pg_drop_events can be used to perform point-in-time recovery, well that is the aim of the extension, to aid point-in-time recovery.

PostgreSQL provides various runtime config to perform point-in-time recovery. The full list can be found here recovery target

Personally, I prefer using xact_id to perform point-in-time recovery of a dropped database object. I will talk about the reasons in the next chapter.

Top comments (2)

tfoertsch profile image
Torsten Förtsch

Congrats Bolaji. Seems like you were a good student.

bolajiwahab profile image
Bolaji Wahab

Yes and thanks so much for all your guidance, really grateful.
Hopefully we get to meet sometimes in Berlin. 🙂

An Animated Guide to Node.js Event Loop

>> Check out this classic DEV post <<