1. Using Oracle GoldenGate on Oracle Cloud Marketplace
  2. Using Oracle GoldenGate Classic on Oracle Cloud Marketplace
  3. Oracle GoldenGate Classic for PostgreSQL

7 Oracle GoldenGate Classic for PostgreSQL

Parent topic: Using Oracle GoldenGate Classic on Oracle Cloud Marketplace

7.1 Deploying Oracle GoldenGate Classic Architecture for PostgreSQL Platforms

Using Oracle GoldenGate Classic Architecture on Marketplace, you can deploy and manage your deployment from a single location.

Here are the prerequisites which are required before deployment:

Parent topic: Oracle GoldenGate Classic for PostgreSQL

7.2 Finding Oracle GoldenGate for Non-Oracle (PostgreSQL) within the Oracle Cloud Marketplace

The following steps help you to deploy Oracle GoldenGate for Non-Oracle (PostgreSQL) from Oracle Cloud Marketplace:
  1. Log in to Oracle Cloud Marketplace.
  2. From the Oracle Cloud Marketplace home page, use the search box under Applications and search for the keyword GoldenGate.
  3. From the Search Results, select Oracle GoldenGate for Non-Oracle.

Parent topic: Oracle GoldenGate Classic for PostgreSQL

7.3 Launching Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)

The following steps help you to launch Oracle GoldenGate Classic for Non-Oracle (PostgreSQL) on Oracle Cloud Marketplace:
  1. From the application page, select Get App.
  2. Select OCI Region or Log in using your OCI account login credentials.
    1. OCI Region – Select the OCI Region for which you want to install the compute node.
    2. Click Sign In.
  3. In the Sign In to the Cloud Tenant page, provide the OCI tenant details.
    You may be asked to sign in to your identity provider.
  4. On the Oracle GoldenGate for Non-Oracle (PostgreSQL) page, find the selection box and provide the following information:
    1. Select Version – It provides a list of versions that are available in the listing. It is set to Oracle GoldenGate for Non-Oracle (PostgreSQL) by default.
    2. Select Compartment – Specifies the compartment where the compute node will be built. It is generally the location that you have access to build the compute node.
    3. Terms of Use – This check box is selected by default. Oracle recommends to review the licenses before proceeding with the instance creation.
    4. Launch Stack – It launches the stack in the OCI environment.
  5. Fill in the required Stack information:
    • Name - Name of the Stack. It has a default name and provides a date time stamp. You can edit this detail, if required.
    • Description - Description that you provide while creating the Stack.
    • Create In Compartment – It defaults to the compartment you have selected on the Oracle GoldenGate 19c for Oracle page.
    • Tags (optional) – Tags are a convenient way to assign a tracking mechanism but are not mandatory. You can assign a tag of your choice for easy tracking. You have to assign a tag for some environments for cost analysis purposes.
    • Click Next.
  6. Fill in the required details to Configure Variables. This information is required to build the compute node for for Non-Oracle (PostgreSQL):
    1. Name for New Resources -
      • Display Name - Display Name used to identify all new OCI resources.
      • Host DNS Name – Domain Name Service for the new compute node.
    2. Network Settings -
      • Create New Network – Select this check box, if you wish to create a new network resource.

        If you select this check box, the Create New Network wizard appears allowing you to add and edit the new network information such as:

        • Network Compartment
        • New VCN DNS Name
        • New VCN CIDR
        • New Subnet DNS Name
        • New Subnet CIDR

        If you do not select this check box, the Create New Network wizard does not appear and the compute node is created with the existing network options in the VCN.

      • Network Compartment (optional) – Compartment for new or existing network resources.
      • VCN (optional) – Existing VCN to use for the newly created instance if you are not creating a new network.
      • Subnet (optional) – Existing subnet to use for the newly created instance if you are not creating a new network. The subnet that you have selected must match the same Availability Domain set in the Instance Settings.
    3. Instance Settings -
      • Availability Domain – It specifies the availability domain for the newly created Oracle GoldenGate Instance. It must match the Subnet that you have selected in the Use Existing Network settings.
      • Compute Shape – Shape of new compute instance. Supported shapes are VM.Standard2.4, VM.Standard2.8, VM.Standard2.16, VM.Standard2.24
      • Assign Public IP – Indicates if the new VM should have a public IP address.
      • Custom Volume Sizes – Use this checkbox to customize the size of new block storage volumes.
    4. SSH Public Key - Public Key for allowing SSH access as the ‘opc’ user.
  7. Click Next.
  8. On the Review page, review the information you provided then click Create.
  9. After clicking Create, you will be taken to the Stacks page. You can monitor the creation of the compute node using this page.
  10. Upon completion, you can view the Oracle GoldenGate for Non-Oracle (PostgreSQL) compute node under Compute -> Instances.

Parent topic: Oracle GoldenGate Classic for PostgreSQL

7.4 Connecting to Oracle GoldenGate Classic for Non-Oracle (PostgreSQL) Compute Node

To access your new Oracle GoldenGate deployment, log in to the compute node and access Oracle GoldenGate Software Command Interface (GGSCI). For this, you need to identify the public IP address of the compute node where Oracle GoldenGate Classic is running. The following steps help you to identify the public IP address:
  1. Log in to your Oracle Cloud Console.
  2. Select Compute -> Instances.
  3. Select the hyperlink name of the compute node. The public IP Address is listed under Primary VNIC Information.
  4. To access the compute node where Oracle GoldenGate is running, connect as the opc account using SSH. For more information on how to access a node using the opc account refer to Connecting to an Instance.
A public SSH key is specified as a part of the deployment process and you can use the private key when you are connecting to the Oracle GoldenGate instance. The following example illustrates how you connect to the Oracle GoldenGate compute node:
ssh -i <private-key-filename> opc@<public-id-address>

Parent topic: Oracle GoldenGate Classic for PostgreSQL

7.5 Working with Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)

After deploying Oracle GoldenGate Classic for Non-Oracle (PostgreSQL) on Oracle Cloud Marketplace, you can access the latest release of Oracle GoldenGate Classic for Non-Oracle (PostgreSQL).

Before you can start using Oracle GoldenGate Classic for Non-Oracle (PostgreSQL), there are a few tasks that you must perform to ensure that your environment is complete and ready to replicate your data. Before you begin data replication, you must perform the following tasks:

Topics:

Parent topic: Oracle GoldenGate Classic for PostgreSQL

7.5.1 Establishing Connectivity

The Oracle GoldenGate Classic for Non-Oracle (PostgreSQL) on Oracle Cloud Marketplace compute node acts as a hub where you can manage your connections to source and target PostgreSQL databases. To establish connectivity from an Oracle GoldenGate Classic for Non-Oracle (PostgreSQL) compute node to your on-premises PostgreSQL database, use the following options:

  • IPSec VPN
  • FastConnect

For establishing an IPSec VPN with OCI, refer to IPSec VPN documentation. For establishing a FastConnect connection, refer to the FastConnect documentation.

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)

7.5.2 Oracle GoldenGate for PostgreSQL Requirements

Before you can begin replicating data, for a PostgreSQL database, review the instance, database, LD_LIBRARY_PATH, and user requirements.

To replicate data:
  1. Oracle GoldenGate for PostgreSQL requires certain PostgreSQL client libraries to replicate the data and therefore, you must Install minimum PostgreSQL client library. See Installing for PostgreSQL in Installing Oracle GoldenGate Guide and Using Oracle GoldenGate for PostgreSQL chapter of Using Oracle GoldenGate for Heterogeneous Databases. These sections contain important information regarding database requirements and supported features.
    1. To install PostgreSQL client, execute: sudo yum install postgresql-client.

      Note:

      While installing PostgreSQL client or plugin, ensure that the PostgreSQL database version matches with PostgreSQL client and plugin.
      • For database version PostgreSQL 11, it is sudo yum install postgresql11-contrib.
      • For database version PostgreSQL 10, it is sudo yum install postgresql10-contrib.
  2. Check whether or not the PostgreSQL client library (LIBPQ) exists.
  3. After the installation is complete, add the LD_LIBRARY_PATH. If the PostgreSQL client library path is -/opt/pg/lib and the Oracle GoldenGate home directory path is /opt/ogg/postgresql, then set LD_LIBRARY_PATH as export LD_LIBRARY_PATH= /opt/pg/lib:/opt/ogg/postgresql/lib:$LD_LIBRARY_PATH.

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)

7.5.3 Creating Database User and Schema

To create database user and schema:
  1. Create database user ogg1 with password ogg1. See PostgreSQL 12 Documentation - Create User.
  2. Create schema. See PostgreSQL 12 Documentation - Create Schema.
  3. Create database. See PostgreSQL 12 Documentation - Create Database.
  4. Grant role permissions to the user for more privileges. See PostgreSQL12 Documentation - Grant Role.
create user ogg1 with password 'ogg1';
CREATE SCHEMA dbo;
CREATE DATABASE qadb;
GRANT replication ON DATABASE qadb TO ogg1;

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)

7.5.4 Setting up and Configuring odbc.ini File

For more information about setting up odbc.ini and LD_LIBRARY_PATH, see Database Requirements PostgreSQL in the Installing Oracle GoldenGate Guide.

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)

7.5.5 Starting GGSCI

Oracle GoldenGate Classic for Non-Oracle (PostgreSQL) allows you to quickly access the GoldenGate Service Command Interface (GGCSI) and is preconfigured with a running Manager process. After logging in to the compute node, you can find GGCSI from the postgres directory (/home/opc/postgres).

To start GGSCI:
  1. Run the following command: 
    export LD_LIBRARY_PATH=/home/opc/postgres/lib:/opt/PostgreSQl/pg11/lib:$LD_LIBRARY_PATH
  2. To start GGSCI, execute the following command:
    $ ./ggsci

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)

7.5.6 Registering a Replication Slot

Oracle GoldenGate needs to register the extract with the database replication slot.

Before registering the Extract, ensure to have the DBLOGIN connected to the source database.
For example, if the Extract group name is extr, then run the following command from GGSCI to register the Extract:
GGSCI> register extract extr with <dbname>

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)

7.5.7 Enabling Supplemental Logging for a Source PostgreSQL Database

You must enable the supplemental logging of tables for PostgreSQL Change Data Capture, to capture transactional data from a source PostgreSQL database. Perform the following steps to enable table level Change Data Capture.

To enable supplemental logging for a source PostgreSQL:

  1. From GGSCI, connect to the source database with a PostgreSQL login:
    GGSCI> DBLOGIN sourcedb <DSN> USERID <oggsourceuser> PASSWORD <Pwd>
GGSCI > ADD TRANDATA dbo.table1
GGSCI > ADD TRANDATA dbo.table2

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)

7.5.8 Configuring and Creating a Remote Extract for PostgreSQL

To capture transactional data from a source PostgreSQL database, you must configure a capture process, also known as an Extract. To build an Extract, you can perform the following steps with Oracle GoldenGate for Non-Oracle.

For more information about connecting to Remote Capture and Apply, see How to Connect Remote Capture and Apply for PostgreSQL in Using Oracle GoldenGate for Heterogeneous Databases Guide.
  1. Create an Extract parameter file.
    GGSCI> EDIT PARAMS extsql

    Sample Extract parameter file:

    EXTRACT extsql
DBLOGIN sourcedb <DSN> USERID <oggsourceuser> PASSWORD <Pwd>
EXTTRAIL ./dirdat/et
TABLE dbo.table1;
TABLE dbo.table2;

    Save and close the file.

  2. Add the Extract and Extract’s local trail.
    GGSCI> ADD EXTRACT extsql, TRANLOG, BEGIN NOW
GGSCI> ADD EXTTRAIL ./dirdat/et, EXTRACT extsql
  3. Verify the connectivity to the PostgreSQL source database and start the Extract.
    GGSCI> DBLOGIN sourcedb <DSN> USERID <oggsourceuser> PASSWORD <Pwd>
GGSCI> START EXTRACT extsql

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)

7.5.9 Configuring and Creating a Remote Replicat for PostgreSQL

The apply process for replication, also known as Replicat, is used to deliver data to a target database. To build a Replicat, perform the following steps with Oracle GoldenGate for Non-Oracle.
  1. From GGSCI, log in to the target database and create a checkpoint table.
    GGSCI> DBLOGIN sourcedb <DSN> USERID <oggsourceuser> PASSWORD <Pwd>
GGSCI> ADD CHECKPOINTTABLE oggtargetschema.oggcheck
  2. Create a Replicat parameter file.
    GGSCI > EDIT PARAMS repsql

    Sample Replicat parameter file:

    REPLICAT repsql
TARGETDB <DSN> USERID <oggtargetuser> PASSWORD <Pwd>
MAP dbo.table1, TARGET dbo.table1;
MAP dbo.table2, TARGET dbo.table2;

    Save and close the file.

  3. Add and start the Replicat.
    GGSCI> ADD REPLICAT repsql, EXTTRAIL ./dirdat/et, CHECKPOINTTABLE oggtargetschema.oggcheck
GGSCI> START REPLICAT repsql

Parent topic: Working with Oracle GoldenGate Classic for Non-Oracle (PostgreSQL)