Postgresql – How to configure replication from PostgreSQL (RDS) to vanilla PostgreSQL

amazon ec2awspostgresqlreplication

I have an Amazon RDS Postgres DB and I created a "replicator" user and granted it the "replication" privilege. 

db=> create role replicator login password 'something';
CREATE ROLE
db=> alter role replicator replication;
ALTER ROLE
db=>

However, when I try to run pg_basebackup from the target host to create the replica, I get a pg_hba.conf error:

# pg_basebackup -D /data/from-master -U replicator -h db-master.example.com -v
pg_basebackup: could not connect to server: FATAL:  no pg_hba.conf entry for replication connection from host "10.1.20.19", user "replicator", SSL on
FATAL:  no pg_hba.conf entry for replication connection from host "10.1.20.19", user "replicator", SSL off

The problem is that, this being RDS, I can't get to the pg_hba.conf at all. Any ideas?

Best Answer

You can use AWS Database Migration Services (DMS) to replicate full and/or change data to another system (such as S3 or a PostgreSQL database running on-premises or on EC2)

  • make sure use DMS v3.3 if you use PG 10+
  • create a new pg parameter group. set:
  • rds.logical_replication=1
  • save. modify the RDS instance to use that parameter group, apply changes
  • reboot the RDS instance

You'll need a VPN connection if your PG instance is on-prem (not on AWS network)

DMS supports DDL changes too

You can use AWS Schema Conversion Tool to migrate your schema from source to target

Related Question