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 FOREI...