# Postgres CDC for Marketing Data: 7 Checks Before You Trust the Pipeline

Postgres change data capture can keep customer, subscription and order data current without repeatedly scanning every source row. Reliability depends on the full chain: a consistent initial copy, usable row identity, retained log history, repeat-safe delivery and correct downstream handling of updates and deletes. Monitor source retention and the business output separately, then rehearse interruption and recovery before activating the data.

Canonical URL: https://www.martechdevs.com/blog/postgres-cdc-marketing-data

By [Jafrin Akter Alo](https://www.martechdevs.com/authors/jafrin-akter-alo)

Data Engineer and Co-founder at [Martechdevs](https://www.martechdevs.com) and [Growth and Analytics Partners](https://www.growthandanalytics.com)

[LinkedIn](https://linkedin.com/in/jafrinalo)

Last updated: 2026-10-25

Next step: [HubSpot Google Sheets Integration: Refresh Data Without Duplicate Rows](https://www.martechdevs.com/blog/hubspot-google-sheets-integration)

## When is Postgres CDC worth using for marketing data?

Use change data capture when the business needs source changes that a scheduled timestamp query cannot reliably or economically supply. The benefit is strongest when important updates or deletes must reach reporting or customer activation within a defined deadline.

PostgreSQL logical decoding translates its write-ahead log, or WAL, into a change stream. A logical replication slot tracks a consumer's position for one database. This is a different mechanism from selecting rows whose application `updated_at` value changed. [PostgreSQL logical decoding](https://www.postgresql.org/docs/18/logicaldecoding-explanation.html).

| Consumer | Required source change | Acceptance question |
| --- | --- | --- |
| Subscription dashboard | Upgrade, downgrade, cancellation or correction | Does the current account state reconcile after each operation? |
| Renewal audience | Eligibility and suppression changes | Does the destination stop using an ineligible record in time? |
| Order reporting | Refund, amended amount or deleted test order | Is the report's chosen financial treatment preserved? |
| Product-led sales feed | Account or usage-state changes | Does the correct CRM account receive the approved update? |

CDC still needs transformations, ownership and consumer checks. A database change is not automatically a meaningful customer event: an internal maintenance update can produce a change record without representing a new purchase or lifecycle milestone.

If a daily complete extract already meets the requirement and remains affordable, CDC may introduce unnecessary operating work. Where it is justified, our [warehouse and activation implementation](https://www.martechdevs.com/data-warehouse-cdp) connects source capture to the report or customer action that must work. For connector-specific sync modes, use the [Airbyte implementation checks](https://www.martechdevs.com/blog/airbyte-implementation-mistakes).

## What must be configured before the first snapshot?

Confirm the PostgreSQL version, connector support, replication configuration, source scope and access before starting capture. The SQL and catalog fields below target PostgreSQL 18; managed providers can impose additional setup requirements.

Logical replication requires `wal_level=logical`, enough replication slots and enough WAL senders for the intended connections. PostgreSQL's configuration guidance also reserves capacity for synchronization and physical replicas. Set those values from the actual deployment rather than copying a large number from a tutorial. [Logical replication configuration](https://www.postgresql.org/docs/18/logical-replication-config.html).

This read-only inspection returns configured values and their units. Run it using an authorized database identity; a missing or restricted setting needs DBA review.

```sql
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
    'wal_level',
    'max_replication_slots',
    'max_wal_senders',
    'max_slot_wal_keep_size',
    'idle_replication_slot_timeout'
)
ORDER BY name;
```

Record the publication, source tables, output plugin, slot name and connector state location. A publication identifies the table changes offered to subscribers; PostgreSQL permits selecting operation types such as insert, update, delete and truncate. Inspect the actual selection so a pipeline intended to capture deletes is not configured for inserts alone. [PostgreSQL publications](https://www.postgresql.org/docs/18/logical-replication-publication.html).

For native replication, PostgreSQL requires a replication-capable login and access rules; initial table copying also requires `SELECT` on the published tables. Publication creation and table membership require their own privileges. Have the DBA grant the required scope and review the provider's supported identity setup. [Logical replication security](https://www.postgresql.org/docs/18/logical-replication-security.html).

Keep production mutations such as slot creation, publication changes and replica-identity changes in the deployment plan. They affect source behavior and deserve a controlled rollout with an owner and a recovery path.

## How do you connect the initial copy to ongoing changes?

Use the connector's supported snapshot-to-stream handoff so records changed during the initial copy are not lost or applied in the wrong order. An arbitrary table export followed later by streaming leaves an unproven gap between the two.

Debezium's PostgreSQL connector supports an initial snapshot followed by streaming from its recorded log position. Its snapshot modes change that behavior, so inspect the configured mode and saved offsets before interpreting a restart. [Debezium snapshot behavior](https://debezium.io/documentation/reference/stable/connectors/postgresql.html).

Test the handoff with a controlled record that changes during the snapshot. Confirm the final destination state and the source position from which streaming continues. Also test an interrupted snapshot: the recovered run must establish complete coverage, not merely resume with a small set of recent changes.

Do not describe the initial copy as complete historical activity. It can establish the current rows at the snapshot boundary. Earlier states already overwritten or removed from the source require separate retained history. Decide whether the marketing output needs current state, a record of changes, or both.

Gate downstream activation until the initial population and subsequent catch-up meet the agreed contract. A half-loaded subscription table must not cause the CRM to mark every absent account as cancelled. The [data incident runbook](https://www.martechdevs.com/blog/data-incident-runbook) provides the consumer protection and restoration record for this handoff.

## Why do updates and deletes need a valid row identity?

Updates and deletes need an identity that lets the receiver locate the intended business record. For published PostgreSQL tables, the default replica identity uses the primary key when one exists; `FULL` uses the whole row and should be treated as a fallback with performance implications. [PostgreSQL replica identity](https://www.postgresql.org/docs/18/logical-replication-publication.html).

Check the key at the real source grain. An order-line table needs a line identity, and a multi-tenant table may need tenant plus record ID. The destination key must preserve that scope. A source update to account A must never overwrite an unrelated record carrying the same local ID in account B.

Define the downstream meaning of each operation before testing it:

| Source operation | Current-state output | Historical or activation question |
| --- | --- | --- |
| Insert | One eligible business record | Is this a new entity or a replayed delivery? |
| Update | Replace the relevant current values under the ordering rule | Which previous values must remain available? |
| Delete | Apply the agreed removal or deletion marker | Must reports retain history while audiences exclude the entity? |
| Key change | Retire the old identity and establish the new one as supported | Can an old-key record remain incorrectly active? |
| Truncate | Treat as a distinct high-impact table operation | Should consumers pause pending a verified reload? |

A technical delete is not a universal instruction to erase financial history. Equally, retaining an audit row does not mean that row should remain eligible for a customer campaign. Keep history retention and operational eligibility as separate rules.

PostgreSQL's native logical replication does not replicate schema DDL or sequence state automatically. Schema changes therefore need coordination; copying row values into a table does not prove that every schema or operational dependency followed them. [Logical replication restrictions](https://www.postgresql.org/docs/18/logical-replication-restrictions.html).

Test wide text and JSON fields as well as small scalar columns. Debezium documents that unchanged TOAST values can be absent from an update message under the default identity and represented by an unavailable-value placeholder. That placeholder is not a source null; the destination must not erase a known value merely because the update omitted it. [Debezium TOAST handling](https://debezium.io/documentation/reference/stable/connectors/postgresql.html).

## How should the destination handle repeated or delayed changes?

Make replay leave the accepted business state unchanged, and retain enough ordering information to prevent an older change from replacing a newer value. Source identity, event identity and delivery identity serve different purposes.

PostgreSQL documents that a crash can cause a logical slot to resend recent changes because its persisted position may return to an earlier log sequence number, or LSN. Consumers must tolerate that repetition. [Logical decoding replay behavior](https://www.postgresql.org/docs/18/logicaldecoding-explanation.html).

Use the connector's documented event and transaction metadata. An LSN should not be assumed to identify one unique business event across every delivery format. A transaction can affect several rows, and a downstream system can transform one source change into several messages.

For a hypothetical subscription, the accepted sequence is version 1 at USD 100, version 2 at USD 120, a duplicate delivery of version 2, then a deletion at version 3. Replaying version 2 after the deletion must not resurrect the subscription. The current eligible count should remain zero; the retained history should still explain the prior states.

Test same-position conflicts explicitly. Two different values claiming the same ordering position need an exception or a documented resolution rule, not an arbitrary last-arriving winner. Avoid using destination arrival time as source business ordering when queues or retries can reorder delivery.

Verify transaction boundaries for related records. An order header and its lines may need a consistent consumer view even when the transport emits separate records. A batch being technically valid does not establish that a financial or audience model is ready to publish. Keep [data quality controls](https://www.martechdevs.com/blog/marketing-data-quality) at the business output.

## What should you monitor on the replication slot?

Monitor slot validity, progress and retention headroom alongside source disk capacity. A slot being active tells you that it is being streamed; it does not prove that the warehouse or customer destination has applied every change.

This read-only PostgreSQL 18 query runs on the primary for the connected database. Its byte distance is a log-position measurement, not the exact disk space attributable to that slot.

```sql
WITH position_now AS (
    SELECT pg_current_wal_lsn() AS current_lsn
)
SELECT
    s.slot_name,
    s.active,
    s.wal_status,
    s.invalidation_reason,
    s.restart_lsn,
    s.confirmed_flush_lsn,
    pg_wal_lsn_diff(p.current_lsn, s.restart_lsn) AS restart_distance_bytes,
    s.safe_wal_size
FROM pg_replication_slots AS s
CROSS JOIN position_now AS p
WHERE s.slot_type = 'logical'
  AND s.database = current_database()
ORDER BY restart_distance_bytes DESC NULLS LAST;
```

`restart_lsn` identifies the oldest WAL the slot may still need. `confirmed_flush_lsn` is the position through which the consumer has confirmed receipt. `safe_wal_size` estimates remaining WAL bytes before the slot risks becoming lost under a finite retention limit. A null value can mean unlimited configured retention or a lost slot, so interpret it with status and configuration. [Slot catalog definitions](https://www.postgresql.org/docs/18/view-pg-replication-slots.html).

`pg_wal_lsn_diff` returns the byte difference between two log positions. The current-WAL functions used here are read-only and cannot be substituted blindly on a standby. [PostgreSQL WAL functions](https://www.postgresql.org/docs/18/functions-admin.html).

Track these boundaries separately:

| Boundary | Evidence | A failure can mean |
| --- | --- | --- |
| Source retention | Slot status, restart position, available disk | Source history or source availability is at risk |
| Connector progress | Read position, durable checkpoint and error state | Capture or transport has stalled |
| Destination application | Applied position, exceptions and current-state checks | Changes arrived but are not usable yet |
| Consumer readiness | Report cutoff or audience acceptance | The final business output remains stale |

Do not add every slot's restart distance and call the result disk usage. Slots can retain overlapping WAL segments. Monitor actual source storage separately, and investigate long-running transactions when the oldest required position stays behind despite other progress.

## How much interruption can your CDC pipeline survive?

The recoverable interruption depends on retained WAL, slot validity, source storage, connector state and catch-up capacity. It is not simply a fixed number of days promised by enabling CDC.

PostgreSQL's `max_slot_wal_keep_size=-1` permits unlimited slot WAL retention by default, which can consume source storage. A finite limit can permit required WAL removal at checkpoint. PostgreSQL 18 also has an idle-slot timeout that can invalidate an inactive slot; zero disables that timeout. [Replication retention settings](https://www.postgresql.org/docs/18/runtime-config-replication.html).

Consider a hypothetical constant workload with 8 GiB of WAL generated per hour. There are 32 GiB of slot headroom and 20 GiB of source disk headroom reserved for additional WAL. If the consumer makes no progress, the nominal slot window is four hours, but disk headroom allows only 2.5 hours under these assumptions. Neither estimate includes a safety margin or other disk growth.

| Planning quantity | Calculation | Result |
| --- | --- | ---: |
| Slot headroom at constant generation | 32 GiB / 8 GiB per hour | 4 hours |
| Reserved disk headroom at the same rate | 20 GiB / 8 GiB per hour | 2.5 hours |
| Backlog after a separate four-hour outage, if retention permits | 4 hours × 8 GiB per hour | 32 GiB |
| Net catch-up at 24 GiB/hour consumption | 24 − 8 GiB per hour | 16 GiB/hour |
| Catch-up time for that backlog | 32 GiB / 16 GiB per hour | 2 hours |

The four-hour-outage row is a separate scenario that assumes sufficient retention and disk. It would exceed the 20-GiB disk allowance in the first scenario. The distinction matters: an arithmetic catch-up estimate does not prove the source could safely survive that outage.

Use compatible measured units for generation and consumption. If the consumer advances no faster than new WAL is generated, the backlog cannot shrink under those conditions. Test peak behavior, reserve room for detection and response, and alert before the modeled boundary rather than exactly at it.

## Why can WAL grow when the marketing tables are quiet?

WAL can grow elsewhere in the cluster while a connector's selected tables produce few changes. A quiet marketing dataset therefore does not establish that its replication slot needs no monitoring.

AWS describes this failure pattern for low-traffic capture scopes amid heavier unrelated activity. Its diagnostic guidance distinguishes instance-level storage and lag observations from individual-slot investigation. [AWS guidance on quiet capture scopes](https://aws.amazon.com/blogs/database/resolving-postgresql-replication-lag-with-heartbeat-tables-in-change-data-capture-scenarios/).

Use the selected connector's heartbeat mechanism when it addresses the observed cause. Debezium distinguishes periodic heartbeat emission from a source-side heartbeat action used for a quiet database; a heartbeat table must be included in the relevant publication for its changes to be captured. Verify that the mechanism advances the required positions. [Debezium heartbeat configuration](https://debezium.io/documentation/reference/stable/connectors/postgresql.html).

A heartbeat is not a substitute for fixing an unavailable destination or a consumer that cannot commit progress. Measure its effect on the actual slot and destination, and keep the synthetic heartbeat outside business event counts.

## What should happen after slot loss or failover?

Treat lost continuity as a recovery decision, not a reason to start streaming from an arbitrary new point and declare success. Preserve the last trusted output and investigate the required source history, connector checkpoint and destination state together.

A lost slot is no longer usable. Recreating its name does not restore the missing change history or prove that a saved connector offset can resume. Re-establish a consistent baseline through the supported recovery path and reconcile the affected population before consumers resume. [Slot validity](https://www.postgresql.org/docs/18/view-pg-replication-slots.html).

PostgreSQL supports failover-slot synchronization with specific primary/standby prerequisites. Successful database promotion alone does not prove the logical consumer can resume. Test the connector and managed-provider procedure against the actual slot state and new primary. [Logical slot synchronization](https://www.postgresql.org/docs/18/logicaldecoding-explanation.html).

Keep recovery bounded and inspect the final consumer. A rebuilt source copy may be correct while a downstream model retains deleted keys or a CRM audience still contains obsolete members. Use the [schema drift workflow](https://www.martechdevs.com/blog/schema-drift) when an incompatible field change contributed to the incident.

![Slot state, source history and the last trusted output determine whether capture can resume or needs a consistent replacement baseline. Both routes require reconciled keys, deletions, values and consumer cutoff. Failed checks keep consumers gated; accepted recovery resumes delivery with retention monitoring.](https://www.martechdevs.com/assets/blog/postgres-cdc-marketing-data.svg)

Diagram connections:

- Inspect slot state source history and last trusted output to Required log continuity and connector state are usable?. 
- Required log continuity and connector state are usable? to Resume the supported stream and measure catch-up: Yes. 
- Required log continuity and connector state are usable? to Protect consumers and establish a consistent replacement baseline: No. 
- Resume the supported stream and measure catch-up to Keys deletes values and consumer cutoff reconcile?. 
- Protect consumers and establish a consistent replacement baseline to Keys deletes values and consumer cutoff reconcile?. 
- Keys deletes values and consumer cutoff reconcile? to Keep consumers gated and repair the failed boundary: No. 
- Keep consumers gated and repair the failed boundary to Inspect slot state source history and last trusted output. 
- Keys deletes values and consumer cutoff reconcile? to Resume consumers and monitor source retention and delivery: Yes

## Which seven tests belong in the release checklist?

Use seven controlled cases that test both the source change and the consumer's required result. Preserve the records, expected outcome and checkpoint evidence so the cases can be repeated after connector or schema changes.

| Test | Deliberate operation | Required result |
| --- | --- | --- |
| 1. Snapshot handoff | Update a record while the initial copy runs | Complete final population with the correct latest state |
| 2. Scoped identity | Reuse a local ID in two tenants | Both entities remain separate |
| 3. Replay and ordering | Repeat a change and deliver an older version | No duplicated value or state regression |
| 4. Delete and key change | Remove a record or change its key | Agreed current-state and historical treatment |
| 5. Wide-field and schema behavior | Update another field while large text stays unchanged; add a field | No placeholder-to-null corruption; reviewed schema coverage |
| 6. Interruption and catch-up | Pause capture within the safe test envelope | Source remains healthy and the consumer recovers by its deadline |
| 7. Failover or lost continuity | Rehearse the supported recovery procedure | No silent gap before consumer release |

## Checklist

- [ ] Source version, provider, connector and publication scope are recorded.
- [ ] Source identity and destination business keys preserve the required grain.
- [ ] Initial coverage and snapshot-to-stream handoff are proven.
- [ ] Repeated changes, deletions and unavailable values have tested semantics.
- [ ] Slot retention, idle timeout and source disk have actionable monitoring.
- [ ] Catch-up capacity and the final consumer deadline are measured separately.
- [ ] Recovery and failover leave evidence that the business output reconciles.

For a stalled or unreliable feed, [get the $99 martech audit](https://www.martechdevs.com/pricing) with its slot state and one affected business record. For a new source-to-warehouse and activation path, [request a detailed implementation scope](https://www.martechdevs.com/data-warehouse-cdp#offer) that includes recovery and operating handover.

## Frequently asked questions

### Can a healthy replication slot prove that an audience is current?

No, the slot describes a source-side part of the delivery chain. The connector, warehouse models, activation job and destination can each be behind or reject changes. Verify the final audience membership and accepted cutoff as well as source progress. Keep a controlled eligibility change in the acceptance fixture so monitoring proves the full path the business relies on.

### Is increasing the WAL retention limit enough to repair lost continuity?

No, increasing a limit can protect future retention but cannot recreate changes already removed or restore an invalidated slot's earlier state. Establish what history and checkpoints remain, then use the supported recovery procedure. Reconcile the rebuilt dataset and downstream consumer before resuming. A larger retention budget is a capacity decision, not evidence that a previous data gap has disappeared.

### Should two independent connectors share one replication slot?

Use independent slot state for independent consumers unless the selected architecture explicitly supplies another supported coordination mechanism. PostgreSQL allows only one active receiver for a logical slot, and different receivers using it at different times continue from its shared progress. A slot is not a broadcast subscription for two unrelated pipelines. Keep each consumer's progress and retirement procedure identifiable.

### Does a database backup replace CDC recovery planning?

No, a backup serves database restoration, while a CDC pipeline also depends on its capture state, retained log continuity and destination interpretation. Define how the selected connector restarts after a source restore or replacement, and test the resulting baseline and changes. Preserve downstream history that cannot be recreated from a current snapshot, rather than assuming every recovery source contains every earlier business state.

### How should a retired CDC pipeline be cleaned up?

Identify its consumers, preserve required history and confirm that another route or an agreed retirement covers the business need. Then have the source owner remove the obsolete capture resources through the supported procedure. A disconnected consumer can leave retention behind, so verify slot and source-storage behavior after cleanup. Do not remove a slot merely because its name looks unused.

## Sources

1. [PostgreSQL logical decoding concepts](https://www.postgresql.org/docs/18/logicaldecoding-explanation.html)

2. [PostgreSQL logical replication configuration](https://www.postgresql.org/docs/18/logical-replication-config.html)

3. [PostgreSQL publications and replica identity](https://www.postgresql.org/docs/18/logical-replication-publication.html)

4. [PostgreSQL logical replication security](https://www.postgresql.org/docs/18/logical-replication-security.html)

5. [PostgreSQL logical replication restrictions](https://www.postgresql.org/docs/18/logical-replication-restrictions.html)

6. [PostgreSQL replication-slot catalog](https://www.postgresql.org/docs/18/view-pg-replication-slots.html)

7. [PostgreSQL WAL administration functions](https://www.postgresql.org/docs/18/functions-admin.html)

8. [PostgreSQL retention and timeout configuration](https://www.postgresql.org/docs/18/runtime-config-replication.html)

9. [Debezium PostgreSQL connector](https://debezium.io/documentation/reference/stable/connectors/postgresql.html)

10. [AWS: replication lag and heartbeat tables](https://aws.amazon.com/blogs/database/resolving-postgresql-replication-lag-with-heartbeat-tables-in-change-data-capture-scenarios/)