[netcraft]

This problem is one of my biggest needs with postgres, although its certainly not limited to pg. listen/notify is fragile - you either have a single point of failure or you run multiple workers and have to deal with de-duping. Logical replication might one day be an answer, but not likely to ever work completely and without issue on cloud hosted instances. (maybe im wrong about this... see debezium comments elsewhere in the comments)

What ive been thinking of lately is doing triggers to write to a different table with a timestamp and doing a rolling log - the worker can listen/notify on that but can also keep track of the last message it saw and can go back and catch up when it first starts, so if it fails you dont lose anything. All I really need is a type (table) and the relevant keys. I think I would prefer a way to configure which tables I want to listen on and which columns (keys) I care about.

Really wish there was a standard postgres provided way to notify an external system of changes though. Its certainly not an easy issue.

[fritzy]

`table -> trigger to log table -> trigger to notify` is exactly the right solution here. You can even use the same stored proc to replay old logs.