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 becausePgBouncer
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
andPassword
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 theUSERIDALIAS
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.
-
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
-
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.
-
On the Windows system, open the Control Panel folder.
-
Open the Administrative Tools folder.
-
Open ODBC Data Sources (64-bit). The ODBC Data Source Administrator dialog box is displayed.
-
Select the System DSN tab, and then click Add.
-
Under Create New Data Source, select the Oracle GoldenGate PostgreSQL Wire Protocol driver and click Finish.
-
The Create a New Data Source wizard is displayed.
-
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.
-
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
- A delete is issued for
emp_src
. - It cascades a delete to
salary_src
. - Oracle GoldenGate sends both deletes to the target.
- The parent delete arrives first and is applied to
emp_targ
. - The parent delete cascades a delete to
salary_targ
. - The cascaded delete from
salary_src
is applied tosalary_targ
. - 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:
-
Primary key
-
First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.
-
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.
-
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 ExtractTABLE
parameter and the ReplicatMAP
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 setFULL
logging for the table, otherwiseDEFAULT
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