I’m trying to separate control DB and Metadata DB which are on PostgeSQL but facing issue while using the external dblink option

I ran following in the Postgres DB running on the agent server.  Everything works until the last step to created a connection to the foreign server running on the controller server.

 

CREATE EXTENSION dblink;

 

SELECT pg_namespace.nspname, pg_proc.proname

FROM pg_proc, pg_namespace

WHERE pg_proc.pronamespace=pg_namespace.oid

AND pg_proc.proname LIKE ‘%dblink%’;

 

SELECT dblink_connect(‘host=pin3002.autotrader.com user=direpusr password=password dbname=direpdb’);

 

CREATE FOREIGN DATA WRAPPER fdw_dbdirep VALIDATOR postgresql_fdw_validator;

 

CREATE SERVER svrController FOREIGN DATA WRAPPER fdw_dbdirep OPTIONS (host ‘pin3002.autotrader.com’, dbname ‘direpdb’, port ‘9003’);

 

GRANT USAGE ON FOREIGN SERVER svrController TO direpusr;

 

CREATE USER MAPPING FOR direpusr SERVER svrController OPTIONS (user ‘direpusr’, password ‘password’);

 

SELECT dblink_connect(‘svrController’);

       SQL Error [08001]: ERROR: could not establish connection

       Detail: missing “=” after “svrController” in connection info string

Beginner Asked on February 26, 2020 in Installation.
Add Comment
1 Answer(s)

Can you try to provide svrController in lower case svrcontroller, as in Postgresql everything is lowercase

 

Below are our test results.

 

Failure:

direpdb=# SELECT dblink_connect(‘svrController’);
ERROR:  could not establish connection
DETAIL:  missing “=” after “svrController” in connection info string

 

Success:

direpdb=# SELECT dblink_connect(‘svrcontroller’);
dblink_connect
—————-
OK
(1 row)

Expert Answered on February 28, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.