Articles in this section

FAQs – PostgreSQL listener for change data capture (CDC)

Saikumar Rachamalla
Updated

These FAQs relate to the PostgreSQL listener for change data capture (CDC), also referred to as the PostgreSQL CDC listener.

Prerequisites

Before you begin:

  • PostgreSQL CDC listener requires a Professional or Enterprise edition. See Celigo platform editions.
  • You need an existing PostgreSQL connection, or you can create one during setup. See Set up a connection to PostgreSQL.
  • Your PostgreSQL server must have wal_level set to logical. See How do I verify my PostgreSQL server is configured for CDC? below.

How does each PostgreSQL CDC export relate to the WAL and replication slots?

Every PostgreSQL export configured as a CDC listener creates its own Debezium consumer, and Debezium uses a dedicated replication slot on your PostgreSQL server to track its position in the write-ahead log (WAL).

  • One replication slot per export. Each CDC export creates and manages its own slot. Multiple exports mean multiple slots, each consuming WAL resources independently.
  • WAL retention. PostgreSQL retains WAL segments until all active replication slots have consumed them. If an export falls behind or is paused, WAL can accumulate on disk and cause storage issues.
  • Scale consideration. A single export can listen to all tables, but if you have high change volume across many tables, it may fall behind. Consider splitting high-volume tables across separate exports.

Tip: Best practice for scale: Create multiple exports, each scoped to a subset of tables using table.include.list in Additional properties; for example, one export for orders and another for products.

Note: Do not leave a CDC export paused for extended periods. An idle replication slot continues to hold WAL segments, which can exhaust disk space on the PostgreSQL server. If you no longer need an export, delete it so Celigo can drop the slot.

What snapshot.mode options do I see versus what can I actually use?

For snapshot.mode, the drop-down list shows only:

  • no_data
  • when_needed

In Additional properties, advanced users can manually type any Debezium snapshot.mode value supported by the Debezium PostgreSQL connector, such as initial, always, exported, or others.

That is:

  • The drop-down list provides safe, common choices.
  • The text field gives you full Debezium power, if you know what you're doing.

What's the basic difference between when_needed and no_data?

The basic difference in these snapshot.mode values in Additional properties:

  • no_data: Does not read existing data. Only captures new changes from the WAL from the moment the listener starts and its replication slot is created.
  • when_needed: On first run (no stored offset yet): performs an initial snapshot of the matching tables, then streams new changes from that LSN position. On later runs (offset already exists): no new snapshot; it resumes streaming from the stored offset.

How should I load historical data the first time for a table?

Recommended: set snapshot.mode=when_needed in Additional properties on a new export for that table.

  1. Create a new listener/export for the table(s) you're onboarding.
  2. Set snapshot.mode=when_needed in Additional properties.
  3. Let it run once to load the existing rows and establish the LSN offset.
  4. Keep it running to continue as a PostgreSQL CDC listener going forward.

Note: Use no_data only if you never want this export to snapshot existing rows; for example, you already bulk-loaded historical data through a separate process.

I started with when_needed for the orders table, then later added products to the same export. Why didn't I get historical data for products?

Because by the time you added products, the export already had a stored LSN offset.

  • when_needed only snapshots when there is no stored offset yet.
  • Once an offset exists, adding new tables to the same export does not trigger another snapshot.
  • Result: you get only new product changes going forward, no historical rows.

This is the key "gotcha" to understand.

How can I get historical data for a new table if my export already has a stored offset?

When an export already has a stored cursor, adding a new table does not automatically trigger a new snapshot. The export typically continues from its existing cursor and captures only new changes.

To load historical data for a newly added table, choose one of the following options.

Option A – Reset the cursor (recommended)

Use Cursor management to reset the export's stored cursor.

Important: The Cursor management section is displayed only when the export has a stored CDC cursor (offset). Celigo typically stores the offset after approximately five minutes of active CDC streaming. If no cursor exists, the Cursor management section is hidden.

Once the section becomes available:

  1. Expand Cursor management.
  2. Click Reset to stage a cursor reset.
  3. Save the export to apply the reset.

When the export is saved, Celigo clears the stored cursor and restarts CDC processing. The export typically resumes processing within approximately one minute.

After restart, behavior depends on the configured snapshot.mode value. Depending on the selected mode, the connector may perform a snapshot before resuming CDC processing.

Pros

  • Product-supported approach.
  • No need to create additional exports.
  • No need to manually manage cursor state.

Note: After a cursor reset is saved, the CDC service restarts. The export typically resumes processing within approximately one minute.

Option B – Temporarily change snapshot.mode on the existing export

  1. Add the new table (for example, public.products) to Tables.
  2. In Additional properties, change snapshot.mode to initial (or another full-snapshot mode).
  3. Restart the export so it snapshots everything that matches, including the new table.
  4. After the snapshot completes, switch snapshot.mode back to when_needed or no_data.
  • Pros: Single export handles everything.
  • Cons: Resnapshots all tables in the export, not just the new one. This can be slow and resource-intensive on large databases.

Option C – Use a separate export for historical loads (recommended)

  1. Create a new export only for historical data, scoping Tables to just the new table(s) you want history for.
  2. In Additional properties, set snapshot.mode=when_needed.
  3. Run it once to load historical rows.
  4. Disable or delete that export when the historical load is complete.
  5. Add the new table to your main CDC export; it will capture only new changes going forward.
  • Pros: Keeps historical loading isolated from your main, stable listener. No disruption to existing offsets.

Why don't I see the before field in my listener data?

In PostgreSQL, the before field in a CDC event is controlled at the database level, not by a Debezium connector setting. There are two layers:

Layer 1 – PostgreSQL REPLICA IDENTITY must be set on the source table

By default, PostgreSQL only includes the primary key columns in the before image of UPDATE and DELETE events. To get the full before row, a database administrator must run:

ALTER TABLE schema.table_name REPLICA IDENTITY FULL;

Without REPLICA IDENTITY FULL, the before field will be null or contain only the primary key, even if you select before in Fields to include.

Note: This is a PostgreSQL server-level change that requires DBA access. It must be applied per table. Confirm with your database administrator before enabling it, as it increases WAL volume.

Layer 2 – Fields to include decides whether Celigo keeps it

Fields to include is a filter on top of what Debezium provides. Even if PostgreSQL is correctly configured to emit before, Celigo will drop it if you haven't selected it in the Fields to include.

  • If you select before, but REPLICA IDENTITY FULL is not set on the table, the field will be null or missing; Debezium received nothing to forward.
  • If REPLICA IDENTITY FULL is set but you don't select it before in Fields to include: Celigo drops it, and it won't appear in your flow payload.

Tip: Both conditions must be true to see before: (1) REPLICA IDENTITY FULL is set on the PostgreSQL table, and (2) before is selected in Fields to include.

Why does my PostgreSQL CDC listener break when I change the table list or scope?

This usually happens because the stored LSN offset (replication slot position) was created under a different scope, and the change can cause Debezium to encounter events it can no longer process correctly.

  • Debezium stores progress as an LSN position in the replication slot so it can resume on restart. See Debezium PostgreSQL connector docs.
  • The replication slot's publication on PostgreSQL is configured to capture specific tables. If you change which tables are included, the publication and slot behavior may become inconsistent with the stored offset.

Shrinking scope (more restrictive, higher risk)

Removing tables from Tables or table.include.list after a slot is established means the stored offset may reference events that no longer fall within the new scope. This can cause Debezium to stall or error on restart.

Recommended options:

  • Option A – Start fresh (cleanest). Create a new export with the updated table scope. Let it establish a new replication slot and offset from scratch.
  • Option B – Filter downstream. Keep the table scope in the export stable. Apply row-level filtering later in your flow using output filters or transformations, so the slot and offset are not affected.
  • Option C – Reset the slot. Delete and recreate the export (which drops and recreates the replication slot). This loses your offset position; the next run will start fresh per the configured snapshot.mode.

Expanding scope (less restrictive, lower risk)

Adding new tables is generally safer. The existing LSN offset remains valid, and Debezium can resume from the same position for existing tables while beginning to capture the new tables going forward.

Tip: Simple mental model: the replication slot is like a bookmark in the WAL. Removing tables is like tearing out pages; the bookmark referenced may no longer point to a valid position. Adding tables is like adding new chapters; the bookmark still works.

What is a replication slot and what happens if I don't manage it?

A replication slot is a PostgreSQL server-side object that tracks how far a consumer (in this case, Celigo's Debezium integration) has read the WAL. Celigo automatically creates a slot when you save a CDC export and drops it when you delete the export (for service-managed slots).

What can go wrong if a slot is not managed:

  • WAL accumulation. PostgreSQL will not remove WAL segments that an active slot hasn't consumed yet. If your CDC export is paused, stopped, or falls far behind, WAL segments pile up on disk. This can fill up storage and, in extreme cases, crash the PostgreSQL server.
  • Orphaned slots. If a Celigo export is deleted without cleanly dropping the slot (for example, the server was unreachable at deletion time), the slot may remain on the server as an orphan. Monitor your slots with SELECT * FROM pg_replication_slots; and drop any orphaned ones manually.

Important: If you stop using a PostgreSQL CDC export, delete it in Celigo rather than simply disabling it. Deletion triggers slot cleanup. A disabled export with an active slot will continue to hold WAL segments.

What database permissions does my PostgreSQL connection user need?

The PostgreSQL user configured in the Celigo connection must have the following privileges for CDC to work:

  • REPLICATION role attribute is required to create and use replication slots. Grant with: ALTER ROLE your_user REPLICATION;
  • LOGIN - standard login privilege.
  • SELECT on the tables you want to capture is required for snapshot reads.
  • CREATE on the database is required if Celigo needs to create a publication automatically (publication.autocreate.mode=filtered).

Tip: If your organization's security policy does not allow granting REPLICATION to an application user, you can pre-create the replication slot and publication manually, then provide the slot name using the cdc.slotName field in Advanced properties (if available). Confirm with your engineering team whether this option is supported in your version.

How do I verify my PostgreSQL server is configured for CDC?

PostgreSQL CDC requires wal_level=logical on the server. This is a server-level setting; it cannot be changed per database or per session.

To check the current setting, run the following query as a superuser or a user with SELECT on pg_settings: SHOW wal_level;

If the result is not logical, ask your database administrator to update postgresql.conf: wal_level = logical

A PostgreSQL server restart is required after changing wal_level. This is a breaking change for the server; coordinate with your DBA before making this change in production.

Note: Changing wal_level requires a full PostgreSQL server restart. It cannot be changed online. Plan for a maintenance window if modifying a production server.

After setting up your listener, connect it to an import step in the flow builder to begin processing change events from PostgreSQL.