Cross Database Querying on Postgres #psql #postgres

fdw_setup.sql

--for a cross db query on the same host
--no hostname or password is needed.
--the following script adds the extension, 
--  creates a server, maps the postgres user, 
--  and imports the foreign db into a new schema

BEGIN;
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
DROP SERVER IF EXISTS fdw_server CASCADE;

CREATE SERVER fdw_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'mydatabasename');


CREATE USER MAPPING FOR postgres
  SERVER fdw_server
  OPTIONS (user 'postgres');


DROP SCHEMA IF EXISTS fdw;
CREATE SCHEMA fdw;

IMPORT FOREIGN SCHEMA public
  FROM SERVER fdw_server
  INTO fdw;
  
COMMIT;