Advanced Analytica Advanced Analytica: IBOM
← All connectors
Postgres logo

Destination connector

Postgres

Send governed data into Postgres with a clear, practical setup guide.

Version 3.0.24 certified generally available

Postgres

Set up the Postgres destination connector using this guide.

:::info Direct Load

From version 3.0.0, the Postgres destination uses Direct Load architecture. Data goes directly into final tables without intermediate raw tables. This improves performance and reduces storage costs.

For migration details and backward compatibility options, see Postgres Migration Guide.

:::

Important limitation

:::warning

Postgres is an excellent relational database, but it isn’t a data warehouse. Use Postgres as a destination for small data volumes, such as less than 10GB, or for testing. For larger volumes, use a data warehouse such as BigQuery, Snowflake, or Redshift. Learn more here.

:::

Prerequisites

To use the Postgres destination, you’ll need:

  • A Postgres server running version 9.5 or later

Airbyte Cloud only supports connections to your Postgres instances using SSL or TLS encryption. TLS is used by default. Otherwise, follow the open-source instructions below.

You’ll also need this information to configure the Postgres destination:

  • Host - The server’s host name.
  • Port - The server’s listening port. It defaults to the PostgreSQL™ standard port number (5432).
  • Username
  • Password
  • Default Schema Name - Specify one schema, or several separated by commas, for the search-path. These schemas resolve unqualified object names in statements executed over this connection.
  • Database - The database name. By default, the connector uses a database matching the user name.
  • JDBC URL Params (optional)

Refer to this guide for more details

Configure network access

Make sure Airbyte can access your Postgres database. For a database inside a VPC, allow access from the IP exposing Airbyte. If you’re using Airbyte Cloud, add Airbyte’s IP addresses to your allowlist.

Step 1: Set up Postgres

Permissions

Create or choose a Postgres user with these permissions:

  • Can create tables and write rows.
  • Can create schemas, for example:

Run the following command to create this user:

CREATE USER airbyte_user WITH PASSWORD '<password>';
GRANT CREATE, TEMPORARY ON DATABASE <database> TO airbyte_user;

You can use an existing user, but a dedicated Airbyte user is strongly recommended.

Step 2: Set up the Postgres connector in Airbyte

Choose the target database

Choose an existing database or create one to store data synced from Airbyte.

Naming conventions

According to Postgres SQL Identifiers syntax:

  • SQL identifiers and key words must start with a letter or an underscore (_). Letters can include diacritical marks and non-Latin characters.

  • Later characters can be letters, underscores, digits (0-9), or dollar signs ($).

    The SQL standard doesn’t allow dollar signs in identifiers, so they can make applications less portable. The standard won’t define key words containing digits or starting or ending with an underscore. These identifiers are therefore safe from conflicts with future standard extensions.

  • The system uses no more than NAMEDATALEN-1 bytes of an identifier. You can write longer names in commands, but Postgres truncates them. By default, NAMEDATALEN is 64, making the maximum identifier length 63 bytes.

  • Quoted identifiers can contain any character except code zero. (To include a double quote, write two double quotes.) This supports otherwise invalid table or column names, including names with spaces or ampersands. The length limit still applies.

  • Quoting an identifier makes it case-sensitive. Unquoted names are always converted to lower case.

  • Keep quoting consistent for each name to make applications more portable and less error-prone. Either always quote a name or never quote it.

:::info

The Airbyte Postgres destination uses Quoted identifiers for final tables and columns, preserving case sensitivity. It replaces special characters in table and column names with underscores.

In the legacy “Raw tables only” mode, raw tables and schemas use Unquoted identifiers. Any special characters are replaced with an underscore.

:::

For Airbyte Cloud:

  1. Log into your Airbyte Cloud account.

  2. In the left navigation bar, click Destinations. In the top-right corner, click new destination.

  3. On the Set up the destination page, name the Postgres connector. Select Postgres from the Destination type dropdown.

  4. Enter a name for your destination.

  5. For Host, Port, and DB Name, enter your Postgres database’s host name, port number, and name.

  6. List the Default Schemas.

    :::note

    Schema names are case-sensitive. The ‘public’ schema is selected by default. You can use multiple schemas at once. If you don’t specify any schemas, all existing schemas will sync.

    :::

  7. For User and Password, enter the username and password you created in Step 1.

  8. For Airbyte Open Source, turn on the switch to connect using SSL. Airbyte Cloud uses SSL by default.

  9. For SSL Modes, select:

    • disable to disable encrypted communication between Airbyte and the destination
    • allow to enable encrypted communication only when the destination requires it
    • prefer to allow unencrypted communication only when the destination doesn’t support encryption
    • require to always require encryption. The connection fails if the destination doesn’t support encryption.
    • verify-ca to always require encryption and verify that the destination has a valid SSL certificate
    • verify-full to always require encryption and verify the destination’s identity
  10. To customise the JDBC connection beyond common options, specify additional supported JDBC URL parameters in the JDBC URL Parameters (Advanced) field. Enter key-value pairs separated by the & symbol.

    Example: key1=value1&key2=value2&key3=value3

    Airbyte adds these parameters to the end of the JDBC URL used to connect to your Postgres database.

    The connector supports connectTimeout and defaults to 60 seconds. Set connectTimeout to 0 seconds to use the longest available timeout.

    Note: Don’t use these keys in the JDBC URL Params field because Airbyte overwrites them: currentSchema, user, password, ssl, and sslmode.

:::warning

This is an advanced configuration option. Use it carefully.

:::

  1. For SSH Tunnel Method, select:

    • No Tunnel to connect directly to the database
    • SSH Key Authentication to use an RSA Private as your secret for establishing the SSH tunnel
    • Password Authentication to use a password as your secret for establishing the SSH tunnel

:::warning

Airbyte Cloud requires encrypted communication. Select SSH Key Authentication or Password Authentication if your SSL Mode is disable, allow, or prefer. Otherwise, the connection will fail.

:::

  1. Click Set up destination.

Supported sync modes

The Postgres destination connector supports these sync modes:

Sync modeSupported?
Full Refresh - OverwriteYes
Full Refresh - AppendYes
Full Refresh - Overwrite + DedupedYes
Incremental Sync - AppendYes
Incremental Sync - Append + DedupedYes

Schema map

Direct Load output schema

The Postgres destination uses Direct Load architecture. Each stream goes directly into a final table in your configured schema. The table contains your data columns and these Airbyte metadata columns:

  • _airbyte_raw_id: A uuid that Airbyte assigns to each processed event. Its Postgres column type is VARCHAR.
  • _airbyte_extracted_at: A timestamp showing when the event was pulled from the data source. Its Postgres column type is TIMESTAMP WITH TIME ZONE.
  • _airbyte_meta: A jsonb column containing record metadata, including sync information and schema changes. Its Postgres column type is JSONB.
  • _airbyte_generation_id: An identifier for the sync generation. Its Postgres column type is BIGINT.

Raw tables output schema: deprecated

:::warning Deprecated

Raw tables are deprecated from version 3.0.0. The connector now uses Direct Load to write directly to final tables. For backward compatibility options and migration guidance, see Postgres Migration Guide.

:::

Legacy Raw Tables Documentation

Each stream maps to a separate raw table in Postgres. By default, raw tables are created in the airbyte_internal schema. You can override this in the configuration. Each table contains four columns:

  • _airbyte_raw_id: A uuid that Airbyte assigns to each processed event. Its Postgres column type is VARCHAR.
  • _airbyte_extracted_at: A timestamp showing when the event was pulled from the data source. Its Postgres column type is TIMESTAMP WITH TIME ZONE.
  • _airbyte_loaded_at: A timestamp showing when the row was processed into the final table. Its Postgres column type is TIMESTAMP WITH TIME ZONE.
  • _airbyte_data: A json blob containing the event data. Its Postgres column type is JSONB.

Final table data type mapping

Airbyte TypePostgres Type
stringVARCHAR
numberDECIMAL
integerBIGINT
booleanBOOLEAN
objectJSONB
arrayJSONB
timestamp_with_timezoneTIMESTAMP WITH TIME ZONE
timestamp_without_timezoneTIMESTAMP
time_with_timezoneTIME WITH TIME ZONE
time_without_timezoneTIME
dateDATE

Naming limits

Postgres limits all identifiers to 63 characters. It truncates longer column names in your stream to this length. If two columns then share a name, Airbyte may change them to prevent a collision.

Create dependent objects

:::caution

This section involves running DROP ... CASCADE on tables that Airbyte produces. Understand the consequences before enabling this option. This option can cause Permanent data loss.

:::

You may want to create objects that depend on Airbyte-generated tables, such as views. If you do, follow these recommendations:

  • Use a tool such as dbt to automate creation.
  • Use an orchestrator to trigger dbt.

You must enable the “Drop tables with CASCADE” option. The connector sometimes needs to recreate tables. If dependent objects exist, Postgres requires the connector to run drop statements with CASCADE enabled. However, this causes the connector to also drop the dependent objects. You MUST be able to recreate those dependent objects from scratch.

Tutorials

After setting up the Postgres destination connector, explore these tutorials:

Vendor-specific connector limits

For vendor-specific limitations and known issues, see Postgres Troubleshooting Guide.

Namespace support

This destination supports namespaces. The namespace maps to a Postgres schema.

Changelog

Expand to review
VersionDatePull RequestSubject
3.0.242026-10-2289654Upgrade to Bulk CDK 1.1.9: fix critical CVEs in Apache MINA (mina-core 2.0.31), MINA SSHD 2.20.0 and BouncyCastle 1.86; netty 4.1.139; PostgreSQL JDBC driver 42.7.14.
3.0.232026-10-0187617Upgrade to Bulk CDK 1.1.1.
3.0.222026-09-2386926Strip the leading + from extended-year date/timestamp values so PostgreSQL COPY accepts them
3.0.212026-09-1586331Version bump to republish the Cloud-specific connector spec.
3.0.202026-09-1185835Upgrade to Bulk CDK 1.1.0 and replace the startup COUNT(*) with an existence check, so syncs no longer scan large tables before replicating.
3.0.192026-09-0984953Add missing Airbyte meta columns (_airbyte_meta, _airbyte_generation_id) to tables created by pre-direct-load connector versions, and read a missing _airbyte_generation_id column as generation 0, when the AIRBYTE_DESTINATION_POSTGRES_META_COLUMN_REPAIR env var is set to true (off by default).
3.0.182026-09-0184989Upgrade to Bulk CDK 1.0.25.
3.0.172026-08-1884853Strip null bytes nested inside objects and arrays, which PostgreSQL rejects in jsonb columns
3.0.162026-03-3175902Fix silent error swallowing in COPY flush and sanitize null bytes in raw JSON data
3.0.152026-08-0783235Fail sync on transient DB errors.
3.0.142026-07-3082273Remove column DROP logic during schema evolution; upgrade CDK to 1.0.20
3.0.132026-04-1776409Upgrade CDK to 1.0.9.
3.0.122026-03-2675481Upgrade CDK to 1.0.6; fix duplicate records in dedup+truncate mode by dropping temp tables after successful upsert.
3.0.112026-02-2574040Upgrade CDK to 1.0.2 and base image to 2.0.4 for CVE patches.
3.0.102026-02-0972858Update to latest CDK with DestStream changes; remove unused Java CDK dependencies.
3.0.92026-01-2872292Upgrade CDK to 0.2.0
3.0.82026-01-2872412Promoting release candidate 3.0.8-rc1 to a main version.
3.0.72026-01-2071745Improve error message for dependent views/rules with CASCADE option guidance.
3.0.62026-01-0671146Fix: Only apply CASCADE to DROP COLUMN, not ALTER COLUMN TYPE during schema changes.
3.0.52025-12-1270895Update CDK to 0.1.86.
3.0.42025-12-0570364Fix: Force Append mode when “Raw tables only” mode is enabled, bypassing Dedupe mode to avoid errors.
3.0.32025-12-0470347Fix index recreation on non-existent columns in raw tables mode.
3.0.22025-12-0470337Refactor: Move raw tables mode check to index creation for better code clarity.
3.0.12025-12-0470326Fix PSQLException when running in legacy “Raw tables only” mode.
3.0.02025-12-0369846Breaking Change: Introduces Direct Load architecture. Data is now written directly to final tables without using intermediate raw tables, providing improved performance and reduced storage costs. Raw tables are deprecated; use “Raw tables only” option if needed for backward compatibility.
2.4.72025-08-2965620Noop release.
2.4.62025-08-2663769Fix numeric columns synced as NULL value in incremential sync.
2.4.52025-04-2458652Upgrade libraries for security patches
2.4.42025-03-2456355Upgrade to airbyte/java-connector-base:2.0.1 to be M4 compatible.
2.4.32025-02-1453686Add option to use unconstrained DECIMAL type for numeric columns.
2.4.22025-01-1051483Use a non root base image
2.4.12024-12-1849885Use a base image: airbyte/java-connector-base:1.0.0
2.4.02024-08-18#45434upgrade all dependencies.
2.3.22024-08-07#43331bump java CDK.
2.3.12024-08-07#43363Adopt latest CDK.
2.3.02024-07-22#41954Support for refreshes and resumable full refresh. WARNING: You must upgrade to platform 0.63.7 before upgrading to this connector version.
2.2.12024-07-22#42423no-op. Bumping to a clean image
2.2.02024-07-22#42423Revert refreshes support
2.1.12024-07-22#42415fixing PostgresSqlOperations.isOtherGenerationIdInTable to close the streams coming from JdbcDatabase.unsafeQuery
2.1.02024-07-22#41954Support for refreshes and resumable full refresh. WARNING: You must upgrade to platform 0.63.7 before upgrading to this connector version.
2.0.152024-06-26#40554Convert all strict-encrypt prod code to kotlin.
2.0.142024-06-26#40563Convert all test code to kotlin.
2.0.132024-06-13#40159Config error on drop failure when cascade is disabled
2.0.122024-06-12#39388Sources auto-conversion to Kotlin
2.0.112024-06-10#39372Fixed function already exists error
2.0.102024-05-07#37660Adopt CDK 0.33.2
2.0.92024-04-11#36974Add option to drop with CASCADE
2.0.82024-04-10#36805Adopt CDK 0.29.10 to improve long column name handling
2.0.72024-04-08#36768Adopt CDK 0.29.7 to improve destination state handling
2.0.62024-04-05#36620Adopt CDK 0.29.3 to use Kotlin CDK
2.0.52024-03-07#35899Adopt CDK 0.27.3; Bugfix for case-senstive table names in v1-v2 migration, _airbyte_meta in raw tables
2.0.42024-03-07#35899Adopt CDK 0.23.18; Null safety check in state parsing
2.0.32024-03-01#35528Adopt CDK 0.23.11; Use Migration framework
2.0.22024-03-01#35760Mark as certified, add PSQL exception to deinterpolator
2.0.12024-02-22#35385Upgrade CDK to 0.23.0; Gathering required initial state upfront
2.0.02024-02-09#35042GA release V2 destinations format.
0.6.32024-02-06#34891Remove varchar limit, use system defaults
0.6.22024-01-30#34683CDK Upgrade 0.16.3; Fix dependency mismatches in slf4j lib
0.6.12024-01-29#34630CDK Upgrade; Use lowercase raw table in T+D queries.
0.6.02024-01-19#34372Add dv2 flag in spec
0.5.52024-01-18#34236Upgrade CDK to 0.13.1; Add indexes in raw table for query optimization
0.5.42024-01-11#34177Add code for DV2 beta (no user-visible changes)
0.5.32024-01-10#34135Use published CDK missed in previous release
0.5.22024-01-08#33875Update CDK to get Tunnel heartbeats feature
0.5.12024-01-04#33873Install normalization to enable DV2 beta
0.5.02023-12-18#33507Upgrade to latest CDK; Fix DATs and tests
0.4.02023-06-27#27781License Update: Elv2
0.3.272023-04-04#24604Support for destination checkpointing
0.3.262022-09-27#17299Improve error handling for strict-encrypt postgres destination
0.3.242022-09-08#16046Fix missing database name URL Encoding
0.3.232022-07-18#16260Prevent traffic going on an unsecured channel in strict-encryption version of destination postgres
0.3.222022-07-18#13840Added the ability to connect using different SSL modes and SSL certificates
0.3.212022-07-06#14479Publish amd64 and arm64 versions of the connector
0.3.202022-05-17#12820Improved ‘check’ operation performance
0.3.192022-04-25#12195Add support for additional JDBC URL Params input
0.3.182022-04-12#11729Bump mina-sshd from 2.7.0 to 2.8.0
0.3.172022-04-05#11729Fixed bug with dashes in schema name
0.3.152022-02-25#10421Refactor JDBC parameters handling
0.3.142022-02-14#10256(unpublished) Add -XX:+ExitOnOutOfMemoryError JVM option
0.3.132021-12-01#8371Fixed incorrect handling “\n” in ssh key
0.3.122021-11-08#7719Improve handling of wide rows by buffering records based on their byte size rather than their count
0.3.112021-09-07#5743Add SSH Tunnel support
0.3.102021-08-11#5336Destination Postgres: fix \u0000(NULL) value processing
Advanced Analytica

Most businesses are sitting on information they can’t use at speed. The people who hold it are busy, and when they leave, the knowledge goes with them. The documents are static, and the processes around them weren’t designed for AI.

Advanced Analytica works with these businesses to turn that information into structured data assets. Once they're integrated, AI systems can govern it, and the business can use it. We bring in the strategy, the specification and the AI skills to rework how the business operates.

MOVE FAST. STAY SAFE.