Building a Real-Time Banking Data Platform: From PostgreSQL to Kafka via Oracle GoldenGate
How to eliminate nightly batch jobs and stream core banking transactions with sub-10 second latency.
TL;DR: Building a real-time Change Data Capture (CDC) system from PostgreSQL to Kafka requires configuring a Logical Replication Slot, a GoldenGate Extract process to collect Write-Ahead Logs (WAL), and a Replicat process to push the data. This architecture helps transition core banking models from slow batch processing to high-speed, event-driven data streams without straining the source database.
What Is Change Data Capture (CDC) with Oracle GoldenGate?
Oracle GoldenGate Change Data Capture (CDC) is a mechanism that captures and extracts row-level data changes (INSERT, UPDATE, DELETE) from a source database in real-time to synchronize them to target systems.
Instead of running full-table scan queries that overload the system, GoldenGate connects directly to PostgreSQL’s Logical Replication mechanism. It reads the underlying Write-Ahead Logging (WAL) files, decodes these changes, and transports them with millisecond latency.
Why Does Streaming Data Matter?
Most traditional core banking systems still operate on a batch model: transaction data is dumped from the OLTP system to a Data Warehouse at midnight. As a result, all reports, fraud detection alerts, or branch manager dashboards the next morning reflect yesterday’s numbers. Transitioning to a CDC streaming architecture completely eliminates this latency.
How Does the System Architecture Work?
The data flow architecture of Oracle GoldenGate 26ai (Microservices Architecture) in this scenario consists of 4 core components:
Logical Replication Slot (At Source PostgreSQL): Why is it needed? By default, PostgreSQL overwrites or deletes Write-Ahead Logs (WAL) files as soon as the data is safely flushed to disk to save space. If the CDC system is slow or disconnected, WALs might be deleted before being read, resulting in data loss. The Replication Slot solves this problem: it acts as an “anchor,” forcing PostgreSQL to retain the WAL until the client (GoldenGate) confirms it has consumed them. Simultaneously, through an output plugin (like
pgoutput), it decodes the physical binary WAL format into logical events (INSERT/UPDATE/DELETE).Extract Process (Collection): Extract acts as a logical decoding client connecting directly to the PostgreSQL Replication Slot. It receives Logical Change Records (LCRs), normalizes Postgres-specific data types into a common format, and writes them to the Trail File.
Trail File (Local Buffer): These are binary files that store transactions sequentially. The Trail File acts as a crucial buffer to decouple the read speed of the Extract and the write speed of the Replicat. If Kafka goes down, the Extract continues reading the WAL and frees up disk space for Postgres, safely storing the pending data in the Trail File.
Replicat Process (Big Data Sync): Using GoldenGate for Big Data, the Replicat acts as a Kafka Producer. It sequentially reads the Trail Files, converts the data into JSON or Avro structures, and pushes the messages to the corresponding topics on Apache Kafka.
Practical Guide: Building the CDC Pipeline
Below are the detailed steps to configure the system from the source PostgreSQL to the target Kafka.
