Postgres CDC

[cgardens]

Tell us about the problem you're trying to solve

• CDC is one of the most sought after features from our users.

Describe the solution you’d like

• Define what features are included in CDC
• Spec out how we will implement it in Airbyte
• POC to see if debezium is a good choice (issue)
• Consensus on CDC's interaction with the AB protocol (issue)
• Implement debezium jdbc source
• Handle state management (issue)
• Handle logic on when to close debezium (issue)

Spec

Related

• Support WAL replication #957

[sherifnada]

TIL Debezium supports embedded CDC. I don't know if this is a good solution, but it's relevant since we brought up Debezium last week.

Relevant section from the README:

Applications that don't need or want this level of fault tolerance, performance, scalability, and reliability can instead use Debezium's embedded connector engine to run a connector directly within the application space. They still want the same data change events, but prefer to have the connectors send them directly to the application rather than persist them inside Kafka.

[cgardens]

wal2json is the library that I spent some time looking at a little while ago. I found it, because it is what the the Singer tap-postgres uses. The thing to note here is that it only handles writing postgres WALs to a json object formatted specifically for wal2json. It is not obviously extensible to other dbs. It may still be useful in just extract the WAL from a postgres db (even if we have to do further transformation).

I spent some time trying to set this up on 2 local databases but got hung up and never got back to finishing it. The key here is that it's non-trivial to set up because you have to mess with your db settings. I think this is likely going to be true no matter what tool we use.

[michel-tricot]

That's really cool to get an MVP up and running!

[michel-tricot]

Yep regarding the setup complexity. CDC requires the logs to be created which isn't a default

[ChristopheDuong]

You can take a look at this too: https://www.stitchdata.com/docs/integrations/databases/postgresql/v1#install-wal2json-plugin

[sherifnada]

Separately, some questions I have about CDC:

1. Is it going to be only within DBs (e.g: PG to PG) or is there a world where it makes sense to have it work across DBs as well? We don't need to implement anything more than single-DB CDC out the gate but it may affect our approach if the latter idea is desirable.
2. How will it impact the protocol? An assumption we had previously is that you can hook up any source to any destination. With CDC this assumption changes.

It may make sense to address this in a quick RFC

[cgardens]

proposal on how to handle CDC metadata. Ultimately we are not going in this direction, but documenting it here for posterity.

Based on a conversation with michel, jared, sherif, and me, we agreed that for the first iteration that the CDC metadata fields will go in the data section of the airbyte record. We will document the common naming conventions used for thos metadata fields.

Reasoning:

1. more clearly a 2-way door, and less additional upfront work.
2. we can handle marking a column as a deletion field as a new feature in the future in the same way we do for cursor field and primary key.
3. keeps the destination CDC-unaware
4. ultimately it is the prerogative of sources to add "metadata" in their output already. ultimately cdc deleted, etc follow under this jurisdiction.
5. any more invasive changes to the protocol to support this opens lots of cans of worms that we have do not have the data to make well informed choices on. since there are alternatives it is better to punt on making those decisions.

[cgardens]

updated the description with progress from last week and with new to dos for this week.

[jrhizor]

Remaining:

• configuring table includes
• updating catalog for metadata fields
• allowing replication modes based on primary key existence
• set up replication slot as a oneOf field
• handle termination
• make sure full refresh edge cases are handled
• make sure WAL expiration edge cases are handled

[jrhizor]

@cgardens should we rename this to postgres / mark it as a duplicate with #957 ? Then create separate tickets for other CDC sources?

[cgardens]

Yeah

…

On Sun, Apr 11, 2021, 10:21 PM Jared Rhizor ***@***.***> wrote: @cgardens <https://github.com/cgardens> should we rename this to postgres / mark it as a duplicate with #957 <#957> ? Then create separate tickets for other CDC sources? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#2156 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACFLY335I6KEIOKK7EHUQ3TTIJ7MFANCNFSM4YBNXNLA> .