Prepare Database Connection

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

Oracle GoldenGate for PostgreSQL connects to PostgreSQL databases using a pre-packaged ODBC driver. Connections can be established using a Data Source Name (DSN) or using a direct connection and supplying the database server host, port, database, and other information.

Using DSN connections requires connection details to be listed in an odbc.ini file, while using direct entries are entered manually when adding a database connection to the Administration Service's web interface or through the Admin Client.

Note:

PgBouncer is not supported for Oracle GoldenGate connections.

Note:

Oracle GoldenGate does not support connections to PostgreSQL that use Pgpool.

After performing the steps given below to create the DSN entries or you plan to use direct connections, proceed to the Add Database Connections topic to know how to create database connections.

Configure a DSN Connection in Linux

To create a DSN connection for Oracle GoldenGate processes, you will first need to add an environment variable for the Oracle GoldenGate for PostgreSQL deployment and then create an odbc.ini file to store the connection attributes.

  1. Log in to the Service Manager web interface.

  2. From the left navigation pane, click Deployments and then select the Oracle GoldenGate PostgreSQL deployment. This expands the various settings for the deployment.

  3. Click Configuration and then click the plus sign (+) next to Environment Variables.

  4. Provide the following information in the two available fields. For the Environment Variable Value field, ensure it is the absolute path of the odbc.ini file:

    Environment Variable Name = ODBCINI

    Environment Variable Value = /ogg/deployment/pgsql/odbc.ini


    Add Environment Variable dialog box for setting the odbc.ini environment variable for PostgreSQL.

  5. Click Submit to create the new variable and then restart the deployment from the Deployments pane, for the changes to take effect.

  6. In the Oracle GoldenGate installation's deployment folder, manually create an odbc.ini file and add data sources in this 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 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 name of the Oracle GoldenGate PostgreSQL wire protocol driver path, and can use a relative path, like: InstallDir =./datadirect

  • Driver: Is the name of the Oracle GoldenGate PostgreSQL Wire Protocol driver file, and can use a relative path, like: Driver=./datadirect/lib/ggpsql25.so.

  • Database: This is the name of the source or target database.

  • Hostname: This is the database host IP address or host name.

  • PortNumber: This is the listening port of the database.

  • Oracle GoldenGate for PostgreSQL support multiple hosts and ports for PostgreSQL connections. This feature is useful in the Oracle GoldenGate High Availability (HA) setup. In a PostgreSQL HA setup there is one primary and multiple standby servers. You can set up the connection string format to specify multiple hosts:

    Connection String Description 
    db-user@host1,host2,host3/db-name
    		 The connections would be attempted using default PostgreSQL database port 5432 for all the hosts (host1, host2, host3). 
    db-user@host1:1234,host2:2345,host3:3456/db-name
    		 The connections would be attempted using the ports associated with each host specified in the connection string. 
    db-user@host1,host2,host3:1234/db-name
    		 All hosts using the same port number (1234) on all the hosts (host1, host2, host3). The connections would be attempted using the port 1234 for all the hosts (host1, host2, host3). 
    db-user@host1,host2,host3:1234/db-name
    		 All hosts using the same port number (1234) on all the hosts (host1, host2, host3). The connections would be attempted using the port 1234 for all the hosts. 
    db-user@host1:3456,host2,host3:1234/db-name

    If the connection string is specified in other formats where the port number is specified for some hosts while the other hosts are specified without any ports, then the last or right-most port specification will be used as the port number for all the hosts which do not have a port number specified in the connection string. As shown in the connection string:

    db-user@host1:3456,host2,host3:1234/db-name

    The port number for host2 would be 1234, which is the right-most port number specified in the connection string.

  • 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 an example odbc.ini file with two DSN entries. The Data Source names used in the example below are PG_src and PG_tgt.

  1. Save and close the odbc.ini file.

  2. To set up the database connection from Oracle GoldenGate for a PostgreSQL deployment, see Add Database Connections.

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:

  1. Modify the postgresql.conf file to set the password_encryption option to scram-sha-256.

  2. Modify the pg_hba.conf file to set the Method option to scram-sha-256, as md5 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 to scram-sha-256. You can use the same password to be regenerated.

    For example, if the database user, named admin uses the password as password123, then the same password can be regenerated using the scram-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

Changes required in odbc.ini 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 following ODBC DSN attributes should be setup accordingly in the odbc.ini file.

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

However, the KeyStore file is not mandatory for validation of client certificates (by the server) when SSLCert and SSLKey are already provided.

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.