Not able to connect to foreign server

Question

I am getting following error on PostgreSQL:

SQL Error [08001]: ERROR: could not connect to server "regionaldb_server"
  Detail: connection to server at "localhost" (127.0.0.1), port 5404 failed: Connection refused

Following are the settings to create the foreign server setup on local DB:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER regionaldb_server
                  FOREIGN DATA WRAPPER postgres_fdw
                  OPTIONS (host 'localhost', dbname 'platform_test', port '5404');

CREATE USER MAPPING FOR current_user
                  SERVER regionaldb_server
                  OPTIONS (user 'admin', password 'nimda');

CREATE FOREIGN TABLE "users-meta_foreign" (
              id uuid,
              email VARCHAR(500),
              phone VARCHAR(10),
              "internationalCode" VARCHAR(5),
              region VARCHAR(255) NOT NULL,
              "userRoles" TEXT NOT NULL,
              "createdAt" timestamptz NOT NULL,
              "updatedAt" timestamptz NOT NULL,
              "deletedAt" timestamptz
          )
          SERVER regionaldb_server
          OPTIONS (schema_name 'platform.public', table_name 'users-meta');

Following statement is giving the above stated error:

INSERT INTO "public"."users-meta_foreign" ("id", "createdAt", "updatedAt", "deletedAt", "email", "phone", "internationalCode", "region", "userRoles") VALUES (DEFAULT, DEFAULT, DEFAULT, DEFAULT, $1, DEFAULT, DEFAULT, $2, $3) RETURNING "id", "createdAt", "updatedAt", "deletedAt";

Note that the foreign server and the source server, both are hosted on same machine but on different ports.

I have tried to connect to the foreign server using DBeaver client and it's working. But the foreign server connection on query is not working.

Expected: The query is executed on foreign tables.

Answer 1

Please provide more information. Your code itself seems proper so I assume there must be an issue with your system or psql configuration. Here's what I suggest for debugging:

Ensure that the PostgreSQL service is running on port 5404.

Command to check listening ports:

netstat -plunt | grep 5404

Test the direct connection to the PostgreSQL instance on port 5404 using psql:

psql -h localhost -p 5404 -U admin -d platform_test

If you can connect using psql, it confirms that the PostgreSQL instance is accessible and the issue might be related to FDW setup or permissions.

Ensure that there are no firewall rules blocking the connection to port 5404

On Linux you can use

sudo iptables -L