PostgreSQL implements a feature called Foreign Data Wrapper. The feature supports creating a PostgreSQL foreign table . The foreign table acts as a proxy to a remote data source. It’s a similar concept – to SQL Server Linked Server

Here is an example of the basic steps to set up up a foreign data wrapper to a MongoDB. The example is for illustrative purposes.

-- load extension first time after install
CREATE EXTENSION mongo_fdw;
-- create server object
CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address 'mymongoserver.net', port '16013');
-- create user mapping
CREATE USER MAPPING FOR Postgres
SERVER mongo_server
OPTIONS (username 'user_read_only', password 'hUvxYY2_!');
-- create foreign table (Note: first column of the table must be "_id" of type "NAME".)
CREATE FOREIGN TABLE mytable(
_id NAME,
status text)
SERVER mongo_server
OPTIONS (database 'poc', collection 'collection1');

Some useful commandsqueries  when setting up PostgreSQL foreign data wrappers.

List all extensions

dx 

List servers

des+

Check user mapping

select um.*,rolname
from pg_user_mapping um
join pg_roles r on r.oid = umuser

Author: Jack Vamvas