Tag: migration

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

~ Shadab Mohammad ~ Leave a comment

Most migrations from on-prem PostgreSQL to Aurora rely on logical replication or AWS DMS 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 EC2.

Architecture in brief:

On-prem PostgreSQL (A) → Physical replication → EC2 PostgreSQL (B) → Logical replication (AWS DMS) → Aurora PostgreSQL (C)

This architecture is valid and commonly 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 (EC2 PostgreSQL) via physical replication
  • B (EC2 PostgreSQL) → C (Aurora PostgreSQL) via logical replication / AWS DMS

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 EC2 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 DMS

Trade-offs:

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

This pattern is especially useful in regulated environments or where direct connectivity to AWS is restricted.

It is not the simplest design, but it is a powerful one when used in the right context.

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 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.conf is 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 EC2 Postgres Instance B

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

sudo systemctl stop postgresql-17
mv /var/lib/pgsql/17/data/ /var/lib/pgsql/17/data_old

Take backup on EC2 instance (B) :

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

This:

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

Start B as Standby

sudo systemctl start postgresql-17

Verify:

SELECT * FROM pg_stat_wal_receiver;
 15436 | streaming | 0/7000000         |                 1 | 0/7384628   | 0/7384628   |            1 | 2026-04-11
 12:08:24.757316+00 | 2026-04-11 12:08:24.75985+00 | 0/7384628      | 2026-04-11 12:08:24.75581+00 |           | 1
0.140.1.41 |        5432 | user=replicator password=******** channel_binding=prefer dbname=replication host=10.140
.1.41 port=5432 fallback_application_name=walreceiver sslmode=prefer sslnegotiation=postgres sslcompression=0 sslc
ertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 targ
et_session_attrs=any load_balance_hosts=disable

🔹 Phase 2: Prepare B for Logical Replication / DMS

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)

SELECT * FROM pg_create_logical_replication_slot('dms_slot', 'pgoutput');

🔹 Phase 3: Setup AWS DMS (B → C)

1. Create DMS Components

In Amazon Web Services:

  • DMS Replication Instance
  • Source Endpoint → B
  • Target Endpoint → Aurora (C)

2. Configure Source Endpoint (B)

  • Enable CDC
  • Provide replication slot name (dms_slot)
  • Plugin: pgoutput

3. Configure Target Endpoint (C)

Aurora PostgreSQL:

  • Ensure parameter group has:
rds.logical_replication = 1

4. Create DMS Task

  • Migration type: Full load + CDC
  • Table mapping: select schemas/tables
  • Enable:
    • Ongoing replication
    • LOB handling if needed

5. Start Task

DMS will:

  1. Do initial full load
  2. Stream ongoing changes from B

Why This Works

  • Physical replication ensures exact byte-level copy
  • Logical replication enables selective, flexible migration
  • B acts as:
    • Load buffer
    • Isolation layer
    • Transformation point

Advantages

1. Offload Source (A)

  • No logical decoding overhead on A
  • Reduces production risk

2. Network Isolation

  • B can sit inside AWS VPC
  • Avoids direct on-prem → Aurora connectivity

3. Migration Flexibility

  • Filter tables
  • Transform data via DMS

4. Reduced Blast Radius

  • Issues in DMS don’t impact A

5. Replay Capability

  • You can restart DMS without touching A

Disadvantages / Risks

1. Replication Lag Compounding

  • A → B lag
  • B → C lag
    👉 Total lag increases

2. Logical Decoding on Standby Limitations

  • Requires newer PostgreSQL versions (≥14)
  • Some edge cases with WAL replay

3. Operational Complexity

  • Two replication mechanisms
  • More monitoring required

4. Failover Complications

If A fails:

  • B becomes primary?
  • Logical slots may break

5. WAL Retention Pressure

  • Logical slots prevent WAL cleanup
  • Risk of disk bloat

6. DMS Limitations

  • Doesn’t replicate:
    • Sequences perfectly
    • Some DDL changes
    • Extensions

When This Architecture Makes Sense

Use it when:

✔ Direct A → Aurora is not feasible
✔ You want zero impact on source DB
✔ You need data transformation/filtering
✔ You want controlled cutover with buffer layer

Avoid it when:

✖ Simplicity is priority
✖ Low-latency replication required
✖ PostgreSQL version < 14

PostgreSQL 14 Streaming Replication on Oracle Cloud Infrastructure (OCI) VM

~ Shadab Mohammad ~ Leave a comment

Introduction

PostgreSQL 14 is a powerful and feature-rich open-source relational database management system.

In this guide, we’ll walk through the process of installing and configuring PostgreSQL 14 on Oracle Cloud Infrastructure (OCI) with Oracle Linux 8. The setup includes one master node and two slave nodes, forming a streaming replication setup.

Note : The word slave and replica is used interchangeably in this article when referring to anything which is not a master node

ARCHITECTURE

OS– Oracle Linux 8

PostgreSQL Version – 14.10

1 Master Node – IP- 10.180.2.102

2 Slave Nodes – IPs- 10.180.2.152, 10.180.2.58

3 Node PostgreSQL 14 Cluster on OCI

You can create a DR architecture using streaming replication. Put 1 replica in the same region and 2 additional replicas in another OCI region.The VCN’s in both OCI regions have to be remotely peered using a DRG and all routes should permit the traffic over the different subnets and allow communication over port 5432. You can refer to this articleo n how to configure VCN remote peering on OCI: https://docs.oracle.com/en-us/iaas/Content/Network/Tasks/scenario_e.htm

4-Node Cross-Region PostgreSQL 14 Cluster on OCI

SETUP

Step 1: Installing PostgreSQL 14 on Master and Slave Nodes

Start by updating the system and installing necessary dependencies on both the master and slave nodes:

sudo dnf update -y

sudo dnf module list postgresql

sudo yum -y install gnupg2 wget vim tar zlib openssl

sudo dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

sudo yum -qy module disable postgresql

sudo yum install postgresql14-server -y

sudo yum install postgresql14-contrib -y

sudo systemctl enable postgresql-14

sudo postgresql-14-setup initdb

sudo systemctl start postgresql-14

sudo systemctl status postgresql-14

Step 2: Enabling Postgres User and Streaming Replication

Enable the Postgres user and configure streaming replication on both the master and slave nodes:

sudo -iu postgres

psql -c "ALTER USER postgres WITH PASSWORD 'RAbbithole1234#_';"

tree -L 1 /var/lib/pgsql/14/data

psql -U postgres -c 'SHOW config_file'

              config_file
----------------------------------------
 /var/lib/pgsql/14/data/postgresql.conf
(1 row)

Step 3: Configuring pg_hba.conf and Firewall Settings

Update the pg_hba.conf file on both the master and slave nodes to allow connections and adjust firewall settings:

sudo -iu postgres

vim /var/lib/pgsql/14/data/pg_hba.conf

# If ident is available in file then replace 'ident' with 'md5' or 'scram-sha-256'

# Change this line to allow all hosts 0.0.0.0/0 

# IPv4 local connections:
host    all             all             0.0.0.0/0               scram-sha-256

exit

sudo systemctl restart postgresql-14

#Whitelist Ports on Instance

sudo firewall-cmd --list-ports

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

sudo firewall-cmd --reload

sudo firewall-cmd --list-ports

Step 4: Configuring Master Node for Streaming Replication

On the master node (10.180.2.102), configure streaming replication:

sudo -iu postgres

mkdir -p /var/lib/pgsql/14/data/archive

vim /var/lib/pgsql/14/data/postgresql.conf

## Uncomment and set below parameters
listen_addresses = '*'
archive_mode = on    # enables archiving; off, on, or always
archive_command = 'cp %p /var/lib/pgsql/14/data/archive/%f' 
max_wal_senders = 10            # max number of walsender processes
max_replication_slots = 10      # max number of replication slots
wal_keep_size = 50000           # Size of WAL in megabytes; 0 disables
wal_level = replica             # minimal, replica, or logical
wal_log_hints = on               # also do full page writes of non-critical updates

## Only set below if you want to create synchronous replication##
synchronous_commit = remote_apply
synchronous_standby_names = '*'

exit

sudo systemctl restart postgresql-14

netstat -an | grep 5432

Update pg_hba.conf on the master node:

sudo -iu postgres

vim /var/lib/pgsql/14/data/pg_hba.conf

#Add below entry to end of file

host    replication     all             10.180.2.152/32         scram-sha-256
host    replication     all             10.180.2.58/32         scram-sha-256

exit

sudo systemctl restart postgresql-14

Step 5: Configuring Slave Nodes for Streaming Replication

On the slave nodes (10.180.2.152 and 10.180.2.58), configure streaming replication:

sudo -iu postgres

mkdir -p /var/lib/pgsql/14/data/backup

vim /var/lib/pgsql/14/data/pg_hba.conf

exit

sudo systemctl restart postgresql-14

sudo chmod 0700 /var/lib/pgsql/14/data/backup

sudo -iu postgres

#Backup and Clone Database from Slave Node using IP of Master Node

pg_basebackup -D /var/lib/pgsql/14/data/backup -X fetch -p 5432 -U postgres -h 10.180.2.102 -R

cd /var/lib/pgsql/14/data/backup

cat postgresql.auto.conf

#Stop the Instance and Restart using Data in New location

/usr/pgsql-14/bin/pg_ctl stop

/usr/pgsql-14/bin/pg_ctl start -D /var/lib/pgsql/14/data/backup

waiting for server to start....2023-11-27 03:36:48.205 GMT [169621] LOG:  redirecting log output to logging collector process
2023-11-27 03:36:48.205 GMT [169621] HINT:  Future log output will appear in directory "log".
 done
server started

Step 6: Checking Replication Status from Slave Nodes

Check the status of streaming replication from slave nodes using psql:

psql -h localhost -p 5432 -U postgres -d postgres

postgres# select pg_is_wal_replay_paused();

 pg_is_wal_replay_paused
-------------------------
 f
(1 row)

Note - f means , recovery is running fine. t means it is stopped.



postgres# select * from pg_stat_wal_receiver;

-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 414090
status                | streaming
receive_start_lsn     | 0/A000000
receive_start_tli     | 1
written_lsn           | 0/A002240
flushed_lsn           | 0/A002240
received_tli          | 1
last_msg_send_time    | 2023-12-04 11:40:51.853918+00
last_msg_receipt_time | 2023-12-04 11:40:51.853988+00
latest_end_lsn        | 0/A002240
latest_end_time       | 2023-11-30 08:16:43.217865+00
slot_name             |
sender_host           | 10.180.2.102
sender_port           | 5432
conninfo              | user=postgres password=******** channel_binding=prefer dbname=replication host=10.180.2.102 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

Step 7: Checking Replication Status from Master Node

On the master node, check the status of replication:

psql -h localhost -p 5432 -U postgres -d postgres:

postgres# select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------
pid              | 382513
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 10.180.2.152
client_hostname  |
client_port      | 47312
backend_start    | 2023-11-30 08:11:42.536364+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/A002240
write_lsn        | 0/A002240
flush_lsn        | 0/A002240
replay_lsn       | 0/A002240
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-12-04 11:43:12.033364+00

-[ RECORD 2 ]----+------------------------------
pid              | 382514
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 10.180.2.58
client_hostname  |
client_port      | 35294
backend_start    | 2023-11-30 08:11:42.542539+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/A002240
write_lsn        | 0/A002240
flush_lsn        | 0/A002240
replay_lsn       | 0/A002240
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2023-12-04 11:43:10.113253+00

Step 8: Additional Notes and References

To restart slave nodes, use the following commands:

/usr/pgsql-14/bin/pg_ctl stop

sudo rm -rf /var/lib/pgsql/14/data/backup/postmaster.pid

/usr/pgsql-14/bin/pg_ctl start -D /var/lib/pgsql/14/data/backup

REFERENCES:
  1. DBA Class
  2. Narasimman Tech
  3. PostgreSQL Continuous Archiving Documentation
  4. Stack Overflow
  5. Girders
  6. Kinsta

Follow this comprehensive guide to set up PostgreSQL 14 streaming replication on Oracle Cloud Infrastructure with Oracle Linux 8. Ensure high availability and robust backup capabilities for your PostgreSQL database