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