9

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?

Evan
  • 199
  • 1
  • 4

3 Answers3

1

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

Neil McGuigan
  • 8,653
  • 5
  • 42
  • 57
0

RDS Postgresql never supports External replica(we can do some cheats, but by native they didn't provide) and we they won't support base backup as well.

Let's understand the basebackup. While shooting this basebackup, it needs replication role and that should be added into the pg_hba.conf file.

In RDS, we have the role called rds_superuser. But we can't do much with that. Read why this is actually not a superuser. https://blog.2ndquadrant.com/the-rds_superuser-role-isnt-that-super/

So, you can grant replication role to the user, but you can't add it into the pg_hdf file. This is the reason for the error.

Then How can we achieve the replication?

There are many ways available for this.

  1. Use AWS DMS.
  2. Use Bucardo replication (this is trigger based replication).
  3. Use Pg_logical.
TheDataGuy
  • 1,986
  • 5
  • 36
  • 67
0

After thinking about this some more, this is probably not possible since Postgres does binary replication at the cluster level, meaning you replicate all databases in the instance. I assume RDS is a multi-tenant Postgres installation, so they can't let you do that (unless they heavily modify postgres itself).

Evan
  • 199
  • 1
  • 4