# 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](/concepts/change-capture.md).

## Prerequisites

Ensure your PostgreSQL instance has logical replication enabled:

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

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

```sql
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:

```sql
-- 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;
```

{% hint style="info" %}
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.
{% endhint %}

{% hint style="info" %}
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`.
{% endhint %}

## Quick Start

```bash
# 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'
```

```yaml
# 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
```

```bash
# 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.

```yaml
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.

```yaml
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.

```yaml
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.

```yaml
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.

```yaml
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.

```yaml
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 for each CDC stream:

* **Replication slot**: Created with a deterministic name (`sling_cdc_<hash>`) derived from the host, port, and table list. Each stream group gets its own slot for isolation.
* **Publication**: Created automatically for the captured tables (e.g., `CREATE PUBLICATION sling_cdc_<hash> FOR TABLE ...`).
* **REPLICA IDENTITY**: Set to `FULL` on all captured tables so PostgreSQL includes complete row data in UPDATE and DELETE WAL messages.

These resources are cleaned up automatically when you remove the CDC configuration.

## 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:

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

{% hint style="warning" %}
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.
{% endhint %}

## Troubleshooting

### "wal\_level must be 'logical'"

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

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

### "current user does not have REPLICATION privilege"

Grant the `REPLICATION` attribute to the CDC user:

```sql
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:

```sql
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.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.slingdata.io/concepts/change-capture/postgres.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.