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?

Improve this question
4
  • 1
    As far as I know RDS does not currently support streaming replication. You'd need a "replication" option in security groups, since that's what RDS generates pg_hba.conf from.
    – Craig Ringer
    Commented Jan 28, 2014 at 4:44
  • 1
    @CraigRinger Hmm, that doesn't make any sense - why do they explicitly mention the replication privilege in the docs? From docs.aws.amazon.com/AmazonRDS/latest/UserGuide/…: "Grant and revoke the replication attribute onto all roles that are not the rds-superuser role"
    – Evan
    Commented Jan 28, 2014 at 13:42
  • 2
    Good question - maybe they do support it and it's just woefully underdocumented. If they do I'm quite keen to hear about it, as it'll make it much easier to migrate customers out of RDS. If you get an answer, a comment here noting so would be greatly appreciated so I (and any other commenters) are notified.
    – Craig Ringer
    Commented Jan 29, 2014 at 2:20
  • 1
    Thanks Craig - I gave up on it and just spun up a real 9.3 Postgres EC2 instance. RDS is a nice idea but without the ability to spin up our my read-only replicas it's not an option for me right now. They have the Multi-AZ option but I don't see any way to connect to the backup instance.
    – Evan
    Commented Jan 29, 2014 at 13:04

3 Answers 3

Reset to default
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

Improve this answer
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).

Improve this answer
2
  • 1
    They could well be running a postmaster per user database, and quite likely are because of challenges with the global shared tables, like pg_role and pg_database. Or they might've patched Pg to limit access or filter the contents of the global tables, pg_stat_activity, etc. They haven't said much about the implementation of Pg on RDS and aren't contributing anything to the PostgreSQL community about it, code or otherwise. If they're patching Pg, you might not be able to stream to an unpatched Pg anyway as they might not be fully on-disk compatible.
    – Craig Ringer
    Commented Feb 6, 2014 at 3:26
  • 2
    RDS runs on EC2 instances dedicated to you. Therefore, the actual PG instance is your own, with no multi-tenancy (unless you count the server virtualization).
    – Rajiv Makhijani
    Commented May 2, 2014 at 9:38
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.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.