Postgres

Overview

Extract connects to Postgres as a source by running SQL queries you define and streaming the results as datasets. Each dataset is a named SQL query that becomes a stream in Extract. Queries can run in Full Refresh mode or Incremental Changes mode when you provide an incremental timestamp column.

Prerequisites

1. A reachable Postgres instance (self-hosted or managed).
2. A database user with permission to connect and run SELECT queries on the schemas/tables you want to extract.
3. Network access from Extract to your Postgres host (allowlist as needed).

Setup Guide

Connect as an admin and create a dedicated user:

CREATE USER extract_reader WITH PASSWORD 'replace-with-strong-password';

Step 2 - Grant SELECT access

Grant access to the schemas and tables you want Extract to query:

GRANT CONNECT ON DATABASE <database> TO extract_reader;
GRANT USAGE ON SCHEMA <schema> TO extract_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO extract_reader;
GRANT SELECT ON FUTURE TABLES IN SCHEMA <schema> TO extract_reader;

Step 3 (Optional) - Enable TLS

If your Postgres instance requires TLS, set Use TLS to true in the connector settings.

Extract connects with sslmode=prefer and does not verify server certificates. If you require certificate verification, use trusted networks and/or an SSH tunnel.

Step 4 - Configure the connector in Extract

Fill in the connection fields:

• Hostname - Your Postgres host or IP.
• Port - Default 5432.
• Username - The service user created above.
• Password - The service user password.
• Database - The database to query.

Then define at least one Dataset (see below), save, and run a test sync.

Step 5 (Supabase only) - Use the Session pooler endpoint if the direct endpoint is unreachable

If you’re connecting to Supabase and your host looks like db.<project-ref>.supabase.co, the direct database endpoint may be unreachable from some networks (it commonly requires IPv6).

In Supabase, go to Connect → Session pooler and copy the exact hostname and username into the Postgres connector settings:

• Use the Session pooler (not the Transaction pooler on port 6543).
• Use port 5432, database postgres, your database password, and TLS.
• The pooled username includes the project reference (for the default role it looks like postgres.<project-ref>).

Alternatively, enable Supabase’s IPv4 add-on or provide IPv6 connectivity.

Configuration Parameters

Required Parameters

• Hostname - Postgres server hostname or IP address.
• Port - Postgres server port (default 5432).
• Username - Database username.
• Password - Database password.
• Database - Target database to query.
• Datasets - List of SQL queries that define your streams.

Optional Parameters

• Use TLS - Enable TLS for the Postgres connection (default false).

Dataset Configuration

Each dataset is a SQL query that defines a stream. Configure the following fields:

• Dataset Name - Unique name for the stream (used as the stream title).
• SQL Query - The SELECT query to run. Extract prepares this statement to infer columns.
• Primary Key (optional) - Column name used for dedupe and diffing.
• Incremental Timestamp Field (optional) - Column name used for incremental syncs.

Query Guidelines

• Use a SELECT query that returns a stable schema.
• Avoid non-deterministic expressions unless required.
• If you enable incremental sync, the timestamp field must be a Postgres TIMESTAMP or TIMESTAMPTZ column (recommend TIMESTAMPTZ).

Extract Modes

Full Refresh

Extract runs the dataset query as-is.

If diffing is enabled and a primary key is defined, Extract orders results by the primary key and computes changes. For stable ordering across runs, Extract uses:

• Numeric primary keys: ORDER BY <primary_key> ASC
• Non-numeric primary keys: ORDER BY <primary_key>::text COLLATE "C" ASC

This ensures a consistent sort order for diffing, especially for text-like keys.

Incremental Changes

If you set Incremental Timestamp Field, Extract filters results by:

WITH query AS (<your query>)
SELECT * FROM query WHERE <timestamp_field> > '<last_cursor_value>'

On the first run (no cursor yet), Extract runs:

WITH query AS (<your query>)
SELECT * FROM query

The cursor updates to the max timestamp seen in each run.

Incremental timestamp field requirements

The Incremental Timestamp Field must:

• Exist in the dataset query output (the selected columns).
• Be typed as TIMESTAMP or TIMESTAMPTZ.

If the field is missing, or is a different type (for example DATE or TEXT), the sync will fail with an error indicating the field must be TIMESTAMP/TIMESTAMPTZ.

Data Streams

The Postgres source connector exposes one stream per selected table or view.

Streams are typed based on the underlying Postgres column types. The connector maps Postgres types to the following schema types and serialized representations:

Postgres type	Stream field type	Notes / serialization
boolean	boolean
varchar, text	string
varchar[], text[]	array\<string\>
smallint, integer, bigint	integer
real, double precision	number
numeric	string	Serialized as a string to preserve precision/scale.
json, jsonb	json
date	string (format: date)	Serialized as YYYY-MM-DD.
timestamp	string (format: date-time)	Serialized as RFC 3339 in UTC.
timestamptz	string (format: date-time)	Serialized as RFC 3339.
uuid	string	Serialized as a canonical UUID string.

If a column uses an unsupported Postgres type, the connector may fail the extract for that stream.

Additional Information

Extract maps common Postgres types to JSON-friendly types, including:

• BOOL -> boolean
• VARCHAR, TEXT -> string
• VARCHAR[], TEXT[] -> array of strings
• INT2, INT4, INT8 -> integer
• FLOAT4, FLOAT8 -> number
• NUMERIC -> string (to preserve precision)
• JSON, JSONB -> json
• DATE -> string (YYYY-MM-DD)
• TIMESTAMP, TIMESTAMPTZ -> string (RFC 3339)
• UUID -> string

Other/unknown types are treated as strings.

PostgreSQL does not support NUL (\0) characters in text values. During loads, Extract strips NUL characters from string values (including strings nested inside arrays and JSON).

If a JSON object contains a key with a NUL character, the load will fail because PostgreSQL JSON object keys cannot contain NUL characters.

Diffing Requirements

Diffing is only available when a primary key is defined on the dataset. If you want change detection, ensure the query includes a stable unique key.

Troubleshooting

PostgreSQL does not allow NUL (\0) characters in text values. If your source data contains NUL characters inside strings (including strings nested inside arrays or JSON objects), loads can fail with errors similar to:

• invalid byte sequence for encoding "UTF8": 0x00
• unsupported Unicode escape sequence

The connector strips NUL characters from string values before writing rows to Postgres (including strings nested in arrays/objects).

PostgreSQL JSON object keys cannot contain NUL characters. If a record contains a JSON object with a key that includes \0, the connector will fail the run with an error indicating that JSON object keys cannot contain NUL characters.

To resolve this, sanitize or rename keys upstream to remove NUL characters before syncing.

Supabase: database endpoint unreachable (IPv6 vs session pooler)

If you’re connecting to Supabase using the direct database hostname (for example, db.<project-ref>.supabase.co) and the run fails with a network error indicating the host is unreachable, the direct endpoint may require IPv6 connectivity from the worker network.

To resolve:

• In Supabase, go to Connect → Session pooler and copy the exact hostname and username into the Postgres connector configuration.
• Use:
  • Port: 5432
  • Database: postgres
  • Password: your database password
  • TLS: enabled
• Use the Session pooler (not the Transaction pooler on port 6543).
• Ensure you use the pooled username format (it includes the project reference, e.g. postgres.<project-ref> for the default role).

Alternatively, enable Supabase’s IPv4 add-on or provide IPv6 connectivity.