CDC Sink for PostgreSQL: Manual REPLICA IDENTITY Setup

• If the PostgreSQL user configured in the CDC Sink connection string cannot alter an affected table,
  a database administrator must configure REPLICA IDENTITY manually before the task starts.

• This article shows the SQL commands for manual setup. For background on what REPLICA IDENTITY is,
  when it is required, and when CDC Sink can configure it automatically, see REPLICA IDENTITY.

• In this article:

  • When manual setup is required
  • Setting REPLICA IDENTITY FULL
  • Using an index instead of FULL
  • Verifying the configuration

When manual setup is required

If the PostgreSQL user configured in the CDC Sink connection string owns theaffected embedded source table, or has SUPERUSER, CDC Sink can automatically set REPLICA IDENTITY FULL when that table's current setting is insufficient.

If that PostgreSQL user cannot alter the table, the task fails to start with a permissions error.
A database administrator must configure REPLICA IDENTITY manually before the task can run.

See Automatic vs manual configuration for details on when this applies.

Setting REPLICA IDENTITY FULL

The simplest approach is to set REPLICA IDENTITY FULL on each affected embedded source table:

• SQL

ALTER TABLE order_lines REPLICA IDENTITY FULL;
ALTER TABLE line_attributes REPLICA IDENTITY FULL;

With REPLICA IDENTITY FULL, PostgreSQL can include all old column values in DELETE and UPDATE events.
This is the most compatible option, but it increases WAL volume for tables with many or large columns.

Using an index instead of FULL

If WAL size is a concern, you can use a specific unique index instead of FULL.

The designated replica identity index must cover both the join columns and PrimaryKeyColumns of the embedded table, because CDC Sink needs those old values to find the parent document and the matching embedded item.

---

Step 1: Create a unique index covering the required columns

• SQL

-- For order_lines: join column is order_id, PK is line_id
CREATE UNIQUE INDEX order_lines_replica_idx
    ON order_lines (order_id, line_id);

Step 2: Set REPLICA IDENTITY to use this index

• SQL

ALTER TABLE order_lines
    REPLICA IDENTITY USING INDEX order_lines_replica_idx;

This instructs PostgreSQL to include old row values for the indexed columns in DELETE and UPDATE events,
rather than all columns.

• The index must be UNIQUE and NOT DEFERRABLE.
  It cannot use expressions or partial predicates.

• Every indexed column must be marked NOT NULL;
  if a required join column is nullable, make it NOT NULL first or use FULL.

Verifying the configuration

• After applying the changes, confirm that each affected table shows the expected REPLICA IDENTITY mode using the query in Checking current setting.

• A table configured with FULL is ready for CDC Sink embedded-table delete processing.
  A table configured with INDEX is ready when the designated replica identity index covers the required join columns and PrimaryKeyColumns.