PostgreSQL
This section lists details about configuring Oracle GoldenGate for PostgreSQL.
Topics:
Prepare Database Users and Privileges
Learn about creating database users and assigning privileges for Oracle GoldenGate for PostgreSQL.
Topics:
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.
Privilege | Extract | Replicat | Purpose |
---|---|---|---|
Database Replication Privileges |
|||
|
Yes |
Yes |
Required for database connectivity.
|
|
Yes |
NA |
Required for the user to register Extract with a replication slot.
|
|
Yes |
NA |
Required to enable table level supplemental logging (
For Azure Database for PostgreSQL, only the Admin user has SUPERUSER authority and is the only user that can enable TRANDATA. |
|
Yes |
Yes |
For metadata access to tables in the schema to be replicated.
|
|
Yes |
Yes |
Grant select access on tables to be replicated.
|
|
NA |
Yes |
Apply replicated DML to target objects.
|
Heartbeat and Checkpoint Table Privileges |
|||
|
Yes |
Yes |
Required by the Extract and Replicat user to add an Oracle GoldenGate schema for heartbeat and checkpoint table creation.
Alternatively, if |
|
Yes |
Yes |
For heartbeat and checkpoint table creation/deletion if the Extract or Replicat user does not own the objects.
|
|
Yes |
Yes |
For heartbeat update and purge function execution if the user calling the functions does not own the objects.
|
|
Yes |
Yes |
For heartbeat and checkpoint table inserts, updates and deletes if the user does not own the objects.
|
Prepare Database Connection, System, and Parameter Settings
Learn about configuring database connection, system, and parameter settings for Oracle GoldenGate for PostgreSQL.
Topics:
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 usePgBouncer
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.
Note:
Topics:
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.
-
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 https://docs.progress.com/bundle/datadirect-connect-odbc-71/page/IANAAppCodePage_9.html#IANAAppCodePage_9 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
Connecting to a FIPS-enabled PostgreSQL System with Version 14 or Lower
When the Oracle GoldenGate Extract is run from a Federal Information Processing Standards (FIPS) enabled system installed with PostgreSQL database lower than version 14, it generates the following error:
ERROR OGG-25359 Could not connect to server with database 'postgres', host
'localhost', port '5432' and user name 'postgres'. Error Message: connection to
server at "localhost" (::1), port 5432 failed: could not encrypt password:
disabled for FIPSfe_sendauth: error sending password authentication.
The encryption algorithm md5
is the default encryption
algorithm on PostgreSQL database version lower than 14 and causes the Extract to
abend with an error.
To run Extract on a FIPS-enabled system running PostgreSQL database version lower than 14, perform the following steps:
-
Modify the
postgresql.conf
file to set thepassword_encryption
option toscram-sha-256
. -
Modify the
pg_hba.conf
file to set theMethod
option toscram-sha-256
, asmd5
is not supported on a FIPS-enabled system. However, this is an optional step.The password for the database user that is used by Oracle GoldenGate Extract, must be re-generated or modified if the database user has already been created, after the
password_encryption
option is set toscram-sha-256
. You can use the same password to be regenerated.For example, if the database user, named
admin
uses the password aspassword123
, then the same password can be regenerated using thescram-sha-256
encryption.
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
Topics:
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
Note:
Azure Database for PostgreSQL defaults to enforce SSL connections. To adhere to this requirement, perform the requirements listed here, or optionally, you can disable enforcing SSL connections from the Connection security settings of the database instance using the Microsoft Azure Portal.
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.
Topics:
Database Settings for PostgreSQL Cloud Databases
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.
Topics:
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 calledrds.logical_replication
, whose default is0
and should be set to1
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.
Prepare Tables for Processing
You must perform the following tasks to prepare your tables for use in an Oracle GoldenGate environment for PostgreSQL.
Topics:
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.
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.
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 efficientWHERE
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 Parameters and Functions 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
.
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, theALLCOLS
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.
INFO TRANDATA schema.tablename