DEV Community

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

Posted on • Edited on

11 3

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.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (2)

Collapse
 
tfoertsch profile image
Torsten Förtsch

Congrats Bolaji. Seems like you were a good student.

Collapse
 
bolajiwahab profile image
Bolaji Wahab

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

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more