RE: 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
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)