PostgreSQL-to-PostgreSQL CDC using Oracle GoldenGate Microservices : A Hands-On Guide to avoid issues during setup.

This post documents a real local-PGSQL-to-AWS-EC2-target CDC pipeline using GoldenGate 21.3 Microservices Architecture (MA) for PostgreSQL. Note this article focuses more on the issues that you will encounter more than the base setup itself. By the end, you'll have a working Extract -> Distribution Path -> Receiver -> Replicat pipeline replicating DML in near real-time — and you'll understand why each step is the way it is, because most of these steps exist specifically to avoid failures we hit along the way.

Architecture at a Glance

Two independent GoldenGate deployments — one acting as the "source hub" (Extract + Distribution Server), one as the "target" (Receiver + Replicat) — connected over a non-secure WebSocket path.

Step 1: Get the Right Binary

This trips up almost everyone coming from Oracle-to-Oracle GoldenGate. There isn't one universal GoldenGate download — for PostgreSQL you specifically need:

Oracle GoldenGate 21.3.0.0.0 for Non-Oracle Database (PostgreSQL)

Not the "Big Data" edition (that's for Kafka/Cassandra/Mongo targets and uses a Java VAM parser doesn't work for PostgreSQL). The PostgreSQL edition uses a DataDirect ODBC driver (GGpsql25.so) for both capture and apply .

Step 2: Prepare the Target Host BEFORE Installing GoldenGate

If you're on Amazon Linux 2023 (or any minimal RHEL-derivative), install everything GoldenGate will need upfront:

sudo dnf install -y \
  libnsl.x86_64 \
  java-1.8.0-amazon-corretto \
  libpq \
  postgresql15 \
  postgresql15-server

Why each one matters:

• libnsl — without it, the Oracle Universal Installer fails with a completely misleading error about java.library.path / NoClassDefFoundError. The real cause is this missing library.

• libpq — without it, adminsrvr will crash-loop the moment you create your deployment, with error while loading shared libraries: libpq.so.5. This one cost us an entire debugging session the first time.

• java-1.8.0-amazon-corretto — the installer's JVM.

Also, the installer requires at least 150MB of swap. Create it and persist it:

sudo dd if=/dev/zero of=/swapfile bs=1M count=256
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
echo '/swapfile none swap sw 0 0' | sudo tee -a /etc/fstab

That last line matters more than it looks — forget it, and the first time you stop/start your EC2 instance, Replicat will abend with OGG-25715: No Swap environment.

And for Amazon Linux 2023 specifically, the installer doesn't recognize the OS — bypass that check:

export CV_ASSUME_DISTID=OEL8.6

Step 3: Install GoldenGate Silently

unzip V1011479-01.zip
cd ggs_Linux_x64_PostgreSQL_services_shiphome/Disk1

cat > /tmp/oggcore.rsp << 'EOF'
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v21_1_0
INSTALL_OPTION=PostgreSQL
SOFTWARE_LOCATION=/u01/ogg
INVENTORY_LOCATION=/home/ec2-user/oraInventory
UNIX_GROUP_NAME=ec2-user
EOF

./runInstaller -silent -nowait -responseFile /tmp/oggcore.rsp
sudo /home/ec2-user/oraInventory/orainstRoot.sh

A headless server can't run the GUI installer — silent mode with a response file is the only option, and it's also the production-correct, automatable approach.

Step 4: Create the Deployment

This is where most of the response-file pain lives. The schema validator (oggca.sh) demands a long list of keys, even ones you'll leave blank — and it gives unhelpful errors (INS-10105, INS-85039) if anything's missing. Here's a complete, working response file:

cat > /tmp/oggca.rsp << 'EOF'
oracle.install.responseFileVersion=/oracle/install/rspfmt_oggca_response_schema_v21_1_0
CONFIGURATION_OPTION=ADD
DEPLOYMENT_NAME=PG-Target
ADMINISTRATOR_USER=oggadmin
ADMINISTRATOR_PASSWORD=OggAdmin_123
SERVICEMANAGER_DEPLOYMENT_HOME=/u02/deployments/ServiceManager
HOST_SERVICEMANAGER=localhost
PORT_SERVICEMANAGER=9011
SECURITY_ENABLED=false
STRONG_PWD_POLICY_ENABLED=false
CREATE_NEW_SERVICEMANAGER=true
REGISTER_SERVICEMANAGER_AS_A_SERVICE=false
INTEGRATE_SERVICEMANAGER_WITH_XAG=false
EXISTING_SERVICEMANAGER_IS_XAG_ENABLED=false
OGG_SOFTWARE_HOME=/u01/ogg
OGG_DEPLOYMENT_HOME=/u02/deployments/PG-Target
OGG_ETC_HOME=
OGG_CONF_HOME=
OGG_SSL_HOME=
OGG_VAR_HOME=
OGG_DATA_HOME=
ADMINISTRATION_SERVER_ENABLED=true
PORT_ADMINSRVR=9012
DISTRIBUTION_SERVER_ENABLED=true
PORT_DISTSRVR=9013
NON_SECURE_DISTSRVR_CONNECTS_TO_SECURE_RCVRSRVR=false
RECEIVER_SERVER_ENABLED=true
PORT_RCVRSRVR=9014
METRICS_SERVER_ENABLED=true
METRICS_SERVER_IS_CRITICAL=false
PORT_PMSRVR=9015
UDP_PORT_PMSRVR=9016
PMSRVR_DATASTORE_TYPE=LMDB
PMSRVR_DATASTORE_HOME=/u02/deployments/PG-Target/lmdb
OGG_SCHEMA=ggadmin
EOF

export LD_LIBRARY_PATH=/usr/lib64:/usr/lib/jvm/java-1.8.0-amazon-corretto/jre/lib/amd64/server:
/u01/ogg/bin/oggca.sh -silent -responseFile /tmp/oggca.rsp

The OGG_SCHEMA=ggadmin line is non-negotiable — without it you get INS-85039: Empty GoldenGate replication schema, and it's not obvious from the error that this is a PostgreSQL schema name, not a database "schema" in the Oracle sense.

The empty OGG_*_HOME entries are required by the schema but resolve to sensible defaults under OGG_DEPLOYMENT_HOME — leave them blank.

Step 5: Wire Up ODBC and Environment Variables — Before First Real Use

Create /etc/odbc.ini:

[ODBC Data Sources]
PG_tgt=DataDirect 7.1 PostgreSQL Wire Protocol

[ODBC]
IANAAppCodePage=4
InstallDir=/u01/ogg

[PG_tgt]
Driver=/u01/ogg/lib/GGpsql25.so
Description=DataDirect 7.1 PostgreSQL Wire Protocol
Database=billing_db
HostName=localhost
PortNumber=5432
LogonID=ggadmin
Password=ggadmin_pw
TransactionErrorBehavior=2

Then tell the GoldenGate deployment where to find it — and where libpq lives — via the REST API, before restarting ServiceManager:

curl -s -u oggadmin:OggAdmin_123 -X PATCH \
  -H "Content-Type: application/json" \
  -d '{"environment":[
        {"name":"LD_LIBRARY_PATH","value":"/usr/lib64:/usr/lib/jvm/java-1.8.0-amazon-corretto/jre/lib/amd64/server:"},
        {"name":"ODBCINI","value":"/etc/odbc.ini"}
      ]}' \
  "http://localhost:9011/services/v2/deployments/PG-Target"

Then restart ServiceManager (since REGISTER_SERVICEMANAGER_AS_A_SERVICE=false, this is a manual kill + relaunch — kill alone sends SIGTERM, which is a clean shutdown):

kill $(pgrep -f "ServiceManager --config")
sleep 3
export LD_LIBRARY_PATH=/usr/lib64:/usr/lib/jvm/java-1.8.0-amazon-corretto/jre/lib/amd64/server:
export ODBCINI=/etc/odbc.ini
/u01/ogg/bin/ServiceManager --config /u02/deployments/ServiceManager/var/temp/ServiceManager-config.dat --force &

If you skip this step, your database credential test will fail with a deeply unhelpful error: [DataDirect][ODBC lib] Driver Manager Message file not found.

Step 6: Connect the Two Deployments — Use ws://, Not ogg://

This is the step that will burn the most time if you get it wrong, so let's be explicit.

GoldenGate MA's Distribution Path supports three protocols:

• wss:// — secure, requires SSL certs (used for OCI GoldenGate)

• ogg:// — the legacy/Classic protocol. Technically supported, but requires legacyProtocolEnabled=true on the Receiver Server — and that setting lives in a config file (recvsrvr-config.dat) that gets regenerated by ServiceManager on every restart. Editing it directly is a rabbit hole.

• ws:// — the correct MA-native non-secure protocol. Use this one.

ws:// requires USERIDALIAS authentication — a GoldenGate-to-GoldenGate network credential, separate from any database credential. Here's the three-step setup:

1. On the target deployment, create a user with the Operator role:

• Admin Service → Administrator → Add User → username ggnetwork, role Operator, password-authenticated.

2. On the source deployment, create a credential alias for that user — with no DSN (this isn't a database connection):

• Configuration → Database → Add Credential → Domain GGNetwork, Alias ec2alias, User ID ggnetwork, Password matches.

• You'll see an ODBC connect-test error here (no default driver specified) — that's expected and harmless for a no-DSN credential.

3. Create the Distribution Path referencing this alias:

• Distribution Service → Add Path

• Source: your Extract + trail (e.g., EBLNG / lt)

• Target protocol: ws

• Target host/port: your target's public IP, port 9014

• Target trail: rt

• Target Authentication Method: UserID Alias → Domain GGNetwork, Alias ec2alias

Click Create and Run. If everything's wired correctly, the path goes green immediately and trail files start appearing on the target side within seconds.

Step 7: PostgreSQL Identity Columns — The ALWAYS vs BY DEFAULT Trap

If your tables use GENERATED ALWAYS AS IDENTITY for primary keys (the modern, "safe" PostgreSQL default), your Replicat will abend the moment it tries to apply the first insert:

OGG-25558: Table public.customers has an identity always column customer_id
which is not supported by PostgreSQL Replicat. The user must change the
identity always column to identity default to continue the replication.

Why: GENERATED ALWAYS means the database refuses any explicitly-supplied value for that column — even from Replicat, which needs to insert the exact primary key value it captured from the source.

The fix — on the target only:

ALTER TABLE customers ALTER COLUMN customer_id SET GENERATED BY DEFAULT;
-- repeat for every identity PK column

Keep the source as GENERATED ALWAYS — that's correct and shouldn't change; it protects your primary database from application code accidentally hardcoding IDs. Only the replication target, which receives pre-assigned IDs, needs BY DEFAULT.

Side note on sequences: even after this fix, the target's sequence counter doesn't know about the IDs Replicat just inserted. If this target ever becomes a live primary (post-cutover), you'll need SELECT setval('customers_customer_id_seq', (SELECT MAX(customer_id) FROM customers)); before allowing direct application writes — otherwise you'll get primary key collisions on the first new insert. This is a plain PostgreSQL housekeeping task, not something GoldenGate handles.

Step 8: Adding New Tables to a Running Pipeline — Order Matters

Suppose your application team adds a new table, promotions, to the source schema. Here's the order that avoids an abend:

1. Create the table on the TARGET first

2. Make sure Replicat's MAP covers it (if you're using MAP *.*, TARGET *.*, you're already covered)

3. Add a TABLE public.promotions; line to the Extract parameter file and restart Extract

4. Then let the application start writing to the new table on source

If you do this out of order — table created on source and written to before step 3 — Replicat will abend:

OGG-00199: Table public.promotions does not exist in target database.
PROCESS ABENDING.

The fix at that point is simple: create the missing table on target, then just restart Replicat. Because GoldenGate's checkpoint file (.cpr) tracks the exact trail position and this particular abend happens before any data is applied (0 records processed), restarting resumes cleanly from the same point — no manual repositioning needed.

One subtlety worth knowing: any rows inserted into the new table on source before you restart Extract with the new TABLE entry are permanently invisible to the replication slot — even though they're physically present in the WAL. GoldenGate's table filter is applied at decode time. Treat "add a new table" as a mini Initial Load: backfill any pre-restart rows manually via COPY/\copy.

Step 9: Why DDL Doesn't Just Replicate Itself

If you're coming from Oracle-to-Oracle GoldenGate, you might expect CREATE TABLE / ALTER TABLE on the source to show up automatically on the target. For PostgreSQL sources, this never happens — and it's not a GoldenGate gap.

PostgreSQL's logical decoding (the mechanism GoldenGate, Debezium, native logical replication, and AWS DMS all build on) only decodes DML. DDL statements never enter the logical replication stream at the protocol level. Oracle's Integrated Extract can mine DDL because Oracle's redo log encodes it; PostgreSQL's WAL, as exposed via logical decoding, simply doesn't.

Practical takeaway: every schema change needs a manual, coordinated step — apply the DDL on target before the corresponding DML arrives (see Step 8's ordering).

Step 10: Verify End-to-End

# Source
psql -d billing_db -c "INSERT INTO customers (name, email) VALUES ('Test','test@example.com');"

# Target (after a few seconds)
psql -d billing_db -c "SELECT * FROM customers WHERE email='test@example.com';"

If the row appears on target, your pipeline is live: Extract -> trail -> Distribution Path (ws://) -> Receiver -> trail -> Replicat -> target DB.

Quick-Reference: adminclient Commands

GoldenGate MA's CLI (adminclient) is the spiritual successor to ggsci — and for day-to-day operations, it's faster than the browser UI:

/u01/ogg/bin/adminclient
CONNECT http://localhost:9011 deployment PG-Source as oggadmin password OggAdmin_123

INFO ALL
STATUS EXTRACT EBLNG
INFO REPLICAT RBLNG DETAIL
INFO DISTPATH ALL

EDIT PARAMS EBLNG
RESTART EXTRACT EBLNG

ALTER DISTPATH DIST_TO_EC2 TARGET URI ws://<new_ip>:9014/services/v2/targets?trail=rt
START DISTPATH DIST_TO_EC2

Note: ggsci doesn't exist at all in the PostgreSQL MA build — adminclient is the only CLI.

Closing Thoughts

GoldenGate MA for PostgreSQL is well-documented at the conceptual level but thin on practical, "here's exactly what breaks and why" guidance — most of the friction comes from environment plumbing (libpq, ODBC, environment variables, response file schemas) rather than GoldenGate's actual replication logic, which once correctly wired, works reliably and resumes cleanly across restarts and even full host reboots.

If you're setting this up for an interview, a PoC, or a production migration, the single highest-leverage thing you can do is get Steps 2, 5, and 6 right the first time — everything else follows naturally.