PostgreSQL Foreign Data Wrapper

29 August,2019 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.dba-ninja.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on PostgreSQL Foreign Data Wrapper


dba-ninja.com