Add PostgreSQL Database CDC source to an eventstream

This article shows you how to add a PostgreSQL Database Change Data Capture (CDC) source to an eventstream.

The PostgreSQL Database Change Data Capture (CDC) source connector for Microsoft Fabric event streams allows you to capture a snapshot of the current data in a PostgreSQL database. Currently, PostgreSQL Database Change Data Capture (CDC) is supported from the following services where the databases can be accessed publicly:

• Azure Database for PostgreSQL
• Amazon RDS for PostgreSQL
• Amazon Aurora PostgreSQL
• Google Cloud SQL for PostgreSQL

Once the PostgreSQL Database CDC source is added to the eventstream, it captures row-level changes to the specified tables. These changes can then be processed in real-time and sent to different destinations for further analysis.

Napomena

This source is not supported in the following regions of your workspace capacity: West US3, Switzerland West.

Prerequisites

• Access to a workspace in the Fabric capacity license mode (or) the Trial license mode with Contributor or higher permissions.

• Registered user access in the PostgreSQL database.

• Your PostgreSQL database must be publicly accessible and not be behind a firewall or secured in a virtual network.

• CDC enabled in the PostgreSQL database and tables.

  If you have Azure Database for PostgreSQL, follow the steps in the next section to enable CDC. For detailed information, see Logical replication and logical decoding - Azure Database for PostgreSQL - Flexible Server.

  For other PostgreSQL databases, see Debezium connector for PostgreSQL :: Debezium Documentation.

• If you don't have an eventstream, create an eventstream.

Enable CDC in your PostgreSQL Database

This section uses Azure Database for PostgreSQL as an example.

To enable CDC in your Azure Database for PostgreSQL Flexible Server, follow these steps:

1. On your Azure Database for PostgreSQL Flexible Server page in the Azure portal, select Server parameters in the navigation menu.

2. On the Server parameters page:

   • Set wal_level to logical.
   • Update the max_worker_processes to at least 16.

   

3. Save the changes and restart the server.

4. Confirm that your Azure Database for PostgreSQL Flexible Server instance allows public network traffic.

5. Grant the admin user replication permissions by running the following SQL statement. If you want to use other user account to connect your PostgreSQL DB to fetch CDC, ensure the user is the table owner.

   SQL

   ALTER ROLE <admin_user_or_table_owner_user> WITH REPLICATION;
   

Launch the Select a data source wizard

If you haven't added any source to your eventstream yet, select Use external source tile.

If you're adding the source to an already published eventstream, switch to Edit mode, select Add source on the ribbon, and then select External sources.

 

On the Select a data source page, search for and select Connect on the PostgreSQL DB (CDC) tile.

Configure and connect to PostgreSQL Database CDC

1. On the Connect page, select New connection.

   

2. In the Connection settings section, enter the following information.

   • Server: The server address of your PostgreSQL database, for example my-pgsql-server.postgres.database.azure.com.

   • Database: The database name, for example my_database.

     

3. Scroll down, and in the Connection credentials section, follow these steps.

   1. For Connection name, enter a name for the connection.

   2. For Authentication kind, select Basic.

      Napomena

      Currently, Fabric event streams supports only Basic authentication.

   3. Enter Username and Password for the database.

   4. Select Connect.

      

4. Now, on the Connect page, do these steps:

   1. For Port, enter the port number or leave the default value: 5432. If your selected cloud connection is configured in Manage connections and gateways, ensure that the port number matches the one set there. If they don't match, the port number in cloud connection in Manage connections and gateways take precedence.

   2. For table, select All tables or Enter table name(s). If you select the latter, specify tables using a comma-separated list of full table identifiers (schemaName.tableName) or valid regular expressions. For example:

      • Use dbo.test.* to select all tables whose names start with dbo.test.
      • Use dbo\.(test1|test2) to select dbo.test1 and dbo.test2. You can enter up to 100 tables, and each table name (including the schema) can be up to 128 characters.

      You can mix both formats using commas. Up to 100 tables can be entered, with each table name (including the schema name) limited to 128 characters if using full table identifiers directly.

   3. Slot name（optional): Enter the name of the PostgreSQL logical decoding slot that was created for streaming changes from a particular plug-in for a particular database/schema. The server uses this slot to stream events to Eventstream streaming connector. It must contain only lowercase letters, numbers, and underscores.

   4. Select Next at the bottom of the page.

      

5. On the Review + connect page, review the summary, and then select Add.

   

Napomena

The maximum number of sources and destinations for one eventstream is 11.

View updated eventstream

1. You can see the PostgreSQL Database CDC source added to your eventstream in Edit mode.

   

2. To implement this newly added PostgreSQL DB CDC source, select Publish. After you complete these steps, your PostgreSQL DB CDC source is available for visualization in the Live view.

   

Related content

Other connectors:

• Amazon Kinesis Data Streams
• Azure Cosmos DB
• Azure Event Hubs
• Azure Service Bus
• Azure IoT Hub
• Azure SQL Database Change Data Capture (CDC)
• Confluent Kafka
• Custom endpoint
• Google Cloud Pub/Sub
• MySQL Database CDC
• PostgreSQL Database CDC
• Sample data
• Azure Blob Storage events
• Fabric workspace event