Skip to main content

Create a PostgreSQL sink connector

Use a PostgreSQL sink connector to keep tables in a PostgreSQL database up to date with the changes a source connector captures. The connector reads the Kafka topics you select and writes each topic to the table you map it to.

Prerequisites​

Prepare the PostgreSQL database​

The connector inserts and updates rows by primary key, deletes the rows that are deleted in the source, and creates the tables and columns that are missing. Its user needs INSERT, UPDATE, and DELETE on the existing target tables, and USAGE and CREATE on the target schema.

  1. Connect to the target database as a user that can grant these privileges.

  2. Run the statements below. Replace YOUR_USER with the connector user, and public with your schema if you write to another one.

    GRANT USAGE ON SCHEMA public TO YOUR_USER;
    GRANT CREATE ON SCHEMA public TO YOUR_USER;
    GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO YOUR_USER;
  3. Make the connector user the owner of each existing target table that the connector needs to add columns to. PostgreSQL lets only the owner add columns, and the connector user already owns the tables it creates.

    ALTER TABLE public.YOUR_TABLE OWNER TO YOUR_USER;

Create the connector​

  1. Select Data platform → Workspace management, then select your workspace.

  2. On the Overview tab, under Services, select the CDC card.

  3. Select the Connectors tab, then select Create Connector.

    Connectors tab of a CDC Service in Healthy status, with no connector yet and the Create Connector button

  4. On the Connector Information screen, enter a Name, select sink in Type, and select PostgreSQL in Database.

    Connector Information screen of the Create Connector form with the Name, Type, and Database fields

  5. Select Next Step. On the Properties screen, enter the connection details, then select Test connection. Next Step becomes available after the test succeeds.

    Database Information section with Manual configuration selected and the Host name, Port, Database, Username, and Password fields

    Database Information section with From FPT Database Engine selected and the Database Name list above the connection fields

  6. On the same screen, choose a Converter class and set the four converter fields. Under Kafka topic, select +, then select the topics to read in the Select topics panel.

    Converter section with the Converter class, Converter key, Converter value, and schema enable lists

    Kafka topic section with one topic in the list, the add and clear buttons, the Filter box, and the Topic and Action columns

  7. Select Next Step. On the Additional properties screen, set the fields in the Database timezone, Database source, Task, Schema, Map table, and Error Tolerance sections.

    Map table section with a topic mapped to the users table, Create new turned on, and the Enable filter errors checkbox

  8. Select Next Step and check the settings on the Review screen.

    Review screen with the Basic Information, Database Information, Task, Converter, Database source, Schema, and Kafka topic sections and the Create button

  9. Select Create.

    The portal returns to the Essential Information tab of the CDC Service. On the Connectors tab, the new connector shows the status Running, with every Tasks counter at 0 until its task starts, which can take up to about 2.5 minutes.

Connector settings​

Connector information​

FieldRequiredDescription
NameYesThe connector name. Use lowercase letters, digits, and hyphens (-) only. Upper-case letters, _, ., and spaces are rejected with "Name has invalid characters".
TypeYesSelect sink.
DatabaseYesSelect PostgreSQL.

Properties​

In the Database Information section, choose From FPT Database Engine or Manual configuration. Manual configuration is selected by default. With From FPT Database Engine, select your instance in Database Name to fill in Host name and Port. See Connection mode.

FieldRequiredDescription
Database NameYesShown with From FPT Database Engine only. The PostgreSQL instance in FPT Database Engine. Enter the database to write to in Database.
Host nameYesThe host name or IP address of the PostgreSQL server.
PortYesThe port of the PostgreSQL server, for example 5432. The field has no default value in Manual configuration.
DatabaseYesThe database that the connector writes to.
UsernameYesThe user that the connector connects as. It needs the privileges in Prepare the PostgreSQL database.
PasswordYesThe password of that user.
Converter classYesThe converter class for this connector. The portal offers JDBC. The four converter fields stay greyed out until you choose a class. See Converter.
Converter key, Converter key schema enable, Converter value, Converter value schema enableYesHow the connector reads the keys and values of the messages, and whether they carry a schema. With JDBC, the key and value converters offer only org.apache.kafka.connect.json.JsonConverter, and the schema enable fields offer only true.
Kafka topicYesThe topics to read, up to 100. Select + to open the Select topics panel, select the topics, then select Select. See Topics.

Additional properties​

FieldRequiredDescription
TimezoneNoThe timezone of the data in the source database: UTC or Asia/Ho_Chi_Minh. The field has no default. See Timezone.
Type (under Database source)YesThe type of the source database that the topics come from: None, PostgreSQL, MySQL, MariaDB, or SQLServer.
Number of tasksYesHow many tasks process the topics in parallel. Default 1. See Tasks max.
SchemaYesThe schema in the target database that the connector writes tables to. The list shows the schemas of that database. Default public. Grant the connector user rights on it first.
Topic, Create new, Table (under Map table)YesThe table that receives the records of each topic. Create new is on by default, and Table shows the table name from the topic. Keep it on to create the table, or turn it off to use an existing one. Table names are case-sensitive. Select Enable filter errors to list only the rows with errors. See Map table.
Mode (under Error Tolerance)YesWhat the connector does with a message that it cannot read or convert. None stops the connector with status Failed. All skips the message and sends it to the topic you enter for failed records. The field has no default. See Error tolerance.

Next steps​