I am trying to replicate my on-prem postgres 12.4 database to AWS RDS 13.2 with minimal downtime.
I tested pglogical to create a replica from my source db to another on prem host and found that pglogical requires superuser role such as postgres with Replication attribute.
I followed all the steps of setting logical configs on the target RDS instance and onprem source host (https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-from-postgresql-on-amazon-ec2-to-amazon-rds-for-postgresql-using-pglogical.html).
I am getting a timeout error at the create subscription step with a message that it is unable to contact source. I have allowed connections for RDS endpoint in my source pg_hba.confg.
Since rds_superuser in RDS is not really a super user, I am wondering if replication from on-prem to RDS using pglogical even possible?
Any ideas or previous experiences?
Appreciate if someone could share.
Thanks
A timeout error on connection makes it sound like your on-prem firewall is not allowing RDS to initiate a connection to your on-prem database. Which certainly makes sense, as most firewalls would block that, and you don't describe having changed it.
It is true that rds_superuser is not a real superuser, but AWS has hacked around that problem to make it work for replication anyway.