question

MohsenAkhavan avatar image
0 Votes"
MohsenAkhavan asked RabbaniAzra-2843 edited

Logical replication problem with Azure Postgresql Flexible edition

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://docs.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-postgresql
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @MohsenAkhavan Thanks for posting your question. We are looking into this and will get back.
Regards,
Oury

1 Vote 1 ·

Hello @MohsenAkhavan sorry for the delay. I am investigating the issue . I will get back to you shortly.
Regards,
Oury

1 Vote 1 ·

Hello @MohsenAkhavan Are you using native logical replication or pglogical?
Regards,
Oury

0 Votes 0 ·
Show more comments
MohsenAkhavan avatar image
0 Votes"
MohsenAkhavan answered

This problem solved by executing the below command on Azure PostgreSQL Flexible (Slave)

alter user azure_pg_admin with login;

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RabbaniAzra-2843 avatar image
0 Votes"
RabbaniAzra-2843 answered

Hi @MohsenAkhavan,

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MohsenAkhavan avatar image
0 Votes"
MohsenAkhavan answered

Hi @RabbaniAzra-2843

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

https://docs.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,

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RabbaniAzra-2843 avatar image
0 Votes"
RabbaniAzra-2843 answered RabbaniAzra-2843 edited

Thanks @MohsenAkhavan 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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.