Real-Time Intelligence on Microsoft Fabric provides multiple database change data capture connectors, including SQL Database, MySQL, PostgreSQL, and Cosmos DB, allowing anyone to easily react and take action on database changes in real time. Each of the databases works differently when it comes to enabling CDC, granting permissions to users, and how they replicate database changes to Eventstream. This blog outlines some important points to keep in mind when using PostgreSQL Database CDC on Eventstream.
The PostgreSQL CDC connector is based on the Debezium connector for PostgreSQL.
Setting up this CDC source in Eventstream is covered in the documentation “ Add PostgreSQL Database CDC Source to an Eventstream “.
There are important considerations regarding the PostgreSQL database and Debezium properties that users should keep in mind when using this connector source.
Enable logical decoding
- The PostgreSQL database must have logical decoding enabled and WAL_LEVEL set to logical. The Eventstream the presentation of the school of international cooperation uses the “pgoutput” output plugin for write-ahead logs. You can check the current WAL level by running the following code on your PostgreSQL database.
User permissions
- The user that needs to be configured for CDC in Eventstream needs to have minimum replication permissions on the database. Run the following command on the h5 creative strategy and practice database:
- To work with publishing, the user needs to have administrator/superuser permission. If the user cannot be granted administrator/superuser permissions, we need to consider setting the publication.autocreate.mode property of the Debezium connector to disabled. The default option for this property is filtered on the Eventstream connector, which requires administrator permission.
Publication
- PostgreSQL works on the concept of publications in which CDC records are published. By default, the connector always tries to create a publication in the database with the name ‘dbz_publication’. A superuser/administrator on the database will by default have permissions to create/alter a publication. This also means that if multiple connectors are being used, the publication will be overwritten by clean email created later and connectors created earlier will not receive CDC events. Since they use the same publication name, connectors created later will update their list of filtered tables in the publication.
- To avoid giving higher permission to users or connector who try to create their own publication, we can set publication.autocreate.mode property to disabled.
- Setting this property to disabled means that the connector will not attempt to create “dbz_publication” and will look for an existing publication. Therefore, make sure that publications are pre-created in the database.
- Pre-creating publications in the database will provide full control to DBAs to add tables that will be attached to each publication from which CDC is extracted.
- A database administrator can create a publication and attach tables to it by running the following command:
Replication slot
- The Eventstream UI provides an optional placeholder to provide the slot name. It is recommended that you provide a mandatory replication slot name. To control which slots are being consumed by the Eventstream connector and other replication channels in the database.
- You can create a replication slot in your PostgreSQL database by running the following command.