PostgreSQL CDC Source Connector (Debezium) for Confluent Cloud

Note

If you are installing the connector locally for Confluent Platform, see Debezium PostgreSQL Source connector for Confluent Platform.

The Kafka Connect PostgreSQL Change Data Capture (CDC) Source connector (Debezium) for Confluent Cloud can obtain a snapshot of the existing data in a PostgreSQL database and then monitor and record all subsequent row-level changes to that data. The connector supports Avro, JSON Schema, Protobuf, or JSON (schemaless) output data formats. All of the events for each table are recorded in a separate Apache Kafka® topic. The events can then be easily consumed by applications and services.

Important

If you are still on Confluent Cloud Enterprise, please contact your Confluent Account Executive for more information about using this connector.

Features

The PostgreSQL CDC Source connector (Debezium) provides the following features:

  • Topics created automatically: The connector automatically creates Kafka topics using the naming convention: <database.server.name>.<schemaName>.<tableName>. The tables are created with the properties: topic.creation.default.partitions=1 and topic.creation.default.replication.factor=3.
  • Logical decoding plugins supported: wal2json, wal2json_rds, wal2json_streaming, wal2json_rds_streaming, pgoutput, decoderbufs. The default used is pgoutput.
  • Database authentication: Uses password authentication.
  • SSL support: Supports one-way SSL.
  • Data Format with or without a Schema: The connector supports Avro, JSON Schema, Protobuf, JSON (schemaless), or Bytes. Schema Registry must be enabled to use a Schema Registry-based format (for example, Avro, JSON_SR (JSON Schema), or Protobuf).
  • Tasks per connector: Organizations can run multiple connectors with a limit of one task per connector (that is, "tasks.max": "1").
  • Select configuration properties:
    • Tables included and Tables excluded: Allows you to set whether a table is or is not monitored for changes. By default, the connector monitors every non-system table.
    • Snapshot mode: Allows you to specify the criteria for running a snapshot.
    • Tombstones on delete: Allows you to configure whether a tombstone event should be generated after a delete event. Default is true.
    • Other configuration properties:
      • poll.interval.ms
      • max.batch.size
      • max.queue.size

See the PostgreSQL CDC Source connector configuration properties for properties and definitions. See the Confluent Cloud connector limitations for connector limitations.

For more information and examples to use with the Confluent Cloud API for Connect, see the Confluent Cloud API for Connect section.

Quick Start

Use this quick start to get up and running with the Confluent Cloud PostgreSQL CDC Source (Debezium) connector. The quick start provides the basics of selecting the connector and configuring it to obtain a snapshot of the existing data in a PostgreSQL database and then monitoring and recording all subsequent row-level changes.

Prerequisites
  • Authorized access to a Confluent Cloud cluster on Amazon Web Services (AWS), Microsoft Azure (Azure), or Google Cloud Platform (GCP).

  • The Confluent Cloud CLI installed and configured for the cluster. See Install the Confluent Cloud CLI.

  • Schema Registry must be enabled to use a Schema Registry-based format (for example, Avro, JSON_SR (JSON Schema), or Protobuf).

  • You cannot use a basic database with Azure. You must use a general purpose or memory-optimized PostgreSQL database.

  • The PostgreSQL database must be configured for CDC. For details, see PostgreSQL in the Cloud.

  • Clients from Azure Virtual Networks are not allowed to access the server by default. Please ensure your Azure Virtual Network is correctly configured and that Allow access to Azure Services is enabled.

  • Public access may be required for your database. See Internet Access to Resources for details. The following example shows the AWS Management Console when setting up a PostgreSQL database.

    AWS example showing public access for PostgreSQL

    Public access enabled

  • A parameter group with the property rds.logical_replication=1 is required. An example is shown below. Once created, you must reboot the database.

    Parameter Group

    Parameter group

    RDS Logical Replication

    RDS logical replication

  • For networking considerations, see Internet access to resources. To use static egress IPs, see Static Egress IP Addresses. The following example shows the AWS Management Console when setting up security group rules for the VPC.

    AWS example showing security group rules

    Open inbound traffic

    Note

    See your specific cloud platform documentation for how to configure security rules for your VPC.

  • Kafka cluster credentials. You can use one of the following ways to get credentials:
    • Create a Confluent Cloud API key and secret. To create a key and secret, you can use the Confluent Cloud CLI or you can autogenerate the API key and secret directly in the Cloud Console when setting up the connector.
    • Create a Confluent Cloud service account for the connector. Make sure to review the ACL entries required in the service account documentation. Some connectors have specific ACL requirements.

Using the Confluent Cloud Console

Step 1: Launch your Confluent Cloud cluster.

See the Quick Start for Apache Kafka using Confluent Cloud for installation instructions.

Step 2: Add a connector.

In the left navigation menu, click Data integration, and then click Connectors. If you already have connectors in your cluster, click + Add connector.

Step 3: Select your connector.

Click the PostgreSQL CDC Source connector icon.

PostgreSQL Source Connector Icon

Step 4: Set up the connection.

Complete the following and click Continue.

Note

  • Make sure you have all your prerequisites completed.
  • An asterisk ( * ) designates a required entry.
  1. Enter a connector name.

  2. Enter your Kafka Cluster credentials. The credentials are either the API key and secret or the service account API key and secret.

  3. Add the connection details for the database.

    Important

    Do not include jdbc:xxxx:// in the Connection host field. The example below shows a sample host address.

    ../_images/ccloud-postgresql-source-connect-to-data.png

    If you do not choose an SSL mode, disable is the default option used. When require is selected, the connector uses a secure (encrypted) connection. The connector fails if a secure connection cannot be established. This mode does not do Certification Authority (CA) validation.

  4. Add the Database details for your database. Review the following notes for more information about field selections.

    • Tables included: Enter a comma-separated list of fully-qualified table identifiers for the connector to monitor. By default, the connector monitors all non-system tables. A fully-qualified table name is in the form schemaName.tableName.
    • Tables excluded: Enter a comma-separated list of fully-qualified table identifiers for the connector to ignore. A fully-qualified table name is in the form schemaName.tableName. This property cannot be used with the property Tables included.
    • Snapshot mode: Specifies the criteria for performing a database snapshot when the connector starts.
      • The default setting is initial. When selected, the connector takes a snapshot of the structure and data from captured tables. This is useful if you want the topics populated with a complete representation of captured table data when the connector starts.
      • never specifies that the connector should never perform snapshots, and that when starting for the first time, the connector starts reading from where it last left off.
      • exported specifies that the database snapshot is based on the point in time when a replication slot was created. Note that this is a good way to perform a lock-free snapshot (see Snapshot isolation).
    • Tombstones on delete: Configure whether a tombstone event should be generated after a delete event. The default is true.
    • Columns Excluded: An optional, comma-separated list of regular expressions that match the fully-qualified names of columns to exclude from change event record values. Fully-qualified names for columns are in the form databaseName.tableName.columnName.
    • Plugin name: Select the plugin to use. Options are wal2json, wal2json_rds, wal2json_streaming, wal2json_rds_streaming, pgoutput, and decoderbufs. The default is pgoutput. For more information, see PostgreSQL logical decoding plugin.
    • Slot name: The name of the PostgreSQL logical decoding slot created for streaming changes from a plugin and for a database. The slot name can contain only lower-case letters, numbers, and the underscore character. The default value is debezium.
  5. Select the values for the following properties:

    • Output message format: (data coming from the connector): AVRO, JSON (schemaless), JSON_SR (JSON Schema), or PROTOBUF. A valid schema must be available in Schema Registry to use a schema-based message format (for example, Avro, JSON_SR (JSON Schema), or Protobuf).

    • After-state only: (Optional) Defaults to true, which results in the Kafka record having only the record state from change events applied. Select false to maintain the prior record states after applying the change events. For additional details, see After-state only output limitation.

    • JSON output decimal format: (Optional) Defaults to BASE64.

      JSON output decimal format property
  6. Enter the number of tasks in use by the connector. Organizations can run multiple connectors with a limit of one task per connector (that is, "tasks.max": "1").

  7. Transforms and Predicates: See the Single Message Transforms (SMT) documentation for details.

See the PostgreSQL CDC Source connector configuration properties for values and definitions.

Step 5: Launch the connector.

Verify the connection details and click Launch.

Step 6: Check the connector status.

The status for the connector should go from Provisioning to Running. It may take a few minutes.

Step 7: Check the Kafka topic.

After the connector is running, verify that messages are populating your Kafka topic.

For more information and examples to use with the Confluent Cloud API for Connect, see the Confluent Cloud API for Connect section.

See also

For an example that shows fully-managed Confluent Cloud connectors in action with Confluent Cloud ksqlDB, see the Cloud ETL Demo. This example also shows how to use Confluent Cloud CLI to manage your resources in Confluent Cloud.

../_images/topology.png

Using the Confluent Cloud CLI

Complete the following steps to set up and run the connector using the Confluent Cloud CLI.

Note

Make sure you have all your prerequisites completed.

Step 1: List the available connectors.

Enter the following command to list available connectors:

ccloud connector-catalog list

Step 2: Show the required connector configuration properties.

Enter the following command to show the required connector properties:

ccloud connector-catalog describe <connector-catalog-name>

For example:

ccloud connector-catalog describe PostgresCdcSource

Example output:

Following are the required configs:
connector.class: PostgresCdcSource
name
kafka.api.key
kafka.api.secret
database.hostname
database.user
database.dbname
database.server.name
output.data.format
tasks.max

Step 3: Create the connector configuration file.

Create a JSON file that contains the connector configuration properties. The following example shows the required connector properties.

{
  "connector.class": "PostgresCdcSource",
  "name": "PostgresCdcSourceConnector_0",
  "kafka.api.key": "****************",
  "kafka.api.secret": "****************************************************************",
  "database.hostname": "debezium-1.<host-id>.us-east-2.rds.amazonaws.com",
  "database.port": "5432",
  "database.user": "postgres",
  "database.password": "**************",
  "database.dbname": "postgres",
  "database.server.name": "cdc",
  "table.include.list":"public.passengers",
  "plugin.name": "pgoutput",
  "output.data.format": "JSON",
  "tasks.max": "1"
}

Note the following property definitions:

  • "connector.class": Identifies the connector plugin name.
  • "name": Sets a name for your new connector.
  • "table.includelist": (Optional) Enter a comma-separated list of fully-qualified table identifiers for the connector to monitor. By default, the connector monitors all non-system tables. A fully-qualified table name is in the form schemaName.tableName.
  • "database.sslmode": If not entered, disable is the default option used. If you enter "database.sslmode" : "require", the connector uses a secure (encrypted) connection. The connector fails if a secure connection cannot be established. This mode does not do Certification Authority (CA) validation.
  • "output.data.format": Sets the output message format (data coming from the connector). Valid entries are AVRO, JSON_SR, PROTOBUF, or JSON. You must have Confluent Cloud Schema Registry configured if using a schema-based message format (for example, Avro, JSON_SR (JSON Schema), or Protobuf).
  • "after.state.only": (Optional) Defaults to true, which results in the Kafka record having only the record state from change events applied. Enter false to maintain the prior record states after applying the change events. For additional details, see After-state only output limitation.
  • "json.output.decimal.format": (Optional) Defaults to BASE64. Specify the JSON/JSON_SR serialization format for Connect DECIMAL logical type values with two allowed literals:
    • BASE64 to serialize DECIMAL logical types as base64 encoded binary data.
    • NUMERIC to serialize Connect DECIMAL logical type values in JSON or JSON_SR as a number representing the decimal value.
  • "column.exclude.list": (Optional) A comma-separated list of regular expressions that match the fully-qualified names of columns to exclude from change event record values. Fully-qualified names for columns are in the form databaseName.tableName.columnName.
  • "plugin.name": (Optional) Sets the plugin to use. Options are wal2json, wal2json_rds, wal2json_streaming, wal2json_rds_streaming, pgoutput, and decoderbufs. The default is pgoutput.
  • "slot.name": (Optional) The name of the PostgreSQL logical decoding slot created for streaming changes from a plugin and for a database. The slot name can contain only lower-case letters, numbers, and the underscore character. The default value is debezium.
  • "snapshot.mode": (Optional) Specifies the criteria for performing a database snapshot when the connector starts.
    • The default setting is initial. When selected, the connector takes a snapshot of the structure and data from captured tables. This is useful if you want the topics populated with a complete representation of captured table data when the connector starts.
    • never specifies that the connector should never perform snapshots, and that when starting for the first time, the connector starts reading from where it last left off.
    • exported specifies that the database snapshot is based on the point in time when a replication slot was created. Note that this is a good way to perform a lock-free snapshot (see Snapshot isolation).
  • "tasks.max": Enter the number of tasks in use by the connector. Organizations can run multiple connectors with a limit of one task per connector (that is, "tasks.max": "1").

Single Message Transforms: See the Single Message Transforms (SMT) documentation for details about adding SMTs using the CLI.

See the PostgreSQL CDC Source connector configuration properties for values and definitions.

Step 4: Load the properties file and create the connector.

Enter the following command to load the configuration and start the connector:

ccloud connector create --config <file-name>.json

For example:

ccloud connector create --config postgresql-cdc-source.json

Example output:

Created connector PostgresCdcSourceConnector_0 lcc-ix4dl

Step 5: Check the connector status.

Enter the following command to check the connector status:

ccloud connector list

Example output:

ID          |            Name              | Status  |  Type
+-----------+------------------------------+---------+-------+
lcc-ix4dl   | PostgresCdcSourceConnector_0 | RUNNING | source

Step 6: Check the Kafka topic.

After the connector is running, verify that messages are populating your Kafka topic.

For more information and examples to use with the Confluent Cloud API for Connect, see the Confluent Cloud API for Connect section.

Configuration Properties

The following connector configuration properties can be used with the PostgreSQL CDC Source connector for Confluent Cloud.

database.hostname

IP address or hostname of the PostgreSQL database server.

  • Type: String
  • Importance: High
database.port

Integer port number of the PostgreSQL database server.

  • Type: Integer
  • Importance: Low
  • Default: 5432
database.user

Username to use when connecting to the PostgreSQL database server.

  • Type: String
  • Importance: High
database.password

Password to use when connecting to the PostgreSQL database server.

  • Type: Password
  • Importance: High
database.dbname

The name of the PostgreSQL database from which to stream changes.

  • Type: String
  • Importance: High
database.server.name

The logical name of the PostgreSQL database server cluster. This logica name forms the namespace and is used in all Kafka topic names and Connect schema names. If Avro data format is used, the logical name is also used for the namespaces of the corresponding Avro schema. Kafka topics are created with the prefix database.server.name. Only alphanumeric characters, underscores, hyphens, and periods (dots) are allowed.

  • Type: String
  • Importance: High
database.sslmode

Sets whether or not to use an encrypted connection to the PostgreSQL server. Options are disable (default) to use an unencrypted connection. The option require enables the connector to use a secure (encrypted) connection. The connector fails if a secure connection cannot be established.

  • Type: String
  • Importance: Low
  • Default: disable
table.include.list

An optional comma-separated list of regular expressions that match fully-qualified table identifiers for tables to be monitored. Any table not included is excluded from monitoring. Each identifier is in the form schemaName.tableName. By default the connector monitors every non-system table in each monitored schema. May not be used with table.exclude.list.

  • Type: List of strings
  • Importance: Low
table.exclude.list

An optional comma-separated list of regular expressions that match fully-qualified table identifiers for tables to be excluded from monitoring. An excluded table is not monitored. Each identifier is in the form schemaName.tableName. May not be used with table.include.list.

  • Type: List of Strings
  • Importance: Low
snapshot.mode

The criteria for running a snapshot when the connector starts up. The default setting is initial. When selected, the connector takes a snapshot of the structure and data from captured tables. This is useful if you want the topics populated with a complete representation of captured table data when the connector starts. The option never specifies that the connector should never perform snapshots, and that when starting for the first time, the connector starts reading from where it last left off. The `exported option specifies that the database snapshot is based on the point in time when a replication slot was created.

  • Type: String
  • Importance: Medium
  • Default: initial
  • Valid values: [exported, initial, never]
tombstones.on.delete

Controls whether a tombstone event should be generated after a delete event. When set to true (default), the delete operations are represented by a delete event and a subsequent tombstone event. When set to false, only a delete event is sent. Emitting a tombstone event allows Kafka to completely delete all events pertaining to the given key when the source record is deleted.

  • Type: String
  • Importance: High
  • Default: true
column.exclude.list

An optional comma-separated list of regular expressions that match the fully-qualified names of columns that should be excluded from change event message values. Fully-qualified names for columns use the format schemaName.tableName.columnName.

  • Type: List of strings
  • Importance: Low
"plugin.name"

The name of the PostgreSQL logical decoding plugin installed on the server. Options are wal2json, wal2json_rds, wal2json_streaming, wal2json_rds_streaming, pgoutput, and decoderbufs. The default is pgoutput. For additional information, see the Debeziium logical decoding overview.

  • Type: String
  • Importance: Medium
  • Default: pgoutput
slot.name

The name of the PostgreSQL logical decoding slot created for streaming changes from a plugin and database instance. Values must conform to the PostgreSQL replication slot naming rules which state that the replication slot name can contain lower-case letters, numbers, and the underscore character.”

  • Type: String
  • Importance: Medium
  • Default: debezium
poll.interval.ms

Positive integer value that specifies the number of milliseconds (ms) the connector should wait before polling for new change events. Defaults to 1000 ms.

  • Type: Integer
  • Importance: Low
  • Default: 1000
max.batch.size

Positive integer value that specifies the maximum size of each batch of change events that may be processed.

  • Type: Integer
  • Importance: Low
  • Default: 1000
event.processing.failure.handling.mode

Specifies how the connector should react to exceptions during deserialization of binlog events.

  • Type: String
  • Importance: Low
  • Default: fail
  • Valid values: [fail, skip, warn]
heartbeat.interval.ms

Controls how frequently the connector sends heartbeat messages to a Kafka topic. The default value 0 specifies that the connector does not send heartbeat messages.

  • Type: Int
  • Importance: Low
  • Default: 0
heartbeat.action.query

Adds a query that the connector runs on the source database when the connector sends a heartbeat message.

  • Type: String
  • Importance: Low
after.state.only

Defaults to true, which results in the Kafka record having only the record state from change events applied. Enter false to maintain the prior record states after applying the change events.

  • Type: String
  • Importance: Low
  • Default: true
  • Valid values: [true, false]
json.output.decimal.format

Specify the JSON or JSON_SR serialization format for Connect DECIMAL logical type values with two allowed literals. The BASE64 option designates that the connector serialize DECIMAL logical types as base64 encoded binary data and. The NUMERIC option designates that the connector serialize Connect DECIMAL logical type values in JSON or JSON_SR as a number that represents the decimal value.

  • Type: String
  • Importance: Low
  • Default: BASE64
  • Valid values: [BASE64, NUMERIC]

Next Steps

See also

For an example that shows fully-managed Confluent Cloud connectors in action with Confluent Cloud ksqlDB, see the Cloud ETL Demo. This example also shows how to use Confluent Cloud CLI to manage your resources in Confluent Cloud.

../_images/topology.png

After-state only output limitation

When a connector is configured with the property After-state only=false, you expect to see the previous values of all columns under before in the record. However, for certain conditions before will contain null or partially displayed columns. If Protobuf is used, the record may not contain the before field at all. The following example shows this issue and provides a corrective action to take.

For example, the connector is configured with JSON and After-state only is set to false. When a record is updated in the PostgreSQL database, you may see a record similar to the following sample, where "before" is null.

{
  "before": null,
  "after": {
    "id": 5,
    "name": "Allen William Henry",
    "sex": "male",
    "age": 25,
    "sibsp": 0,
    "parch": 0,
    "created_at": "2018-01-02T15:22:14.831461Z"
   },
   "source": {
     "version": "1.3.1.Final",
     "connector": "postgresql",
     "name": "test",
     "ts_ms": 1621389097781,
     "snapshot": "false",
     "db": "postgres",
     "schema": "public",
     "table": "passengers",
     "txId": 572,
     "lsn": 872429856,
     "xmin": null
   },
   "op": "u",
   "ts_ms": 1621389098688,
   "transaction": null
}

For an updated record to contain the previous (before) values of all columns in the row, you need to modify the passengers table by running ALTER TABLE passengers REPLICA IDENTITY FULL. After you make this change in the PostgreSQL database, and records are updated, you should see records similar to the following sample.

{
  "before": {
    "id": 8,
    "name": "Gosta Leonard",
    "sex": "male",
    "age": 2,
    "sibsp": 3,
    "parch": 1,
    "created_at": "2018-01-03T20:53:55.955056Z"
  },
  "after": {
    "id": 8,
    "name": "Gosta Leonard",
    "sex": "male",
    "age": 25,
    "sibsp": 3,
    "parch": 1,
    "created_at": "2018-01-03T20:53:55.955056Z"
  },
  "source": {
    "version": "1.3.1.Final",
    "connector": "postgresql",
    "name": "test",
    "ts_ms": 1621390542864,
    "snapshot": "false",
    "db": "postgres",
    "schema": "public",
    "table": "passengers",
    "txId": 581,
    "lsn": 1207967968,
    "xmin": null
  },
  "op": "u",
  "ts_ms": 1621390544032,
  "transaction": null
}