Postgres

The Postgres destination connector:

• Writes each stream straight into a table of its own in the schema you choose. There are no intermediate raw tables to clean up.
• Supports every sync mode, including deduplication.
• Maps each source namespace to a Postgres schema.

This page covers what you need, how to set it up, and reference material such as the data type mapping and limitations.

:::warning Postgres is an excellent relational database, but it isn't a data warehouse. Use it as a destination for small data volumes (roughly under 10 GB) or for testing. For larger volumes, use a data warehouse such as BigQuery, Snowflake or Redshift. See Limitations. :::

Before you start

You need:

• A Postgres server, version 9.5 or later.
• A database for the synced data. Use an existing one or create a new one.
• A Postgres user that can create schemas and tables and write rows (see Step 1).
• Network access from Pipeloom to the database. If the database is inside a VPC or behind a firewall, allow inbound connections from Pipeloom, or connect through an SSH tunnel.
• Access to the /tmp file system for the database. The connector loads data in bulk through the database's temporary storage, so loading fails without it.

Step 1: Create a Postgres user

We recommend a dedicated user for Pipeloom. You can use an existing Postgres user instead.

The user needs to be able to create schemas and tables and write rows. To create one:

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

Step 2: Create the Postgres destination in Pipeloom

Create a new destination and choose Postgres. You can do this from the Destinations page, or from a Destination step on a pipeline canvas.

Fill in the form:

1. Enter your Postgres server's host name and port (the Postgres default is 5432), and the name of the database to write to.

2. Under Default Schema, enter the schema that tables are written to when a stream doesn't set its own. It defaults to public. Schema names are case-sensitive.

3. Enter the user and password from Step 1.

4. Turn on SSL Connection and choose an SSL mode (see SSL modes). Without SSL, use an SSH tunnel so the connection is still encrypted.

5. If the database isn't reachable directly, set up an SSH tunnel.

6. Optionally, set the advanced options.

Save the destination. Pipeloom tests the connection to your database, and the destination is ready once the test passes.

Connecting with SSL or an SSH tunnel

SSL modes

• disable: never encrypt the connection
• allow: encrypt only if the database requires it
• prefer: encrypt unless the database doesn't support it
• require: always encrypt. The connection fails if the database doesn't support encryption.
• verify-ca: always encrypt, and check that the database's SSL certificate is valid. Paste the CA Certificate; the Client Key Password is optional and generated for you if you leave it empty.
• verify-full: always encrypt, and check the database's identity. Paste the CA Certificate, Client Certificate and Client Key; the Client Key Password is optional, as above.

SSH tunnel

With an SSH tunnel, Pipeloom connects to an intermediate server (a bastion or jump server) that can reach your database, and the bastion then connects to the database for it. If you chose disable, allow or prefer as the SSL mode, use a tunnel so the connection is still encrypted.

For SSH Tunnel Method, choose:

• No Tunnel to connect to the database directly
• SSH Key Authentication to open the tunnel with a private key
• Password Authentication to open the tunnel with a password

Then fill in:

1. SSH Tunnel Jump Server Host: the host name or IP address of the bastion server.
2. SSH Connection Port: the bastion's SSH port. The default is 22.
3. SSH Login Username: the user to sign in to the bastion as. Note: this is an operating system user, not a Postgres user.
4. Then:
   • For SSH Key Authentication, paste that user's private key into SSH Private Key.
   • For Password Authentication, enter that operating system user's password. Note: this is the operating system password, not the Postgres password.

To create a key pair, for example an RSA key in PEM format:

ssh-keygen -t rsa -m PEM -f myuser_rsa

Add the public key to the bastion for the user Pipeloom signs in as, and paste the private key into the destination's settings.

Advanced options

JDBC URL Params

Extra JDBC connection parameters, as key=value pairs separated by &, for example key1=value1&key2=value2&key3=value3. They're added to the end of the connection URL.

connectTimeout is supported and defaults to 60 seconds; 0 means the longest timeout available.

Don't set currentSchema, user, password, ssl or sslmode here. The connector sets those itself and overwrites them.

:::warning This is an advanced option. Use it with care. :::

Internal schema name

The schema the connector uses for its own internal tables, and for raw tables in the legacy raw-tables mode. It defaults to airbyte_internal.

Unconstrained numeric columns

Creates numeric columns as unconstrained DECIMAL instead of NUMBER(38, 9), so numbers keep their full precision. It's off by default to stay compatible with existing tables, but we recommend turning it on for new destinations.

CDC deletion mode

What happens in this destination when a row is deleted in a source that uses change data capture:

• Hard delete (the default): the row is deleted here too.
• Soft delete: the row is kept here as a tombstone record that marks it as deleted.

Drop tables with CASCADE

:::caution This option runs DROP ... CASCADE on the tables this destination writes. Permanent data loss is possible: every object that depends on those tables, such as views, is deleted along with them. :::

Sometimes the connector has to recreate a table. If you've built objects on top of it, such as views, Postgres won't drop the table unless the drop cascades, which deletes those objects too. Turn this on only if you can rebuild them from scratch. We recommend creating them with a tool like dbt, triggered by an orchestrator, so they're rebuilt automatically.

Raw tables only (legacy)

Writes only raw tables, with each record as JSON in _airbyte_data, instead of typed final tables. This option is unstable: the format of _airbyte_data is likely to stay the same, but the other metadata columns may change. When it's on, syncs always append, even if deduplication is selected.

Sync modes

This destination supports every sync mode:

It supports namespaces: each namespace maps to a Postgres schema.

Output tables

Each stream is written to a table of its own in the configured schema. Besides your data columns, each table has these metadata columns:

• _airbyte_raw_id: a UUID assigned to each record. Postgres type VARCHAR.
• _airbyte_extracted_at: when the record was read from the source. Postgres type TIMESTAMP WITH TIME ZONE.
• _airbyte_meta: metadata about the record, including sync information and any schema changes. Postgres type JSONB.
• _airbyte_generation_id: the generation of the sync that wrote the record. Postgres type BIGINT.

Raw tables (legacy)

With raw tables only turned on, each stream is written to a raw table in the internal schema (airbyte_internal unless you change it). Each raw table has four columns:

• _airbyte_raw_id: a UUID assigned to each record. Postgres type VARCHAR.
• _airbyte_extracted_at: when the record was read from the source. Postgres type TIMESTAMP WITH TIME ZONE.
• _airbyte_loaded_at: when the record was written to a final table. Postgres type TIMESTAMP WITH TIME ZONE.
• _airbyte_data: the record itself, as JSON. Postgres type JSONB.

Schema changes

When the source schema changes, the connector adds new columns and changes column types as needed. When a column is removed from the source, the connector keeps the column and the data it already holds, and writes NULL to it for new rows.

Data type mapping

Naming

The connector creates tables and columns with quoted identifiers, so names keep their case. Special characters in table and column names are replaced with underscores. In the legacy raw-tables mode, raw tables and schemas use unquoted identifiers, again with special characters replaced by underscores.

The Postgres rules for identifiers, in short:

• An identifier starts with a letter (a–z, including letters with diacritical marks and non-Latin letters) or an underscore (_).
• Later characters can be letters, underscores, digits (0–9) or dollar signs ($). Dollar signs aren't allowed by the SQL standard, so they make SQL less portable. The standard will never define a key word that contains digits or starts or ends with an underscore, so names like those are safe from clashing with future key words.
• Postgres uses at most NAMEDATALEN - 1 bytes of an identifier. NAMEDATALEN is 64 by default, so names are limited to 63 bytes; longer names are accepted in commands but truncated.
• A quoted identifier can contain any character except the one with code zero (write a double quote as two double quotes). That allows names with spaces or ampersands, for example. The length limit still applies.
• Quoting makes a name case-sensitive; unquoted names are always folded to lower case. Quote a name consistently: always or never.

Because of the 63-byte limit, longer column names are truncated. If two columns end up with the same name, the connector may rename them to avoid the clash.

Limitations

• Large volumes perform poorly. Even Postgres-compatible services such as AWS Aurora slow down with large writes or updates over about 100 GB, especially with deduplication. Watch the database's memory and CPU while syncs run: a database can lock up and run up high costs under large syncs.
• Scale disk throughput too. When sizing Postgres for more data, disk throughput (IOPS) matters as much as memory and compute.
• Long names can collide. Nested sources that are flattened into columns often produce names longer than 63 bytes. For example, {63-byte name}_a and {63-byte name}_b both truncate to {63-byte name}, and Postgres then rejects the duplicate column name. The same limit applies to table names.
• The database needs /tmp. Data is loaded in bulk through the database's temporary storage. If the database can't access the /tmp file system, loading fails. Some hosted or locked-down deployments don't allow it.