Source Postgres: Clarify the behaviour of CDC replication with two or more destination

[killthekitten]

Tell us about the documentation you'd like us to add or update.

Currently, it’s possible to connect more than one destination to a single CDC source (PostgreSQL), which might lead to an inconsistent replication behavior. There's no explicit documentation of what would happen in case two or more connections would run a replication from the same PostgreSQL replication slot, and the UI encourages reusing the same source multiple times.

My assumption is that both destinations would end up with an incomplete set of changes, and perhaps the replication process itself will fail. From PostgreSQL docs:

A logical replication slot knows nothing about the state of the receiver(s). It's even possible to have multiple different receivers using the same slot at different times; they'll just get the changes following on from when the last receiver stopped consuming them. Only one receiver may consume changes from a slot at any given time.

I’d say the desired behaviour would be for Airbyte to prevent setting up a second connection on a CDC source without removing the first one, but as discussed on Slack, I'd start with making the documentation clearer.

If applicable, add links to the relevant docs that should be updated

• Postgres connector docs

[subodh1810]

@killthekitten your understanding is true. CDC source should not be used with multiple destinations cause connection would end up interfering with each other. Airbyte should prohibit usage of a postgres source in cdc mode with multiple destinations