What is the best way to ETL data from one Postgres cluster to another (by ETL I mean things like data sanitization and denormalization)?
a) I think most people end up doing some kind of batch synchronization, but I'm interested in streaming solutions.
b) A lot of folks use trigger based replication, but triggers have to be on the primary/master node, and not just on the replicas.
c) Another common solution is to force the database client to write to a message broker, but that opens the door to data discrepancies and synchronization issues.
d) In theory I think the best way is to do something like bottledwater-pg or pg_kafka [1] [2] [3], but I'm not sure how battle hardened these are. I think logical replication of the WAL is the right approach, but there is still not much tooling around this.
[1] https://github.com/confluentinc/bottledwater-pg
[2] https://github.com/xstevens/pg_kafka
[3] https://github.com/xstevens/decoderbufs
PS: There are a bunch of interesting MySQL solutions out there, such as Zendesk's Maxwell:
https://github.com/zendesk/maxwell
a) I think most people end up doing some kind of batch synchronization, but I'm interested in streaming solutions.
b) A lot of folks use trigger based replication, but triggers have to be on the primary/master node, and not just on the replicas.
c) Another common solution is to force the database client to write to a message broker, but that opens the door to data discrepancies and synchronization issues.
d) In theory I think the best way is to do something like bottledwater-pg or pg_kafka [1] [2] [3], but I'm not sure how battle hardened these are. I think logical replication of the WAL is the right approach, but there is still not much tooling around this.
[1] https://github.com/confluentinc/bottledwater-pg
[2] https://github.com/xstevens/pg_kafka
[3] https://github.com/xstevens/decoderbufs
PS: There are a bunch of interesting MySQL solutions out there, such as Zendesk's Maxwell:
https://github.com/zendesk/maxwell