Configuring Oracle GoldenGate for PostgreSQL

By John Doe April 29, 2024

Summary: This article lists details about configuring Oracle GoldenGate for PostgreSQL.

Table of Contents

Prepare Database Users and Privileges

Learn about creating database users and assigning privileges for Oracle GoldenGate for PostgreSQL.

Database Privileges for Oracle GoldenGate for PostgreSQL

Oracle GoldenGate processes require a database user to capture and deliver data to a PostgreSQL database and it is recommended to create a dedicated PostgreSQL database user for Extract and Replicat.

The following database user privileges are required for Oracle GoldenGate to capture from and apply to a PostgreSQL database.

Database Replication Privileges:

Privilege Extract Replicat Purpose
CONNECT Yes Yes Required for database connectivity.GRANT CONNECT ON DATABASE dbname TO gguser;
WITH REPLICATION Yes NA Required for the user to register Extract with a replication slot.ALTER USER gguser WITH REPLICATION;
WITH SUPERUSER Yes NA Required to enable table level supplemental logging (ADD TRANDATA) but can be revoked after TRANDATA is enabled for the table(s).ALTER USER gguser WITH SUPERUSER;
USAGE ON SCHEMA Yes Yes For metadata access to tables in the schema to be replicated.GRANT USAGE ON SCHEMA tableschema TO gguser;
SELECT ON TABLES Yes Yes Grant select access on tables to be replicated.GRANT SELECT ON ALL TABLES IN SCHEMA tableschema TO gguser;
INSERT, UPDATE, DELETE,TRUNCATE on target tables. Alternatively, if replicating every table, then you can use the GRANT INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA TO… to the Replicat user, instead of granting INSERT, UPDATE, DELETE to every table. NA Yes Apply replicated DML to target objects.GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLE tablename TO gguser;

Heartbeat and Checkpoint Table Privileges:

Privilege Extract Replicat Purpose
CREATE ON DATABASE Yes Yes Required by the Extract and Replicat user to add an Oracle GoldenGate schema for heartbeat and checkpoint table creation.GRANT CREATE ON DATABASE dbname TO gguser;Alternatively, if GGSCHEMA is the same as the user, then the objects can be created under the user by issuing CREATE SCHEMA AUTHORIZATION ggsuser;
CREATE, USAGE ON SCHEMA Yes Yes For heartbeat and checkpoint table creation/deletion if the Extract or Replicat user does not own the objects.GRANT CREATE, USAGE ON SCHEMA ggschema TO gguser;
EXECUTE ON ALL FUNCTIONS Yes Yes For heartbeat update and purge function execution if the user calling the functions does not own the objects.GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ggschema TO gguser;
SELECT, INSERT, UPDATE, DELETE Yes Yes For heartbeat and checkpoint table inserts, updates and deletes if the user does not own the objects.GRANT SELECT, INSERT, UPDATE, DELETE, ON ALL TABLES IN SCHEMA ggschema TO gguser;

Prepare Database Connection, System, and Parameter Settings

Learn about configuring database connection, system, and parameter settings for Oracle GoldenGate for PostgreSQL.

Configuring a Database Connection

Oracle GoldenGate connects to a PostgreSQL database through an ODBC (Open Database Connectivity) driver and requires a system Data Source Name (DSN) be created with the correct database connection details for each source and target PostgreSQL database.

This section contains instructions for setting up the DSN connections that Extract and Replicat will use.

Ensure that you have installed and configured the driver prior to creating a DSN, by following the Installing the DataDirect driver for PostgreSQL instructions.

Note: Do not use PgBouncer setup for Extract connections to the PostgreSQL database because PgBouncer does not understand the replication protocol, because of which the Extract connection is not identified as replication connection.

Configuring a Database Connection in Linux

To create a database connection in Linux for Oracle GoldenGate processes, create a DSN (Data Source Name) inside the /etc/odbc.ini file. Multiple DSNs can be part of the same ODBC file.

Use the following minimum settings when creating the DSN file:

  • Data Source Name – A user defined name of a source or target database connection that will be referenced by Oracle GoldenGate processes, such as Extract or Replicat. DSN names are allowed up to 32 alpha-numeric characters in length, and can include only underscore (_) and dash (-) from special characters.

  • IANAAppCodePage=4 – Is the default setting but can be modified according to the guidance specified on the IANAAppCodePage page when the database character set is not Unicode.

  • InstallDir – Is the value of the Oracle GoldenGate installation path, for example: /u01/app/ogg.

  • Driver – For Oracle GoldenGate release versions prior to 21.8, set to /<GoldenGate_Installation_Path>/lib/GGpsql25.so.

    For Oracle GoldenGate release versions 21.8 and later, set the value to /<GoldenGate_Installation_Path>/datadirect/lib/ggpsql25.so.

  • Database – Is the name of the source or target database.

  • HostName – Is the database host IP address or host name.

  • PortNumber – Is the listening port of the database.

  • You can also provide a LogonID and Password for the Extract or Replicat user, but these will be stored in clear text. It is recommended to leave these fields out of the DSN and instead store them in the Oracle GoldenGate wallet as a credential alias, and reference them with the USERIDALIAS parameter in Extract and Replicat.

The following is a sample /etc/odbc.ini file with two DSN entries. The Data Source names used in the example below are PG_src and PG_tgt.

  1. Create a DSN for each source or target database in the /etc/odbc.ini file.

    sudo vi /etc/odbc.ini

    #Sample DSN entries [ODBC Data Sources]
PG_src=Oracle GoldenGate PostgreSQL Wire Protocol
PG_tgt=Oracle GoldenGate PostgreSQL Wire Protocol
[ODBC] IANAAppCodePage=4 InstallDir=/u01/app/ogg

    [PG_src]
Driver=/u01/app/ogg/datadirect/lib/ggpsql25.so
Description=Oracle GoldenGate PostgreSQL Wire Protocol
Database=sourcedb
HostName=remotehost
PortNumber=5432

    [PG_tgt]
Driver=/u01/app/ogg/datadirect/lib/ggpsql25.so
Description=Oracle GoldenGate PostgreSQL Wire Protocol
Database=targetdb
HostName=remotehost
PortNumber=5432

  2. Save and close the odbc.ini file.

Configuring a Database Connection in Windows

To create a database connection in Windows, use the Windows ODBC Data Source Administrator to create a system DSN for each source and target database.

  1. On the Windows system, open the Control Panel folder.

  2. Open the Administrative Tools folder.

  3. Open ODBC Data Sources (64-bit). The ODBC Data Source Administrator dialog box is displayed.

  4. Select the System DSN tab, and then click Add.

  5. Under Create New Data Source, select the Oracle GoldenGate PostgreSQL Wire Protocol driver and click Finish.

  6. The Create a New Data Source wizard is displayed.

  7. Supply the following:

    • For Data Source Name, type a name for the DSN, up to 32 alpha-numeric characters in length, excluding special keyboard characters except for the underscore and dash.
    • (Optional) For Description, type a description of this DSN.
    • Provide the database server’s Host Name, the database Port Number, and Database Name.

  8. Click OK to close the dialog box.

    You can also provide the User Name information under the Security tab but it is recommended instead to leave this field empty and instead store the user name and password in the Oracle GoldenGate wallet as a credential alias, and reference them with the USERIDALIAS parameter in Extract and Replicat.

Configuring SSL Support for PostgreSQL

SSL can be enabled by setting the configuration parameter SSL to on in the PostgreSQL configuration file ($PGDATA/postgresql.conf ). If SSL is enabled, the corresponding hostssl entry must be present or added in the pg_hba.conf file.

When SSL is enabled, Oracle GoldenGate uses the root certificate, root certification revocation list (CRL), server client certificate, and key from the default locations, as shown in the following snippet:

~/.postgresql/root.crt
~/.postgresql/root.crl
~/.postgresql/postgresql.crt
~/.postgresql/postgresql.key

You need to create the desired entities from this list, and store them in appropriate locations.

If the SSL configuration is setup using non-default locations, then the following environment variables should be set up as per the environment.

PGSSLROOTCERT
PGSSLCRL
PGSSLCERT
PGSSLKEY
Changes required in $ODBCINI file

The SSL support can be enabled by setting the EncryptionMethod DSN attribute to 1 or 6 in the $ODBCINI file.

If set to 0 (No Encryption), data is not encrypted.

If set to 1 (SSL), data is encrypted using the SSL protocols specified in the Crypto Protocol Version connection option. If the specified encryption method is not supported by the database server, the connection fails and the driver returns an error.

If set to 6 (RequestSSL), the login request and data are encrypted using SSL if the server is configured for SSL. If the server is not configured for SSL, an unencrypted connection is established. The SSL protocol used is determined by the setting of the Crypto Protocol Version connection option.

If the database server/client certificates also need to be validated, then the corresponding KeyStore file needs to be created and the below mentioned ODBC DSN attributes should be setup accordingly in $ODBCINI.

KeyStore=<path to .p12 keystore file>
KeyStorePassword=<keystore-passwd>
TrustStore=<path to root certificate>
ValidateServerCertificate=1

Database Configuration

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.

Prepare Tables for Processing

You must perform the following tasks to prepare your tables for use in an Oracle GoldenGate environment for PostgreSQL.

Disabling Triggers and Cascade Constraints on the Target

If Oracle GoldenGate is configured to capture DML operations from source tables that occur due to trigger operations or cascade constraints, then disable the triggers and cascade delete and cascade update constraints on the target tables.

If not disabled, the same trigger or constraint gets activated on the target table and becomes redundant because of the replicated data. Consider the following example, where the source tables are emp_src and salary_src and the target tables are emp_targ and salary_targ

  1. A delete is issued for emp_src.
  2. It cascades a delete to salary_src.
  3. Oracle GoldenGate sends both deletes to the target.
  4. The parent delete arrives first and is applied to emp_targ.
  5. The parent delete cascades a delete to salary_targ.
  6. The cascaded delete from salary_src is applied to salary_targ.
  7. The row cannot be located because it was already deleted in step 5.

In the Replicat MAP statements, map the source tables to appropriate targets, and map the child tables that the source tables reference with triggers or foreign-key cascade constraints. Triggered and cascaded child operations must be mapped to appropriate targets to preserve data integrity. Include the same parent and child source tables in the Extract TABLE parameters.

Ensuring Row Uniqueness for Tables

Oracle GoldenGate requires some form of unique row identifier on the source and target tables to locate the correct target rows for replicated updates and deletes.

Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:

  1. Primary key

  2. First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.

  3. If none of the preceding key types exist (even though there might be other types of keys defined on the table) Oracle GoldenGate constructs a pseudo key of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are excluded from the Oracle GoldenGate configuration. For PostgreSQL LOB types such as text, xml, bytea, char, varchar, Oracle GoldenGate supports these columns as a primary key in source or target tables up to a length of 8191 bytes.

    Note: If there are other, non-usable keys on a table or if there are no keys at all on the table, Oracle GoldenGate logs an appropriate message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE clause.

    For tables that have no uniqueness and have repeat rows with the same values, Replicat will Abend on update and delete operations for these rows.

  4. If a table does not have an appropriate key, or if you prefer that the existing key(s) are not used, you can define a substitute key, if the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key will override any existing primary or unique key that Oracle GoldenGate finds. See TABLE | MAP in Reference for Oracle GoldenGate.

Enabling Table-Level Supplemental Logging

Enabling Supplemental logging is a process in which Oracle GoldenGate sets source database table level logging to support change data capture of source DML operations, and depending on the level of logging, to include additional, unchanged columns which would be needed in cases such as bi-directional replication with conflict detection and resolution configured.

There are four levels of table level logging in PostgreSQL, which equate to the REPLICA IDENTITY setting of a table, and those include NOTHING, USING INDEX, DEFAULT, and FULL.

Oracle GoldenGate requires FULL logging for use cases that require uncompressed trail records and Conflict Detection and Resolution, but in cases where tables have a Primary Key or Unique Index whose changes are being replicated in a simple uni-directional configuration or where full before-images or uncompressed records are not needed, then the DEFAULT level is acceptable. NOTHING and USING INDEX logging levels are not supported by Oracle GoldenGate and cannot be set with ADD TRANDATA.

The following is the syntax for issuing ADD TRANDATA from the Admin Client.

DBLOGIN SOURCEDB dsn_name USERIDALIAS alias_name
ADD TRANDATA schema.tablename ALLCOLS

Note: For tables that have a primary key or unique index, the ALLCOLS option is required in order to set FULL logging for the table, otherwise DEFAULT logging is set.

FULL logging is always set for tables without a primary key or unique index, regardless of whether ALLCOLS is specified or not.

To check the level of supplemental logging:

INFO TRANDATA schema.tablename
comments powered by Disqus

Search

Categories

Tags