如何配置“用于PostgreSQL服务器的Azure数据库".用postgres_fdw到另一个PostgreSQL数据库 [英] How to configure "Azure Database for PostgreSQL server" with a postgres_fdw to another PostgreSQL Database

问题描述

我在"PostgreSQL的Azure数据库"中有一个postgres_fdw.连接到另一个PostgreSQL数据库的数据库，该数据库位于运行Windows OS的Azure VM上，并且在非标准端口5543上运行.

在执行以下操作时:从xfer.xx_table中选择* ，我会收到此错误:

SQL错误[08001]:错误:无法连接到服务器"xx_db_xxx_xxx_xx_xxx"
 详细信息:无法连接到服务器:网络已关闭(0x00002742/10050)
服务器是否在主机"xxx.xxx.xx.xxx"上运行并接受
端口5435上的TCP/IP连接?

在我要连接的PostgreSQL数据库上-pg_hba.conf完全打开.实际上，我可以将相同的Server/Foreign Table创建语句带到我的本地postgres数据库中，并且效果很好.

因此，我似乎在用于PostgreSQL服务器的Azure数据库"中没有正确配置某些内容.实例.

关于连接安全性似乎没有很多选择.

我确实尝试过"允许访问到Azure服务"但是如果我理解正确，那是0.0.0.0防火墙设置吗?任何状况之下， 它没有帮助.

"用于PostgreSQL服务器的Azure数据库"位于 美国东部.

原始PostgreSQL数据库位于美国中南部 并位于不同的资源组中.我认为这无关紧要，因为数据库是完全开放的，并且我可以从Azure平台外部连接到数据库.

我遇到了用于PostgreSQL服务器的Azure数据库"的局限性吗?实例?还是其他?

解决方案

有关详细信息，请参阅" Azure Database for PostgreSQL服务器防火墙规则".

在创建服务器时，请确保您使用了正确的端口号在提到 postgres_fdw 示例.

关于限制，您可以参考"Azure数据库中PostgreSQL的局限性".

希望这会有所帮助.

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?

解决方案

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.

这篇关于如何配置“用于PostgreSQL服务器的Azure数据库".用postgres_fdw到另一个PostgreSQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！