Skip to main content

Postgres (CDC)

Overview​

Extract's Postgres (CDC) connector captures row-level changes from Postgres logical replication (pgoutput) and streams inserts, updates, deletes, and truncates into Extract.

Use this connector when you need ongoing change capture. If you only need scheduled query-based extraction, use the standard Postgres source connector.

Requirements​

  • PostgreSQL version: PostgreSQL 15+ is required.

    • Read replica mode: If you plan to run in read-replica mode, PostgreSQL 16+ is required (standby logical decoding).

  • Logical replication settings:

    • wal_level must be set to logical.
    • max_replication_slots must be >= 1.
    • max_wal_senders must be >= 1.
    • On AWS RDS, ensure rds.logical_replication is enabled (on / 1) or CDC will not work.

  • Replication slot:

    • The connector uses a logical replication slot with the pgoutput plugin.
    • If you provide an existing slot, it must be created for the same database and use pgoutput.

  • Publication:

    • A PostgreSQL publication is required for the tables you want to replicate.

  • Permissions:

    • The connector user must be able to connect to the database and read the replicated tables.
    • The user must have permissions required for logical replication (for example, to use the replication slot and read changes via the replication protocol).

Setup Guide​

Follow the steps below to set up Postgres CDC in Extract. You’ll configure Postgres for logical replication, create a dedicated user, and then provide the required connection and replication settings to the connector.

Before you begin, confirm:

  • Your database is PostgreSQL 15+.
  • wal_level is set to logical.
  • max_replication_slots >= 1 and max_wal_senders >= 1.
  • If you are using AWS RDS, ensure rds.logical_replication is enabled.
  • If you plan to run in read replica mode, you must be on Postgres 16+ (standby logical decoding requirement).

How It Works​

  1. Extract validates your Postgres CDC settings and server capabilities.
  2. Optional initial snapshot reads current table state.
  3. Extract tails WAL changes from a logical replication slot using pgoutput.
  4. Extract applies table filters and writes records to selected streams.

Compatibility and Requirements​

  • Postgres 15+ (primary/standard CDC).
  • wal_level=logical.
  • max_replication_slots >= 1.
  • max_wal_senders >= 1.
  • A database user with login, table read permissions, and replication capability.
  • Network access from Extract to the database host (directly or via SSH tunnel).

You can verify the required server settings with:

SHOW server_version_num;
SHOW wal_level;
SHOW max_replication_slots;
SHOW max_wal_senders;

Step 1 - Create a Dedicated CDC User​

CREATE USER extract_cdc WITH PASSWORD 'replace-with-strong-password';
GRANT CONNECT ON DATABASE <database> TO extract_cdc;
GRANT USAGE ON SCHEMA <schema> TO extract_cdc;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO extract_cdc;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <schema> TO extract_cdc;
ALTER ROLE extract_cdc WITH REPLICATION;

If you use auto-managed publications, ensure this user can create/alter publications.

Step 2 - Decide Publication and Slot Ownership​

Use defaults:

  • create_publication=true
  • create_replication_slot=true
  • publication_name=extract_cdc_pub
  • replication_slot_name=extract_cdc_slot

Extract will create missing objects and keep publication tables aligned with selected streams.

Option B: Manage them manually​

Set both creation flags to false and create objects yourself.

Example:

CREATE PUBLICATION extract_cdc_pub FOR TABLE public.orders, public.customers;
SELECT * FROM pg_create_logical_replication_slot('extract_cdc_slot', 'pgoutput', false);

If manual mode is enabled and objects are missing, the connector fails fast at runtime.

Step 3 - Configure Replica Identity​

For safe update/delete CDC behavior, tracked tables should use REPLICA IDENTITY FULL.

Manual approach:

ALTER TABLE public.orders REPLICA IDENTITY FULL;

Or set connector parameter replica_identity_override=FULL to apply it automatically on selected tables.

Notes:

  • Only FULL is supported.
  • replica_identity_override cannot be applied when read_replica=true.

Step 4 - Configure Connector Parameters in Extract​

Account Connector Parameters​

  • host
  • port (default 5432)
  • username
  • password
  • database
  • tls (default false)
  • use_ssh_tunnel and SSH fields (ssh_host, ssh_port, ssh_user, ssh_key)

Connection Parameters​

  • replication_slot_name
  • publication_name
  • create_publication
  • create_replication_slot
  • slot_temporary
  • read_replica
  • initial_snapshot
  • resnapshot
  • lag_warning_consecutive_runs (default 3)
  • include_schemas, exclude_schemas
  • include_tables, exclude_tables
  • replica_identity_override

Read Replica Mode​

Set read_replica=true only when you are intentionally decoding from a replica.

Requirements and constraints:

  • Replica host must support standby logical decoding (Postgres 16+).
  • create_publication=false and create_replication_slot=false are required.
  • Publication/slot must already exist and be valid for the target database.
  • replica_identity_override is not applied in read-replica mode.

Snapshot and Resnapshot Behavior​

  • initial_snapshot=true: performs a baseline snapshot before WAL streaming.
  • resnapshot=true: clears CDC state and forces a new snapshot on the next run.
  • If a slot becomes invalidated/lost, Extract forces a resnapshot for recovery.

Table Selection Rules​

Table filters are applied in this order:

  1. include_schemas (if provided)
  2. exclude_schemas
  3. include_tables (if provided, supports schema.table or bare table)
  4. exclude_tables (supports schema.table or bare table)

Use fully qualified names (schema.table) when possible to avoid ambiguity.

Records and CDC Metadata​

For change events, the connector emits table records and includes CDC metadata fields:

  • _extract_commit_timestamp: commit timestamp for the change (when available from the replication stream)
  • _extract_cdc_lsn: WAL LSN associated with the change
  • _extract_deleted: true for delete events, otherwise false/unset

Truncate operations are also captured. Depending on your destination and modeling, truncates may be represented as control events rather than row-level records.

Validation and Monitoring Queries​

Confirm publication members​

SELECT schemaname, tablename
FROM pg_publication_tables
WHERE pubname = 'extract_cdc_pub'
ORDER BY schemaname, tablename;

Check slot health and WAL retention​

SELECT
  slot_name,
  active,
  restart_lsn,
  confirmed_flush_lsn,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
WHERE slot_name = 'extract_cdc_slot';

Troubleshooting​

  • Postgres CDC requires Postgres 15+ (server_version_num=...)

    • Upgrade the source database to Postgres 15 or later.

  • wal_level must be 'logical' for CDC

    • Set wal_level=logical and restart Postgres.

  • max_replication_slots must be >= 1 for CDC

    • Increase max_replication_slots to at least 1 and restart Postgres.

  • max_wal_senders must be >= 1 for CDC

    • Increase max_wal_senders to at least 1 and restart Postgres.

  • rds.logical_replication is disabled; CDC will not work on RDS.

    • If you’re using Amazon RDS for PostgreSQL, enable logical replication (parameter rds.logical_replication=1) and reboot the instance if required.

  • Replication slot '<slot>' does not exist and create_replication_slot=false

    • Create the slot manually or enable slot creation.

  • Replication slot '<slot>' uses plugin '<plugin>', expected 'pgoutput'

    • Recreate the slot using the pgoutput plugin (the connector requires pgoutput).

  • Publication '<pub>' does not exist and create_publication=false

    • Create the publication manually or enable publication creation.

  • read_replica=true requires create_publication=false and create_replication_slot=false

    • Disable both creation flags in read-replica mode.

  • read_replica requires Postgres 16+ for standby logical decoding

    • Use a primary host for CDC, or upgrade the replica to Postgres 16+ with standby logical decoding support.

  • CDC requires REPLICA IDENTITY FULL for update safety

    • Set replica_identity_override=FULL or apply ALTER TABLE ... REPLICA IDENTITY FULL manually.

Best Practices​

  • Use a dedicated replication slot per Extract connection.
  • Keep runs frequent to prevent excessive WAL retention.
  • Prefer explicit table lists for production CDC.
  • Avoid temporary slots (slot_temporary=true) unless you explicitly want ephemeral state.