Im trying to remote connect MsSQL from PostgreSQL using Pg-admin, but every time i try to connect using dblink_connect function, im not able to connect and the Pg-admin throw the error :
ERROR: could not establish connection
DETAIL: could not connect to server: Connection timed out (0x0000274C/10060)
Is the server running on host "xxx.xxx.xxx.xxx" and accepting
TCP/IP connections on port 5432?
SQL state: 08001
The things I have tried:
specify the port number in the connection string in db_link which is 8583 but it still throw the error as below
ERROR: could not establish connection
DETAIL: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
SQL state: 08001
changing the port in postgresql.conf file from 5432 to 8583 and restart, but it still throws error as below
ERROR: could not establish connection
DETAIL: could not connect to server: Connection timed out (0x0000274C/10060)
Is the server running on host "xxx.xxx.xxx.xxx" and accepting
TCP/IP connections on port 5432?
SQL state: 08001
So, can I have more than one port number in the postgresql.conf
file where hoping it will work that way.
You cannot connect to Microsoft SQL Server from PostgreSQL using dblink
, that is only for connecting to remote PostgreSQL databases.
You should use tds_fdw and define foreign tables in PostgreSQL, but that works only for reading data.
If you need to write to the Microsoft SQL Server database, you have few options:
You could write to the Microsoft SQL Server database from your client code rather than from the PostgreSQL database; that is probably the most viable solution.
You could write a PL/Python or PL/Perl stored procedure and write to the remote database from there.
User contributions licensed under CC BY-SA 3.0