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
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)
Congrats Bolaji. Seems like you were a good student.
Yes and thanks so much for all your guidance, really grateful.
Hopefully we get to meet sometimes in Berlin. 🙂