ADF and PostgreSQL

KD107 1 Reputation point
2022-01-23T05:19:36.16+00:00

Hi,
I am trying to pull data from Postgresql on Azure using Azure Data Factory but have no luck. Both ADF and VM are in the same Azure Vnet. In my VM networking settings I allowed port 5432 inbound access from ADF to VirtualNetwork. Similar outbound vnet to vnet. I also installed ODBC driver on VM and added it to firewall. I installed also the selfhosted integration runtime on VM which connects to ADF. When creating a new Linked Service on ADF I chose PostgreSQL as source. Not able to establish connection though. Tried different settings: -IP and DNS as servername -self hosted and auto resolve integration runtime -public and private IP -no encryption and encryption -temporarily turned off firewall on vm to test still no success. What am I missing? password and database names are correct.

The error message I am getting when testing connection: Error code 9603 Details ERROR [08001] [Microsoft][ODBC PostgreSQL Wire Protocol driver]Connection refused. Verify Host Name and Port Number. ERROR [HY000] [Microsoft][ODBC PostgreSQL Wire Protocol driver]Can't connect to server on 'xx.xxx.xxx.xx'

I would appreciate it if anyone could help. Thank you

Azure Virtual Machines
Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
7,130 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,544 questions
Azure Database for PostgreSQL

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ShaikMaheer-MSFT 37,896 Reputation points Microsoft Employee
    2022-01-24T12:18:53.707+00:00

    Hi @KD107 ,

    Thank you for posting query in Microsoft Q&A Platform.

    If your Postgresql is located inside an on-premises network, an Azure virtual network, or Amazon Virtual Private Cloud, you need to configure a self-hosted integration runtime to connect to it.

    If your data store is a managed cloud data service, you can use the Azure Integration Runtime. If the access is restricted to IPs that are approved in the firewall rules, you can add Azure Integration Runtime IPs to the allow list.

    You can also use the managed virtual network integration runtime feature in Azure Data Factory to access the on-premises network without installing and configuring a self-hosted integration runtime.

    The Integration Runtime provides a built-in PostgreSQL driver starting from version 3.7, therefore you don't need to manually install any driver.

    While creating linked service use "PostgreSQL" connector.
    167826-image.png

    ADF need An ODBC connection string to connect to Azure Database for PostgreSQL. Below is sample format.
    Server=<server>;Database=<database>;Port=<port>;UID=<username>;Password=<Password>

    Below is sample json of linked service.
    167873-image.png

    Click here to know more about PostgreSQL connector configuration details.

    Could you please try to follow above all points and see if that helps?

    Hope this will help. Please let us know if any further queries.

    ---------------

    Please consider hitting Accept Answer. Accepted answers helps community as well.

    1 person found this answer helpful.