> For the complete documentation index, see [llms.txt](https://docs.slingdata.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.slingdata.io/concepts/change-capture/postgres.md). # 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 ``` Sling connects with a **least-privilege, read-only** role: it needs only the `REPLICATION` attribute and `SELECT` on the source tables. Sling does not run any DDL against your source — the publication and replica identity are provisioned once by a DBA (see [CDC Setup](#cdc-setup-dba-owned) below). ```sql -- Grant replication privilege (needed to create/read the replication slot) ALTER ROLE sling_user REPLICATION; -- Grant read access to source tables (needed for the initial snapshot) GRANT SELECT ON ALL TABLES IN SCHEMA public TO sling_user; ``` {% hint style="info" %} The Sling role does **not** need `ALTER` or ownership on the tables. Replica identity and the publication are set up by a DBA, not by Sling. This is what makes CDC work on managed PostgreSQL (Cloud SQL, RDS, Azure) where superuser/owner rights are unavailable. {% 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 %} ## CDC Setup (DBA-owned) Sling reads from a pre-provisioned **publication**; it never creates one. A DBA (or a role that owns the tables) runs this setup once, then Sling references it via the [`change_feed`](/concepts/change-capture.md#options-reference) option. **1. Create a publication scoped to the tables you want to capture:** ```sql CREATE PUBLICATION sling_cdc_pub FOR TABLE public.customers, public.orders; ``` Scope the publication to exactly the streams you replicate — avoid `FOR ALL TABLES` unless you truly want every table in scope (it also requires superuser). **2. Ensure each captured table has a CDC-usable replica identity.** PostgreSQL only includes old-row data in UPDATE and DELETE WAL messages when the table has a replica identity — without one, those changes carry no key and cannot be applied to the target. Tables with a **primary key** already satisfy this under the default replica identity (PostgreSQL logs the key — sufficient for a PK-based merge), so no DDL is needed. Tables **without** a primary key need one set explicitly. Either form works: ```sql -- Option A: log the full old row. Always works, but increases WAL volume. ALTER TABLE public.events REPLICA IDENTITY FULL; -- Option B: log only a key, via an existing unique index on NOT NULL column(s). -- Cheaper than FULL. The index must be unique, valid, non-partial and -- non-deferrable, over NOT NULL columns. CREATE UNIQUE INDEX events_id_uq ON public.events (id); ALTER TABLE public.events REPLICA IDENTITY USING INDEX events_id_uq; ``` Sling accepts either, and validates it before streaming. Note that `REPLICA IDENTITY NOTHING` is never usable for CDC — including on tables that have a primary key. **3. Point Sling at the publication** with `change_feed`: ```yaml defaults: mode: change-capture primary_key: [id] object: public.{stream_table} change_capture_options: change_feed: sling_cdc_pub # the DBA-provisioned publication streams: public.customers: public.orders: ``` Sling validates the publication exists and covers each configured stream, then creates and manages its own replication slot (which needs only the `REPLICATION` attribute). If the publication is missing, does not cover a table, or a table lacks a usable replica identity, Sling stops with a clear error containing the exact DDL for a DBA to run. {% hint style="info" %} If `change_feed` is omitted, Sling expects a publication named after its derived slot (`sling_cdc_`) and errors with the `CREATE PUBLICATION` DDL if it is not found. Setting `change_feed` explicitly is recommended so the publication name is stable and readable. {% 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 The **publication** is DBA-provisioned (see [CDC Setup](#cdc-setup-dba-owned)) and shared by all streams that reference the same `change_feed`. The **replication slot** is created and managed by Sling — it needs only the `REPLICATION` attribute, never ownership. How the slot is scoped depends on the [`slot_level`](/concepts/change-capture.md#options-reference) option, which defaults to `shared` for PostgreSQL. ### `slot_level: shared` (default) All streams in the replication share **one** replication slot (and read from the one `change_feed` publication): * **Replication slot**: Created once with a deterministic name (`sling_cdc_`) 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**: The one named by `change_feed`. It must cover every configured stream; Sling validates this and errors with `ALTER PUBLICATION … ADD TABLE` if a table is missing. 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: ```sql SELECT pg_drop_replication_slot('sling_cdc_'); -- The publication is DBA-owned; drop it only if no stream still uses it: -- DROP PUBLICATION sling_cdc_pub; ``` ### `slot_level: stream` Each captured table gets its **own** slot (all still reading from the DBA's publication): * **Replication slot**: Named `sling_cdc_` where the hash is derived from the host, port, and that table's name, so each stream is fully isolated and advances independently. * **Publication**: The `change_feed` publication, which must include the 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. {% hint style="info" %} Cleanup drops **only** the Sling-managed replication slot. The publication and each table's replica identity are DBA-owned, so Sling leaves them exactly as it found them — deactivating CDC never issues DDL against your tables. {% endhint %} ## 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. ### "CDC publication … does not exist" The publication named by `change_feed` (or the derived default) has not been created. Sling does not create it — a DBA must, once: ```sql CREATE PUBLICATION sling_cdc_pub FOR TABLE public.customers, public.orders; ``` Then set `change_feed: sling_cdc_pub` in `change_capture_options`. ### "table … is not a member of publication" A configured stream is not covered by the publication. A DBA adds it: ```sql ALTER PUBLICATION sling_cdc_pub ADD TABLE public.orders; ``` ### "table … has no CDC-usable replica identity" PostgreSQL is not logging a key for UPDATE/DELETE on the table, so those changes cannot be applied to the target. The error reports the table's current `relreplident` value. Causes: | Cause | Fix (run once, by a table owner) | | ------------------------------------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------- | | No primary key, replica identity left at `DEFAULT` (`d`) | `ALTER TABLE … REPLICA IDENTITY FULL;` or nominate a unique index (see below) | | Replica identity explicitly set to `NOTHING` (`n`) — applies even if the table has a primary key | `ALTER TABLE … REPLICA IDENTITY DEFAULT;` (PK tables) or `FULL` | | `USING INDEX` (`i`) whose nominated index was dropped, invalidated, or is partial/deferrable/over nullable columns | Re-nominate a usable index, or switch to `FULL` | ```sql -- Log the full old row: ALTER TABLE public.events REPLICA IDENTITY FULL; -- Or log only a key, using a unique index on NOT NULL column(s): ALTER TABLE public.events REPLICA IDENTITY USING INDEX events_id_uq; ``` Tables that have a primary key and are left at the default replica identity do **not** need any of this — PostgreSQL already logs the key for a PK-based merge. ### "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 This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## 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, and the optional `goal` query parameter: ``` GET https://docs.slingdata.io/concepts/change-capture/postgres.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. 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.