none
How to configure "Azure Database for PostgreSQL server" with a postgres_fdw to another PostgreSQL Database

    Question

  • I have a postgres_fdw in a "Azure Database for PostgreSQL" that connects to another PostgreSQL database that resides on an Azure VM running Windows OS and is running on a non-standard port: 5435.

    When executing a: select * from xfer.xx_table, I receive this error:

    SQL Error [08001]: ERROR: could not connect to server "xx_db_xxx_xxx_xx_xxx"
      Detail: could not connect to server: Network is down (0x00002742/10050)
    Is the server running on host "xxx.xxx.xx.xxx" and accepting
    TCP/IP connections on port 5435?

    On the PostgreSQL database that I am connecting to--I have the pg_hba.conf wide open.  In fact, I can take the same Server/Foreign Table creation statements to my local postgres database and it works perfectly.

    So it appears that I don't have something configured correctly in the "Azure Database for PostgreSQL server" instance.

    There doesn't seem to be a lot of options with connection security.

    I did try "Allow access to Azure services" but if I understand it correctly, that is a 0.0.0.0 firewall setting? In any case, it did not help.

    The "Azure Database for PostgreSQL server" is located in East US.

    The original PostgreSQL database resides in South Central US and in a different resource group.  I wouldn't think this should matter since the database is wide open and I can connect to it from outside the Azure platform.

    Am I running into a limitation of the  "Azure Database for PostgreSQL server" instance? Or something else?


    Tuesday, January 8, 2019 2:10 PM

All replies

  • Connections to your Azure Database for PostgreSQL server communicate over port 5432. When you try to connect from within a corporate network, outbound traffic over port 5432 might not be allowed by your network's firewall. If so, you can't connect to your server unless your IT department opens port 5432.

    For more details, refer “Configure a server-level firewall rule” and “Azure Database for PostgreSQL server firewall rules”.

    While creating server, make sure you have used the right port number which is mentioned in postgres_fdw examples.

    Regarding limitations, you may refer “Limitations in Azure Database for PostgreSQL”.

    Hope this helps.

    Wednesday, January 9, 2019 8:57 AM
    Moderator
  • To be clear, the postgres_fdw works as expected from a postgresql database on my local workstation.

    It does not work in the "Azure Database for PostgreSQL" instance.

    CREATE SERVER xx_db_xxx_xxx_xx_xxx
    	FOREIGN DATA WRAPPER postgres_fdw
    	OPTIONS (host 'xxx_xxx_xx_xxx, dbname 'xxx', port '5435');



    Wednesday, January 9, 2019 1:52 PM
  • Hi Jay,

    Do you have sufficient permissions to enable postgres_fdw in your Azure Database for PostgreSQL? If the above query works, then this might work. I see there are a couple more steps required such as :

    CREATE EXTENSION
    CREATE SERVER
    CREATE USER MAPPING
    CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA

    Please follow this additional documentation: postgres_fdw

    The issue with Azure Database for PostgreSQL (PaaS) is that you may not have sufficient privileges to perform the more advanced functionality, such as database replication. You can sync from on-premise to Azure but not Azure to on-premise because you have limited access, requiring superuser access to set-up the sync functionality. The provisioned user is NOSUPERUSER. There are a couple SUPERUSER accounts created when you provision an Azure Database for PostgreSQL instance but those are Azure specific and used to manage your deployment.

    I hope this information is helpful.

    Regards,

    Mike

    Friday, January 25, 2019 12:39 AM
    Moderator
  • Just checking in to see if the above answer helped. If this answers your query, do click “Mark as Answer” and Up-Vote for the same. And, if you have any further query do let us know.

    Tuesday, January 29, 2019 4:18 AM
    Moderator