PostgreSQL

The PostgreSQL source uses logical replication with the built-in pgoutput plugin: one replication slot and one publication per task, no extensions and no triggers.

Prerequisites

Server settings

Logical replication must be enabled. Set it in postgresql.conf (or with ALTER SYSTEM) and restart the server:

ALTER SYSTEM SET wal_level = logical;
-- Each running task holds one replication slot and, while streaming, one WAL sender.
ALTER SYSTEM SET max_replication_slots = 10;   -- at least the number of tasks, plus other consumers
ALTER SYSTEM SET max_wal_senders = 10;
-- Optional safety net: cap the WAL a forgotten slot can retain (PostgreSQL 13+).
ALTER SYSTEM SET max_slot_wal_keep_size = '20GB';

Check with SHOW wal_level;. If it is not logical, the task fails with:

postgres: wal_level is "replica" but logical replication needs "logical"; set wal_level = logical in postgresql.conf (or ALTER SYSTEM SET wal_level = logical) and restart the server

On managed services the setting has another name: on Amazon RDS and Aurora set the parameter rds.logical_replication = 1 in the parameter group; on Google Cloud SQL set the flag cloudsql.logical_decoding = on; on Azure Database for PostgreSQL set wal_level = logical in the server parameters. Each needs a restart.

Table requirements

• The table needs a primary key, or REPLICA IDENTITY FULL, so deletes carry the row's key. Without either the task fails: table public.items has no primary key; add one or run ALTER TABLE "public"."items" REPLICA IDENTITY FULL so deletes can be captured. With REPLICA IDENTITY FULL and no primary key, set mapping.primaryKey to the columns that identify a row.
• REPLICA IDENTITY NOTHING is refused (deletes would carry no key). Use DEFAULT or FULL.
• Regular and partitioned tables are supported (relkind r and p); views are not.

A dedicated user

The user needs the REPLICATION attribute (to create the slot and stream), SELECT on the table (for the snapshot), and either the right to create the publication or a publication created for it.

CREATE ROLE cdc WITH LOGIN REPLICATION PASSWORD 'change-me';
GRANT CONNECT ON DATABASE shop TO cdc;
GRANT USAGE ON SCHEMA public TO cdc;
GRANT SELECT ON public.products TO cdc;

The publication: by default the task creates one named after itself with CREATE PUBLICATION … FOR TABLE, which PostgreSQL only allows to a user with CREATE on the database who owns the table. If the replication user does not own the table, create the publication yourself as the owner (or a superuser) and enter its name in the task's Publication field:

-- as the table owner
CREATE PUBLICATION cdc_products FOR TABLE public.products;

A publication you name is never altered or dropped by the service; if it does not include the table, the task fails with the ALTER PUBLICATION … ADD TABLE … statement to run. On Amazon RDS, the replication attribute is granted with GRANT rds_replication TO cdc; instead of REPLICATION.

Connection string

Any connection string the pgx driver accepts, as a URL or in key/value form:

postgres://cdc:change-me@db.internal:5432/shop?sslmode=require
host=db.internal port=5432 user=cdc password=change-me dbname=shop sslmode=require

The database is part of the connection string. The password is masked as •••••• whenever the connector or task is shown.

Task fields

How changes are applied

• Snapshot. The table is read in chunks of 5,000 rows (in primary-key order when the table has a primary key). Changes during the copy are replayed afterwards from the slot.
• Inserts and updates become upserts. Changes are emitted per committed transaction, with the commit time used for lag.
• Primary key changes (an UPDATE of a key column) delete the old document and write the new one.
• Deletes delete the document by key.
• TRUNCATE holds the task unless allowTruncate is on, in which case the index is emptied.
• Unchanged TOAST values. PostgreSQL does not send large column values (TOASTed text, jsonb, bytea over about 2 kB) that an UPDATE did not change. Those columns are left out of the change. With the default replace write mode the document is replaced without them, so the field disappears from the index until the row is written again. For tables with large values that are updated without changing them, use index.writeMode: upsert, which merges fields into the existing document.

Types are decoded from their text form: integers stay integers, numeric and floats become numbers, json and jsonb become nested objects, arrays become arrays, timestamps become times (formatted by mapping.timeFormat) and bytea becomes base64.

Positions and resume

Positions are WAL LSNs such as 16/B374D848. Each change carries the end LSN of its transaction's commit, so a checkpoint always covers whole transactions. Once Meilisearch confirms a batch, the slot's confirmed position is advanced (status updates every 10 seconds, well within the default wal_sender_timeout of 60 s), which lets PostgreSQL recycle WAL.

While a task is stopped, its slot keeps the WAL it has not confirmed, so nothing is lost, but WAL accumulates on the server. If the slot is invalidated (max_slot_wal_keep_size exceeded) or dropped, the task reports the position as lost and runs a full resync.

Deleting a task

When you delete a task with Drop source replication objects checked (the default in the dashboard, dropSource=true in the API), the service terminates the WAL sender still holding the slot, drops the slot, and drops the publication if it generated it. Without it, the slot stays and keeps retaining WAL.

SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS behind
FROM pg_replication_slots WHERE slot_name LIKE 'cdc_%';
SELECT pg_drop_replication_slot('cdc_old_task');

Troubleshooting