PostgreSQL Foreign Data Wrapper

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 commands\queries  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