Streaming from Postgres’ Logical replication log is the most efficient means of capturing changes with the least amount of overhead to your database. However, in some environments (i.e., unsupported versions, Heroku Postgres), you’re left with polling the database to monitor changes.
Typically when polling PostgreSQL to capture data changes, you can track the max value of a primary key (id) to know when an
INSERT operation occurred. Additionally, if your database has an
updateAt column, you can look at timestamp changes to capture
UPDATE operations, but it’s much harder to capture
Postgres Triggers and Functions are powerful features of Postgres that allow you to listen for
DELETE operations that occur within a table and insert the deleted row in a separate archive table. You can consider this a method of performing soft deletes, and this model is helpful to maintain the records for historical or analytical purposes or data recovery purposes.
In the commands below, we capture deletes from a table called
User, and the trigger will insert the deleted row into a table called
Step One: Create a new table
If you don’t have a table yet, you’ll need to create one. To help, you can easily copy an origin table:
CREATE TABLE “Deleted_User” AS TABLE “User” WITH NO DATA;
WITH NO DATA allows you to copy a table’s structure without data.
Step Two: Create a new Postgres Function
Next, we can create a new function named
CREATE FUNCTION moveDeleted() RETURNS trigger AS $$ BEGIN INSERT INTO "Deleted_User" VALUES((OLD).*); RETURN OLD; END; $$ LANGUAGE plpgsql;
Here we are using
VALUES((OLD).*) to send every column to the archive table, but you may update this to omit or even add new columns.
Step Three: Create a new Postgres Trigger
Lastly, we can create a Postgres Trigger named
moveDeleted that calls the
CREATE TRIGGER moveDeleted BEFORE DELETE ON "User" FOR EACH ROW EXECUTE PROCEDURE moveDeleted();
If you perform a
DELETE operation on the
User table, a new row with the deleted data will move to the
Now your archive table will begin to populate, data won’t be lost, and you can now monitor the archive table to capture
DELETE operations within your application.