PostgreSQL

CDC source setup for PostgreSQL

Sling supports Change Data Capture from PostgreSQL by reading the Write-Ahead Log (WAL) via logical replication. Each run reads row-level inserts, updates, and deletes from the WAL and merges them into the target table.

For general CDC concepts, the two-phase process, and all available options, see the Change Capture overview.

Prerequisites

Ensure your PostgreSQL instance has logical replication enabled:

-- Verify WAL level is set to logical
SHOW wal_level;  -- Must be 'logical'

If wal_level is not logical, update it and restart PostgreSQL:

ALTER SYSTEM SET wal_level = logical;
-- Restart PostgreSQL for the change to take effect

The PostgreSQL user must have the REPLICATION attribute and SELECT access on the source tables:

-- Grant replication privilege
ALTER ROLE sling_user REPLICATION;

-- Grant read access to source tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sling_user;

The user also needs ALTER privilege on captured tables so Sling can set REPLICA IDENTITY FULL, which is required for PostgreSQL to include old row data in UPDATE and DELETE WAL messages.

Ensure pg_hba.conf allows replication connections for the user and database. For example:

host    replication     sling_user      0.0.0.0/0       md5

Reload PostgreSQL after editing pg_hba.conf.

Quick Start

# Source PostgreSQL
sling conns set MY_PG_SOURCE type=postgres host=source.example.com user=sling_user password=secret database=my_database

# Target PostgreSQL
sling conns set MY_PG_TARGET type=postgres host=target.example.com user=postgres password=secret database=analytics

# State store (required for CDC)
export SLING_STATE='MY_PG_TARGET/sling_state'

# replication.yaml
source: MY_PG_SOURCE
target: MY_PG_TARGET

defaults:
  mode: change-capture
  primary_key: [id]
  object: public.{stream_table}

streams:
  public.customers:
  public.orders:

# or declare state here
env:
  SLING_STATE: MY_PG_TARGET/sling_state

# First run: performs the initial snapshot
sling run -d replication.yaml

# Subsequent runs: captures and applies changes
sling run -d replication.yaml

Examples

Large Tables with Custom Chunk Size

For very large tables, adjust the chunk size to control memory usage and checkpointing frequency during the initial snapshot.

source: MY_PG_SOURCE
target: MY_PG_TARGET

defaults:
  mode: change-capture
  primary_key: [id]
  object: analytics.{stream_table}
  change_capture_options:
    snapshot_chunk_size: 50000  # 50k rows per chunk

streams:
  public.transactions:
    # This 10M-row table will be loaded in ~200 chunks
    # If interrupted, it resumes from the last completed chunk

Time-Bounded Snapshots for Very Large Tables

For tables with hundreds of millions of rows, the initial snapshot can take hours. Use snapshot_run_duration to cap how long each run spends on the snapshot. The next run automatically resumes from the last completed chunk.

source: MY_PG_SOURCE
target: MY_PG_TARGET

defaults:
  mode: change-capture
  primary_key: [id]
  object: analytics.{stream_table}
  change_capture_options:
    snapshot_chunk_size: 50000
    snapshot_run_duration: 30m  # spend at most 30 minutes per run on the snapshot

streams:
  public.huge_events:
    # 500M rows — will take multiple runs to complete the initial load
    # Each run processes ~30 minutes worth of chunks, then exits cleanly

High-Throughput Workloads

For tables with heavy write activity, increase run_max_events and run_max_duration so each run captures more changes.

source: MY_PG_SOURCE
target: MY_PG_TARGET

defaults:
  mode: change-capture
  primary_key: [id]
  object: warehouse.{stream_table}
  change_capture_options:
    run_max_events: 50000  # Process up to 50k events per run
    run_max_duration: 60s  # Wait up to 60 seconds for events

streams:
  public.click_events:
  public.page_views:

Soft Deletes

Keep deleted rows in the target instead of physically removing them. Deleted rows are marked with _sling_synced_op = 'D'. Useful for audit trails or when downstream queries need to detect deletions.

source: MY_PG_SOURCE
target: MY_PG_TARGET

defaults:
  mode: change-capture
  primary_key: [id]
  object: public.{stream_table}
  change_capture_options:
    soft_delete: true

streams:
  public.customers:
  public.subscriptions:

When a row is deleted in the source, the target row is preserved with _sling_synced_op set to 'D' and _sling_synced_at updated to the current timestamp. A subsequent re-insert of the same primary key restores the row with the appropriate operation type.

Mixed Streams with Per-Stream Overrides

Different tables can have different CDC options.

source: MY_PG_SOURCE
target: MY_PG_TARGET

defaults:
  mode: change-capture
  primary_key: [id]
  object: public.{stream_table}

streams:
  # High-volume table: larger batches
  public.events:
    change_capture_options:
      run_max_events: 100000
      run_max_duration: 2m

  # Audit table: keep soft deletes
  public.user_accounts:
    change_capture_options:
      soft_delete: true

  # Standard table: uses defaults
  public.products:

Replay / Backfill from a Point in Time

If target data becomes inconsistent, you can replay changes from an earlier position. The replay_from value is applied exactly once per unique value.

source: MY_PG_SOURCE
target: MY_PG_TARGET

defaults:
  mode: change-capture
  primary_key: [id]
  object: public.{stream_table}
  change_capture_options:
    replay_from: "0/16B3748"  # Re-process all changes from this WAL position

streams:
  public.orders:

After the replay run completes, remove or change the replay_from value. Leaving it unchanged has no effect (it is only applied once).

Replay Formats

The replay_from option accepts these PostgreSQL-specific position formats:

• RFC 3339 timestamp: 2025-06-01T00:00:00Z — resolves to the replication slot's restart_lsn (best-effort; PostgreSQL WAL does not support direct timestamp seeking)

• PostgreSQL LSN: 0/16B3748 — a Write-Ahead Log position in hex format

Replication Slots and Publications

Sling automatically manages replication slots and publications. How they are scoped depends on the slot_level option, which defaults to shared for PostgreSQL.

slot_level: shared (default)

All streams in the replication share one replication slot and one publication:

• Replication slot: Created once with a deterministic name (sling_cdc_<hash>) derived from the host, port, and database — independent of the table list. Every stream reads from this single slot, so they all converge to the same unified WAL position (LSN), giving a point-in-time-consistent view across tables. The WAL is decoded only once per run regardless of how many tables are captured.

• Publication: Created as CREATE PUBLICATION sling_cdc_<hash> FOR ALL TABLES, so every table is decodable from the slot's consistent point regardless of which stream initializes the slot first.

• REPLICA IDENTITY: Set to FULL on each captured table so PostgreSQL includes complete row data in UPDATE and DELETE WAL messages.

Because the slot is shared across streams, it is not dropped automatically when you remove a single stream — removing it would break the other streams reading from it. To tear down a shared slot manually:

SELECT pg_drop_replication_slot('sling_cdc_<hash>');
DROP PUBLICATION sling_cdc_<hash>;

slot_level: stream

Each captured table gets its own slot and publication:

• Replication slot: Named sling_cdc_<hash> where the hash is derived from the host, port, and that table's name, so each stream is fully isolated and advances independently.

• Publication: Created per table (CREATE PUBLICATION sling_cdc_<hash> FOR TABLE ...).

• REPLICA IDENTITY: Set to FULL on the captured table.

Per-table slots are cleaned up automatically when you remove the corresponding CDC stream. Use this mode when you want each table to advance fully independently rather than sharing a unified position.

WAL Retention

Replication slots prevent PostgreSQL from recycling WAL segments that haven't been consumed yet. If CDC runs are paused for an extended period, WAL can accumulate and consume significant disk space.

To protect against unbounded WAL growth, set a safety limit:

-- Cap WAL retained per slot to 10 GB (PostgreSQL 13+)
ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
SELECT pg_reload_conf();

If a replication slot exceeds max_slot_wal_keep_size, PostgreSQL may invalidate the slot. Sling will detect this and automatically recreate the slot with a fresh initial snapshot on the next run.

Troubleshooting

"wal_level must be 'logical'"

The PostgreSQL instance is not configured for logical replication. Set wal_level and restart:

ALTER SYSTEM SET wal_level = logical;
-- Then restart PostgreSQL

"current user does not have REPLICATION privilege"

Grant the REPLICATION attribute to the CDC user:

ALTER ROLE sling_user REPLICATION;

"could not open replication connection"

Ensure pg_hba.conf allows replication connections for the user and database. Add a line like:

host    replication     sling_user      0.0.0.0/0       md5

Then reload PostgreSQL configuration.

"could not set REPLICA IDENTITY FULL"

The CDC user needs ALTER privilege on the captured tables:

GRANT ALL ON ALL TABLES IN SCHEMA public TO sling_user;

"replication slot is already active"

Another consumer (another Sling process or a different tool) is connected to the same replication slot. Ensure only one CDC process is running per stream group at a time.

Initial snapshot keeps restarting

Ensure SLING_STATE is configured. Without state persistence, Sling cannot track that the snapshot completed and will restart it on every run.

Large WAL lag warning

If Sling warns about WAL lag exceeding 5 GB, the CDC consumer is falling behind. Increase the run frequency or raise run_max_events / run_max_duration to process more changes per run. Consider setting max_slot_wal_keep_size as a safety net to prevent unbounded disk usage.