5 ms·
pg_flo – Stream, transform, and re-route PostgreSQL data in real-time
- ibgeek 2y agoThis is very cool!
- shayonj 2y agoThank you! Still very early days, would love to hear any feedback.
- rileymichael 2y agoPerfect timing — I was just looking into similar tools. If I want to do a bulk copy (say, nightly) with various transformations but not continually stream after, is that supported / a use case that’d be a good fit for your tool?
- zorgmonkey 2y agoIt looks with what is currently implemented you'd have to drop the tables from the destination if you want repeated copies, probably not quite what you want. Close enough to your use case that it still might be worth testing it out though.
- rileymichael 2y agoyeah that’s not an immediate deal breaker for me, I’m essentially looking for pgdump/restore + transformations. I’ll give it a look and see how it performs
- shayonj 2y agoThank you for giving this a spin. That's correct, today you'd need to drop the table before another sync with pg_flo. That said, I have given delta syncs some thought, and also looking into a control plane that can make some of these things easier. Would love to hear your feedback.
- shayonj 2y agoI think this also might be of interest to you - just one time copies and apply transformations - https://github.com/shayonj/pg_flo/issues/6 https://github.com/shayonj/pg_flo/issues/6. I will look into shipping that very soon.
- rileymichael 2y agoOh awesome, yeah that’d work perfect then. I’d just prep beforehand and run the one-time copy.
- shayonj 2y agoamazing! Will plan a release by Tuesday latest. You can experiment with `--copy-and-stream ` in the meantime [1] [1] https://github.com/shayonj/pg_flo?tab=readme-ov-file#streaming-modes https://github.com/shayonj/pg_flo?tab=readme-ov-file#streami...
- shayonj 2y agoHeya! v0.0.4 is now out with `--copy` support, which will stream all the data from the table and the transformation/routing will get applied before the changes land in destination. [1] https://github.com/shayonj/pg_flo?tab=readme-ov-file#streaming-modes https://github.com/shayonj/pg_flo?tab=readme-ov-file#streami... [2] https://github.com/shayonj/pg_flo/releases https://github.com/shayonj/pg_flo/releases
- rileymichael 2y agoAwesome turn around — I’ll give it a go this week!
- scirob 2y agoCool, Hope it can give an alterantive to Debezium. I never liked Debezium how it first must copy the whole CDC state to kafka. And you must set the kafka retnetion time to infinity, which many kafka as a service systems don't allow anyway.
- FridgeSeal 2y ago> how it first must copy the whole CDC state to kafka There’s a setting that controls whether it will do a snapshot first. Turn it off and it will just start sending through new cdc entries. > you must set the kafka retention time to infinity Is this a new retirement? I’ve never had to do this.
- jitl 2y agoI recently set up a Debezium / Kafka Connect cluster and didn’t need to do either of these things. I have some topics with 7 day retention, and some with 0 second retention. No full scan at all, not necessary for my use case.
- scirob 2y agopglogical can live inside postgres, looks like pg_flo is an external service not an extension. Maybe a benefit actually. Do you think we could use pg_flo with Postgres as a service instances like Azure postgres, Supabase, Neon etc? Like you just read the WAL without needing to install an extension that is not approved by the vendor.
- shayonj 2y agoYeah, absolutely! There are other benefits of being an extension, and yes you can use pg_flo with any PostgreSQL database or service since it uses logical replication to listen on changes and CTIDs for bulk copies.
- oulipo 2y agoWould it be better to use replication rather than a backup on S3?
- shayonj 2y agoIt depends how you are backing up to S3 and in what format. If you are using something like pg_dump, this tool may not make sense. But if you want to say sync to S3 in parquet or something like that, then it might. Parquet support is TBD for now.
- iyn 2y agoLooks interesting, I’ve been investigating Postgres CDC solutions for a while, so I’m curious if this could help my use-case. Can you elaborate on failure modes? What happens if e.g. NATS server (or worker/replicator) node dies? In principle, how hard is it move data from Postgres not to another PG but e.g. ElasticSearch/ClickHouse?
- shayonj 2y agore: worker/replicator dying, if the worker dies, nothing is basically reading from subject/queue (NATS), so the changes aren't reaching the destination. The default config is to have one worker. If the replicator dies, the changes from the replication slot isn't getting delivered to NATS, so the WAL size will grow on the source DB. There are some plans to introduce metrics/instrumentation to stay on top of these things, to make it more production ready. Replicator and worker can pause/resume the stream when say shutting down, say during a deploy. re: moving data, I haven't attempted it, but I have seen PeerDB mentioned for moving data to Clickhouse and it seems quite nice. I am starting w/ PostgreSQL <> PostgreSQL to mostly get the fundamentals right first. Would love to hear any use cases you have in mind.
- iyn 2y agoI have these use cases: 1. Syncing data in Postgres to ElasticSearch/ClickHouse (which handle search/analytics on the data I store in PG) 2. Invoking my own workflow engine — I have built a system that allows end-users to define triggers that start workflows when some data change/is created. To determine whether I need to start the workflow, I need to inspect every CRUD operation and check it against triggers defined by the users. I'm currently doing that in a duck-tape like way by publishing to SNS from my controllers and having SQS subscribers (mapped to Lambdas) that are responsible for different parts of my "pipeline". I don't like this system as it's not fault-tolerant and I'd prefer to do this by processing WAL and explicitly acknowledging processed changes.
- shayonj 2y agoI think PeerDB is a smart choice if you are looking to sync the data to Clickhouse. They have nice integration support as well. re: 2. I have some plans around a control plane that allows users to define these transformation rules and routing config and then take further actions based on the outcomes. If you are interested in it, feel free to sign up on the homepage. Also, very happy for some quick chats too (shayonj at gmail). Thanks
- gkhartman 2y agoI'm really looking forward to taking this for a spin. Thanks for sharing. Question: Can this handle network disconnection/instability mid copy?
- shayonj 2y agoThank you! Today, an active stream is resumeable, say in case a shutdown / SIGTERM come up. It also stores some state every flush. However, as for copy-and-stream, it’s not quite resumable today, but that's coming soon. Some more guardrails around network disruption and retries is on the way too.
- davidarenas 2y agoHow does it compare against something like Sequin? Looking into something that will allow a cdc event driven workflow but also arbitrarily replaying the event stream on demand. [1] https://github.com/sequinstream/sequin https://github.com/sequinstream/sequin
- shayonj 2y agoSequin sounds like a nice fit for that. I like it and yeah it does have replay. With pg_flo, I am looking for the tool to be fairly hands off, while declarative at the same time for data filtering, transformation and re-routing like tasks, starting with PostgreSQL as the destination for now. The less time you can spend on cleaning, moving / syncing your data the better. Also quite curious to see what other use cases folks find it useful for. Replay/reusability has been on my mind and have a sort of an old PR open. Will give it a shot soon. Open to any suggestions and feedback.
- 2000swebgeek 2y agopg_flo looks like a very interesting project we might end up using. I like the archival to s3 and cheap versions of tools that get done at lower scale like GBs instead of TBs worth of data (Which is what I think Debezium does and something I can easily test locally) The work/effort I need to put in with kafka etc., w/ Debezium is a short term effort, but evaluating the hassle. Other solutions being evaluated by my team is peerdb - https://docs.peerdb.io/why-peerdb https://docs.peerdb.io/why-peerdb
- shayonj 2y agoThanks for checking it out - Curious are you syncing/storing the data in some specific format in s3?
- mritchie712 2y agopeerdb was acquired by clickhouse, so if you're syncing to a destination other than clickhouse, you may see support for it wane
- bzzzt 2y agoThere's a standalone version of Debezium nowadays which can publish directly to a NATS queue. No Kafka required anymore.
- oriavicii 2y agoGreat tool! I've recently started experimenting with PostgreSQL logical replication as part of a project where we’re pruning data from our main database and archiving it to S3 for future use. However, some tables need to be available in a secondary Postgres instance specifically for read operations, which is where replication comes in. I’m encountering a challenge due to two types of delete operations in the main database. The first delete operation is for data pruning, and I don’t want these deletions to be reflected in the replicated database. The second delete operation is for rollback purposes, which alters the data state, and these deletions should indeed be replicated to maintain consistency. Is there a way to distinguish between these two delete operations so that only the rollback deletions are replicated? And would your tool support this kind of use case?
- shayonj 2y agoThanks for sharing. That is an interesting use case. I reckon you will be able to take advantage of the rules or routing feature in pg_flo. Is there some column that can help you identify the two separate types of deletion operations or perhaps time of the day? If its easier, happy to also chat more on an issue in the repo or email (shayonj at gmail)
- seedless-sensat 2y agoLooks interesting. Just wondering, how did you decide to use NATS as the transport, instead of Kafka?
- shayonj 2y agoHi! thanks for checking it out. Mostly based on my own familiarity and interest at the time, especially with NATS Jetstream being out now. That said, depending on the feedback and roadmap evolves - I have thought about, say, supporting different intermediaries where it makes sense. The API interface is more/less left open from that perspective. I am also planning on also making NATS optional, for smaller and simpler setups.
- distantsounds 2y agohttps://github.com/eulerto/wal2json https://github.com/eulerto/wal2json has existed for this purpose for many years, just transform the incoming records into JSON and parse with literally any standard tool that reads the format. There's a literal postgres extension for this.
- Dowwie 2y agoI'll evaluate this during my next CDC endeavor. Also on my list is Dozer: https://github.com/getdozer/dozer https://github.com/getdozer/dozer
- tempest_ 2y agoIf you click on getdozer.io you get redirected to https://langdb.ai/ https://langdb.ai/ Looks like they are pivoting to LLM integration.
- dboreham 2y agoHow does this related to disease control?
- lhoff 2y agoNot op but i assume he is referring to change data capture.
- shayonj 2y agoThank you! Would love to receive feedback, if you are up for it.
- Kerbonut 2y agoWould this be useful to upgrade major versions of PostgreSQL servers?
- shayonj 2y agoYeah, it would be, since its using logical replication. However, the standard logical replication limitation applies [1] pg_flo can perform common DDLs for you (not something that's in built in logical replication today, hopefully soon) [2] [1] https://www.postgresql.org/docs/current/logical-replication-restrictions.html https://www.postgresql.org/docs/current/logical-replication-... [2] https://github.com/shayonj/pg_flo/blob/0e8b6b9ca1caf768b71d3fd8883d3985c9682123/internal/pg-flo.yaml#L16 https://github.com/shayonj/pg_flo/blob/0e8b6b9ca1caf768b71d3...