I have a postgresql database that is in the cloud, and I want to connect this remote database to my localhost using foreign table, so I did:
CREATE SERVER testeServer
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'my ip v4', port '5432',dbname 'my db');
CREATE USER MAPPING FOR my user
SERVER testeServer
OPTIONS (user 'userlocal', password 'passuserlocal');
IMPORT FOREIGN SCHEMA public
LIMIT TO (jsontest, json, jsonlog)
FROM SERVER testeServer
INTO public;
I got my ip from my cmd by running the ipconfig command getting the parameter: IPv4 address
The creation of the server and user mapping happen normally, but the import of the schema to the server does not happen the way it should and gives me this error:
ERROR: could not connect to server "testeserver"
Detalhe: connection to server at "my ip", port 5432 failed: Connection timed out
Is the server running on that host and accepting TCP/IP connections?
I read some articles that asked me to change some postgresql files, like pg_hba.conf mine is like this:
# "local" is for Unix domain socket connections only
local all all scram-sha-256
# IPv4 local connections:
host all all 0.0.0.0/0 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
mine looked like this: listen_addresses = "*"
I also defined entry rules in my firewall, but it still didn't work, I disabled the firewall, and it didn't work either
I don't know if I was very clear in what I'm trying to do, what I did:
I connected to my cloud server using dbeaver, and on my localhost I have data that I want to pass to the server in the cloud, that's why I'm taking this approach..
even with all the changes I showed above, why do I get the error:
ERROR: could not connect to server "testeserver"
Detalhe: connection to server at "my ip", port 5432 failed: Connection timed out
Is the server running on that host and accepting TCP/IP connections?
I'm using Windows 11