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.
Note
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:
On your Azure Database for PostgreSQL Flexible Server page in the Azure portal, select Server parameters in the navigation menu.
On the Server parameters page:
- Set wal_level to logical.
- Update the max_worker_processes to at least 16.
Save the changes and restart the server.
Confirm that your Azure Database for PostgreSQL Flexible Server instance allows public network traffic.
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.
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
On the Connect page, select New connection.
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.
Scroll down, and in the Connection credentials section, follow these steps.
For Connection name, enter a name for the connection.
For Authentication kind, select Basic.
Note
Currently, Fabric event streams supports only Basic authentication.
Enter Username and Password for the database.
Select Connect.
Now, on the Connect page, do these steps:
For table, Enter a table name.
For Port, enter the port number or leave the default value: 5432.
Select Next at the bottom of the page.
On the Review + connect page, review the summary, and then select Add.
Note
The maximum number of sources and destinations for one eventstream is 11.
View updated eventstream
You can see the PostgreSQL Database CDC source added to your eventstream in Edit mode.
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: