29 August,2019 by Rambler
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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |