I've been reading about postgresql replication and was wondering if it was somehow possible to listen to the replication binary log and push that to some tool like sumologic or splunk so that any changes made could be monitored without having to build update triggers on every table. Is this possible?
2
-
Yes, you can do that through logical decoding– user1822Dec 4, 2019 at 18:51
-
Perhaps the wal2json logical decoding plugin is what you need.– Laurenz AlbeDec 5, 2019 at 8:19
Add a comment
|
1 Answer
As I understand it, your options depend a bit on a key requirement: Do you need the changes recorded in exact transactional order or not?
If you need them in exact order, then a change table or replication decoding are the two workable options I've heard of. Postgres does not implement incrementally updated materialized views, so that's out.
If you do not need the changes in precise commit order, then you can use an alternative strategy. For tables of interest, include a column that takes an ever-incrementing number. This is first assigned on INSERT (easy) and then you have to manage updates on UPDATE (harder.) From there, you have a number line that you can select from. If you track the last number you processed, you can then grab everything > than that, and how you have your unprocessed rows. The primary advantage of such a solution is that you can use that same number line to track different rollups, etc.
Logical decoding is probably going to be simplest.
