Skip to main content
Connector ID postgres connectors/postgres/sink (registered as postgres, beta) loads each resource into its own typed PostgreSQL table. Native column types are derived from the source schema, and primary keys and NOT NULL constraints are preserved. It supports snapshot, incremental, and CDC pipelines against the same destination tables.

​
Configuration

A schema left empty is defaulted by the server to the normalized source connection name. Open sizes a pgx pool to at least the run’s snapshot parallelism and runs CREATE SCHEMA IF NOT EXISTS for the destination schema.

​
Supported write modes

The sink advertises full_replace, full_append, full_upsert, incremental_upsert, cdc_append, and cdc_merge. See replication modes for how the planner picks one. They bind to three write behaviors:
  • full_replace replaces the destination; full_append inserts without removing existing rows.
  • full_upsert and incremental_upsert insert with ON CONFLICT (pk) DO UPDATE (DO NOTHING when the row is PK-only), so re-runs and retries are idempotent.
  • cdc_append retains every change as a history row. cdc_merge maintains current state: inserts and updates upsert, and deletes delete.

​
Table management

With schema_evolution disabled, EnsureSchema runs per resource before extraction.
  1. CREATE TABLE IF NOT EXISTS with columns from the source schema, a primary key from the source’s key fields, and NOT NULL from field nullability.
  2. TRUNCATE — only for replace. Append preserves existing rows; upsert and merge preserve resumable state.
  3. ADD COLUMN IF NOT EXISTS for each column, so new source fields appear on existing tables. Evolution is add-only. Type changes, dropped columns, and nullability changes are not applied. An incompatible type change fails later when a write cannot cast the value.

​
Schema drift rules

Enable strict or nullable_add to compare the complete requested schema with the actual destination table before writing. Both policies create missing tables. For existing tables, strict blocks differences and nullable_add permits only new nullable columns. Type, nullability, primary key, and removed field changes block with schema_blocked. Renames are treated as a removal and an addition. Column order may differ because writes name columns explicitly. The sink checks the entire diff before making changes. Permitted additions, verification, and any replace-mode truncation share one PostgreSQL transaction. A rejected diff leaves that table and its rows unchanged. Reconciliation is per table, so earlier tables in a multi-resource run may already have been prepared. A retry inspects the actual table again and skips additions already committed. Each bounded write transaction and continuous epoch rechecks the prepared schema under a table lock held through commit or rollback. A change made after startup therefore blocks before the affected write. These checks add catalog queries and can wait for concurrent DDL. Sources with complete schemas reconcile before extraction. NATS instead probes each buffered epoch before encoding rows. With nullable_add, newly observed payload fields become nullable columns. Missing fields remain in the accepted layout and produce nulls. With strict, the initial batch establishes the layout and subsequent additions block. For NATS, additions and that epoch’s data writes share the same destination transaction. Accepted decoder layouts are saved with the epoch checkpoint before acknowledgement. Restarts restore those layouts instead of inferring them again. Older streams without saved layouts require a new stream generation to enable this feature. Other message sources retain their existing inference behavior. This does not make destination commits and checkpoint commits atomic together. The existing recovery rules for uncertain destination commits still apply. Checked schemas currently support the portable scalar mappings below, including decimal precision and scale. Unsupported native modifiers, arrays, custom types, identity or generated columns, custom collations, partitioning, inheritance, row security, and user triggers block. These restrictions apply only when a schema policy is enabled. A continuous schema block suppresses automatic retries using the persisted attempt reason. After resolving the issue, pause and resume to request another assessment. Existing ownership and clean-shutdown checks still apply. Bounded runs report the schema error through their normal failure path.

​
Type mapping

​
Write mechanics and atomicity

Rows are encoded into PostgreSQL’s binary COPY format directly from the Arrow columns. Replace and append batches copy into the destination table. Upserts copy into a per-connection temporary table and merge into the destination with ON CONFLICT, inside one transaction. For bounded runs, CDC merge batches are split into consecutive delete and non-delete ranges and applied in arrival order inside one transaction per batch. Each range is copied to a temporary row or key table before its upsert or delete statement. A merge against a keyless resource is an error. Before sending a COPY payload, the sink verifies a CRC embedded in its encoded form. It returns that checksum as EncodedCRC alongside the Arrow batch checksum, so the engine records both the in-memory handoff and PostgreSQL serialization boundaries. This is not a query that reads committed rows back from the table. Bounded runs commit batches as they are applied, so partial data can be visible mid-run. Commit confirms those durable writes and Close releases the pool. There is no end-of-run atomic swap. Continuous planning prefers the sink’s commit-durable append, upsert, and merge policies. One Begin opens the write transaction, all batches use that transaction, and Commit makes them durable together. The pool stays open for the next boundary. The runner then saves the epoch certificate before acknowledging the source. Existing saved policies retain their declared durability when the sink still supports that exact policy.

​
Failure behavior

A failed transactional write rolls back its pending rows and transactional schema changes. Earlier committed boundaries remain visible. A confirmed rollback can establish clean shutdown. An uncertain commit response or failed rollback can block automatic restart. For bounded writes, Abort clears only non-resumable replace tables. Append, upsert, and merge tables retain durable rows. Cleanup attempts every eligible table and reports all failures. Delivery remains at least once. A crash after destination commit but before certificate persistence can cause replay. Append can duplicate rows in that case. Upsert and merge use primary keys to converge when replayed.