Skip to main content

Create a PostgreSQL source connector

Stream every row change in your PostgreSQL tables to Kafka topics, where sink connectors can deliver it to other systems. The connector reads the write-ahead log (WAL) through logical replication with the pgoutput plugin.

Prerequisites​

  • A CDC Service whose status is Healthy. See Create a CDC Service.
  • Superuser access to the primary PostgreSQL server. CDC runs on the primary, not on a hot or warm standby replica.
  • The database prepared for CDC as described in Prepare the PostgreSQL database.

Prepare the PostgreSQL database​

Run these statements on the primary server as a superuser.

  1. Check wal_level, and set it to logical if it has another value.

    SHOW wal_level;
    ALTER SYSTEM SET wal_level = 'logical';
  2. Restart the PostgreSQL service so that the new wal_level takes effect. Skip this step if wal_level was already logical.

    warning

    Restarting the primary server interrupts the applications that use the database. Plan the restart for a maintenance window.

  3. Create the user that the connector connects as, with the REPLICATION role. You can also use an existing superuser.

    CREATE USER YOUR_USER WITH REPLICATION LOGIN PASSWORD 'YOUR_PASSWORD';

    To check whether an existing user is a superuser, run SELECT rolsuper FROM pg_roles WHERE rolname = 'YOUR_USER';.

  4. Create a publication for the tables to capture if the connector user is not a superuser. Give it a name in lowercase letters and digits only, because the Publication name field accepts nothing else.

    -- All tables
    CREATE PUBLICATION YOUR_PUBLICATION FOR ALL TABLES;
    -- Or only some tables
    CREATE PUBLICATION YOUR_PUBLICATION FOR TABLE YOUR_SCHEMA.YOUR_TABLE, YOUR_SCHEMA.OTHER_TABLE;

    A superuser can skip this step. The connector then creates a publication for all tables, with the name you enter in Publication name.

  5. Grant SELECT on the captured tables to the connector user. Skip this step if the user is a superuser.

    GRANT SELECT ON TABLE YOUR_SCHEMA.YOUR_TABLE TO YOUR_USER;

    To grant SELECT on every table in a schema, run:

    DO $$
    DECLARE
    table_record RECORD;
    BEGIN
    FOR table_record IN
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'YOUR_SCHEMA' AND table_type = 'BASE TABLE'
    LOOP
    EXECUTE 'GRANT SELECT ON TABLE YOUR_SCHEMA."' || table_record.table_name || '" TO YOUR_USER';
    END LOOP;
    END $$;
  6. Let the user create and write the signal table public.cdc_signal if you plan to select Enable incremental snapshot. Creating the connector creates this table, and snapshots run through it.

    GRANT USAGE, CREATE ON SCHEMA public TO YOUR_USER;
    -- If public.cdc_signal already exists, for example from another connector
    GRANT INSERT ON public.cdc_signal TO YOUR_USER;

    If your publication lists its tables one by one, add public.cdc_signal to it after you create the connector. See Change the tables in a publication.

  7. Set REPLICA IDENTITY FULL on each captured table, so that change events carry the row values before and after the change.

    ALTER TABLE YOUR_SCHEMA.YOUR_TABLE REPLICA IDENTITY FULL;

    To change every table in a schema, run:

    DO $$
    DECLARE
    table_record RECORD;
    BEGIN
    FOR table_record IN
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema = 'YOUR_SCHEMA' AND table_type = 'BASE TABLE'
    LOOP
    EXECUTE 'ALTER TABLE YOUR_SCHEMA."' || table_record.table_name || '" REPLICA IDENTITY FULL';
    END LOOP;
    END $$;
  8. Create the heartbeat table. The connector writes its heartbeat to this table whenever Heartbeat interval (ms) is above 0. The default is 0, which sends no heartbeat.

    CREATE TABLE IF NOT EXISTS public.cdc_heartbeat (
    id integer PRIMARY KEY,
    last_heartbeat timestamptz NOT NULL
    );
  9. Check that the server has room for one more replication slot. The connector creates the slot you name in Slot name, or reuses an existing slot with that name.

    SHOW max_replication_slots;
    SELECT slot_name, plugin, slot_type, database, active FROM pg_replication_slots;

    To free a slot that is no longer active, run SELECT pg_drop_replication_slot('YOUR_SLOT');. To raise the limit, change max_replication_slots in postgresql.conf.

Change the tables in a publication​

If you created the publication for a list of tables, run these statements as a superuser when the set of captured tables changes.

-- List the publications
SELECT * FROM pg_publication;
-- Add tables
ALTER PUBLICATION YOUR_PUBLICATION ADD TABLE YOUR_SCHEMA.YOUR_TABLE;
-- Remove tables
ALTER PUBLICATION YOUR_PUBLICATION DROP TABLE YOUR_SCHEMA.YOUR_TABLE;

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 Healthy CDC Service with the No connector yet message and the Create Connector button

  4. On the Connector Information screen, enter a Name, select source 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, the Topic prefix, and the slot and publication names, then select Test connection. Next Step becomes available after the test succeeds with the message "Connect Successfully".

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

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

    Kafka topic section with the Topic prefix field

    Replication section with the Slot name and Publication name fields

  6. Select Next Step. On the Additional properties screen, select the snapshot Mode, add the tables to capture, and set the Heartbeat interval (ms).

    Snapshot section with initial selected in Mode

    Include section with the add and clear buttons, the Filter box, and four tables listed by Schema, Table, and Table path

    Heartbeat section with 3000 entered in Heartbeat interval (ms)

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

    Review screen of a PostgreSQL source connector with the Basic Information, Kafka topic, Database Information, Snapshot, Include, and Heartbeat sections

  8. Select Create.

    The portal shows "Create Successfully" and opens the Essential Information tab of the CDC Service. On the Connectors tab, the new connector shows Running, with every Tasks counter at 0 until its task starts, for up to about 2.5 minutes. Snapshot Status shows Not Started until you run an incremental snapshot.

Connector settings​

Connector information​

FieldRequiredDescription
NameYesThe connector name, in lowercase letters, digits, and hyphens (-). Uppercase letters, _, ., and spaces give the error "Name has invalid characters".
TypeYesSelect source.
DatabaseYesSelect PostgreSQL.

Properties​

FieldRequiredDescription
From FPT Database Engine / Manual configurationYesSelect a PostgreSQL instance from FPT Database Engine, or enter the connection details yourself. Manual configuration is selected by default. See Connection mode.
Database NameWith From FPT Database EngineThe PostgreSQL instance in FPT Database Engine. The list also shows the instances that Lakehouse created. Selecting one fills in Host name and Port, which you can still edit. Database, Username, and Password stay empty.
Host nameYesHost name or IP address of the PostgreSQL server.
PortYesThe PostgreSQL server port, for example 5432. The field has no default in Manual configuration.
DatabaseYesThe database to capture changes from.
UsernameYesThe user you prepared in step 3.
PasswordYesThe user's password.
Enable incremental snapshotNoLets you run incremental snapshots on this connector. Creating the connector then also creates the signal table public.cdc_signal, and Test connection checks the CREATE TABLE and INSERT permissions. The signal table also gets a topic, TOPIC_PREFIX.public.cdc_signal. Without this option there is no signal table, so Snapshot → Execute cannot run, although the menu still shows it. See Incremental snapshot.
Topic prefixYesPrefix of the Kafka topics the connector writes to. Each table gets the topic TOPIC_PREFIX.SCHEMA.TABLE, for example syncdata.inventory.customer. See Topic prefix.
Slot nameYesThe replication slot the connector reads from. Lowercase letters and digits only. Other characters, such as _, give the error "Slot name has invalid characters". The connector creates the slot, or reuses an existing slot with this name.
Publication nameYesThe publication you created in step 4. For a superuser, the name of the publication that the connector creates. Lowercase letters and digits only. Other characters, such as _, give the error "Publication name has invalid characters".

Additional properties​

FieldRequiredDescription
ModeYesWhat the connector does with existing rows. The field has no default, so select one. initial snapshots them, then captures changes. initial_only snapshots them without capturing changes. no_data captures new changes only. See Snapshot mode.
IncludeNoThe tables to capture. Select + to load the schemas and tables of the database, then select tables, up to 100. Use Filter to search the list and the delete icon to remove a table. If you add no table, the connector captures all tables.
Heartbeat interval (ms)YesTime between heartbeat messages, from 0 to 30000. Default 0, which sends no heartbeat. The connector writes the heartbeat to the table from step 8. See Heartbeat.

Clean up after you delete the connector​

Deleting a PostgreSQL source connector leaves its replication slot and publication in the database.

warning

Until you drop the replication slot, PostgreSQL keeps all WAL that the slot has not consumed, so disk usage keeps growing.

Drop the slot and the publication as a superuser.

SELECT pg_drop_replication_slot('YOUR_SLOT');
DROP PUBLICATION YOUR_PUBLICATION;

Next steps​