Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 Jan 28 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 Jan 28 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 Jan 29 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 Jan 29 at 13:04
add comment

1 Answer

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).

share|improve this answer
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 Feb 6 at 3:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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