PostgreSQL – can I use logical replication from a replica’s WAL

amazon-rdsawspostgresqlpostgresql-9.5replication

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?

Best Answer

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/

Related Question