FDW connect errors

How do I troubleshoot the error

ERROR: could not connect to server "ar_db_server" (SQLSTATE 08001)

ERROR: could not connect to server "ar_db_server" (SQLSTATE 08001)

ERROR: could not connect to server "ar_db_server" (SQLSTATE 08001)

ERROR: could not connect to server "ar_db_server" (SQLSTATE 08001) that I get trying to get fdw working? This error code suggests a network issue, and it occurs immediately (doesn't seem like a timeout). This is being run in the Neon SQL Editor, the foreign db is a separate Neon project that I also own. I have checked the host / username / pw / schema many times and verified the role on the foreign db has permissions to use the schema and SELECT from the table (the same role is in use elsewhere and is successfully reading from these schema tables). Everything thru creating the schema imported_data works fine. Also, if instead of IMPORTing the foreign table I create it explicitly with (removing all enum and primary key references), the table gets created properly but if you try to query it you get the same

could not connect

could not connect

could not connect

could not connect error as above.

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER IF NOT EXISTS ar_db_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'ep-super-fire-a4tmuc9l-pooler.us-east-1.aws.neon.tech', port '5432', dbname 'ar_db', updatable 'false', truncatable 'false');

CREATE USER MAPPING IF NOT EXISTS FOR CURRENT_USER
SERVER ar_db_server
OPTIONS (user 'contract_reader', password <notCopiedHere>);

CREATE SCHEMA IF NOT EXISTS imported_data;

IMPORT FOREIGN SCHEMA contract
-- only 1 table needed ATM:
-- LIMIT TO (us_counties)
FROM SERVER ar_db_server
INTO imported_data;

-- This code successfully creates a foreign table, but trying to query it fails with the same error:
CREATE FOREIGN TABLE "imported_data"."us_counties" ("id" integer NOT NULL, "state_code" text NOT NULL, "name_short" text NOT NULL, 
"name_short_ascii" text NOT NULL, "name_full" text NOT NULL, "county_fips" text NOT NULL, "city_county" boolean NULL, 
"county_tier" text NULL)
SERVER ar_db_server;

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER IF NOT EXISTS ar_db_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'ep-super-fire-a4tmuc9l-pooler.us-east-1.aws.neon.tech', port '5432', dbname 'ar_db', updatable 'false', truncatable 'false');

CREATE USER MAPPING IF NOT EXISTS FOR CURRENT_USER
SERVER ar_db_server
OPTIONS (user 'contract_reader', password <notCopiedHere>);

CREATE SCHEMA IF NOT EXISTS imported_data;

IMPORT FOREIGN SCHEMA contract
-- only 1 table needed ATM:
-- LIMIT TO (us_counties)
FROM SERVER ar_db_server
INTO imported_data;

-- This code successfully creates a foreign table, but trying to query it fails with the same error:
CREATE FOREIGN TABLE "imported_data"."us_counties" ("id" integer NOT NULL, "state_code" text NOT NULL, "name_short" text NOT NULL, 
"name_short_ascii" text NOT NULL, "name_full" text NOT NULL, "county_fips" text NOT NULL, "city_county" boolean NULL, 
"county_tier" text NULL)
SERVER ar_db_server;

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER IF NOT EXISTS ar_db_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'ep-super-fire-a4tmuc9l-pooler.us-east-1.aws.neon.tech', port '5432', dbname 'ar_db', updatable 'false', truncatable 'false');

CREATE USER MAPPING IF NOT EXISTS FOR CURRENT_USER
SERVER ar_db_server
OPTIONS (user 'contract_reader', password <notCopiedHere>);

CREATE SCHEMA IF NOT EXISTS imported_data;

IMPORT FOREIGN SCHEMA contract
-- only 1 table needed ATM:
-- LIMIT TO (us_counties)
FROM SERVER ar_db_server
INTO imported_data;

-- This code successfully creates a foreign table, but trying to query it fails with the same error:
CREATE FOREIGN TABLE "imported_data"."us_counties" ("id" integer NOT NULL, "state_code" text NOT NULL, "name_short" text NOT NULL, 
"name_short_ascii" text NOT NULL, "name_full" text NOT NULL, "county_fips" text NOT NULL, "city_county" boolean NULL, 
"county_tier" text NULL)
SERVER ar_db_server;

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER IF NOT EXISTS ar_db_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'ep-super-fire-a4tmuc9l-pooler.us-east-1.aws.neon.tech', port '5432', dbname 'ar_db', updatable 'false', truncatable 'false');

CREATE USER MAPPING IF NOT EXISTS FOR CURRENT_USER
SERVER ar_db_server
OPTIONS (user 'contract_reader', password <notCopiedHere>);

CREATE SCHEMA IF NOT EXISTS imported_data;

IMPORT FOREIGN SCHEMA contract
-- only 1 table needed ATM:
-- LIMIT TO (us_counties)
FROM SERVER ar_db_server
INTO imported_data;

-- This code successfully creates a foreign table, but trying to query it fails with the same error:
CREATE FOREIGN TABLE "imported_data"."us_counties" ("id" integer NOT NULL, "state_code" text NOT NULL, "name_short" text NOT NULL, 
"name_short_ascii" text NOT NULL, "name_full" text NOT NULL, "county_fips" text NOT NULL, "city_county" boolean NULL, 
"county_tier" text NULL)
SERVER ar_db_server;

FDW connect errors

Similar Threads

Similar Threads

Similar Threads