PostgreSQL

Available v4.12.0 Released October 31, 2026

Overview  

PostgreSQL is supported as both a source (extract from tables and views) and a target (load and historize into a PostgreSQL warehouse or database). It works with self-hosted PostgreSQL and managed services that expose a standard PostgreSQL endpoint. PostgreSQL connects to Amfelt’s data plane through Apache Arrow: the connector hands records to the agent as Arrow record batches.

Support for pgvector vector properties is on the roadmap.

Capabilities  

Prerequisites  

Before connecting PostgreSQL:

• Network access from the Amfelt agent to the PostgreSQL host and port (default 5432).
• A database and schema to read from (source) or write to (target).
• A login role with the privileges in the Required privileges section below.
• Supported versions: PostgreSQL 12 and later as a source; PostgreSQL 15 and later as a target. The target’s history tables use a unique index with NULLS NOT DISTINCT, which PostgreSQL added in version 15, so the target’s connection test refuses an older server.

Required Privileges  

Create a dedicated, least-privilege login role and grant it only what it needs: the source grants for extraction, the target grants for loading and historization. Store the password in your key vault; it is never part of the connection descriptor.

Source (Read-Only)  

-- PostgreSQL source: least-privilege, read-only.
-- Replace orders / public / amfelt_src to match your environment; store the
-- password in the Amfelt key vault (never inline in a deployed grant).
CREATE ROLE amfelt_src LOGIN PASSWORD '***';
GRANT CONNECT ON DATABASE orders TO amfelt_src;
GRANT USAGE ON SCHEMA public TO amfelt_src;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO amfelt_src;
-- Future tables created in the schema are readable automatically:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO amfelt_src;

Target (Read-Write + DDL)  

-- PostgreSQL target: read-write plus the DDL rights to create the raw/staging/
-- integration/bridge layer objects Amfelt generates. Store the password in the
-- Amfelt key vault.
CREATE ROLE amfelt_tgt LOGIN PASSWORD '***';
GRANT CONNECT ON DATABASE warehouse TO amfelt_tgt;
-- Create and own the warehouse layer schemas, and the state schemas <prefix>_sys and
-- <prefix>_qrn, on which Amfelt revokes every PUBLIC right:
GRANT CREATE ON DATABASE warehouse TO amfelt_tgt;
-- Temporary tables: delta and window loads compute their compare once per apply in one.
-- PUBLIC holds it by default; the grant keeps it where PUBLIC has lost it:
GRANT TEMPORARY ON DATABASE warehouse TO amfelt_tgt;
GRANT USAGE, CREATE ON SCHEMA public TO amfelt_tgt;
-- Read-write on the objects Amfelt manages:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO amfelt_tgt;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO amfelt_tgt;
-- Give a consumer role USAGE on the bridge schema <prefix>_bdg only, never on <prefix>_sys
-- or <prefix>_qrn. A load's bridge apply recreates the bridge views without their grants,
-- so the default privilege grants SELECT on each view amfelt_tgt creates there. After the
-- first deploy, run as amfelt_tgt, with the prefix in place of sales and the consumer role
-- in place of reporting:
-- GRANT USAGE ON SCHEMA sales_bdg TO reporting;
-- GRANT SELECT ON ALL TABLES IN SCHEMA sales_bdg TO reporting;
-- ALTER DEFAULT PRIVILEGES FOR ROLE amfelt_tgt IN SCHEMA sales_bdg GRANT SELECT ON TABLES TO reporting;

Connect  

1. In Amfelt, add a PostgreSQL source or target and select the PostgreSQL connector.
2. Provide the connection properties (see Configuration reference): host, port, database, and schema.
3. Store the login credentials in the configured key vault — they are never part of the connection descriptor.
4. Validate the connection and choose Discover schema.

A connection resolves to an ODCS server descriptor like:

servers:
  - server: postgres-prod
    type: postgres
    host: db.example.com
    port: 5432
    database: orders
    schema: public

Incremental Extracts  

The source performs full extracts and incremental extracts. An incremental extract reads only the rows whose cursor column is at or after the lower bound of the run. The cursor is a timestamp (a timestamp or timestamptz column) or a sequence (a smallint, integer or bigint column); a cursor on a column of any other type fails the run instead of reading the object in full. A timestamp cursor (without time zone) is compared as a UTC wall clock, whatever the session’s TimeZone defaults to. An index whose leading column is the cursor column lets PostgreSQL read a small increment without scanning the table.

To keep a transaction that is still open from hiding rows below the next bound, the source caps its clock at the start of the oldest open transaction of other sessions. PostgreSQL shows those sessions only to a superuser or a member of pg_read_all_stats; grant that role to the source user to get the cap. Without it the source still extracts correctly, but the clock is not capped.

Authentication  

Credentials are supplied through the Amfelt key vault, not the ODCS connection topology: a database username and password, and an optional sslmode (disable, require, verify-full). The connection descriptor above intentionally contains no credential fields.

The password must not contain whitespace, a single quote ('), or a backslash (\): the connection string cannot carry those characters yet, so the connector refuses such a password before it connects.

State Schemas  

A target deploy that creates the history layer also creates the state schema <prefix>_sys. It holds what each history apply commits with its rows: the watermark of each object and the ledger of the files loaded. The first quarantined file of an object creates <prefix>_qrn, when it does not exist yet, and the object’s quarantine table in it, which holds the quarantined rows until they are purged. The target role creates both schemas, so it owns them, and Amfelt revokes every right PUBLIC holds on them. No bridge view reads either schema.

Give a consumer role USAGE on the bridge schema <prefix>_bdg only, never on <prefix>_sys or <prefix>_qrn. A load’s bridge apply drops and recreates the bridge views, and a recreated view has none of the old view’s grants, so grant SELECT on the views there now and, as a default privilege of the target role, on each view it creates there later. Run these as the target role after the first deploy, with the prefix in place of sales, the consumer role in place of reporting and the target role in place of amfelt_tgt:

GRANT USAGE ON SCHEMA sales_bdg TO reporting;
GRANT SELECT ON ALL TABLES IN SCHEMA sales_bdg TO reporting;
ALTER DEFAULT PRIVILEGES FOR ROLE amfelt_tgt IN SCHEMA sales_bdg GRANT SELECT ON TABLES TO reporting;

Sync Modes  

Target History Modes  

Configuration Reference  

Type Mapping  

Each PostgreSQL physical type maps to an Apache Arrow type (the data-plane intermediate) and then to an ODCS logical type. See the official PostgreSQL data type reference  .

Troubleshooting  

Changelog  

Release history for the PostgreSQL connector, newest first.

Licensing  

Third-party open-source packages bundled by this connector, with their licenses. Attribution only: informational, not an evaluation of the connector.