Logical replication problem with Azure Postgresql Flexible edition

Mohsen Akhavan 936 Reputation points
2021-05-21T13:45:25.497+00:00

I have a PostgreSQL 12 on VM and I created an Azure Database PostgreSQL Flexible. I want to sync and replicate data from PostgreSQL on VM to Azure Database PostgreSQL Flexible.
I use the below link of documents for this logical replication issue.
https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-logical
I tested the replication method between on-premise (master) and Azure Database for PostgreSQL Flexible(Salave) for replicating. I have done all steps but I didn't receive the result. Also, I didn't receive any errors.
I tested this replication method on other scenarios and all of them run without problem.
(Master to Slave)
On-Premise to On-Premise (Worked)
Azure PostgreSQL Flexible to On-premise (Worked)
On-premise to Azure PostgreSQL Flexible (Not Worked)

Is there anyone with this experience?

Azure Database for PostgreSQL

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rabbani Azra 1 Reputation point
    2021-11-03T16:50:09.103+00:00

    Hi @Mohsen Akhavan ,

    I saw this thread while looking for a solution for my scenario. It seems that you were able to setup logical replication between on-prem and Azure database.

    If that's correct, Can you please guide me how did you update the pg_hba.conf file so the 2 db can do talk?

    My on prem is secondary and Azure flexible server is primary.

    Currently i am getting an error on the point where we create the subscription, which can be due to other network settings and we are looking into it but other than that i didn't find any way to change the pg_hba.conf file

    Do you have any documentation that i can refer to? We need to do this urgently if you can help soon it would be the best.

    Thanks

    0 comments
  2. Mohsen Akhavan 936 Reputation points
    2021-11-03T21:04:27.653+00:00

    Hi @Rabbani Azra

    Yes sure. In my solution on-premise is primary. I used the above documents.

    https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-logical

    about pg-hba.conf you should config trust IP and user and replication.

    Is it possible to share your error?

    Regards,

    0 comments
  3. Rabbani Azra 1 Reputation point
    2021-11-03T23:55:41.167+00:00

    Thanks @Mohsen Akhavan for your response.

    I am also following the same document but i don't see it describing how to set pg_hba.conf.

    I've done replication before between AWS and on prem so I am familiar with all those basic things that this document shares.

    My error is:
    When enabling replication between my cloud instance and my on prem instance. I am getting the following error.

    I get this error when i create subscription from the on-prem db.

    ERROR: could not connect to the postgresql server: could not connect to server: No route to host
    Is the server running on host "<ip-of azure>" and accepting
    TCP/IP connections on port 5432?

    From the on-prem instance i have checked following, so no issue of port blocking on the on-prem side.

    netstat -nlp | grep 5432
    tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 3146/postmaster

    I am connected to both the dbs via my postgres admin client, also we are using open vpn to connect to these instances.

    >>about pg-hba.conf you should config trust IP and user and replication.

    I know that i need to add an entry to the pg_hba.conf for my secondary db ip.

    How can i do this?
    Can i edit the file itself? if not then,
    Does the Azure portal offers any option to set these for the MI postgres instances?

    I am very new to Azure so if you have any document that can simply guide for this that would be awesome.

    Thanks

    0 comments