Enable change capture on PostgreSQL

PostgreSQL's native CDC is logical decoding: Elglide reads your write-ahead log (WAL) through a replication slot to sync only what changed. Once it's enabled, Elglide manages the slot, the publication, the first consistent snapshot, and WAL cleanup for you — per task, automatically.

Unlike SQL Server (enabled per-database), PostgreSQL's master switch wal_level = logical is set at the server / instance level and needs a one-time restart. After that, it's effectively per-table (each table just needs a usable change key).

Permissions required

To turn on logical WAL (one-time, an admin): a superuser (ALTER SYSTEM SET wal_level + restart), or on managed services the parameter-group change + reboot (see step 1).
For Elglide's connection role (ongoing):
- REPLICATION attribute — or GRANT rds_replication on Amazon RDS — to create and read the replication slot.
- SELECT on each synced table — the initial snapshot + reads.
- CREATE on the database — Elglide creates a per-task PUBLICATION.
- Ownership of the synced tables — PostgreSQL requires the role to own a table to add it to a publication (or be a superuser). Easiest: connect with a role that owns the tables, or make it a member of the owning role.

A typical setup (run by an admin / the table owner):

ALTER ROLE elglide_reader WITH REPLICATION;          -- (RDS: GRANT rds_replication TO elglide_reader;)
GRANT CREATE ON DATABASE yourdb       TO elglide_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO elglide_reader;

-- publication membership needs table ownership — pick one:
ALTER TABLE public.your_table OWNER TO elglide_reader;   -- A) the role owns the table
GRANT owning_role TO elglide_reader;                     -- B) the role joins the owner

If a table loads Full instead of using logical replication, the role is usually missing one of these — most often REPLICATION or table ownership. A table the role can't SELECT at all fails the sync with a permission hint.

1. Turn on logical WAL at the server level (one-time, needs a restart)

On a self-managed PostgreSQL, as a superuser:

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_replication_slots = 10;  -- >= number of concurrent CDC tasks
ALTER SYSTEM SET max_wal_senders     = 10;    -- one sender per active slot

Then restart the instance and confirm:

sudo systemctl restart postgresql      # or your platform's restart

-- after restart:
SHOW wal_level;                        -- must report: logical

Managed services (no shell): set the equivalent parameter and reboot.

Amazon RDS / Aurora: set rds.logical_replication = 1 in the parameter group, then reboot.
Google Cloud SQL: set the cloudsql.logical_decoding = on flag, then restart.
Azure Database for PostgreSQL: set wal_level = LOGICAL in server parameters, then restart.

2. Give the Elglide connection role REPLICATION

The role Elglide connects with needs the REPLICATION attribute (to create slots) plus read access:

ALTER ROLE elglide_reader WITH REPLICATION;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO elglide_reader;

On Amazon RDS, grant the managed replication role instead of WITH REPLICATION:

GRANT rds_replication TO elglide_reader;

3. Make sure each table has a change key (REPLICA IDENTITY)

Logical decoding needs a way to identify a row on UPDATE/DELETE:

Table has a PRIMARY KEY — nothing to do; the default already uses the PK.
No primary key (or you want every column logged) — set full identity:

ALTER TABLE public.your_table REPLICA IDENTITY FULL;

4. That's it — Elglide handles the rest

When you create an Incremental task on a ready table, Elglide automatically:

creates a dedicated replication slot (elglide_slot_<taskId>) and publication (elglide_pub_<taskId>);
takes a consistent snapshot for the first Full load, then streams changes from exactly that point (no gaps, no duplicates);
advances the slot as data lands in Snowflake, releasing WAL so it doesn't pile up;
self-heals — if a slot is ever dropped or invalidated, the next run re-baselines with a full sync and resumes.

Data types in change capture

Change-captured rows are cast to Snowflake the same way as the first Full load — same target types, same representation — so a row updated via logical decoding matches a row from the initial snapshot. High-precision numeric keeps full precision, uuid/json/ timestamptz/bytea land identically, and so on. See the PostgreSQL connector data-type reference (sign-in required) for the per-type rules.

One current exception — array columns. On the Full load, array columns are stored as JSON (e.g. [1,2,3]); over change capture they currently arrive in PostgreSQL array-literal form (e.g. {1,2,3}). The values are fully preserved; only the text shape differs. If you sync array columns over CDC and need the JSON form, let us know.

Keep an eye on WAL retention

A replication slot holds onto WAL until Elglide has consumed it. As long as the task keeps running, that stays small (about one sync interval). If you pause a CDC task for a long time, WAL can accumulate on the source — so either keep it running, deactivate it, or cap retention with max_slot_wal_keep_size.

Verify in Elglide

Open the connection's Pipelines tab. A table that's logical-decoding-ready will create an Incremental task using Logical Replication — a first Full load to seed Snowflake, then change-only syncs. If a table still loads Full, check that all three boxes above are ticked (server wal_level, role REPLICATION, table identity).