Retrieving data from other databases in postgresql

CREATE EXTENSION postgres_fdw;
CREATE EXTENSION dblink;
SELECT dblink_connect(‘host=localhost user=USER password=PW dbname=DB’);
CREATE FOREIGN DATA WRAPPER FDW VALIDATOR postgresql_fdw_validator;
CREATE SERVER myServerName FOREIGN DATA WRAPPER FDW OPTIONS (hostaddr ‘127.0.0.1’, dbname ‘DB’);
CREATE USER MAPPING FOR postgres SERVER myServerName OPTIONS (user ‘USER’, password ‘PW’);
SELECT dblink_connect(‘myServerName’);
GRANT USAGE ON FOREIGN SERVER myServerName TO postgres;
SELECT * FROM dblink(‘myServerName’,’select id from DB.public.TABLE’) AS DATA(id INTEGER);


Reference Url: http://www.leeladharan.com/postgresql-cross-database-queries-using-dblink

https://www.dbrnd.com/2015/05/postgresql-cross-database-queries-using/



This will work, i have tested on 20191024. It worked brought the data. 



CREATE EXTENSION postgres_fdw;
CREATE EXTENSION dblink;
SELECT dblink_connect('host=localhost user=postgres password=N@m0venkatesa dbname=UNLSH_WB_COLLECTIONS');
CREATE FOREIGN DATA WRAPPER FDW5 VALIDATOR postgresql_fdw_validator;
CREATE SERVER loc_UNLSH_WB_CLS4 FOREIGN DATA WRAPPER FDW5 OPTIONS (hostaddr '127.0.0.1', dbname 'UNLSH_WB_COLLECTIONS');
CREATE USER MAPPING FOR postgres SERVER loc_UNLSH_WB_CLS4 OPTIONS (user 'postgres', password 'N@m0venkatesa');
SELECT dblink_connect('loc_unlsh_wb_cls4');

SELECT * FROM dblink
('loc_unlsh_wb_cls4','SELECT "iMonth" FROM public."td_00000019B"')
AS DATA(iMonth INTEGER);