Skip to main content

CDC Sink for PostgreSQL: Cleanup and Maintenance

Why manual cleanup is required​

A replication slot tells PostgreSQL to retain WAL until the consumer confirms it has processed the changes by advancing the slot's confirmed_flush_lsn. If CDC Sink stops consuming from the slot because the task was deleted, confirmed_flush_lsn no longer advances, so PostgreSQL can continue retaining WAL for that slot.

This can lead to:

  • Disk space exhaustion on the PostgreSQL server
  • Degraded performance as old WAL segments pile up

When the slot is no longer needed, a database administrator should drop it.

Why RavenDB does not drop slots automatically:​

There are valid reasons to keep a slot after a task is deleted.

For example, you may want to create a new CDC Sink task that resumes from the same position by reusing the existing slot, or you may want to review what changes are pending before cleaning up.
The PostgreSQL user configured in the CDC Sink connection string may also not have the permissions required to drop replication slots, even if it had the permissions to create them.

For these reasons, PostgreSQL-side cleanup is the responsibility of the database administrator.

Disabled and stopped tasks keep their slot​

Deleting a task is not the only way WAL accumulates.

A task that is disabled or stopped (for example, because the responsible node is down or the connection to PostgreSQL is failing) is not consuming from PostgreSQL, but it keeps its replication slot.

While the task is not consuming, confirmed_flush_lsn stops advancing and PostgreSQL retains WAL for that slot exactly as it would for a deleted task - except that the task still exists, so the slot will not show up as "orphaned".

This makes long-disabled tasks a common and easily overlooked cause of disk growth.
When you disable a task that you do not intend to re-enable soon, decide deliberately whether to keep the slot so the task can resume from the same position later, or drop it and accept that the retained WAL position is gone.

To see how much WAL each slot is holding - active or not - use the retained-WAL query in Monitoring PostgreSQL.

Finding orphaned slots and publications​

List CDC Sink replication slots that use the auto-generated prefix:

SELECT slot_name, active, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_name LIKE 'rvn_cdc_s_%';

An active = false slot is not being consumed.
Compare the list to your CDC Sink tasks in RavenDB - any slot whose corresponding task no longer exists is orphaned.

Note that an inactive slot does not always mean the task is gone: a disabled or stopped task also leaves its slot inactive (see Disabled and stopped tasks keep their slot).

To see how much WAL each slot is retaining, use the query in Monitoring PostgreSQL.

List CDC Sink publications that use the auto-generated publication prefix:

SELECT p.pubname,
       p.puballtables,
       n.nspname AS schema_name,
       c.relname AS table_name
FROM pg_publication p
LEFT JOIN pg_publication_rel pr ON pr.prpubid = p.oid
LEFT JOIN pg_class c ON c.oid = pr.prrelid
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE p.pubname LIKE 'rvn_cdc_p_%'
ORDER BY p.pubname, n.nspname, c.relname;

The rvn_cdc_s_ and rvn_cdc_p_ prefixes match only auto-generated names.

If a slot or publication was created with a custom name, either supplied when the task was created or pre-created by an administrator, drop the LIKE filter and cross-check the full list against your CDC Sink task configurations instead.

Dropping a replication slot​

Use the actual slot name from the CDC Sink task configuration or from pg_replication_slots:

SELECT pg_drop_replication_slot('<slot-name>');

You cannot drop an active replication slot (one with active = true).
Disable or delete the CDC Sink task, or otherwise stop the process that is consuming the slot, before dropping it.

Dropping a slot discards the retained WAL position for that slot.
If a CDC Sink task is later started with saved state that points to WAL no longer retained by the slot,
CDC Sink can report an LSN gap; changes in that gap cannot be recovered from the slot.

Even after the task is stopped or deleted, the slot can remain active for a short time.
PostgreSQL does not release the slot until the streaming connection (the WAL sender) closes,
which can take up to wal_sender_timeout (60 seconds by default).

If pg_drop_replication_slot fails with "replication slot is active for PID …", either wait for the timeout,
or terminate the lingering WAL sender:

SELECT pg_terminate_backend(active_pid)
FROM pg_replication_slots
WHERE slot_name = '<slot-name>' AND active_pid IS NOT NULL;

This is also where you drop a slot that a task reports as using the wrong output plugin ("exists but uses plugin … instead of 'pgoutput'"). Dropping it here lets CDC Sink recreate it with the correct plugin on the next run.
See Task enters error state for the diagnosis.

Dropping a publication​

Use the actual publication name from the CDC Sink task configuration or from pg_publication:

DROP PUBLICATION IF EXISTS "<publication-name>";

Publications are not consumed like slots - they do not accumulate data or hold WAL segments.
However, they should still be dropped when they are no longer needed, to keep the PostgreSQL database clean.

Drop a publication only after confirming that no CDC Sink task or other replication consumer still depends on it.
This is especially important for custom or pre-created publication names.

Too many replication slots​

PostgreSQL limits the total number of replication slots to max_replication_slots.
If the limit is reached, CDC Sink tasks that need to create a new replication slot can fail to start.

Check how many slots exist and what the configured limit is:

SELECT count(*) AS used_slots FROM pg_replication_slots;
SHOW max_replication_slots;

To resolve this, first identify slots that are not currently being consumed:

SELECT slot_name, active
FROM pg_replication_slots
WHERE active = false;

Compare the inactive slots with your CDC Sink task configurations. Drop only slots that are no longer needed:

SELECT pg_drop_replication_slot('<slot-name>');

If the remaining slots are all still needed, increase max_replication_slots in postgresql.conf and restart PostgreSQL.

Preventing WAL from filling the disk​

Regular cleanup removes slots you no longer need, but as a safety net you can also cap how much WAL a single slot is allowed to retain. On PostgreSQL 13 and later, set max_slot_wal_keep_size in postgresql.conf, or with ALTER SYSTEM:

-- Retain at most 10 GB of WAL for any single slot
ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();

If a slot falls further behind than this limit, PostgreSQL can stop retaining WAL for it and the slot can become lost.
This protects the server from running out of disk, but the slot can no longer stream the missing changes.

If CDC Sink has saved state that points to WAL no longer retained by the slot, CDC Sink can report an LSN gap;
changes in that gap cannot be recovered from the slot.

Check each slot's WAL status:

SELECT slot_name, active, wal_status, safe_wal_size
FROM pg_replication_slots;

A wal_status of lost means the slot has already been invalidated.
Use safe_wal_size to see how much more WAL can be written before the slot is at risk.
Treat this setting as a safeguard, not a substitute for dropping slots you no longer need.

Reverting REPLICA IDENTITY changes​

Besides the slot and publication, CDC Sink may modify embedded source tables.

When an embedded table's current replica identity does not include the columns needed to route DELETE events,
CDC Sink can run ALTER TABLE <table> REPLICA IDENTITY FULL on that table.
This change is made on the PostgreSQL table itself, so it persists after the CDC Sink task is deleted.

REPLICA IDENTITY FULL causes PostgreSQL to write the full old row into WAL for every UPDATE and DELETE on that table, which increases WAL volume even for other consumers.

After cleanup, revert the table only if no remaining replication or CDC process depends on the current setting.

-- Inspect current replica identity (d = default, f = full, i = index, n = nothing)
SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       c.relreplident
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = '<schema-name>'
  AND c.relname = '<table-name>';

-- Revert to the default only if DEFAULT is the desired setting after cleanup
ALTER TABLE "<schema-name>"."<table-name>" REPLICA IDENTITY DEFAULT;

Only change replica identity for tables that CDC Sink changed and that no other replication or CDC tool relies on.
If a table still needs REPLICA IDENTITY FULL or a replica identity index for another consumer, leave it.

Slot and publication names are immutable​

The replication slot name and publication name are fixed when the task is created and stored with the task.
You can supply your own names when creating the task; if you omit them, RavenDB auto-generates them (rvn_cdc_s_<task-id> and rvn_cdc_p_<task-id>).

After that, task updates reuse the same slot and publication.
Adding or removing tables, renaming the task, or changing other task settings does not change those names.

When updating a task, you may omit SlotName and PublicationName; RavenDB keeps the existing values.
However, attempting to set a different non-null SlotName or PublicationName results in an error.

If you need to use a different slot or publication, delete the task and create a new one.
If the old slot and publication are no longer needed, clean them up manually -
see Dropping a replication slot and Dropping a publication above.

Keeping the publication in sync when tables change​

While the slot and publication names never change, the publication itself controls which PostgreSQL tables CDC Sink receives changes for. The publication must include all root and embedded source tables configured in the task.

  • Adding tables
    When the task starts after new tables were added to the CDC Sink configuration, CDC Sink checks whether those tables are in the publication. If the PostgreSQL user configured in the CDC Sink connection string has permission, CDC Sink attempts to add the missing tables automatically:

    ALTER PUBLICATION "<publication-name>" ADD TABLE "<schema-name>"."<table-name>";

    If this fails, a database administrator must add the missing tables manually.

  • Removing tables
    CDC Sink does not remove tables from the publication automatically. If a table was removed from the CDC Sink task, a database administrator should remove it from the publication when it is no longer needed:

    ALTER PUBLICATION "<publication-name>" DROP TABLE "<schema-name>"."<table-name>";

If the publication contains tables that are not configured in the CDC Sink task,
CDC Sink discards changes from those tables and raises a warning notification in RavenDB.

For a normal table-list publication,
remove extra tables with ALTER PUBLICATION "<publication-name>" DROP TABLE ....

For a publication created FOR ALL TABLES,
you cannot narrow it with ALTER PUBLICATION ... DROP TABLE or ALTER PUBLICATION ... SET TABLE;

PostgreSQL rejects table-list changes on FOR ALL TABLES publications.
Drop and recreate it as a table-list publication that lists only the tables configured in the CDC Sink task.

See Initial Setup for publication setup and permission guidance.

In this article