Configuring access to multiple postgres databases

postgres_fdw_setup.sql

-- For more see: https://www.compose.com/articles/cross-database-querying-in-compose-postgresql/

-- From client database
CREATE EXTENSION postgres_fdw;

CREATE SERVER my_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'other_db_name', port '5432');

CREATE USER MAPPING FOR my_user SERVER my_server OPTIONS (user 'my_user');

CREATE SCHEMA my_foreign_schema;

IMPORT FOREIGN SCHEMA public LIMIT TO (table1, table2, table3) FROM SERVER my_server INTO my_schema;