Re:A list of PostgreSQL libraries, tools and resources
by AlterEgo20 on Monday February 15, 2016 @03:22PM
(#11105663)
Google "Postgres BDR". It is new, but it allows you to replicate changes on master to another master. With some configuration it can even change replicated data.
Re:A list of PostgreSQL libraries, tools and resources
by Cieplak on Monday February 15, 2016 @03:38PM
(#11105766)
Thank you. I believe that bidirectional replication will solve the ETL problem, if it can be configured to stream from a replica in the source/oltp cluster (to minimize network IO on the source cluster master db) to the master of the destination/olap cluster. The olap master can mirror the oltp data, but also use triggers to denormalize oltp tables.
One thing I'd like to see is the ability to sanitize/munge data from the source cluster before it is sent to the destination cluster, for masking sensitive data in the source cluster (PCI, HIPAA, etc).
Re:A list of PostgreSQL libraries, tools and resources
by beermann on Monday February 15, 2016 @05:32PM
(#11106552)
Bucardo does this and the setup it a lot easier than most tools. I'm currently using it to migrate a live postgres database to another location but it can perform ETL as well.
EDIT: doesn't really address your point b) however.
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