Capturing Transaction Log Data from a PostgreSQL Server database

To configure the database and CDC extract process on Oracle GoldenGate for a PostgreSQL database, follow the below steps.

Creating Users and Privileges

Oracle GoldenGate recommends having a separate user for it. This can be the same user for all Oracle GoldenGate processes that must connect to a database. For more information, see Prepare Database Users and Privileges in the Oracle GoldenGate Microservices Documentation.

Creating Credential in Oracle GoldenGate

Create the credential in GoldenGate Administration Service.

Open the Console page of Administration Service.
Select DB Connections.
Select Add DB Connections (plus icon) to add the database credentials.
Enter the Credential Domain.
Enter the Credential Alias.
Enter the Data Source Name.
Enter the User ID.
Enter the Password.
Select Submit.
After creating the credential, select Log in database icon. This ensures that the newly created credential is able to connect to the target database.

Creating the GoldenGate CDC Extract

Create and run the CDC capture for PostgreSQL.

Open the Console page of Administration Service.
Select Add Extracts (plus icon).
Select the extract type as Change Data Capture Extract
Enter the process name in the Process Name field.
Choose the appropriate Credential Domain from the drop-down list.
Choose the appropriate Credential Alias from the drop-down list.
Select Next.
Select Now from the drop-down list in the Begin field.
Enter the Trail Name. It can be a combination of any two alphabetic characters.
Enter the Trail Subdirectory if customization of the Trail Subdirectory is needed.

The trail subdirectory can be the full path of any directory. This directory must already exist in the file system.
Set the Trail Size (in MB).

Note: In case the record generation rate of GoldenGate is low (less than 50 records per second), then it is recommended to set the Trail Size to lower values. For example, 100MB.
Do not modify the Trail Sequence and Trail Offset.
Select Next.
In the Parameter File subsection, enter the below parameters:
```
EXTRACT <extract name>
SOURCEDB <data source name> USERIDALIAS <credential alias>, DOMAIN <credential domain>
OUTPUTFORMAT XML _AUDIT_VAULT
exttrail <sub directory>/<trail name>
TABLE <schema>.<table name>, GETBEFORECOLS(ON UPDATE KEYANDMOD);
```
For example:
```
EXTRACT exta
SOURCEDB pg_dsn USERIDALIAS pg_foouser, DOMAIN OracleGoldenGate
OUTPUTFORMAT XML _AUDIT_VAULT
exttrail /foo/bar/aa
TABLE HR.*, GETBEFORECOLS(ON UPDATE KEYANDMOD );
```
Note the following parameter guidelines:
- There is space between XML and _AUDIT_VAULT in the OUTPUTFORMAT parameter.
- The OUTPUTFORMAT parameter must be mentioned before the exttrail parameter in the parameter file. Otherwise, the XML files are not generated.
- Ensure the TABLE command always ends with a semicolon (;).
- Ensure the sequence of all the parameters is in the exact order as mentioned above.
- The TABLE command is used to specify the tables for which DML changes need to be captured.
- To get more information about Oracle GoldenGate parameters, see Oracle GoldenGate Parameters in the Reference for Oracle GoldenGate guide.
Select Create and Run to start the CDC Extract process.

The newly created CDC Extract appears in the Extracts section on the Administration Service page.
To view the status of the CDC Extract:
1. Select Actions for the extract.
2. Select Details.
3. Select Report to view the diagnostic messages.
  
  If the extract process fails, this report displays the relevant errors.

Sample Oracle GoldenGate CDC Extract Parameter Files

Use these Oracle GoldenGate CDC Extract parameter files as samples.

Audit DML with GETBEFORECOLS option

The following parameter file configures the Extract process to capture DML operations on a specific table with the GETBEFORECOLS option enabled. This option ensures that key columns appear in the before image of the audit file generated by Oracle GoldenGate, which is essential for displaying key columns in reports for update and delete operations.

EXTRACT <extract name>
SOURCEDB <data source name> USERIDALIAS <credential alias>, DOMAIN <credential domain>
OUTFORMAT XML _AUDIT_VAULT
EXTTRAIL <subdirectory>/<trail name>
TABLE <schema>.<table name>, GETBEFORECOLS(ON UPDATE KEYANDMOD );

Example: The following parameter file audits DML operations on the dbo.employee table. The audit data will be stored in the /foo/bar/ location:

EXTRACT exta
SOURCEDB pg_dsn USERIDALIAS pg_foouser, DOMAIN OracleGoldenGate
OUTPUTFORMAT XML _AUDIT_VAULT
exttrail /foo/bar/aa
TABLE dbo.employee, GETBEFORECOLS(ON UPDATE KEYANDMOD );

exta is the name of the CDC Extract.
dbo is the schema and employee is the table.
pg_dsn is the Data Source Name.
pg_foouser is the Credential Alias.
OracleGoldenGate is the Credential Domain.
Ensure there is space between XML and _AUDIT_VAULT.

For more information on the GETBEFORECOLS options, refer to GETBEFORECOLS in the Reference for Oracle GoldenGate guide.