Migrating On-Premise PostgreSQL to OCI PostgreSQL using a Hybrid Replication Architecture

Most zero downtime migrations from on-prem PostgreSQL to OCI PostgreSQL rely on logical replication directly from the source. But what if you want to reduce load on your production system, add a control layer, or deal with network constraints?

I recently explored an approach using an intermediary PostgreSQL instance on OCI Compute.

Architecture in brief

** On-prem PostgreSQL (A) **
    |
    |
    |
(Physical replication)
    |
    |
    V
** OCI Instance PostgreSQL (B)  **
    |
    |
    |
(Logical replication)
    |
    | 
    V                                                              
 ** OCI PostgreSQL (C) **

This architecture is used in complex migrations, especially when you want to decouple source load, add transformation control, or work around network/security constraints.

We’re essentially proposing a hybrid replication chain:

• A (on-prem PostgreSQL) → B (OCI Instance PostgreSQL) via physical replication
• B (OCI Instance PostgreSQL) → C (OCI PostgreSQL) via logical replication / OCI Goldengate

Important Note : You can use native postgres logical replication, pglogical or any other logical tool for the replication from B → C.
Becuase the target is on OCI PostgreSQL on Oracle Cloud Infrastructure, we’re using OCI Goldengate; which is an industry leading real-time replication solution.

Why this is interesting

• Physical replication ensures a low-impact, byte-level copy from the source
• Logical replication from the intermediary allows selective migration and transformation
• The OCI compute layer acts as a buffer, isolating production from migration tooling

Key benefits

• Zero logical decoding overhead on production
• Better control over migration scope
• Improved fault isolation
• Flexible transformation using OCI Goldengate

Trade-offs

• Increased replication lag due to chaining
• More operational complexity
• WAL retention needs careful monitoring
• Logical decoding on standby requires newer PostgreSQL versions

Pre-Requisities on both A and B

1. Ensure Port 5432 is added to Firewall whitelist on both hosts for ingress and also on the Network Security Group of the instance

sudo firewall-cmd --zone=public --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

2. listen_address in postgresql.conf is set to ‘*’

3. pg_hba.confis set to allow all hosts

host    all             all             0.0.0.0/0               scram-sha-256

Step-by-Step Implementation

Phase 1: Setup Physical Replication (A → B)

1. Prepare On-Premise Source (A)

Update postgresql.conf:

wal_level = replica
max_wal_senders = 10
wal_keep_size = 2048 #2GB
hot_standby = on

Update pg_hba.conf:

host    replication     replicator      <Server-B-IP>/32         scram-sha-256

Create replication user:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'password';

2. Take Base Backup to OCI Instance with Postgres -Instance B

On OCI instance (B) install a fresh PostgreSQL engine or disable the previous Postgresql database for a fresh restore :

mv /var/lib/pgsql/17/data/ /var/lib/pgsql/17/data_old/

sudo systemctl stop postgresql-17

Take backup on OCI instance postgres (B) :

pg_basebackup \
  -h <Server-A-IP> \
  -D /var/lib/pgsql/17/data/ \
  -U replicator \
  -P \
  -R \
  -X stream

-h <Server-A-IP>Host (source database server)

• Specifies the primary server (A) to connect to
• Can be:
• IP address
• DNS name

-D /var/lib/pgsql/17/data/Destination directory (data directory on B)

• Where the base backup will be written
• This becomes the entire PostgreSQL data directory on B

⚠️ Important:

• Must be empty or non-existent
• Will contain:
• base/, pg_wal/, global/, etc.

👉 This is effectively:

“Make this directory an exact clone of A”

-U replicatorReplication user

• PostgreSQL user used to connect
• Must have:
• REPLICATION LOGIN privilige

-PProgress output

• Shows real-time progress of backup:
• Total size
• Data transferred
• Percentage

-RWrite replication configuration automatically

This is a very important flag 👇

It does two things:

1. Creates standby.signal

• Tells PostgreSQL:
• “Start in standby (replica) mode”

2. Writes connection info into:

postgresql.auto.conf

primary_conninfo = 'host=Server-A-IP user=replicator password=xxx'

👉 Without -R, you would have to:

• Manually create standby.signal
• Manually configure primary_conninfo

-X streamWAL (Write-Ahead Log) handling method

This ensures backup consistency.

What it does:

• Streams WAL files in parallel with the base backup
• Ensures backup is transactionally consistent

Alternatives:

-X streamStream WAL during backup (most common)
-X fetchFetch WAL after backup
-X noneNo WAL (⚠️ unsafe for replication)

Get Shadab Mohammad’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

👉 Always use stream for replication setups

⚡What this Does :

• Copies data
• Creates standby.signal
• Configures replication automatically

🔄 What Happens End-to-End

When you run this command:

1. Connects to A using replication protocol
2. Starts a consistent snapshot
3. Copies all data files
4. Streams WAL simultaneously (-X stream)
5. Writes standby config (-R)
6. Prepares B to start as a replica

📦 Result on B

After execution, your directory contains:

/var/lib/pgsql/17/data/
├── base/
├── global/
├── pg_wal/
├── postgresql.auto.conf   <-- contains primary_conninfo
├── standby.signal         <-- enables replica mode

✅ Pro Tips (Real-World)

Add compression (faster over network)

--compress=server-gzip

Limit bandwidth (avoid saturating link)

--max-rate=100MB

Use replication slot (prevent WAL loss)

--slot=my_slot --create-slot

⚠️ Common Mistakes

• Forgetting -R → replica won’t start automatically
• Using non-empty -D directory
• Not allowing replication in pg_hba.conf
• Not enough WAL retention on A

Start B as Standby

sudo systemctl start postgresql-17

Verify:

SELECT * FROM pg_stat_wal_receiver;

Phase 2: Prepare B for Logical Replication / OCI Goldengate

This is the critical step

By default, a physical replica cannot act as a logical replication source unless configured properly.

1. Enable Logical Decoding on B

Update on B:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Important:
–> Requires restart
–> In PostgreSQL 14+, logical decoding on standby is supported

Create Logical Replication Slot (if using native or pglogical. Goldengate creates it’s own replication slot)

SELECT * FROM pg_create_logical_replication_slot('goldengate_slot', 'test_decoding');

Phase 3: Setup OCI Goldengate (B → C)

For detailed steps refer these articles :

[1] https://medium.com/oracledevs/oci-goldengate-to-perform-cross-region-replication-for-oci-postgresql-dbsystems-b14d4b7623d5

[2] https://medium.com/@shadabshaukat/zero-downtime-migration-to-oci-postgresql-from-amazon-rds-postgresql-using-oci-goldengate-76c80c631c45

1. Using OCI Goldengate:

a. Create GoldenGate deployment in OCI

b. Configure Extract on B :

• Capture changes using logical decoding

c. Configure Replicat to Target:

• OCI PostgreSQL

2. Perform Initial Load + CDC:

a. Perform initial load using:

• Native dump/restore OR
• GoldenGate initial load

b. Start CDC pipeline:

GoldenGate streams ongoing changes from B → C

Advantages

Zero Impact on Source (A)

No logical decoding or CDC load on production

High Control Over Migration

GoldenGate provides:

• Filtering
• Transformation
• Conflict handling

Cloud-Native Integration

Seamless integration within OCI ecosystem

Better Failure Isolation

Failures in replication do not impact production

Trade-offs

Increased Complexity

Two replication layers instead of one

Replication Lag

Lag compounds:

• A → B
• B → C

WAL Retention Management

Logical replication slots on B must be monitored

Operational Overhead

Requires monitoring:

• Physical replication health
• GoldenGate processes

When Should You Use This Pattern?

This approach is ideal when:

• Production systems cannot handle additional load especially with replication slots which is an overhead of logical replication
• Strict network/security boundaries exist
• Advanced transformation or filtering is required
• Migration must be highly controlled and observable

Final Thoughts

This hybrid architecture is not the simplest path, but it is a powerful one. By combining PostgreSQL physical replication with OCI GoldenGate’s logical capabilities, you gain a robust and flexible migration pipeline that minimizes production risk while maximizing control.