Upgrading Oracle GoldenGate for PostgreSQL

Learn the steps to upgrade to the latest version of Oracle GoldenGate Microservices Architecture for PostgreSQL.

The Oracle GoldenGate CDC Capture support from PostgreSQL relies on using replication slots on the PostgreSQL database to read the change records from WAL. The replication slot name, used by each CDC Extract, depends on the Oracle GoldenGate installation path.

You may want to use the same Extract from the exiting Oracle GoldenGate environment to continue capturing the change records in the new Oracle GoldenGate environment, after the upgrade although Extract has not finished capturing the change records till the time of upgrade. However, this will not be possible if a new Oracle GoldenGate installation directory is used for the upgrade. You must use the existing Oracle GoldenGate installation directory to make this work.

To upgrade the older Oracle GoldenGate version or migrate from Oracle GoldenGate Classic Architecture to Microservices Architecture, use the following steps:

Note:

The following steps use an Extract name extn as an example.

Obtain the replication slot name that was being used by the Oracle GoldenGate CDC Extract in the older environment. The old replication slot name can be obtained using from the Extract report file or by running the INFO EXTRACT command.
From Extract report file in the older Oracle GoldenGate environment. Following is the example snippet from the Extract report file:
```
2024-01-09 11:49:38  INFO    OGG-25376  Oracle GoldenGate capture 'EXTN' running with replication slot 'ext1_5d87a7db5e810943', slot type 'logical', plugin name
as 'test_decoding' attached with database 'postgres' with slot restart LSN as '6/3CBFE2B8', flush LSN as '6/3CBFE2F0' and its current TXID as '7899713'.
```
Note the replication slot name from the Extract report file. In this example it is extn_5d87a7db5e810943.
You can also run the INFO EXTRACT command from GGSCI or Admin Client in the older Oracle GoldenGate environment, as shown in the following example:
```
GGSCI (phoenix96567 as postgres@pg12) 10> info extract extn
```
Output:
```
Extract    EXT1      Last Started 2024-01-09 11:49   Status RUNNING
Checkpoint Lag       00:00:45 (updated 00:00:01 ago)
Process ID           3640439
VAM Read Checkpoint  2024-01-09 11:48:52.575833
Replication Slot     extn_5d87a7db5e810943 is active with PID 3640450 in database postgres
Slot Restart LSN     6/3CBFE2B8
Slot Flush LSN       6/3CBFE2F0
Current Log Position 6/3CBFE2F0
```
Obtain the replication slot name that is relevant for the Oracle GoldenGate CDC Extract in the newer Oracle GoldenGate environment. Run the UNREGISTER EXTRACT command to obtain the new replication slot name:
```
unregister extract extn
```
Output:
```
2024-01-09 11:43:37  INFO    OGG-25354  The replication slot 'extn_6ec0db7cc27c812' for group 'EXTN' does not exist in database 'postgres'.
```
Note the replication slot name from the error message. In this example it is extn_6ec0db7cc27c812.

Run the following SQL query on the corresponding source PostgreSQL database (PSQL can be used):

SELECT * FROM pg_copy_logical_replication_slot('old-replication-slot', 'new-replication-slot');

For example:

SELECT * FROM pg_copy_logical_replication_slot('extn_5d87a7db5e810943', 'extn_6ec0db7cc27c812');

Make sure to copy the Extract checkpoint files, trail files, and other configuration files from old Oracle GoldenGate environment to the new environment.
Check and confirm that the LSN postitions of new-replication-slot is same as the old-replication-slot.
Run the UNREGISTER EXTRACT extract_name command in the old Oracle GoldenGate environment. For example:
```
UNREGISTER EXTRACT extn
```
Start the Extract in the new Oracle GoldenGate environment to resume capturing of change records.