Open-source Postgres CDC

[u/karakanb]

Hi all, this is Burak. I have built an open-source CLI tool that allows replicating data from Postgres CDC changelog into 20+ destinations: https://github.com/bruin-data/ingestr

The overall idea is that:

• You have your prod postgres DB
• You want to replicate them to analytical databases for analytics purposes, e.g. to Snowflake, BigQuery, Databricks, or Redshift
• You have two ways:
  • You can either run a batch load using tools like ingestr, Airbyte, or Fivetran
  • If you cannot run batch workloads for some reason, e.g. due to the latency requirements, or not having proper cursor columns, you need to run CDC replication using tools like Debezium and Kafka

The problem with CDC using those tools is that they require a buy-in into their ecosystem, which is generally quite invasive, such as being able to run Debezium only with Kafka reliably, or having to deal with their Java client libraries if you ever wanted to integrate them elsewhere, tolerate their high resource requirements, etc.

I never liked running them on production. We have been working on ingestr for quite some time already for batch sources, and CDC became an obvious target.

ingestr has quite a few niceties:

• You don't need any extra services or tooling to run it: just put your credentials in the URI, and you are good to go.
• It is a simple and fast Go binary that runs anywhere, even in your GitHub Actions pipeline.
• It supports both batch and streaming modes in the same binary, which allows changing the deployment modes as your requirements grow. Run locally, deploy on Airflow, or put it in an EC2 server in a streaming mode if you want to.

It is open-source, and you can run it anywhere you like.

It supports:

• PostgreSQL CDC
• MySQL CDC
• SQL Server CDC
• SQL Server Change Tracking
• MongoDB CDC

Give it a try and let me know if you have any questions!

[u/AutoModerator]

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Kazcandra]

/how/ does it work? Wal files? Db access? Logical subscriptions?

[u/karakanb → u/Kazcandra]

it connects to postgres over the normal sql and logical replication protocols, using a publication and persistent replication slot to take an initial snapshot and then stream decoded wal changes. it never accesses wal files directly or creates a postgres subscription, it simply acts as the subscriber and writes the changes to the destination itself.

[u/Kazcandra → u/karakanb]

Ah, then it won't work for us anyway. I guess I'll revisit when DDL is captured in logical replication /and/ our fleet supports it lol

[u/karakanb → u/Kazcandra]

ingestr can detect column changes and resync the table already, would that help for your usecase?

[u/Kazcandra → u/karakanb]

No. It's nice that it can handle it, but the main issue with logical replication is stalled consumers causing WAL buildup on the producer eventually leading to disk exhaustion.

CDC continues to evade us, lol :)

[u/Cautious-Meringue554]

more details on the databricks integration? i know that laskebase can connect directly with some sqlalchemy pipelines for example, but wanted to know where can it fit together

[u/karakanb → u/Cautious-Meringue554]

hey, not sure i got the question right but let me try to answer: the databricks integration moves data into or out of unity catalog/delta tables, using a sql warehouse for reads and parquet uploads plus `merge` for writes, rather than acting as a sqlalchemy connection to lakebase. lakebase can still fit in as a postgres-compatible endpoint for regular batch reads and writes, but postgres CDC is not available there today because lakebase does not yet support native postgres logical replication

[u/WorldOfUmbro → u/Cautious-Meringue554]

In the end Lakebase is a (improved) Postgres DB. Besides the functional improvements I think it wins when you want to use your transactional data in your analytical systems.