PostgreSQL - can I use logical replication from a replica's WAL?

Question

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?

Answer 1

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/

• Ondra Žižka: 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: My question is rather whether I am pursuing an imposible setup, not supported by PostgreSQL.
• TheDataGuy: 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.
• Ondra Žižka: 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?