Allow two RDS databases to connect to each other for replication

Question

I have two RDS databases within the same VPC and with the same security group.

I'm following this guide AWS: how to enable bi-directional replication using pglogical to set up bi-directional replication between the two databases using pglogical.

However, when I try to set up a subscription from one database to the other, I get this error:

ERROR:  could not connect to the postgresql server: timeout expired

DETAIL:  dsn was:  host=xxx port=5432 sslmode=require dbname=main user=xxx password=xxx

I have a rule in my security group to allow all traffic from instances with that security group, but it doesn't seem to help:

I also have a rule to allow all traffic from all the addresses within my VPC:

When I add a rule to allow inbound traffic from all IP addresses (0.0.0.0/0), it works.

Why does my rule to allow traffic from my security group not work by itself?

Answer 1

This is for sure a Security Group setup error due to 0.0.0.0/0 working.

A security group is not like a typical subnet. It does not allow traffic between everything in the same security group.

What you want is a self-referencing security group rule.

You need to allow Inbound/Outbound Port (likely 5432) to the security group you want to talk too.

In the "source" use the ID of the Security Group.

Answer 2

One way to troubleshoot this is to enable the VPC Flow Logs in your VPC and find the specific records related to traffic from RDS-A to RDS-B.

This will help you ascertain whether the ports and src/dst IPs are what you expect them to be.

Answer 3

Create two security groups

    source-db-sg --> for Source RDS instance
    destination-db-sg  --> for destination  RDS instance

• Add security group rules in each security group created above.

source-db-sg:
    -- inbound at 5432 from sg(destdb-sg)
destination-db-sg : 
    -- outbound at 5432 to sg(destdb-sg)