This MongoDB extension implements the PostgreSQL's Foreign Data Wrapper.
Please note that this version of mongo_fdw works with PostgreSQL and EDB Postgres Advanced Server 9.3, 9.4, 9.5, 9.6 & 10.
The MongoDB FDW depends on the official MongoDB C Driver version 0.8 and includes it as a git submodule. If you are cloning this repository for the first time, be sure to pass the --recursive option to git clone in order to initialize the driver submodule to a useable state.
If checked out this project before and for some reason your submodule is not up-to-date, run git submodule update --init.
When you type make
, the C driver's source code also gets automaticallycompiled and linked.
Note: Make sure you have permission to "/usr/local" (default installation location) folder.
Note that we have verified the mongo_fdw
extension only on MacOS X,
Fedora and Ubuntu systems. If you run into issues on other systems, please let us know
The following enhancements are added to the latest version of mongo_fdw
The previous version was only read-only, the latest version provides the write capability. The user can now issue an insert / update and delete statements for the foreign tables using the mongo_fdw.
The latest version comes with a connection pooler that utilizes the same mango database connection for all the queries in the same session. The previous version would open a new MongoDB connection for every query. This is a performance enhancement.
The third enhancement is to add a new MongoDB' C driver. The current implementation is based on the legacy driver of MongoDB. But MongoDB is provided completely new library for driver called MongoDB's Meta Driver. So I have added support of that driver. Now compile time option is available to use legacy and Meta driver. I am sure there are many other benefits of the new Mongo-C-driver that we are not leveraging but we will adopt those as we learn more about the new C driver.
In order to use MongoDB driver 1.0.0+, take the following steps:
- clone
libbson
version 1.0.0+ (https://github.com/mongodb/libbson). Follow install directions on that project's README. - clone
libmongoc
version 1.0.0+ (https://github.com/mongodb/mongo-c-driver). Follow the install directions, except make sure to also run./configure --with-libbson=system
after running automake but before running make. This should be the default behavior, but to be certain include this step. - ensure pkg-config / pkgconf is installed on your system.
- run
make -f Makefile.meta && make -f Makefile.meta install
- if you get an error when trying to
CREATE EXTENSION mongo_fdw;
, then try runningldconfig
Number of manual steps needs to be performed to compile and install different type of MongoDB drivers and supported libraries. If you want to avoid the manual steps, there is a shell script available which will download and install the appropriate drivers and libraries for you.
Here is how it works :
Build with MongoDB's legacy branch driver
- autogen.sh --with-legacy
Build MongoDB's master branch driver
- autogen.sh --with-master
The script will do all the necessary steps to build with legacy and metadriver accordingly.
The following parameters can be set on a MongoDB foreign server object:
address
: the address or hostname of the MongoDB server Defaults to127.0.0.1
port
: the port number of the MongoDB server. Defaults to27017
authentication_database
: database against which user will be authenticated against. Only valid with password based authentication. Defaults to per same database as the MongoDB collection database.replica_set
: replica set the server is member of. If set, driver will auto-connect to correct primary in the replica set when writing.read_preference
: primary [default], secondary, primaryPreferred, secondaryPreferred, or nearest (meta driver only). Defaults toprimary
ssl
: false [default], true to enable ssl (meta driver only). See http://mongoc.org/libmongoc/current/mongoc_ssl_opt_t.html to understand the options.pem_file
: SSL option;pem_pwd
: SSL option;ca_file
: SSL option;ca_dir
: SSL option;crl_file
: SSL option;weak_cert_validation
: SSL option;
The following parameters can be set on a MongoDB foreign table object:
database
: the name of the MongoDB database to query. Defaults totest
collection
: the name of the MongoDB collection to query. Defaults to the foreign table name used in the relevantCREATE
command
As an example, the following commands demonstrate loading the mongo_fdw
wrapper, creating a server, and then creating a foreign table associated with
a MongoDB collection. The commands also show specifying option values in the
OPTIONS
clause. If an option value isn't provided, the wrapper uses the
default value mentioned above.
mongo_fdw
can collect data distribution statistics will incorporate them when estimating costs for the query execution plan. To see selected execution plans for a query, just run EXPLAIN
.
Examples with MongoDB's equivalent statments.
-- load extension first time after install
CREATE EXTENSION mongo_fdw;
-- create server object
CREATE SERVER mongo_server
FOREIGN DATA WRAPPER mongo_fdw
OPTIONS (address '127.0.0.1', port '27017');
-- create user mapping
CREATE USER MAPPING FOR postgres
SERVER mongo_server
OPTIONS (username 'mongo_user', password 'mongo_pass');
-- create foreign table
CREATE FOREIGN TABLE warehouse(
_id NAME,
warehouse_id int,
warehouse_name text,
warehouse_created timestamptz)
SERVER mongo_server
OPTIONS (database 'db', collection 'warehouse');
-- Note: first column of the table must be "_id" of type "NAME".
-- select from table
SELECT * FROM warehouse WHERE warehouse_id = 1;
_id | warehouse_id | warehouse_name | warehouse_created
------------------------+----------------+---------------------------
53720b1904864dc1f5a571a0| 1 | UPS | 12-DEC-14 12:12:10 +05:00
db.warehouse.find({"warehouse_id" : 1}).pretty()
{
"_id" : ObjectId("53720b1904864dc1f5a571a0"),
"warehouse_id" : 1,
"warehouse_name" : "UPS",
"warehouse_created" : ISODate("2014-12-12T07:12:10Z")
}
-- insert row in table
INSERT INTO warehouse values (0, 1, 'UPS', '2014-12-12T07:12:10Z');
db.warehouse.insert
(
{
"warehouse_id" : NumberInt(1),
"warehouse_name" : "UPS",
"warehouse_created" : ISODate("2014-12-12T07:12:10Z")
}
);
-- delete row from table
DELETE FROM warehouse where warehouse_id = 3;
> db.warehouse.remove({"warehouse_id" : 2})
-- update a row of table
UPDATE warehouse set warehouse_name = 'UPS_NEW' where warehouse_id = 1;
db.warehouse.update
(
{
"warehouse_id" : 1
},
{
"warehouse_id" : 1,
"warehouse_name" : "UPS_NEW"
}
)
-- explain a table
EXPLAIN SELECT * FROM warehouse WHERE warehouse_id = 1;
QUERY PLAN
-----------------------------------------------------------------
Foreign Scan on warehouse (cost=0.00..0.00 rows=1000 width=44)
Filter: (warehouse_id = 1)
Foreign Namespace: db.warehouse
Planning time: 0.671 ms
(4 rows)
-- collect data distribution statistics`
ANALYZE warehouse;
select mongo_fdw_version();
mongo_fdw_version
-------------------
50100
(1 row)
-
If the BSON document key contains uppercase letters or occurs within a nested document,
mongo_fdw
requires the corresponding column names to be declared in double quotes. -
Note that PostgreSQL limits column names to 63 characters by default. If you need column names that are longer, you can increase the
NAMEDATALEN
constant insrc/include/pg_config_manual.h
, compile, and reinstall.
Have a fix for a bug or an idea for a great new feature? Great! Check out the contribution guidelines here. For all other types of questions or comments about the wrapper please contact us at mongo_fdw
@
enterprisedb.com
.
This project will be modified to maintain compatibility with new PostgreSQL and EDB Postgres Advanced Server releases.
If you need commercial support, please contact the EnterpriseDB sales team, or check whether your existing PostgreSQL support provider can also support mongo_fdw.
Portions Copyright © 2004-2016, EnterpriseDB Corporation.
Portions Copyright © 2012–2014 Citus Data, Inc.
This program is free software: you can redistribute it and/or modify it under the terms of the GNU Lesser General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
See the LICENSE
file for full details.