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:

• 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.