Introduction
In today’s real-time, analytics-driven world, businesses can’t afford stale or inconsistent data—especially when applications span multiple platforms or regions. PostgreSQL is one of several popular choices for transactional workloads but replicating data from PostgreSQL to other databases—like Oracle—without downtime or data loss is no small feat.
That’s where Oracle GoldenGate comes in, offering high-speed, low-latency replication from homogeneous (Like to like) and heterogeneous environments—from PostgreSQL to a wide range of target databases.
This guide uses a PostgreSQL source and an Oracle target to provide a clean and consistent instantiation strategy using Oracle GoldenGate’s initial load feature. This approach captures a snapshot of PostgreSQL data while simultaneously preparing to replicate ongoing changes in real time — ensuring minimal downtime and data consistency during cutover.
This capability was introduced in Oracle GoldenGate 21c (version 21.8.0). To follow the steps in this guide, ensure you are using Oracle GoldenGate 21.9.0 or later—ideally, Oracle GoldenGate 23.9.0 or later for the source PostgreSQL database.
Prerequisites for PostgreSQL to Oracle Replication using Oracle GoldenGate
Before kicking off the instantiation process, both the source (PostgreSQL) and target (Oracle) environments need to be prepared. Skipping these steps often leads to setup failures, inconsistent data, or unsupported configuration errors.
Here’s what must be in place:
- Install Oracle GoldenGate (Microservices Architecture):
Download and install the latest patch release of Oracle GoldenGate 23ai (Microservices Architecture) for both PostgreSQL and Oracle platforms.
Refer to Oracle GoldenGate Installation Guide for platform-specific instructions.
For the latest OGG versions and patch bundles, refer to this blog: How to Obtain the Latest Oracle GoldenGate Microservices Architecture Bundle Patches.
- Create GoldenGate Deployments:
Set up deployments for both PostgreSQL (source) and Oracle (target). If using a hub deployment, a single Service Manager can manage both.
Deployment configuration steps can be found in the GoldenGate Microservices Configuration Guide.
- Database Configuration
- Prepare PostgreSQL Source DB for Oracle GoldenGate. See the following Oracle documentation
Oracle GoldenGate documentation
- Prepare Oracle DB for Oracle GoldenGate. See the following Oracle Documentation
Oracle GoldenGate documentation
Once these prerequisites are in place, you’re ready to begin the snapshot + real-time replication setup — starting with instantiating the initial data from PostgreSQL to Oracle.
GoldenGate Pre-Instantiation Steps for PostgreSQL to Oracle Replication
When setting up Oracle GoldenGate for PostgreSQL-to-Oracle replication, understanding your table metadata isn’t just helpful — it’s essential. Skipping this step can lead to performance bottlenecks, replication errors, and unpredictable sync behaviour, especially during initial load.
Before instantiating data from PostgreSQL, it’s important to:
Estimate Data Volume
Get row counts and table sizes to assess the scale of replication. This helps estimate:
- Initial load duration
- Required system resources
- Network and I/O impact
SELECT n.nspname AS SchemaName,
c.relname AS TableName,
ROUND(pg_total_relation_size(c.oid) / 1024.0 / 1024 / 1024, 2) AS TableSizeGB
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname = 'public'
ORDER BY TableSizeGB DESC;
Query 1 : To retrieve Table size
Review Key Constraints
Primary and unique keys help GoldenGate identify and replicate rows accurately. If a table lacks a key, GoldenGate defaults to using all columns to uniquely identify rows. In cases where a logical key exists—one that ensures uniqueness and is indexed—you can optionally use the KEYCOLS parameter to define it explicitly.
-- Query for Source PostgreSQL Database
SELECT
t.relname AS TableName,
i.relname AS IndexName,
ix.indisunique AS UC,
ix.indisprimary AS PK
FROM
pg_class t
JOIN
pg_index ix ON t.oid = ix.indrelid
JOIN
pg_class i ON i.oid = ix.indexrelid
JOIN
pg_namespace n ON n.oid = t.relnamespace
WHERE
n.nspname = 'public' -- Change to your schema if needed
ORDER BY
t.relname;
Query 2 : To retrieve Table Constraints for Source PostgreSQL Database
-- Query for Target Oracle Database
SELECT
t.table_name AS TableName,
i.index_name AS IndexName,
DECODE(i.uniqueness, 'UNIQUE', 'YES', 'NO') AS UC,
DECODE(c.constraint_type, 'P', 'YES', 'NO') AS PK
FROM
all_tables t
JOIN
all_indexes i ON t.table_name = i.table_name AND t.owner = i.table_owner
LEFT JOIN
all_constraints c ON t.table_name = c.table_name
AND c.index_name = i.index_name
AND c.constraint_type = 'P'
AND c.owner = i.table_owner
WHERE
t.owner = 'SOE' -- Change to your schema if needed
ORDER BY
t.table_name;
Query 3 : To retrieve Table Constraints for Target Oracle Database
Check Foreign Key Dependencies
While foreign keys aren’t directly used by GoldenGate for replication logic, they play an important role during instantiation and application logic. On the target side, foreign key constraints should typically be disabled during initial load to avoid errors. Additionally, when using Parallel Replicat, foreign keys can help enforce correct transactional ordering, ensuring consistency across dependent tables.
On the source side, understanding foreign key relationships is helpful when distributing tables across multiple Extracts to avoid referential integrity issues during processing.
-- Query for Source PostgreSQL Database
SELECT
tc.table_name AS ParentTable,
ccu.table_name AS ChildTable,
tc.constraint_name AS ForeignKeyConstraint
FROM
information_schema.table_constraints AS tc
JOIN
information_schema.constraint_column_usage AS ccu
ON tc.constraint_name = ccu.constraint_name
AND tc.constraint_schema = ccu.constraint_schema
WHERE
tc.constraint_type = 'FOREIGN KEY'
AND tc.constraint_schema = 'public' -- Replace with your schema
ORDER BY
ParentTable;
Query 4 : To retrieve Foreign Key Constraints for Source PostgreSQL Database
-- Query for Target Oracle Database
SELECT
ac_r.table_name AS ParentTable,
ac.table_name AS ChildTable,
ac.constraint_name AS ForeignKeyConstraint
FROM
all_constraints ac
JOIN
all_constraints ac_r ON ac.r_constraint_name = ac_r.constraint_name
AND ac.r_owner = ac_r.owner
WHERE
ac.constraint_type = 'R'
AND ac.owner = 'SOE_TARGET' -- Replace with your schema or use a bind variable
ORDER BY
ParentTable;
Query 5 : To retrieve Foreign Key Constraints for Target Oracle Database
In our case, the sample schema contains no foreign key constraints, which simplifies replication and avoids ordering conflicts.
Source Side Instantiation Steps of GoldenGate for PostgreSQL
In this section, we’ll complete the required setup steps on the PostgreSQL source side of the GoldenGate replication pipeline.
Add Trandata
Once the source PostgreSQL connection is established, you must enable table-level supplemental logging. PostgreSQL provides this capability through the REPLICA IDENTITY setting, which controls what information is written to the Write-Ahead Log (WAL) during UPDATE and DELETE operations. GoldenGate relies on this logged data to identify and propagate changes downstream.
There are four levels of table-level logging in PostgreSQL, which correspond to the REPLICA IDENTITY setting:
- NOTHING: No old row data is logged. Replication of updates or deletes is not possible.
- USING INDEX: Logs only the columns from a specified unique index.
- DEFAULT: Logs primary key columns (or a unique index if present).
- FULL: Logs the entire old row image, regardless of keys.
For typical uni-directional GoldenGate replication, the default setting of ADD TRANDATA is usually sufficient. It automatically includes primary key, unique key, and foreign key columns, providing the necessary information for reliable replication and optimal performance with Parallel Replicat.
However, in more complex scenarios—such as replicating tables without keys, implementing conflict detection, or supporting bi-directional replication—it is a best practise to configure the table with REPLICA IDENTITY FULL. This ensures that all column values are logged, enabling GoldenGate to accurately capture the full before-and-after state of each row.
Use FULL logging selectively and only where needed, as it can increase WAL volume and impact performance.
Register a Change Data Capture (CDC) Extract with the Source PostgreSQL database.
Before we begin the Initial Load Extract, we first set up the Change Data Capture Extract process. This Extract will eventually capture all ongoing changes (INSERTs, UPDATEs, DELETEs) from the source PostgreSQL database after the initial data has been copied to the target.
However, we do not start the Extract just yet. Because we want to ensure a precise and consistent starting point — one that aligns exactly with GoldenGate Initial Load Extract. Starting the Extract before starting the initial load Extract could result in missed or duplicated changes, leading to data drift on the target.
EXTRACT CDCSOE USERIDALIAS PG_Ashburn_SOE, DOMAIN OracleGoldenGate EXTTRAIL s1 -- Every 60 minutes, report the count of records -- processed since Extract start to the process -- report file. -- Rate reports the number of operations per second -- The report file also contains DELTA throughput which is -- the number of records since the last report divided by -- the time since the last report REPORTCOUNT EVERY 60 MINUTES, RATE -- Select from all tables in the public schema. TABLE public.*;
Parameter File 1 : CDC Extract Parameter File
Create and Start Initial Load Extract with the source PostgreSQL database
Next create the initial load Extract with INITIALLOADOPTIONS USESNAPSHOT parameter. Start the Initial Load Extract. When it completes reading from every table it will automatically shut down.
Open the Initial Load Extract report file and scroll to the last page to see the instantiation LSN and the number of rows read from each table. The instantiation LSN message will look like the one shown below.
2025-07-22 14:20:42 INFO OGG-05378 A consistent point is established in database 'soe' using replication slot ogg_INISOE_3325950 at LSN F/3F3F52B8 and snapshot name 0000000E-00000193-1. 2025-07-22 14:20:42 INFO OGG-05379 Create or position a Change Data Capture Extract to LSN F/3F3F52B8. Example: ADD EXTRACT <Extract-name> TRANLOG LSN F/3F3F52B8 or ALTER EXTRACT <Extract-name> LSN F/3F3F52B8.
EXTRACT INISOE USERIDALIAS PG_Ashburn_SOE, DOMAIN OracleGoldenGate -- Create 2Gb initial load trail files. -- Purge if the file exists EXTFILE IL Megabytes 2000 Purge -- Use precise instantiation INITIALLOADOPTIONS USESNAPSHOT -- Every 60 minutes, report the count of records processed since -- Extract starts to the process report file. -- Rate reports the number of operations per second -- The report file also contains DELTA throughput which is -- the number of records since the last report divided by -- the time since the last report REPORTCOUNT EVERY 60 MINUTES, RATE -- Select from all tables in the dbo schema. TABLE public.*;
Parameter File 2 : Initial Load Extract Parameter File
Start Primary CDC Extract.
Once the Initial load Extract is completed , you can now start the CDC Extract with the LSN tracked in Initial Load Extract report file.
Create Distribution Path
Create and start two Distribution Paths from the source Oracle GoldenGate server to the target Oracle GoldenGate server, as specified in the Oracle GoldenGate documentation, one for the instantiation trail data and another for the CDC trail data.
Target Side Instantiation Steps of GoldenGate for Oracle
While this demonstration uses Oracle Database as the target, the same process can be applied when replicating from PostgreSQL to SQL Server, MySQL, DB2, or any other GoldenGate-supported destination.
If you are unfamiliar with how to create Replicats, please refer to the Oracle GoldenGate documentation.
Create Initial Load Replicat
Before proceeding, it’s important to understand how Oracle GoldenGate handles Initial Load Trail data and how it impacts downstream processing:
- The Initial Load Extract reads data directly from the source tables, meaning no transactional metadata (like BEGIN/COMMIT records) is written to the Initial Load Trail files.
- Oracle GoldenGate does not use a dedicated Initial Load Replicat. Instead, the CDC Replicats are responsible for applying the data captured during the Initial Load.
Since CDC Replicats rely on transaction boundaries in the trail files to group operations into transactions, Oracle GoldenGate generates a synthetic (fake) transaction ID for the Initial Load data.
This ID is derived from:
- The trail file sequence number,
- The relative byte address (RBA)
- The timestamp of the first record in the trail
A synthetic transaction is considered complete under one of the following conditions:
- End-of-file (EOF) is reached in each trail being processed, or
- The number of records read by Replicat reaches the value set by the MAXTRANSOPS parameter (default: 10,000,000 records)
Because the entire Initial Load data may be treated as a single large transaction, it’s critical to adjust the Replicat configuration, especially when using Parallel Replicat. If not properly tuned:
- Replicat may appear to be hung or stalled,
- No statistics will be reported, and
- No checkpoints will be recorded until the synthetic transaction is committed.
To avoid these issues, review and adjust settings such as SPLIT_TRANS_RECS and BATCHSQL.
REPLICAT RINIT USERIDALIAS ashburn_dbcs_pdb1 DOMAIN OracleGoldenGate -- Set minimum number of Appliers to 1 and spawn -- up to 6 to improve initial load performance. -- When everything has been applied, only 1 Applier -- will be running, signalling initial load is complete. MIN_APPLY_PARALLELISM 1 MAX_APPLY_PARALLELISM 6 -- Every 60 Minutes, report the count of records processed since -- Extract starts to the process report file. -- Rate reports the number of operations per second -- The report file also contains DELTA throughput which is -- the number of records since the last report divided by -- the time since the last report REPORTCOUNT EVERY 60 MINUTES, RATE -- Organize similar SQL statements into arrays and apply -- them at an accelerated rate. BATCHSQL -- Large transactions should be broken into pieces of -- specified size and applied concurrently. SPLIT_TRANS_RECS 10000 MAP PUBLIC.*, TARGET DBCSASH_PDB1.SOE_TARGET.*;
Parameter File 3 : Initial Load Replicat Parameter File
Start the Replicat. Note, the Replicat will not stop when it has completed applying the Initial Load Data. There are a couple of ways to determine if instantiation is complete.
- Check Replicat Checkpoint vs. Trail File Size
On the GoldenGate Hub Server, list the trail files used for initial load and compare the last trail sequence number and size with Replicat’s checkpoint sequence number and RBA (from the Web UI).
]$ ls -lrt IL* -rw-r-----. 1 opc opc 2041669605 Jul 22 14:21 IL000000 -rw-r-----. 1 opc opc 2041669353 Jul 22 14:21 IL000001 -rw-r-----. 1 opc opc 2041669432 Jul 22 14:22 IL000002 -rw-r-----. 1 opc opc 2041669457 Jul 22 14:22 IL000003 -rw-r-----. 1 opc opc 2041669524 Jul 22 14:22 IL000004 -rw-r-----. 1 opc opc 2041669604 Jul 22 14:23 IL000005 -rw-r-----. 1 opc opc 2041669542 Jul 22 14:23 IL000006 -rw-r-----. 1 opc opc 2041669479 Jul 22 14:24 IL000007 -rw-r-----. 1 opc opc 1608599889 Jul 22 14:24 IL000008
- Compare Extract and Replicat Statistics
Review the Initial Load Extract report to see how many rows were fetched per table. Then, check Replicat statistics to verify the same row counts. Matching numbers indicate that the initial load completed successfully.
Output to IL:
From table public.addresses:
# inserts: 7500025
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table public.card_details:
# inserts: 7500022
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table public.customers:
# inserts: 5000022
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table public.inventories:
# inserts: 900344
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table public.logon:
# inserts: 11914949
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table public.order_items:
# inserts: 35559979
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table public.orderentry_metadata:
# inserts: 4
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table public.orders:
# inserts: 7149015
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table public.product_descriptions:
# inserts: 1000
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table public.product_information:
# inserts: 1000
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
From table public.warehouses:
# inserts: 1000
# updates: 0
# deletes: 0
# upserts: 0
# discards: 0
If the record counts reported by Initial Load Extract and Replicat match, instantiation is complete.
Create CDC Replicat
Once instantiation is complete, create a CDC (Change Data Capture) Replicat to apply both queued and real-time changes.
REPLICAT RSOE USERIDALIAS mssql_ashburn DOMAIN OracleGoldenGate -- Every 60 minutes, report the count of records processed since -- Extract starts to the process report file. -- Rate reports the number of operations per second -- The report file also contains DELTA throughput which is -- the number of records since the last report divided by -- the time since the last report REPORTCOUNT EVERY 60 MINUTES, RATE -- Organize similar SQL statements into arrays and apply -- them at an accelerated rate. BATCHSQL -- Large transactions should be broken into pieces of -- specified size and applied concurrently. SPLIT_TRANS_RECS 10000 MAP PUBLIC.*, TARGET DBCSASH_PDB1.SOE_TARGET.*;
Parameter File 4 : CDC Replicat Parameter File
To verify that the source and target data are synchronized, you can use the Oracle GoldenGate Veridata product or use your own scripts to select and compare source and target data.
Conclusion
In this article we presented detailed steps to perform precise instantiation of an Oracle target database from a source PostgreSQL database using Oracle GoldenGate. To learn more about Oracle GoldenGate functionality, you may refer to:
- The Oracle Integration Services GoldenGate web site
- The Oracle Data Integration blog site.
- The OracleGoldenGate YouTube channel.