> 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 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](/concepts/change-capture.md). ## Prerequisites CDC needs two things: logical replication turned on, and a Sling role with the replication privilege plus `SELECT` on the source tables. Verify the WAL level first: ```sql SHOW wal_level; -- Must be 'logical' ``` How you set `wal_level` and grant replication depends on where PostgreSQL runs. Pick your platform below.
Self-managed PostgreSQL Set `wal_level` and restart: ```sql ALTER SYSTEM SET wal_level = logical; -- Restart PostgreSQL for the change to take effect ``` Grant the Sling role the `REPLICATION` attribute and read access: ```sql ALTER ROLE sling_user REPLICATION; GRANT SELECT ON ALL TABLES IN SCHEMA public TO sling_user; ``` Ensure `pg_hba.conf` allows replication connections for the user and database: ``` host replication sling_user 0.0.0.0/0 md5 ``` Reload PostgreSQL after editing `pg_hba.conf`.
AWS RDS / Aurora PostgreSQL RDS never grants superuser, so `ALTER ROLE sling_user REPLICATION` always fails. AWS conveys the same capability through membership in the built-in `rds_replication` role instead. **1. Turn on logical replication.** `ALTER SYSTEM` is blocked on RDS. Set `rds.logical_replication = 1` in a **custom parameter group** attached to the instance: ```bash aws rds create-db-parameter-group \ --db-parameter-group-name sling-cdc-logical \ --db-parameter-group-family postgres16 \ --description "Logical replication for Sling CDC" aws rds modify-db-parameter-group \ --db-parameter-group-name sling-cdc-logical \ --parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" aws rds modify-db-instance \ --db-instance-identifier my-instance \ --db-parameter-group-name sling-cdc-logical ``` **2. Reboot the instance.** `rds.logical_replication` is a **static** parameter, so it does not take effect until a reboot: ```bash aws rds reboot-db-instance --db-instance-identifier my-instance ``` Then confirm `SHOW wal_level;` returns `logical`. Setting this parameter also adjusts `max_wal_senders`, `max_replication_slots`, and `max_connections`. **3. Grant replication via role membership.** Run this as the RDS master user (which holds `rds_superuser`): ```sql CREATE ROLE sling_user WITH LOGIN PASSWORD ''; -- The RDS equivalent of the REPLICATION attribute GRANT rds_replication TO sling_user; GRANT SELECT ON ALL TABLES IN SCHEMA public TO sling_user; ``` {% hint style="info" %} `pg_roles.rolreplication` stays `false` for this role — on RDS only the internal `rdsadmin` and `rdsrepladmin` roles carry the attribute. The privilege arrives through `rds_replication` membership, and Sling checks for both. {% endhint %} **4. Connect over SSL.** RDS rejects plaintext connections on most instances, so set `sslmode` on the connection: ```bash sling conns set MY_RDS type=postgres host=my-instance.abc123.us-east-1.rds.amazonaws.com \ user=sling_user password='' database=my_database sslmode=require ``` Make sure to use sling version `1.6.2+` so it can work with RDS. There is **no** `pg_hba.conf` step: RDS manages it and already permits replication connections. Make sure the instance's security group allows inbound 5432 from wherever Sling runs. {% hint style="info" %} Create the publication as the RDS **master user**. On RDS the master user can run `CREATE PUBLICATION`, including `FOR ALL TABLES`, even though it is not a true superuser — `rds_superuser` covers it. The least-privilege Sling role cannot (it has no `CREATE` on the database), which is by design: publication setup is a one-time DBA task. Prefer a scoped publication anyway — see [CDC Setup](#cdc-setup-dba-owned) below. {% endhint %}
Google Cloud SQL / Azure Database for PostgreSQL Both platforms permit the `REPLICATION` attribute, so the grant is the standard one. **Cloud SQL:** set the `cloudsql.logical_decoding` flag to `on`, then: ```sql ALTER ROLE sling_user REPLICATION; GRANT SELECT ON ALL TABLES IN SCHEMA public TO sling_user; ``` **Azure Database for PostgreSQL (Flexible Server):** set the `wal_level` server parameter to `logical` and restart, then apply the same two grants. Neither platform needs a `pg_hba.conf` edit — both manage it — but check the firewall or network rules allow access from wherever Sling runs.
Sling connects with a **least-privilege, read-only** role: replication rights plus `SELECT` on the source tables, and nothing more. It runs no DDL against your source — the publication and replica identity are provisioned once by a DBA (see [CDC Setup](#cdc-setup-dba-owned) below). {% 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 %} ## 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 (on self-managed PostgreSQL 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' ``` {% hint style="info" %} On managed PostgreSQL (RDS, Cloud SQL, Azure), add `sslmode=require` to the source connection. Sling defaults `sslmode` to `disable`, which these platforms typically reject. {% endhint %} ```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. On self-managed PostgreSQL, set `wal_level` and restart: ```sql ALTER SYSTEM SET wal_level = logical; -- Then restart PostgreSQL ``` On **AWS RDS**, `ALTER SYSTEM` is blocked. Set `rds.logical_replication = 1` in a custom parameter group and **reboot** the instance — the parameter is static, so it does not apply until then. On **Cloud SQL**, set the `cloudsql.logical_decoding` flag to `on`. On **Azure**, set the `wal_level` server parameter to `logical` and restart. See [Prerequisites](#prerequisites). ### "current user does not have REPLICATION privilege" Grant the `REPLICATION` attribute to the CDC user: ```sql ALTER ROLE sling_user REPLICATION; ``` On **AWS RDS / Aurora** that statement always fails, because it requires superuser and RDS does not grant it. Use role membership instead, as the RDS master user: ```sql GRANT rds_replication TO sling_user; ``` {% hint style="info" %} `pg_roles.rolreplication` remains `false` after this grant — that is expected on RDS. The privilege comes from `rds_replication` membership, which Sling also checks. {% endhint %} ### "could not open replication connection" On self-managed PostgreSQL, 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. On managed platforms there is no `pg_hba.conf` to edit. Check instead that: * the network path is open (RDS security group, Cloud SQL authorized networks, or Azure firewall rules) for port 5432 * `sslmode` is set on the connection — RDS rejects plaintext on most instances, and Sling defaults to `disable` ### "permission denied for database" when creating a publication `CREATE PUBLICATION` needs `CREATE` on the database, which the least-privilege Sling role deliberately lacks. Create it as an administrative role instead — the RDS master user, the Cloud SQL `postgres` user, or the Azure admin — then point Sling at it with `change_feed`: ```sql CREATE PUBLICATION sling_cdc_pub FOR TABLE public.customers, public.orders; ``` On self-managed PostgreSQL, `FOR ALL TABLES` additionally requires a true superuser. Scoping the publication to named tables avoids that and keeps unrelated tables out of replication scope. ### "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.