Precise Instantiation between PostgreSQL Environments Using pg

Precise Instantiation between PostgreSQL Environments Using pg_dump

When using Oracle GoldenGate to replicate between two or more PostgreSQL compatible databases, you can use the pg_dump (pg_dumpall)/pg_restore backup utility that is included with PostgreSQL. This will create a backup or snapshot of the source database, which will be restored on the target system.

See pg_dump (pg_dumpall) and pg_restore in PostgreSQL documentation.

The following steps describe how to use this backup in conjunction with Oracle GoldenGate to ensure a precise initial instantiation, which does not require the source to be taken offline, or the use of HANDLECOLLISIONS. These steps use the Admin Client, but you can perform the same steps from Oracle GoldenGate Microservices web interface and the Rest API service endpoints.

Before you begin these steps, make sure that you have completed preparing PostgreSQL for Oracle GoldenGate. See Prepare PostgreSQL section for details.

Connect to the deployment from Admin Client:

CONNECT https://source:srvmgrport DEPLOYMENT deployment_name AS deployment_user PASSWORD deployment_password

Connect to the source PostgreSQL database using the DBLOGIN command. If you have not set up a database connection to the source database, then you can set up using the steps from Add Database Connections or using the ALTER CREDENTIALSTORE command from the Admin Client.
```
DBLOGIN USERIDALIAS alias
```
For example, if the alias is ggeast, then the command would be:
```
DBLOGIN USERIDALIAS ggeast
```
Add a checkepoint table to the source database.
```
ADD CHECKPOINTTABLE owner.table_name
```
For example:
```
ADD CHECKPOINTTABLE ggs.checkpoint
```
Create your Extract parameter file to capture the tables you want to replicate, and register a new Extract with the source PostgreSQL database using the REGISTER EXTRACT command, but do not add the Extract.
```
REGISTER EXTRACT extract_name
```
For example:
```
REGISTER EXTRACT extcdc
```
Create a temporary replication slot that will be used to create a starting point for our Extract process and for positioning the PostgreSQL snapshot. This can be done using the CREATE_REPLICATION_SLOT command. Here are the steps to configure the temporary replication slot:
1. Log into the PostgreSQL database using psql as a user with the replication role.
2. Use the replication=database option. This is the same user that you created in step 2.
  
  For example:
```
$ psql "dbname=pgsource replication=database user=ggeast" 
Password for user ggadmin: psql (16.3) 

Type "help" for help.  
pgsource=# CREATE_REPLICATION_SLOT tslot TEMPORARY LOGICAL test_decoding EXPORT_SNAPSHOT;

slot_name | consistent_point | snapshot_name | output_plugin
----------+------------------+---------------------+--------------- 
tslot | 1/4232A6B0 | 00000007-00001142-1 | test_decoding 
(1 row)
```
3. Note down the snapshot_name and the consistent_point.
Create the snapshot using the pg_dump (or pg_dumpall for clustered databases), as shown in the following example:
Note:

This command may take some time execute. It is recommended to issue this command using a nohup option to avoid the command from stopping if the session closes.
```
$ nohup /u01/pgsql-16/bin/pg_dump -h pgsource -p 5432 -U ggeast –snapshot=00000007-00001142-1 -F c -b -v -f latestdump.db pgsource &
```
The pg_dump / pg_dumpall uses the snapshot_name received from the output in step 5c. In the given example, the pg_dump option assumes that the entire database will be replicated. However, if you are replicating a subset of the database, you can dump just those objects.
Add the Extract using the ADD EXTRACT command. The Extract should be positioned at the consistent_point tracked in step 5c:
```
ADD EXTRACT extract_name, TRANLOG, LSN lsn_value
```
For example:
```
ADD EXTRACT extecdc, TRANLOG, LSN 1/42332A6B0
```
Positioning the Extract at this LSN value, ensure that the Extract will not capture any data that was included in the dump.
Add the Extract trail using the ADD EXTTRAIL command.
```
ADD EXTTRAIL trail_name, EXTRACT extract_name
```
For example:
```
ADD EXTTRAIL east/ea, EXTRACT extecdc
```
Start the Extract.
```
START EXTRACT extract_name
```

(Optional) If you need to configure a distribution path to send the trail files to another server, use the ADD DISTPATH command.

ADD DISTPATH name SOURCE source_uri TARGET target_uri

For example:

ADD DISTPATH dpecdc SOURCE trail://localhost:9002/services/v2/sources?trail=ea TARGET wss://localhost:9003/services/v2/targets?trail=ea

Restore the database on the target server using the pg_restore command. Just like in the pg_dump, if the session is lost, the restoration will fail, so it is recommended to use the nohup option. In this example, the following options were used:
```
$ nohup /u01/pgsql-16/bin/pg_restore -h pgtarget -p 6432 -U ggadmin -d pgtarget -v
latestdump.db &
```
(Optional) If you created a distribution path to a Receiver service in another deployment, you may need to use the Admin Client CONNECT command to connect to target deployment.
```
CONNECT https://target:srvmgrport DEPLOYMENT deployment_name AS deployment_user PASSWORD deployment_password
```
Connect to the target PostgreSQL database using the DBLOGIN USERIDALIAS command. If you have not set up a database connection to the source database, then you can set up using the steps from Add Database Connections or use the ALTER CREDENTIALSTORE command from the Admin Client.
Add a checkpoint table to the target database.
```
ADD CHECKPOINTTABLE ggs.chkpoint
```
Add a Replicat that will apply the changes to the target database after the restoration of the database is complete.
```
ADD REPLICAT replicat_name
```
For example:
```
ADD REPLICAT repecdc
```
Start the Replicat after the restoration has completed, as shown in the following example:
```
START REPLICAT repecdc
```