PostgreSQL

DataCater uses a JDBC-based connector for streaming data change events from data pipelines to PostgreSQL.

When assigning a PostgreSQL sink to a data pipeline, the user must map the schema of the pipeline to the schema of the PostgreSQL index.

Requirements

Please make sure that the PostgreSQL user has been granted the following privileges for the used table: SELECT, INSERT, UPDATE, and DELETE.

You may grant these privileges to an existing user with the following SQL query:

psql> GRANT SELECT, INSERT, UPDATE, DELETE ON [table_name] TO [username];

Configuration

This sink connector supports the following configuration options:

Hostname or IP

The hostname or IP address of the machine where PostgreSQL is running.

Port

The port under which PostgreSQL is available (default: 5432).

SSL

Whether to use SSL when connecting to the PostgreSQL database or not.

Username

The username used for authenticating with PostgreSQL.

Password

The password of the user used for authenticating with PostgreSQL.

Database name

The name of the database that should be used as sink.

Schema name

The name of the database schema that should be used as sink (default: public).

Table name

The name of the database table that should be used as sink. You may retrieve the list of tables available in the given PostgreSQL database by clicking on Fetch table names.

Insert mode

INSERT: Only perform INSERT statements, which resembles an append-only mode.

UPSERT: Perform INSERT, UPDATE, and DELETE statements.

Data Types

When retrieving the schema of the PostgreSQL table, DataCater performs the following mapping between PostgreSQL's data types and DataCater's data types: