Postgres sink connector

Use the Postgres Connector to send data from Decodable to Postgres, including all PostgreSQL-compatible databases. The Postgres connector connects to a Postgres table through Postgres JDBC connections. If you are looking for instructions on how to get data from Postgres into Decodable, see Postgres source connector.

There are many Postgres-compatible databases, but some of the main ones include:

  • Amazon Aurora

  • Amazon RDS

  • Azure CosmosDB for PostgreSQL

  • Azure Database for PostgreSQL

  • CockroachDB

  • Google AlloyDB

  • Google Cloud SQL

  • Google Spanner

  • Neon

  • Tembo

  • Timescale

  • YugabyteDB

Features

Delivery guarantee

Exactly once

Read from multiple streams

Yes

Prerequisites

In order for Decodable to connect to a PostgreSQL database server successfully, the following must be true:

  • Your JDBC connection must be publicly accessible. Decodable will establish a connection to the Postgres database by using a username and password to authenticate.

  • The Postgres user must have select, update, delete, and insert permissions for the tables that you want to send data to.

Steps

If you want to use the Decodable CLI or API to create the connection, you can refer to the Property Name column for information about what the underlying property names are. The connector name is postgres.

  1. From the Connections page, select the Postgres Connector and complete the following fields.

    UI Field Property Name Description

    Host

    hostname

    The IP address or host name of the PostgreSQL database server.

    Port

    port

    Optional. The port number of the PostgreSQL database server.

    Defaults to 5432.

    Database

    database-name

    The name of the Postgres database.

    Schema

    schema-name

    Optional. The schema containing your database table. Defaults to public.

    Username

    username

    The username to use when connecting to the Postgres database.

    Password

    password

    The secret containing the password credentials. If you are using the Decodable CLI, run decodable secret list to view available secrets or decodable secret --help for help with creating a new secret.

    Note: For security purposes, Decodable will never display secret values in plaintext. You can manage which users have permissions to create, delete, or modify secrets in the Access Control management view. See Roles, groups, and permissions for more information.

    JDBC Options

    properties.jdbc.options

    Optional. Any additional JDBC options that you want this connection to use. See Connection Parameters in the JDBC documentation for a full list of available JDBC options.

  2. Select Next. The next screen shows the list of Decodable streams in your account.

  3. Select the streams that you want to send to Postgres. Decodable automatically creates a table in your database for each stream selected.

    By default, Decodable uses the stream name as the name of the table it creates. If a table already exists with that name and the schema of the stream matches the schema of the table, Decodable will write to the existing table.

    The schema of each stream is automatically translated to Postgres, including:

    • field names

    • data types (See Data types mapping for how Decodable types map to Postgres types)

    • primary keys

  4. (Optional) If you want to rename the tables to be created, you can override the generated table names.

  5. Select Next.

  6. Give the newly created connection a Name and Description. Then, select Save.

Connector starting state and offsets

A new sink connection will start reading from the Latest point in the source Decodable stream. This means that only data that’s written to the stream when the connection has started will be sent to the external system. You can override this when you start the connection to Earliest if you want to send all the existing data on the source stream to the target system, along with all new data that arrives on the stream.

When you restart a sink connection it will continue to read data from the point it most recently stored in the checkpoint before the connection stopped. You can also opt to discard the connection’s state and restart it afresh from Earliest or Latest as described above.

Learn more about starting state here.

Data types mapping

The table below shows the mapping from Decodable data types to Postgres data types.

Decodable Type Postgres Type

CHAR(n)

CHAR(n)

VARCHAR(n)

VARCHAR(n)

STRING

TEXT

BOOLEAN

BOOLEAN

DECIMAL(p, s)

DECIMAL(p, s)

SMALLINT

SMALLINT

INT/INTEGER

INT

BIGINT

BIGINT

FLOAT

FLOAT

DOUBLE [PRECISION]

DOUBLE

DATE

DATE

TIME(p)

TIME(p)

TIMESTAMP(p) [WITHOUT TIME ZONE]

TIMESTAMP(p)

TIMESTAMP(p) WITH LOCAL TIME ZONE

TIMESTAMP(p)

Resource specifier keys

When using the connection scan command of the Decodable CLI to create a connection specification, the following resource specifier keys are available:

Name Description

table-name

The table name