2

I would like to use logical replication. However, as PostgreSQL works now, it can eat up the whole disk and then block the database. Especially in Amazon Web Services RDS, this is a problem because RDS won't give you SUPERUSER role so you could connect and turn off the slots.

So I set up an RDS replica and set rds.logical_replication to 1. But it seems that RDS replica doesn't react to that - SHOW ALL says wal_level = hot_standby while it should be logical.

This is what I want to achieve:

RDS master
    WAL --> RDS replica
                logical replication ON
                WAL + wal2json  ---->    pg_recvlogical on remote client

Is this possible?

Reference: wal_level

When I try to create a slot, I get:

FATAL: no pg_hba.conf entry for replication connection from host "...", user "postgres", SSL off

This looks like AWS is not putting the right entries to pg_hba.conf - i.e. does not allow the postgres user to create the replication slots.

Is my assumption right? Is there any way around that?

2

You need to assign rds_superuser and rds_replication role to the user.

Refer here: https://aws.amazon.com/blogs/aws/amazon-rds-for-postgresql-new-minor-versions-logical-replication-dms-and-more/

  • Thanks, but I replicate from RDS to RDS. The problem seems to be that rds.logical_replication doesn't do the necessary changes in the replica. – Ondra Žižka Aug 13 '18 at 9:51
  • My question is rather whether I am pursuing an imposible setup, not supported by PostgreSQL. – Ondra Žižka Aug 13 '18 at 9:52
  • Yup, you are correct, if you need to setup RDS to RDS, the option is in preview mode, RDS postgres10(or 11), this feature is available. But you can't use this for general RDS. – Bhuvanesh Aug 13 '18 at 11:17
  • Do I get you right: RDS PostgreSQL 10+ will have an experimentally allow RDS replica WAL at logical level and the replica will be able to have logical replication slots? And no way for 9.x? – Ondra Žižka Aug 13 '18 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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