DEV Community

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

Posted 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.

Discussion (0)