Oracle GoldenGate is a managed service that can collect, replicate, and manage transactional data between databases. GoldenGate can use CockroachDB as a sink by leveraging CockroachDB's PostgreSQL compatibility. This page describes how to:
- Configure Oracle GoldenGate for PostgreSQL.
- Set up Extract to capture data from a source database.
- Set up Replicat to deliver data to CockroachDB.
- Perform bulk replication.
As of this writing, GoldenGate supports the following database sources:
- MySQL
- Oracle
- PostgreSQL
- SQL Server
- Db2 z/OS
This page describes GoldenGate at a high level and assumes some familiarity with this tool. For detailed information, refer to the Oracle GoldenGate documentation.
For limitations on what PostgreSQL features are supported, refer to Oracle's Details of Supported PostgreSQL Data Types.
Before you begin
Oracle GoldenGate runs as a process separate from CockroachDB and the source database. Ensure your host meets the minimum requirements.
Ensure that your Oracle client or client libraries are compatible with the host Oracle version.
Ensure that you have installed both required Oracle GoldenGate components:
- Oracle GoldenGate for Oracle is required to pull source data and route it to proper trail files.
- Oracle GoldenGate for PostgreSQL is required to pull data from the trail files to CockroachDB.
For CockroachDB clusters running v22.1 and earlier, enable the following cluster settings:
SET CLUSTER SETTING sql.defaults.datestyle.enabled = true; SET CLUSTER SETTING sql.defaults.intervalstyle.enabled = true;
For CockroachDB v22.2 and later, these settings are enabled by default.
Ensure libpg is available on the Oracle GoldenGate host.
Ensure you have a secure, publicly available CockroachDB cluster running the latest v23.2 production release, and have created a SQL user.
Configure Oracle GoldenGate for PostgreSQL
This section describes how to configure Oracle GoldenGate for PostgreSQL to work with CockroachDB. Oracle GoldenGate for PostgreSQL is the process that pulls data from trail files over to CockroachDB. This is a separate installation from Oracle GoldenGate for Oracle, which will be used to pull source data and route it to proper trail files. For more information, refer to the Oracle GoldenGate for PostgreSQL documentation. The following example uses a CockroachDB Serverless cluster.
Edit the
ODBC.ini
file to set up the ODBC data sources and configuration:# No changes should be needed for CRDBLOCAL [ODBC Data Sources] PG_src=DataDirect 7.1 PostgreSQL Wire Protocol PG_tgt=DataDirect 7.1 PostgreSQL Wire Protocol CRDB=DataDirect 7.1 PostgreSQL Wire Protocol [ODBC] IANAAppCodePage=4 # The following path is your Oracle GoldenGate for PostgreSQL installation directory InstallDir=/u01/ggs-pg
Ensure that all the PostgreSQL libraries are installed and referenced in
LD_LIBRARY_PATH
.The path should at least include
/usr/pgsql-13/lib
and/u01/ggs-pg/lib
:# The path is a concatenation of your PostgreSQL libraries and GoldenGate installation directory export LD_LIBRARY_PATH=/usr/pgsql-13/lib:/u01/ggs-pg/lib
Set up the
ODBC.ini
file for the Oracle GoldenGate host:# This is needed so that OGG knows where to look for connection details for the database export ODBCINI=/etc/odbc.ini
To make this change permanent, you must also add the command to your shell's configuration file, such as
~/.zshrc
.In the
ODBC.ini
file, set up the CockroachDB Serverless parameters:- Replace the login details with your own. Be sure to prefix the database name with
{hostname}
. - Make sure your Serverless cluster's root CA certificate is in the
TrustStore
path.
[CRDBSERVERLESS] # The following driver will always point to your Oracle GoldenGate for PostgreSQL installation Driver=/u01/ggs-pg/lib/GGpsql25.so Description=DataDirect 7.1 PostgreSQL Wire Protocol Database={hostname}.{database} HostName={host} PortNumber=26257 LogonID={your sql user} Password={your sql user's pass} EncryptionMethod=1 ValidateServerCertificate=1 TrustStore=/root/.postgresql/root.crt
- Replace the login details with your own. Be sure to prefix the database name with
Log in to the database:
cd /u01/ggs-pg ./ggsci # To log into the local database DBLOGIN SOURCEDB CRDBLOCAL # To log into the serverless database and then enter your password DBLOGIN SOURCEDB CRDBSERVERLESS
Set up Extract to capture data from a source database
Extract is Oracle GoldenGate's data capture mechanism that is configured to run against the source database.
Complete the steps in this section on a machine and in a directory where Oracle GoldenGate for Oracle is installed.
In the GGSCI terminal, create and open the
epos
parameter file for the Oracle source:cd /u01/ggs # Otherwise known as $OGG_HOME ./ggsci edit param epos view param epos
Edit the parameters that configure Extract to send data to the trail file at
./dirdat/ab
on the remote host:EXTRACT epos USERIDALIAS gg_source RMTHOST {host-name}, MGRPORT {port} RMTTRAIL ./dirdat/ab TABLE OGGADM1.testtable;
In the GGSCI terminal, run the following and start the Extract service.
add extract epos, tranlog, begin now add rmttrail ./dirdat/ab, extract epos megabytes 10 # This is crucial in order to address the logmining not found error register EXTRACT epos, DATABASE # Starts service start epos
Check the status of the Extract by creating a test table
OGGADM1.testtable
in Oracle and adding a row:CREATE TABLE OGGADM1.testtable (col1 number, col2 varchar2(20)); ALTER TABLE OGGADM1.testtable ADD PRIMARY KEY (col1); INSERT INTO OGGADM1.testtable (col1, col2) VALUES (11, 'Example data'); COMMIT;
In GGSCI, in another terminal, check that Extract is working correctly:
stats EXTRACT epos
You should see an output similar to the following:
Extracting from OGGADM1.TESTTABLE to OGGADM1.TESTTABLE: *** Total statistics since 2023-06-09 19:06:44 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Daily statistics since 2023-06-09 19:06:44 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Hourly statistics since 2023-06-09 19:06:44 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 *** Latest statistics since 2023-06-09 19:06:44 *** Total inserts 1.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 1.00 End of statistics.
Set up Replicat to deliver data to CockroachDB
Replicat is an Oracle process that reads trail files and delivers data to a target database.
Run the steps in this section on a machine and in a directory where Oracle GoldenGate for PostgreSQL is installed.
Log in to the database from GGSCI:
export LD_LIBRARY_PATH=/usr/pgsql-13/lib:/u01/ggs-pg/lib export ODBCINI=/etc/odbc.ini cd /u01/ggs-pg ./ggsci # Log into the DB DBLOGIN SOURCEDB CRDBLOCAL
Check the status in the GGSCI terminal:
info all
You should see the following output:
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
Open the parameter file:
edit param RORPSQL view param RORPSQL
Edit the parameters to configure the
MAP
statement, which maps the source (OGGADM1.testtable
) to the target (public.testtable
):REPLICAT RORPSQL SETENV ( PGCLIENTENCODING = "UTF8" ) SETENV (ODBCINI="/etc/odbc.ini" ) SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") TARGETDB CRDBLOCAL DISCARDFILE ./dirrpt/diskg.dsc, purge MAP OGGADM1.testtable, TARGET public.testtable, COLMAP (COL1=col1,COL2=col2);
In the GGSCI terminal, run the following and start the Replicat service:
add replicat RORPSQL, NODBCHECKPOINT, exttrail ./dirdat/ab start RORPSQL
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING REPLICAT RUNNING RORPSQL 00:00:00 00:00:09
Test that Extract and Replicat are working properly by adding values to
OGGADM1.testtable
:INSERT INTO OGGADM1.testtable (col1, col2) VALUES (12, 'Example data'); COMMIT;
Connect to the target Serverless cluster and check that the data was delivered to
public.testtable
:SELECT * FROM public.testtable;
col1 | col2 -------+--------------- 12 | Example data
Open the GGSCI terminal for Oracle:
cd $OGG_HOME ./ggsci
Check that Extract is working correctly:
stats EXTRACT EPOS
You should see an output similar to the following:
Extracting from OGGADM1.TESTTABLE to OGGADM1.TESTTABLE: *** Total statistics since 2023-06-09 19:06:44 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 4.00 *** Daily statistics since 2023-06-09 19:06:44 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 4.00 *** Hourly statistics since 2023-06-09 19:06:44 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 4.00 *** Latest statistics since 2023-06-09 19:06:44 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 4.00 End of statistics.
Open the GGSCI terminal for CockroachDB:
cd $OGG_PG_HOME ./ggsci
Check that Replicat is working correctly:
stats REPLICAT RORPSQL
You should see an output similar to the following:
Replicating from OGGADM1.TESTTABLE to public.testtable: *** Total statistics since 2023-06-09 19:10:20 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 4.00 *** Daily statistics since 2023-06-09 19:10:20 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 4.00 *** Hourly statistics since 2023-06-09 19:10:20 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 4.00 *** Latest statistics since 2023-06-09 19:10:20 *** Total inserts 4.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 4.00 End of statistics.
Perform bulk replication
- Keep the Extract process running on Oracle and the Replicat process running for CockroachDB.
In the source database, bulk insert some data:
INSERT INTO OGGADM1.testtable2 (col1, col2) SELECT level + 99, 'Example data' FROM dual CONNECT BY level <= 50000 - 99;
Run the status command on the Extract GGSCI terminal:
stats EXTRACT EPOS
You will see an output similar to the following:
Extracting from OGGADM1.TESTTABLE2 to OGGADM1.TESTTABLE2: *** Total statistics since 2023-06-09 21:02:39 *** Total inserts 49903.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 49903.00 *** Daily statistics since 2023-06-09 21:02:39 *** Total inserts 49903.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 49903.00 *** Hourly statistics since 2023-06-09 21:02:39 *** Total inserts 49903.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 49903.00 *** Latest statistics since 2023-06-09 21:02:39 *** Total inserts 49903.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 49903.00 End of statistics.
Run the status command on the Replicat GGSCI terminal:
The number of inserts takes a few minutes to update. After the process finishes inserting and committing to the database, it will report back the final inserts. During the process, it will show the previous state:
Replicating from OGGADM1.TESTTABLE2 to public.testtable: *** Total statistics since 2023-06-09 21:04:05 *** Total inserts 49903.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 49903.00 *** Daily statistics since 2023-06-09 21:04:05 *** Total inserts 49903.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 49903.00 *** Hourly statistics since 2023-06-09 21:04:05 *** Total inserts 49903.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 49903.00 *** Latest statistics since 2023-06-09 21:04:05 *** Total inserts 49903.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 49903.00 End of statistics.
To verify that the insert is happening, you can run:
SELECT COUNT(*) FROM public.testtable;
. The query will hang while it's still inserting.To track the status of an ongoing replication, view the report:
./ggsci VIEW REPORT RORPSQL