FDW connect errors

How do I troubleshoot the error 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 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;

4 Replies

deep-jade•5mo ago

Try remove -pooler from the connection string. That is the flag to use connection pooling via pgbouncer. I imagine FDW via pgbouncer isn't supported

continuing-cyan•5mo ago

That did it. Could you guys add this to your docs on postgres_fdw? Thanks!

deep-jade•5mo ago

Cc @Daniel

continuing-cyan•5mo ago

If a branch is created from a db that has CREATE USER MAPPING needed for fdw, is the password stored with that Mapping brought over to the branch? Does this behavior vary depending on if the parent branch is protected (like Role passwords are reset when branched)?

Gaming

Programming

FDW connect errors

Did you find this page helpful?