Postgre SQL CDC v2

Captures committed PostgreSQL row changes as durable batches for downstream processing. Use explicit acknowledgement after successful destination writes to support restart and replay without silently losing unprocessed changes.

Revision History

0.0.0.15 - Adds PostgreSQL logical replication capture, explicit batch acknowledgement and status monitoring.

Connection

Uses the same PostgreSql Connection profile as Postgre SQL.

Setup Notes

1. Use PostgreSQL 14 or later on a writable primary with wal_level=logical and sufficient max_replication_slots and max_wal_senders.
2. Configure a login with replication permission, database access, catalog visibility and suitable TLS settings.
3. Have the database administrator create a publication and a persistent pgoutput slot dedicated to this capture. The Node does not create or replace them automatically.
4. Ensure UPDATE/DELETE tables have a primary key or explicit replica identity index. Use replica identity FULL when full before-images are required; destination keys still come from the catalog key.
5. Use V2 executing Runtimes and service hosts that support CDC. An older Runtime fails before contacting PostgreSQL.
6. Choose a stable CaptureId and retain it across Workflow publications. Source identity, database, slot and publication are bound to that identity within the Site and environment.

Methods

Listen

Starts capture and delivers one batch object at a time. Validation and replication startup occur while the invocation is awaited.

Acknowledge

Marks a batch delivered after every required destination write has succeeded. Never place this on a failure path.

Get Status

Reports capture, delivery and source progress.

Usage Notes

• Process Batch.Events, validate destination results, and only then call Acknowledge with the current batch identifiers and receipt. Use a For Each only when processing the Events array; Batch itself is an object.
• If a destination reports unsuccessful rows without throwing, check those rows before acknowledgement. Advancing the listener without acknowledging fails and leaves the batch pending for replay.
• Capture continues while the Workflow processes its current batch. Transactions are durably stored before replication feedback is sent. Destination writes and acknowledgement are separate operations, so crashes can cause duplicate delivery. Use stable event IDs and replay-safe destination writes.
• Each event contains ContractVersion, SourceType, SourceId, EventId, Database, Schema, Table, Operation, KeyBefore, KeyAfter, Before, After, BeforeImageKind, TransactionId, EventOrdinal, CommitTimestamp and Position. Position contains CommitLsn and EndLsn. Rolled-back transactions are excluded.
• Column entries carry State, Value and PostgreSQL TypeId. State is Value, Null or Unchanged. Values use PostgreSQL text representations; convert them deliberately to destination types. Preserve the destination value for an Unchanged TOAST column instead of writing NULL.
• BeforeImageKind distinguishes available/full images, key-only old tuples and unavailable before-images. Primary-key changes carry both old and new keys.
• Monitor retained WAL and source disk space even while the listener is stopped. Automatic reconnect does not preserve a slot across primary failover by itself; database failover must preserve the slot and required WAL.
• Get Status includes last feedback and source-confirmed positions. A successful feedback send does not prove the source has persisted slot progress. Check SourceConfirmedPosition and SourceMetricsAt; provider metrics refresh approximately every 15 seconds while connected.
• Batch-size preferences do not split transactions. A transaction exceeding 10000 events or 7 MiB before enrichment stops capture without advancing source feedback. Durable transaction storage also has an 8 MiB limit. Backlog limits apply backpressure rather than discarding pending events.

Examples

Prepare a publication and slot

Have the database administrator adapt these statements to the intended database and tables:

CREATE PUBLICATION flowgear_publication FOR TABLE public.orders;
SELECT pg_create_logical_replication_slot('flowgear_slot', 'pgoutput');

Configure Listen with those exact names. Apply each delivered batch to the destination, confirm success, then acknowledge using the current Batch values.

Known Issues

• Historical snapshots, DDL replication, automatic schema evolution, exactly-once delivery and TRUNCATE are not supported. TRUNCATE fails capture before its transaction is acknowledged.
• Two-phase decoding slots are unsupported. Schema drift across restart is rejected; publication additions require restart and validation.
• A slot advanced beyond the durable checkpoint, lost WAL or a changed physical source requires coordinated recovery. A new capture identity or slot does not fill historical gaps; arrange a consistent external baseline where needed.

See also

• Postgre SQL for queries, stored procedures and table upserts.