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, Google AlloyDB 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.
Amazon RDS does not support prepending or starting the replication slot name with the string rds. The command used by Oracle GoldenGate Extract to create the replication slot returns an error if the replication slot name starts with the string rds. For example, you could name the replication slot as ordscdc instead of rdscdc to avoid this error, as shown in the following example:
```
select slot_name from  pg_create_logical_replication_slot('ordscdc',
'test_decoding');
```
This command adds the ordscdc replication slot. However, if you use the replication slot name as rdscdc, the following error will occur:
```
ERROR:  must be superuser or replication role to use logical replication  slots in the 'rds'
namespace
```

Limitation:

On Amazon Aurora PostgreSQL version 12.17, if upper case SHOW command is executed, it reports the following error:

"ERROR: must be superuser or replication role to run this operation."

You must use lower case SHOW command to avoid this error.

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.