0

I have a AWS postgresql database , and I need some how to track changes on one table ( update ,delete , insert ) ..

I have I have been reading some things about postgresql CDC , but can´t find information how in my spring boot application .. how can I track / listen to this changes ...

if would be possible to shared some links on this or "relate " .. I would be very grateful to you all

thank you

Antonio Roque
  • 165
  • 2
  • 3
  • 12
  • Read about usage of `trigger` to `track` the DML changes to a certain Table, this [article](https://vladmihalcea.com/postgresql-audit-logging-triggers/) – Anand Sowmithiran Nov 26 '21 at 10:49
  • Also, usage of `Temporal` pg extension also can keep track of all data changes done to a table, refer this [article](https://nathancooper.dev/articles/2020-04/temporal-tables-part-2). – Anand Sowmithiran Nov 26 '21 at 11:10
  • I can´t use a polling system read that table with the changes , I´m trying to do this https://debezium.io/documentation/reference/1.0/connectors/postgresql.html#amazon-rds... but this is all need for me ... – Antonio Roque Nov 26 '21 at 13:30
  • Temporal extension or using trigger is not a polling system. Since your initial question is for tracking one table, the above 2 suggestions were given. – Anand Sowmithiran Nov 26 '21 at 14:24
  • yes , but how do you "monitor" the book_audit_log table without a polling system on that table ? – Antonio Roque Nov 26 '21 at 14:32
  • A trigger on a table gets executed after or before the INSERT/UPDATE/DELETE operation by the dbms engine itself, it does not poll. – Anand Sowmithiran Nov 26 '21 at 14:46
  • I in my spring boot application need to reach in time frame of second´s , on any change .. let´s say on books table , those changes are written to book_audit_log table by trigger on the books table ... but .. but who my spring boot´s app monitor the book_audit_log for incoming changes ???? – Antonio Roque Nov 26 '21 at 15:01
  • There are ways to execute java program from within a trigger, but not at all good. There is async notifications in one of the jdbc driver for Postgres named PGJDBC-NG, look at [this](https://stackoverflow.com/questions/21632243/how-do-i-get-asynchronous-event-driven-listen-notify-support-in-java-using-a-p) – Anand Sowmithiran Nov 26 '21 at 15:22
  • Postgresql's `LISTEN` and `NOTIFY` commands are the underlying mechanism, you can read it up [here](https://www.postgresql.org/docs/14/sql-notify.html) – Anand Sowmithiran Nov 26 '21 at 15:39

0 Answers0