3

EDIT: I've resolved the issue. Spoiler, it has nothing to do with psql or fdw. It was a DNS issue because I am running my local DB in a docker machine that was not configured with our internal DNS server.

I am trying to create a foreign table (from another postgres database) in my database. However, when I run a select statement, the foreign data wrapper says it can't translate the provided hostname:

ERROR:  could not connect to server "pgs3"
DETAIL:  could not translate host name "db7.ap.int.unavco.org" to address: Name or service not known

So what's wrong with my hostname? I can connect to the pgs3 database using psql -U myuser -W -h db7.ap.int.unavco.org -d pgs3. My script for creating the foreign table is really simple and modeled on the documentation here.

-- Drop everything if the fdw exists already
DROP EXTENSION IF EXISTS postgres_fdw CASCADE;

-- Add the postgres_fwd extension
CREATE EXTENSION postgres_fdw WITH SCHEMA public;

-- Create foreign server object
CREATE SERVER pgs3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'db7.ap.int.unavco.org', dbname 'pgs3', port '5432');

-- Create user mapping object to authenticate
CREATE USER MAPPING FOR postgres SERVER pgs3 OPTIONS (user 'afakeuser', password 'afakepassword');

-- Create the foreign table, ensuring the types and NOT NULL rules match the target table
-- The target table only has two columns to keep things simple
CREATE FOREIGN TABLE analysis_type (
    type_id smallint,
    type varchar NOT NULL
)
SERVER pgs3;

-- Try a select
SELECT
*
FROM
analysis_type;

-- Get an error...
medley56
  • 1,181
  • 1
  • 14
  • 29
  • do you run `psql -U myuser -W -h db7.ap.int.unavco.org -d pgs3` on server or client?.. client might just use `/etc/hosts` or so... – Vao Tsun Sep 08 '17 at 08:18
  • I think it's a DNS issue. I can ping my internal servers from my host OS (Ubuntu) but not using `docker exec`. – medley56 Sep 08 '17 at 15:00

1 Answers1

1

As someone who recently ran into this problem, here's what I found. This is a DNS issue, as I was able to put the raw server ip address and the connection worked without fail. I was able to ping the domain from my app server, but creating the connection would still fail.

The reason in my case is because we have separate app and database servers. Our database server needed to also be able to resolve the domain I specified. Once I mapped the domain in /etc/hosts on the database server and restarted the database service, I was able to use the domain as the host and the connection worked.

jdhurricanes
  • 142
  • 9