{"id":2834,"date":"2025-03-13T15:44:30","date_gmt":"2025-03-13T15:44:30","guid":{"rendered":"https:\/\/bynatree.com\/?p=2834"},"modified":"2025-03-13T15:44:30","modified_gmt":"2025-03-13T15:44:30","slug":"real-time-data-replication-postgresql-16-%e2%86%92-kafka-%e2%86%92-sql-server-2022-express","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/03\/13\/real-time-data-replication-postgresql-16-%e2%86%92-kafka-%e2%86%92-sql-server-2022-express\/","title":{"rendered":"Real-Time Data Replication: PostgreSQL 16 \u2192 Kafka \u2192 SQL Server 2022 Express"},"content":{"rendered":"

A few days ago, we were approached by a client with a particular objective but a difficult barrier. Their goal? Real-Time Data Replication: PostgreSQL 16 \u2192 Kafka \u2192 SQL Server 2022 Express.<\/strong> They needed their data to be transferred effortlessly from PostgreSQL 16 to SQL Server 2022 Express in real time.<\/p><\/blockquote>\n

Their expanding operations demanded improved system collaboration, and delays caused by outdated data workflows could no longer be tolerated. This challenge set the stage for an innovative and efficient solution.<\/p>\n

System Configuration<\/h3>\n

Linux VM (CentOS 8.5)<\/h3>\n
    \n
  • PostgreSQL 16 (Source Database)<\/li>\n
  • Kafka 3.9.0 (Message Broker)<\/li>\n
  • Debezium Connector for PostgreSQL 3.0.6<\/li>\n
  • Kafka Connect (Handles Source and Sink Connectors)<\/li>\n
  • Kafka JDBC Sink Connector (For SQL Server Integration)<\/li>\n<\/ul>\n

    Windows Machine<\/h3>\n
      \n
    • SQL Server 2022 Express (Target Database)<\/li>\n<\/ul>\n

      Steps to Configure Real-Time Replication<\/h3>\n

      Install and Configure PostgreSQL 16<\/strong>
      \nEnable Logical Replication in PostgreSQL<\/strong>
      \nEdit postgresql.conf:<\/strong><\/p>\n

      sudo vi \/var\/lib\/pgsql\/16\/data\/postgresql.conf<\/pre>\n
      Ensure these settings:<\/p>\n
      wal_level = logical\nmax_replication_slots = 10\nmax_wal_senders = 10<\/pre>\n
      Restart PostgreSQL:<\/p>\n
      sudo systemctl restart postgresql-16<\/pre>\n
      Created a logical replication slot<\/strong> and a publication for the required tables.<\/h3>\n
      Create Replication Slot &amp; Publication<\/p>\n
      Create a logical replication slot to read the transaction logs, create publication with tables need to be replicated.<\/p>\n
      SELECT pg_create_logical_replication_slot(&#39;debezium_slot&#39;, &#39;pgoutput&#39;);\nCREATE PUBLICATION dbz_publication FOR TABLE public.employee;<\/pre>\n
      Install and Configure Kafka 3.9.0
      \nDownload &amp; Extract Kafka<\/h3>\n
      wget https:\/\/downloads.apache.org\/kafka\/3.9.0\/kafka_2.13-3.9.0.tgz\ntar -xvzf kafka_2.13-3.9.0.tgz\ncd kafka_2.13-3.9.0<\/pre>\n
      Start Zookeeper &amp; Kafka Broker<\/h3>\n
      Started both Zookeeper<\/strong> and the Kafka broker<\/strong> to handle the data pipeline.<\/p>\n
      bin\/zookeeper-server-start.sh config\/zookeeper.properties &amp;\nbin\/kafka-server-start.sh config\/server.properties &amp;<\/pre>\n
      Install Debezium PostgreSQL Connector
      \nDownload and Place in Plugins Directory<\/h3>\n
      mkdir -p kafka_2.13-3.9.0\/plugins\/debezium-postgres\ncd kafka_2.13-3.9.0\/plugins\/debezium-postgres\nwget https:\/\/repo1.maven.org\/maven2\/io\/debezium\/debezium-connector-\npostgres\/3.0.6.Final\/debezium-connector-postgres-3.0.6.Final.jar<\/pre>\n
      Register Debezium PostgreSQL Source Connector<\/h3>\n
      Registered the Debezium PostgreSQL source connector<\/strong>, linking PostgreSQL to Kafka.<\/p>\n
      Create register-postgres.json:\n{\n&quot;name&quot;: &quot;postgres-connector&quot;,\n&quot;config&quot;: {\n&quot;connector.class&quot;: &quot;io.debezium.connector.postgresql.PostgresConnector&quot;,\n&quot;database.hostname&quot;: &quot;localhost&quot;,\n&quot;database.port&quot;: &quot;5432&quot;,\n&quot;database.user&quot;: &quot;postgres&quot;,\n&quot;database.password&quot;: &quot;postgres&quot;,\n&quot;database.dbname&quot;: &quot;postgres&quot;,\n&quot;database.server.name&quot;: &quot;postgres&quot;,\n&quot;plugin.name&quot;: &quot;pgoutput&quot;,\n\n&quot;slot.name&quot;: &quot;debezium_slot&quot;,\n&quot;publication.name&quot;: &quot;dbz_publication&quot;,\n&quot;table.include.list&quot;: &quot;public.employee&quot;,\n&quot;topic.prefix&quot;: &quot;postgres&quot;,\n&quot;database.history.kafka.bootstrap.servers&quot;: &quot;localhost:9092&quot;,\n&quot;database.history.kafka.topic&quot;: &quot;schema-changes.postgres&quot;\n}\n}<\/pre>\n
      Register the connector:<\/p>\n
      curl -X POST -H &quot;Accept:application\/json&quot; -H &quot;Content-Type:application\/json&quot; \\\nhttp:\/\/localhost:8083\/connectors\/ -d @register-postgres.json<\/pre>\n
      Setup SQL Server 2022 Express<\/h2>\n
      Enable SQL Server Authentication<\/h3>\n
      1. Open SQL Server Management Studio (SSMS).
      \n2. Right-click Server Name \u2192 Properties \u2192 Security.
      \n3. Change Server Authentication to SQL Server and Windows Authentication mode.
      \n4. Restart SQL Server.<\/p>\n
      Enable sa Login<\/p>\n
      Enabled the sa login<\/strong> to provide full access<\/p>\n
      ALTER LOGIN sa ENABLE;\nALTER LOGIN sa WITH PASSWORD = &#39;YourNewStrongPassword&#39;<\/pre>\n
      Downloaded and installed the JDBC connector<\/strong>.<\/h3>\n
      Install and Configure Kafka JDBC Sink Connector for SQL Server
      \nDownload JDBC Connector<\/p>\n
      mkdir -p kafka_2.13-3.9.0\/plugins\/jdbc-sink\ncd kafka_2.13-3.9.0\/plugins\/jdbc-sink\nwget https:\/\/packages.confluent.io\/maven\/io\/confluent\/kafka-connect-jdbc\/10.8.0\/kafka-connect-\njdbc-10.8.0.jar<\/pre>\n
      Register Kafka JDBC Sink Connector<\/h3>\n
      Create a table called staff in the destination SQL server database and give \u201ctable.name.format\u201d for
      \nthe same. This will replicate the data from employee table in PostgreSQL to staff in SQL server.<\/p>\n
      Create register-mssql-sink.json:\n{\n&quot;name&quot;: &quot;mssql-sink-connector&quot;,\n&quot;config&quot;: {\n&quot;connector.class&quot;: &quot;io.confluent.connect.jdbc.JdbcSinkConnector&quot;,\n&quot;tasks.max&quot;: &quot;1&quot;,\n&quot;topics&quot;: &quot;postgres.public.employee&quot;,\n&quot;connection.url&quot;: &quot;jdbc:sqlserver:\/\/192.168.0.102:1433;databaseName=kafka_db;encrypt=false&quot;,\n&quot;connection.user&quot;: &quot;sa&quot;,\n&quot;connection.password&quot;: &quot;YourNewStrongPassword&quot;,\n&quot;auto.create&quot;: &quot;true&quot;,\n&quot;auto.evolve&quot;: &quot;true&quot;,\n&quot;insert.mode&quot;: &quot;upsert&quot;,\n&quot;pk.fields&quot;: &quot;id&quot;,\n&quot;pk.mode&quot;: &quot;record_value&quot;,\n&quot;table.name.format&quot;: &quot;staff&quot;,\n&quot;dialect.name&quot;: &quot;SqlServerDatabaseDialect&quot;,\n&quot;batch.size&quot;: &quot;1000&quot;,\n&quot;delete.enabled&quot;: &quot;true&quot;,\n&quot;transforms&quot;: &quot;unwrap&quot;,\n&quot;transforms.unwrap.type&quot;: &quot;io.debezium.transforms.ExtractNewRecordState&quot;,\n&quot;transforms.unwrap.drop.tombstones&quot;: &quot;false&quot;,\n&quot;transforms.unwrap.delete.handling.mode&quot;: &quot;drop&quot;\n}\n}<\/pre>\n
      Register the connector:<\/h3>\n
      Registered the Kafka JDBC sink connector<\/strong>, mapping the data flow to SQL Server.<\/p>\n
      curl -X POST -H &quot;Accept:application\/json&quot; -H &quot;Content-Type:application\/json&quot; \\\nhttp:\/\/localhost:8083\/connectors\/ -d @register-mssql-sink.json<\/pre>\n
      Start Zookeeper, Kafka Broker, and Kafka Connect:<\/h3>\n
      Launched Zookeeper<\/strong>, the Kafka Broker<\/strong>, and Kafka Connect<\/strong>, ensuring all components worked in harmony.<\/p>\n
      bin\/zookeeper-server-start.sh config\/zookeeper.properties &amp;\nbin\/kafka-server-start.sh config\/server.properties &amp;\nbin\/connect-distributed.sh config\/connect-distributed.properties &amp;<\/pre>\n
      What we achieved was nothing short of extraordinary: a real-time replication system that seamlessly integrated PostgreSQL and SQL Server. The once-tedious manual operations were automated and streamlined. By connecting these technologies, we provided a solution that not only met, but fulfilled, the client’s expectations.<\/p>\n","protected":false},"excerpt":{"rendered":"
      A few days ago, we were approached by a client with a particular objective but a difficult barrier. Their goal? Real-Time Data Replication: PostgreSQL 16 \u2192 Kafka \u2192 SQL Server 2022 Express. They needed their data to be transferred effortlessly from PostgreSQL 16 to SQL Server 2022 Express in real time. Their expanding operations demanded…<\/p>\n","protected":false},"author":1,"featured_media":2835,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[82,96,133,135,136,141,170,187,266,302,310,322,336],"class_list":["post-2834","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-bigdata","tag-cloudcomputing","tag-dataintegration","tag-datapipeline","tag-datareplication","tag-debezium","tag-innovation","tag-kafka","tag-postgresql","tag-realtimedata","tag-scalablesystems","tag-sqlserver","tag-techsolutions","category-28","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2834","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2834"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2834\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2835"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2834"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2834"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2834"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}