Skip to main content

Postgres

info

Destination Documentation: PostgreSQL Documentation

Postgres is a general-purpose relational database that Extract can load into directly over a standard TCP connection. The destination connector streams data into temporary tables using COPY FROM STDIN (CSV format), then applies the selected load mode (append, replace, upsert, history, or soft delete) to merge data into final tables. You can optionally enable TLS or connect through an SSH tunnel. By default, Extract writes to the public schema and can create schemas automatically unless disabled.

Prerequisites​

  1. A reachable Postgres instance (self-hosted or managed).
  2. A database user with permissions to connect and create schemas/tables in the target database.
  3. Network access from Extract to your Postgres host (allowlist as needed).

Setup Guide​

Step 1 - Create a service user for Extract​

Connect as a superuser or admin role and create a dedicated user:

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

Step 2 - Create or select a target database​

If you want a dedicated database for Extract:

CREATE DATABASE extract;
GRANT CONNECT ON DATABASE extract TO extract_user;

Or grant access to an existing database:

GRANT CONNECT ON DATABASE <existing_database> TO extract_user;

Step 3 (Optional) - Create or select a schema​

Extract writes to a schema (defaults to public). Skip this step if you will use public and allow Extract to create schemas automatically. If you plan to use a custom schema, create it and grant permissions:

CREATE SCHEMA IF NOT EXISTS extract;
GRANT USAGE, CREATE ON SCHEMA extract TO extract_user;

If you set Do not attempt to create new schemas automatically in Extract, the schema must already exist and your user must have permissions to write to it.

Step 4 (Optional) - Enable TLS​

If your Postgres instance requires TLS, set Use TLS to true in the connector settings. Extract uses sslmode=prefer and does not verify certificates, so use trusted networks or SSH tunneling for stricter security requirements.

Step 5 (Optional) - Configure an SSH tunnel​

If your database is only reachable through a bastion host, enable Use SSH Tunnel and supply the SSH host, port, and username. Extract will generate an SSH key pair; add the public key to ~/.ssh/authorized_keys on the bastion.

Step 6 - Configure the connector in Extract​

Provide the required fields:

  • Hostname - Your Postgres host or IP.
  • Port - Default 5432.
  • Username - The service user created above.
  • Password - The service user password.
  • Database - The target database.
  • Schema - Optional; defaults to public.
  • Table Name Prefix - Optional; applied to all created tables.

Save the connector and run a connection to verify the configuration.

Configuration Parameters​

Required Parameters (Account Connector)​

  • Hostname - Postgres server hostname or IP address.
  • Port - Postgres server port (default 5432).
  • Username - Database username.
  • Password - Database password.
  • Database - Target database to write into.

Optional Parameters (Account Connector)​

  • Use TLS - Enable TLS for the Postgres connection (default false).
  • Use SSH Tunnel - Connect through an SSH tunnel (default false).
  • SSH Host - Bastion host for the tunnel (required if using SSH tunnel).
  • SSH Port - Bastion SSH port (default 22).
  • SSH Username - SSH user for the tunnel.
  • SSH Public Key - Public key generated by Extract; add to ~/.ssh/authorized_keys.
  • Do not attempt to create new schemas automatically - Skip schema creation on login (default false).

Connection-Level Parameters​

  • Schema - Target schema (default public if not set).
  • Table Name Prefix - Optional prefix for all table names.
  • Schema Migration Policy - Controls how Extract handles schema changes (auto-activate streams, add fields, deprecate fields).

Connection Settings​

When creating a connection that targets Postgres, you can also configure:

  • Connection Pull Schedule - How frequently data is extracted and loaded.
  • Backfill (Days) - Historical window pulled on each run.
  • Target Schema - Override the destination schema for this connection.
  • Schema Migration Policy - How to handle upstream schema changes.

Additional Information​

How Data Loading Works​

Extract loads data into Postgres with the following flow:

  1. Extract - Data is pulled from the source connector.
  2. Create Temp Table - A temporary table is created with the stream schema.
  3. Stream via COPY - Records are streamed into Postgres using COPY FROM STDIN with CSV formatting.
  4. Apply Load Mode - Data is merged into the final table based on the load mode.
  5. Cleanup - Temporary tables are dropped automatically.

Supported Load Modes​

  • Append - Adds new rows to the destination table.
  • Replace - Truncates the destination table, then inserts new rows.
  • Upsert - Inserts new rows and updates existing rows based on the stream primary key.
  • History - Maintains historical versions using _singular_active, _singular_start, and _singular_end.
  • SoftDelete - Marks removed records with _singular_deleted while keeping history.

Upsert, History, and SoftDelete rely on a defined primary key in the stream.

Schema and Table Naming​

  • Schema defaults to public unless set at connection level.
  • Table Name Prefix is prepended to each table name (e.g., staging_orders).
  • If auto schema creation is enabled, Extract creates the schema if it does not exist.

Networking Notes​

  • Use TLS if your Postgres instance requires encrypted connections.
  • If your instance is not publicly reachable, use an SSH tunnel through a bastion host.