Configuring an Initial Synchronization for a PostgreSQL Source Database using Precise Instantiation

19 Configuring an Initial Synchronization for a PostgreSQL Source Database using Precise Instantiation

Data synchronization from a source PostgreSQL database to an Oracle GoldenGate target can be accomplished with the optional method of using precise instantiation. This method was introduced with Oracle GoldenGate 21c (21.8.0).

Precise instantiation has the advantage of not requiring any collision handling in the target Replicat. This is important for targets that do not support collision handling, such as flat files. This method uses a database snapshot to synchronize the output of the initial load Extract with the starting position of the Change Data Capture Extract. This snapshot is managed by the initial load Extract, so it is not possible for multiple initial load Extracts to use the same snapshot. Therefore, this method is not supported when using multiple intial load Extracts to parallelize the workload.

The following example uses the Admin Client within Microservices Architecture. It is assumed that you are familiar with Oracle GoldenGate and have setup the source and target databases correctly, with all required prerequisites. These steps require a minimum of Oracle GoldenGate 21c (21.8.0) or higher.

Perform the following steps to set up end-to-end initial load and synchronization processes using the precise instantiation method:

Register a Change Data Capture (CDC) Extract with the source PostgreSQL database.
```
DBLOGIN USERIDALIAS src_alias
REGISTER EXTRACT extecdc
```
In this example, extecdc is the Extract name. For Classic Architecture installations, use the DBLOGIN SOURCEDB command with USERIDALIAS or USERID and PASSWORD.
Create an initial load Extract.
```
ADD EXTRACT extinit, SOURCEISTABLE
EDIT PARAMS extinit
```
The initial load Extract parameter file must contain the INITIALLOADOPTIONS USESNAPSHOT parameter. For example:
```
EXTRACT extinit
INITIALLOADOPTIONS USESNAPSHOT
SOURCEDB USERIDALIAS src_alias
EXTFILE ./dirdat/ei, MEGABYTES 500, PURGE
TABLE public.*;
```
See INITIALLOADOPTIONS to learn about the usage of this parameter with the USESNAPSHOT option.
Start the initial load Extract.
```
START EXTRACT extinit
```
When the initial load Extract has completed and stopped, review its report file to determine the positioning LSN to be used by the CDC Extract.
For example, in the following output, the positioning LSN to be used by the CDC Extract will be ‘0/173F770’.
```
INFO    OGG-100001 A consistent point is
established in database 'tpcc' using replication slot ogg_initx_1234 at LSN 0/173F770
and snapshot name '00000003-00000026-1'.
```
```
INFO    OGG-100002 Create or position the CDC
extract to LSN 0/173F770. Example: ADD EXTRACT <cdc-extract> TRANLOG LSN 0/173F770
or ALTER EXTRACT <cdc-extract> LSN 0/173F770.
```

Create and start an initial load Replicat that reads the trail from the initial load Extract.

DBLOGIN USERIDALIAS tgt_alias

ADD CHECKTPOINTTABLE ggs.checkpoint

ADD REPLICAT repinit, EXTTRAIL ./dirdat/ei, CHECKPOINTTABLE ggs.ggcheckpoint

START REPLICAT repinit

Here is an example of the initial load Replicat parameter file:

REPLICAT repinit
TARGETDB USERIDALIAS tgt_alias
END RUNTIME
BATCHSQL
MAP public.*, TARGET public.*;

Add and start the CDC Extract (extecdc) using the consistent LSN value referred to in the initial load Extract report file.
```
ADD EXTRACT extecdc, TRANLOG, LSN 0/173F770
```
```
ADD EXTTRAIL ea, EXTRACT extecdc
```
```
START EXTRACT extecdc
```
Here is an example of a CDC Extract parameter file:
```
EXTRACT extecdc
SOURCEDB USERIDALIAS src_alias
EXTTRAIL ./dirdat/ea
TABLE public.*;
```
When the initial load Replicat completes and stops, add and start a CDC Replicat that reads the trail from the CDC Extract.
```
ADD REPLICAT repecdc, EXTTRAIL ./dirdat/ea, CHECKPOINTTABLE ggs.ggcheckpoint
```
```
START REPLICAT repecdc
```
Monitor the lag in both the CDC Extract and the CDC Replicat, and when they are both close to zero seconds, then the data stream from source to target database should be close to real-time.

Parent topic: Using Oracle GoldenGate for PostgreSQL