RDS PostgreSQL replication connection

Question

It appears that RDS for PostgreSQL does not allow streaming replication connection. The pg_hba.conf that the database uses denies the replication connections for all user-accessible methods:

```
backend=> select * from pg_hba_file_rules ;
 rule_number |           file_name           | line_number |  type   |     database      | user_name  | address  | netmask |  auth_method  |  options  | error
-------------+-------------------------------+-------------+---------+-------------------+------------+----------+---------+---------------+-----------+-------
           1 | /rdsdbdata/config/pg_hba.conf |           4 | local   | {all}             | {rdsadmin} |          |         | peer          | {map=rds} |
           2 | /rdsdbdata/config/pg_hba.conf |           5 | local   | {all}             | {all}      |          |         | scram-sha-256 |           |
           3 | /rdsdbdata/config/pg_hba.conf |          11 | host    | {all}             | {rdsadmin} | all      |         | reject        |           |
           4 | /rdsdbdata/config/pg_hba.conf |          12 | host    | {rdsadmin}        | {all}      | all      |         | reject        |           |
           5 | /rdsdbdata/config/pg_hba.conf |          13 | hostssl | {all}             | {+rds_iam} | all      |         | pam           |           |
           6 | /rdsdbdata/config/pg_hba.conf |          14 | host    | {all}             | {+rds_iam} | all      |         | reject        |           |
           7 | /rdsdbdata/config/pg_hba.conf |          15 | host    | {all}             | {all}      | all      |         | md5           |           |
           8 | /rdsdbdata/config/pg_hba.conf |          16 | host    | {replication}     | {all}      | samehost |         | md5           |           |
           9 | /rdsdbdata/config/pg_hba.conf |          17 | host    | {rds_replication} | {all}      | all      |         | md5           |           |
(9 rows)
```

To be clear, logical replication _still_ _works_. I can create replication slots and I can read the change data from them by repeatedly running: `select * from pg_logical_slot_get_binary_changes('myslog', NULL, NULL, 'publication_names', 'mypub', 'proto_version', '2');`

What doesn't work is the streaming connection, that allows PostgreSQL to efficiently send change notifications.

Is there a way to enable it? It's needed for change data streaming solutions like Debezium.

Didier Durand · Answer

Hi,

I hope that my below proposal is not too convoluted.... ;-)

So, in the worst case, you could use logical replication as in https://aws.amazon.com/blogs/database/using-logical-replication-to-replicate-managed-amazon-rds-for-postgresql-and-amazon-aurora-to-self-managed-postgresql/ to create your own self-managed replicated pg instance (on EC2 or elsewhere).

From there, you can then do whatever you want like streaming to another downstream instance.

Best,

Didier

mahankali vamseekrishna · Answer

Hello

You're right, while RDS for PostgreSQL offers logical replication, it restricts streaming connections through the pg_hba.conf file. This prevents directly establishing a connection for tools like Debezium that rely on streaming changes.

Here's a breakdown of the situation and troubleshooting steps:
** Issue:**
RDS managed pg_hba.conf denies access for replication connections using host or hostssl methods.
Logical replication with slots still functions, but streaming connections for efficient change notification are blocked.

**Troubleshooting Steps:**
**Confirmation:**
Verify that your pg_hba.conf configuration indeed rejects connections as you described. You provided the relevant lines showing reject for replication users.

**Alternative Approach (AWS DMS):**
Consider using AWS Database Migration Service (DMS) instead of a direct streaming connection. DMS supports logical replication for PostgreSQL and allows replicating data to various targets, including other databases and S3.
Refer to the AWS DMS documentation for setting up replication: https://aws.amazon.com/rds/postgresql/

**RDS Limitations:**
Unfortunately, there's no direct way to enable streaming replication connections on RDS for PostgreSQL due to its managed nature. RDS manages security configurations, and modifying pg_hba.conf to allow replication connections isn't supported.

https://aws.amazon.com/rds/postgresql/

RDS PostgreSQL replication connection

답변 추가

관련 콘텐츠