Proposal: pgoutput CDC for postgres_source, who owns the replication transport?

[mattp5657]

Hey all,

postgres_source does CDC today through Postgres's
test_decoding
plugin: an example decoder, parsed by hand from its plain-text
change format.
This thread proposes adding pgoutput as a permanent second backend
(test_decoding stays, no forced migration). Two things to settle, one big and
one smaller.

Why pgoutput

It's the standard: in core since Postgres 10, no extension, what Debezium and
Materialize settled on. test_decoding's text output is fragile (string-sentinel
parsing, no real type fidelity). The catch: pgoutput needs the streaming
replication protocol (START_REPLICATION/COPY-both, XLogData framing, LSN
feedback), which sqlx doesn't support. That's a new transport layer, not a
decoder swap. (wal2json would sidestep it but is a non-core extension, absent
wherever operators can't install one; Debezium
dropped it in 2.0
for the same reason.)

The main question: where does the replication transport come from?

There is no clean crate to lean on. That same streaming replication protocol
carries an LSN-feedback loop where getting it wrong replays or silently drops
WAL, and nothing in the Rust ecosystem hands us the whole thing as neutral,
publishable infrastructure:

• tokio-postgres can't do it as a dependency. The published crate
  (0.7.18) ships no replication support at all (no replication feature,
  no copy_both_simple, no XLogData), and COPY-both duplex isn't reachable
  from its public API. Using it means forking the driver and maintaining that
  fork, git-only, which trips our publish-chain verification. Every production
  Rust CDC project (Supabase ETL, Mooncake) took this route, and it welds them
  to a fork.
• pgwire-replication is the one actively-maintained, crates.io-published,
  correctly-scoped option, but it's young and single-maintainer, and it
  hand-rolls the whole wire client (SCRAM, TLS, startup, framing), so that
  risk covers the entire stack, not one bounded layer, on a path with
  data-loss stakes.
• Supabase ETL is production-proven but git-only today and a full pipeline
  framework we'd pull in whole just for its replication client. (pg_replicate,
  the original, is dead, folded into ETL;
  Mooncake-Labs/pg_replicate
  is a separate hard fork, no commits since April 2025.)

This is almost certainly why pgoutput has stayed a stub (cdc_pg_replicate,
unimplemented) for so long: doing it right means someone owns a replication
transport, and the real question is whether that's us in-tree or an external
crate we lean on.

That leaves two paths I'd call genuinely viable, and I'd like the thread's
read on which to take.

Option A: own the transport in-tree, the way core/binary_protocol/
already owns Iggy's own wire protocol. Build the pgoutput transport on the
published, correctly-scoped
postgres-protocol crate (the
wire-message primitives from rust-postgres, no replication opinion baked in),
and implement START_REPLICATION + the CopyBoth stream + XLogData/pgoutput
decode + the LSN-feedback loop ourselves. It adds no fork and no git
dependency, keeps the correctness-critical LSN loop where we can test and own
it, and is seeded from stable primitives rather than a from-scratch socket
client. The cost is real: we own and maintain that code, and we'd be
hand-writing the same data-loss-critical LSN loop from scratch, which is
exactly where the risk lives in either option.

Option B: ship on
pgwire-replication. It's a
real, crates.io-published, correctly-scoped crate that already does pgoutput

• TLS + SCRAM, so it's the fastest path to a working backend and keeps us off
  any fork. The tradeoff is whole-stack dependence on a young, single-maintainer
  crate for a data-loss-critical path. Its scope is at least bounded to
  replication rather than a full pipeline framework, so this reads as a normal
  dependency call rather than a dealbreaker.

Either way I'd drop the cdc_pg_replicate placeholder, which pointed at the
Supabase fork route I don't think we should take. Between owning the transport
and building on pgwire-replication I don't have a firm lean, and I'd weight
production experience heavily. Keen to hear from anyone who's run any of these,
especially on the LSN-feedback loop and the real fork-maintenance burden.

The smaller question: dual backend forever?

Keep both user-selectable, or an eventual hard cutover? Both need a REPLICATION
role, so that's not the differentiator. But pgoutput needs a walsender
connection with a replication-type pg_hba.conf entry, while test_decoding
runs over ordinary SQL. So the two may map to two real deployment realities
(streaming allowed vs. SQL-only) rather than legacy vs. future.

Full implementation plan and Postgres recon captures exist, kept out to keep the
decision focused. Happy to share on request.

@mmodzelewski Follow up on #3640