PostgreSQL

Data Sync reads PostgreSQL through logical decoding — the same mechanism PostgreSQL uses for its own logical replication — and writes to it with batched upserts. It is one of the few connectors that can sit on either side of a sync, including both sides of the same sync.

Source Sink postgres

Overview

As a source, Data Sync decodes the write-ahead log with the built-in pgoutput plugin. It creates or reuses a replication slot and a publication, then streams Insert, Update, Delete and Truncate events along with the transaction boundaries around them. Nothing polls your tables and nothing writes a trigger into your schema: the slot is a cursor into the WAL, and PostgreSQL retains WAL for it until Data Sync confirms it has been flushed.

As a sink, Data Sync applies batches with INSERT … ON CONFLICT DO UPDATE and keyed DELETEs, which makes a replay converge rather than duplicate. The sink deliberately does not own your schema — it never creates a table, and the only DDL it will ever issue is an additive ADD COLUMN.

Read the two halves independently. The prerequisites, fields and caveats are different in each direction, and configuring one tells you nothing about the other.

As a source

How it captures changes

Data Sync opens a replication connection and starts logical decoding with pgoutput, PostgreSQL's own output plugin. Decoding is scoped by a publication (which tables are visible) and positioned by a replication slot (where in the WAL to resume). On reconnect, Data Sync resumes from the slot's confirmed flush position, so a dropped connection costs latency and not data.

On PostgreSQL 14 and above, Data Sync negotiates logical replication protocol version 2 with streaming enabled. That lets a large transaction be handled incrementally as it is written rather than buffered whole until commit, which is what keeps a bulk UPDATE of ten million rows from becoming a memory event.

One subtlety Data Sync handles for you: PostgreSQL does not re-emit large values stored out of line in TOAST when they were not modified by the statement. An after-image would otherwise be missing those columns entirely. Data Sync backfills unchanged TOAST-stored values from the before-image so the after-image your sink receives is a complete row.

The initial backfill is separate from all of this. It is keyset- or CTID-paginated, split into non-overlapping partitions that run in parallel, and resumable — a partition that fails is re-dispatched without restarting the table. The source log position is captured and persisted before any partition is scanned, so the CDC phase starts from that position when the last partition finishes and the overlap is absorbed by the sink's idempotency.

Before you connect

PostgreSQL does not expose a logical change stream by default. Do this on the server first, because a connection can test green and still be unable to stream.

Set wal_level = logical. This is a restart, not a reload — plan for it.
Leave room in max_replication_slots and max_wal_senders. Data Sync opens one slot and one WAL sender per sync, on top of whatever your own replicas already use.
Create a role with the REPLICATION attribute that can also SELECT from the tables you want to replicate — the snapshot phase reads them as ordinary queries.
Decide who owns the publication. If you let Data Sync create it, the role also needs the right to CREATE PUBLICATION. If you pre-create one, it does not.
Consider REPLICA IDENTITY on every table you replicate. See the warning below — this is the one setting whose default quietly changes what your sink receives.

# postgresql.conf — requires a restart wal_level = logical max_replication_slots = … # one free slot per sync max_wal_senders = … # one free sender per sync

-- a role Data Sync can replicate with CREATE ROLE pulse WITH LOGIN REPLICATION PASSWORD '…'; GRANT CONNECT ON DATABASE app TO pulse; GRANT USAGE ON SCHEMA public TO pulse; GRANT SELECT ON ALL TABLES IN SCHEMA public TO pulse; -- optional: pre-create the publication yourself to restrict it CREATE PUBLICATION pulse_pub_orders FOR TABLE orders, order_items;

A pre-existing publication is left untouched. Data Sync creates one only when the configured name does not resolve to anything. That is the supported way to narrow what leaves your database: create the publication yourself with exactly the tables you are willing to expose, point the sync at it, and Data Sync will use it as-is rather than widening it.

REPLICA IDENTITY decides what your before-images contain. With the default (DEFAULT), the before-image on an UPDATE or DELETE carries the primary-key columns and nothing else. That is enough to key a delete at the sink, and not enough for anything that needs the old row — auditing, change-detection on non-key columns, or a sink whose key differs from the source key. Run ALTER TABLE <t> REPLICA IDENTITY FULL; on the tables where you need a complete before-image. It increases WAL volume, so apply it deliberately rather than everywhere.

Connection fields

Set once on the Connection, in step 2 of the add-a-connection wizard, and reused by every sync that references it.

Field		Default	Description
`host`	Required	—	Hostname or address of the PostgreSQL server.
`port`	Required	`5432`	TCP port.
`database`	Required	—	Database to connect to. Logical decoding is per-database, so one connection covers one database.
`username`	Required	—	The role Data Sync authenticates as. Needs the `REPLICATION` attribute.
`password`	Required	—	Supplied in step 3 as a credential reference. The Connection stores a pointer to the secret, never the value.
`sslmode`	Optional	`require`	One of `disable`, `require`, `verify-ca`, `verify-full`. Use `verify-full` when you have a CA to validate against.
`publication`	Optional	derived	Advanced. The publication to stream from. Name one you created yourself to limit what Data Sync can see; leave it empty and Data Sync derives a name and creates it. See naming.
`wal_heartbeat_enabled`	Optional	`true`	Advanced. Keeps the replication connection active on a quiet database.
`reconnect_budget`	Optional	`15m`	Advanced. How long Data Sync keeps trying to re-establish a dropped connection before the run fails.
`wal_sender_timeout`	Optional	`120s`	Advanced. How long the WAL sender waits before considering the connection dead.

The password is not entered on the configure step. It is attached in step 3 as a reference to a secret, and step 4 tests the whole connection before it is saved.

Sync fields

Set per sync, so two syncs against the same Connection can be tuned independently.

Field		Default	Description
`snapshot_workers`	Optional	`4`	How many snapshot partitions run concurrently.
`chunk_size`	Optional	`10000`	Rows read per page during the snapshot.
`partition_strategy`	Optional	`ctid`	One of `ctid`, `ntile`, `minmax`. See snapshot parallelism.
`partition_column`	Optional	—	Required only when `partition_strategy` is `ntile`; ignored otherwise.
`failover_slots`	Optional	`false`	Uses failover-enabled replication slots, so the slot survives a promotion. PostgreSQL 17 and above.
`replication_slot`	Optional	derived	Advanced, not surfaced in the wizard. See naming.
`publication`	Optional	derived	Set on the connection, under Advanced settings. See naming.
`max_reconnect_attempts`	Optional	`5`	Advanced. Reconnect attempts before the run fails.
`slot_lag_alert_mb`	Optional	`5000`	Advanced. Warns when retained WAL behind the slot exceeds this many MB. `0` disables the check.

slot_lag_alert_mb is worth leaving on. A slot that stops advancing — because a sync is stopped and forgotten, say — makes PostgreSQL retain WAL indefinitely, and the failure mode is a full disk on your primary. The alert is what tells you before that happens.

Slot and publication naming

If you do not set them, both names are derived from the sync: pulse_slot_<sync> for the replication slot and pulse_pub_<sync> for the publication. The sync name is sanitised into a legal identifier and the result is truncated to fit PostgreSQL's 63-character identifier limit.

Two consequences follow from that truncation. First, two syncs whose names differ only past the truncation point would derive the same slot name — set replication_slot explicitly if your naming convention has long shared prefixes. Second, the slot outlives the connection: if you delete a sync, check that its slot is gone, because an orphaned slot keeps retaining WAL.

Type mapping

Source columns are mapped into Arrow, the common representation every sink reads from.

PostgreSQL type	Mapped to
`int2`	Int16
`int4`	Int32
`int8`	Int64
`float4`	Float32
`float8`	Float64
`numeric(p,s)`	Decimal128(p, s)
`numeric` (unconstrained)	String
`text`, `varchar`, `bpchar`	String
`bytea`	Binary
`bool`	Boolean
`timestamptz`	Timestamp (µs)
`timestamp`	Timestamp (µs), no zone
`date`	Date32
`uuid`	String
`json`, `jsonb`	large String
anything else	String (text fallback)

Anything not in that table falls back to text. Enums, arrays, ranges, geometric types, inet, extension types such as PostGIS geometries — they arrive as strings rather than being skipped. The rule exists so that no column is ever silently dropped because Data Sync did not recognise its type. If a column turns up as text at the sink and you expected something narrower, this is why.

Note the unconstrained numeric row in particular. A numeric declared without precision and scale has no fixed decimal representation to map onto, so it is carried as a string. Declare numeric(p,s) on the source if you want a real decimal at the sink.

Snapshot parallelism

The backfill is planned as partitions and dispatched one partition per task, so parallelism is horizontal across runners rather than threads inside one process. partition_strategy chooses how a table is cut up.

Strategy	How it splits	When to use it
`ctid`	Physical page ranges — partitions look like `ctid:<start>-<end>`.	The default. Works on any table, including one with no usable key, and does not depend on key distribution.
`minmax`	Primary-key ranges from the key's minimum and maximum — partitions look like `pk:<lo>-<hi>`.	Tables with a numeric, evenly distributed primary key.
`ntile`	Equal buckets over the column named in `partition_column`.	When you know a column that divides the table evenly and the primary key does not.

Below PostgreSQL 14, parallelism is clamped to one worker. CTID range predicates need a TID range scan to be executed efficiently, and that plan node does not exist before 14. Rather than issue range predicates that degrade into sequential scans of the whole table per partition, Data Sync runs the snapshot single-threaded. snapshot_workers is accepted and ignored. Large backfills on PostgreSQL 12 or 13 will take proportionally longer.

As a sink

How it writes

Change events are batched and applied as upserts. An insert and an update take the same path — INSERT … ON CONFLICT (<pk>) DO UPDATE — because a CDC stream replayed from a checkpoint cannot know whether a given row is already present. A delete becomes a keyed DELETE.

-- inserts and updates INSERT INTO public.orders (id, customer_id, total, updated_at) VALUES (…) ON CONFLICT (id) DO UPDATE SET customer_id = EXCLUDED.customer_id, total = EXCLUDED.total, updated_at = EXCLUDED.updated_at; -- deletes DELETE FROM public.orders WHERE id = …;

Within a batch, events are compacted last-event-wins per key, so a row updated five times in one micro-batch is written once. A delete event whose image carries none of the configured key columns is an error rather than a silent no-op — if you hit that, the usual cause is a source REPLICA IDENTITY that does not include the columns you chose as the sink key.

Connection and sync fields

The connection fields are the same shape as the source side, minus the replication-specific ones. The role here needs ordinary write access, not REPLICATION.

Connection field		Default	Description
`host`	Required	—	Hostname or address of the target server.
`port`	Optional	`5432`	TCP port.
`database`	Required	—	Target database.
`username`	Required	—	Role Data Sync writes as.
`password`	Required	—	Held as a credential reference, not a value.
`sslmode`	Optional	`require`	Same four values as on the source side.

Sync field		Default	Description
`evolve_schema`	Optional	`false`	Allows the sink to add columns to an existing target. Off by default. See schema evolution.
`schema`	Optional	`public`	Schema the target tables live in.
`write_mode`	Optional	`upsert`	`upsert` writes `ON CONFLICT DO UPDATE`. `ignore` writes `ON CONFLICT DO NOTHING`, which keeps the first version of a row and discards every later one.
`table_prefix`	Optional	—	Prepended to every target table name.
per table	Optional	—	Target name, key columns and excluded columns, set in step 4 of the sync wizard. See Tables & columns.

Two things to know before you point a sync at this sink.
1. It never creates tables. Targets are treated as user-owned, so the table must already exist with the columns you are replicating into it. There is no auto-create path for this connector — if the target is missing, the write fails rather than guessing at a schema for you.
2. Without a primary key, updates are silently dropped. If no key columns are configured for a table, the write degrades to INSERT … ON CONFLICT DO NOTHING so that a replay does not hard-fail on a duplicate. The consequence is that every update to an already-present row is discarded, and the sink drifts from the source with nothing in the logs marking the divergence. Configure key columns for every table you care about.

Schema evolution

Evolution is additive only, and only when evolve_schema is on. When a new column appears in the stream and the target does not have it, Data Sync issues:

ALTER TABLE public.orders ADD COLUMN IF NOT EXISTS discount_code TEXT;

That is the entire DDL surface of this sink. A column dropped at the source is not dropped at the target; a column retyped at the source is not retyped at the target. Neither is turned into destructive DDL under any setting, because the sink does not own your schema and a replication tool guessing at DROP COLUMN is not a failure mode worth having. Structural changes that cannot be expressed additively route through the schema-change machinery instead — see Schema changes.

With evolve_schema off, a new column simply is not written; the rest of the row still lands.

Column types for an added column are derived from the Arrow type in the stream:

Arrow / source type	Column type
`bool`	`BOOLEAN`
`int2`	`SMALLINT`
`int4`	`INTEGER`
`int8`	`BIGINT`
`float4`	`REAL`
`float8`	`DOUBLE PRECISION`
`numeric`	`NUMERIC`
`timestamp`	`TIMESTAMP`
`timestamptz`	`TIMESTAMPTZ`
`date`	`DATE`
`uuid`	`UUID`
`jsonb`	`JSONB`
`json`	`JSON`
`bytea`	`BYTEA`
anything else	`TEXT`

Delivery guarantee

The PostgreSQL sink is in the upsert idempotency class: given a stable primary key, replaying a range of changes converges on the same rows no matter how long the outage lasted or how far back the replay starts. That is the strongest class Data Sync offers, and it is what makes the checkpoint-and-replay model add up to exactly-once end to end.

The qualifier is the one from the warning above. Without a key the sink degrades to append semantics, and the guarantee degrades with it — inserts may duplicate and updates are lost. The class you get is a property of your table configuration, not of the connector.

How this composes with the checkpoint, and how the other sinks compare, is in Delivery guarantees.