Database Configuration

Database Software for Capture

To capture from a PostgreSQL database, Oracle GoldenGate requires the test_decoding database plug-in be installed for the database. This plug-in might not have been installed by default when the database was installed.

Ensure that the postgresqlversion#-contrib package is installed on the database server, as shown in the example:

sudo yum install postgresql14-contrib

Parameters in the PostgreSQL Database Configuration File

For Oracle GoldenGate, configure the following parameters in the PostgreSQL database configuration file, $PGDATA/postgresql.conf:

Note:

These changes are required for the primary PostgreSQL database that Oracle GoldenGate will be capturing from as well as any standby database that Oracle GoldenGate could capture from in the event of the primary database failing.

For remote connectivity of an Extract or Replicat, set the PostgreSQL listen_addresses to allow for remote database connectivity. For example:

listen_addresses=remotehost_ip_address

Note:

Ensure that client authentication is set to allow connections from an Oracle GoldenGate host by configuring the pg_hba.conf file. For more information, refer to this document: The pg_hba.conf File
To support Oracle GoldenGate Extract, write-ahead logging must be set to logical, which adds information necessary to support transactional record decoding.

The number of maximum replication slots must be set to accommodate one open slot per Extract, and in general, no more than one Extract is needed per database. If for example PostgreSQL Native Replication is already in use and is using all of the currently configured replication slots, increase the value to allow for the registration of an Extract.

Maximum write-ahead senders should be set to match the maximum replication slots value.

Optionally, commit timestamps can be enabled in the write-ahead log, which when set at the same time logical write-ahead logging is enabled, will track the first DML commit record from that point on, with the correct timestamp value. Otherwise, the first record encountered by Oracle GoldenGate capture will have an incorrect commit timestamp.
```
wal_level = logical                       # set to logical for Capture
 
max_replication_slots = 1                 # max number of replication slots,
                                          # one slot per Extract/client
 
max_wal_senders = 1                       # one sender per max repl slot
 
track_commit_timestamp = on               # optional, correlates tx commit time
                                          # with begin tx log record (useful for
                                          # timestamp-based positioning)
```
After making any of the preceding changes, restart the database.

Use these instructions to manage the database settings for Azure for PostgreSQL, Amazon Aurora PostgreSQL, Amazon RDS for PostgreSQL, GoogleAlloyDB for PostgreSQL, and Google Cloud SQL for PostgreSQL.

Azure Database for PostgreSQL

When configuring Oracle GoldenGate for PostgreSQL Extract against an Azure Database for PostgreSQL, logical decoding must be enabled and set to LOGICAL.

Read the Microsoft documentation for the instructions:

https://learn.microsoft.com/en-us/azure/postgresql/

Other database settings for Azure Database for PostgreSQL can be managed through the Server parameters section of the database instance.

For connections to an Azure Database for PostgreSQL instance, the default Azure Connection Security settings require SSL connections. To adhere to this requirement, further steps are required to support SSL connections with Oracle GoldenGate.

Follow the content listed under Configuring SSL Support for PostgreSQL for more information.

Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL

For Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL, database settings are modified within parameter groups. Review the Amazon AWS documentation for information on how to edit database settings within a new parameter group and assign it to a database instance.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

Ensure that the database configuration settings listed previously are correct, by verifying them in the parameter group assigned to the instance.
The wal_level setting for Amazon database services is configured with a parameter called rds.logical_replication, whose default is 0 and should be set to 1 if the database is to be used a source database for Oracle GoldenGate Extract.

Google AlloyDB for PostgreSQL

Starting with Oracle GoldenGate release 21.14, Oracle GoldenGate supports Google AlloyDB for PostgreSQL. When configuring an Oracle GoldenGate for PostgreSQL Extract for a Google AlloyDB for PostgreSQL, the alloydb.logical_decoding configuration parameter (flag) needs to be set to ON.

Google Cloud SQL for PostgreSQL

When configuring an Oracle GoldenGate for PostgreSQL Extract for a Google Cloud SQL for PostgreSQL database, logical decoding must be set and is done by setting the cloudsql.logical_decoding variable to ON. Follow the instructions provided by Google on how to enable this database flag. For more information, see https://cloud.google.com/sql/docs/postgres/flags#postgres-l.