mongo_fdw
mongo_fdw : foreign data wrapper for MongoDB access
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 8700 | mongo_fdw | mongo_fdw | 5.5.3 | FDW | LGPL-3.0 | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d-- | No | Yes | No | Yes | no | no |
| Relationships | |
|---|---|
| See Also | wrappers redis_fdw kafka_fdw hdfs_fdw documentdb_core documentdb_distributed multicorn jdbc_fdw |
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PGDG | 5.5.3 | 18 17 16 15 14 | mongo_fdw | - |
| RPM | PGDG | 5.5.3 | 18 17 16 15 14 | mongo_fdw_$v | - |
| DEB | PIGSTY | 5.5.3 | 18 17 16 15 14 | postgresql-$v-mongo-fdw | - |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 |
el8.aarch64 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 |
el9.x86_64 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 |
el9.aarch64 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 |
el10.x86_64 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 |
el10.aarch64 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 |
d12.x86_64 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 |
d12.aarch64 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 |
d13.x86_64 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 |
d13.aarch64 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 |
u22.x86_64 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 |
u22.aarch64 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 |
u24.x86_64 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 |
u24.aarch64 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 | PIGSTY 5.5.3 |
u26.x86_64 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 |
u26.aarch64 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 | PGDG 5.5.3 |
Source
Install
Make sure PGDG repo available:
pig repo add pgdg -u # add pgdg repo and update cacheInstall this extension with pig:
pig install mongo_fdw; # install via package name, for the active PG version
pig install mongo_fdw -v 18; # install for PG 18
pig install mongo_fdw -v 17; # install for PG 17
pig install mongo_fdw -v 16; # install for PG 16
pig install mongo_fdw -v 15; # install for PG 15
pig install mongo_fdw -v 14; # install for PG 14Create this extension with:
CREATE EXTENSION mongo_fdw;Usage
Sources: README, REL-5_5_3 release
Create Server
CREATE EXTENSION mongo_fdw;
CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw
OPTIONS (address '127.0.0.1', port '27017');Server Options: address (default 127.0.0.1), port (default 27017), authentication_database, replica_set, read_preference (primary, secondary, primaryPreferred, secondaryPreferred, nearest), ssl (default false), pem_file, pem_pwd, ca_file, ca_dir, crl_file, weak_cert_validation, use_remote_estimate (default false), enable_join_pushdown (default true), enable_aggregate_pushdown (default true), enable_order_by_pushdown (default true).
Create User Mapping
CREATE USER MAPPING FOR pguser SERVER mongo_server
OPTIONS (username 'mongouser', password 'mongopass');Create Foreign Table
CREATE FOREIGN TABLE warehouse (
_id name,
warehouse_id int,
warehouse_name text,
warehouse_created timestamptz
)
SERVER mongo_server
OPTIONS (database 'mydb', collection 'warehouse');Important: The first column must be _id of type name (MongoDB’s object identifier).
Table Options: database (default test), collection (defaults to table name), enable_join_pushdown, enable_aggregate_pushdown, enable_order_by_pushdown.
CRUD Operations
SELECT warehouse_id, warehouse_name FROM warehouse WHERE warehouse_id > 10;
INSERT INTO warehouse VALUES ('new_id', 100, 'New Warehouse', now());
UPDATE warehouse SET warehouse_name = 'Updated' WHERE warehouse_id = 100;
DELETE FROM warehouse WHERE warehouse_id = 100;Pushdown Features
mongo_fdw pushes down WHERE clauses, joins between foreign tables on the same server, aggregate functions, ORDER BY, LIMIT, and OFFSET to MongoDB for efficient query execution. Use the mongo_fdw.enable_join_pushdown, mongo_fdw.enable_aggregate_pushdown, mongo_fdw.enable_order_by_pushdown, and mongo_fdw.log_remote_query GUCs when diagnosing remote plans.
Version Notes
mongo_fdw 5.5.3, released upstream as REL-5_5_3, adds PostgreSQL 18 support, updates bundled mongoc-driver and json-c libraries for MongoDB 8, adds the mongo_fdw.log_remote_query debug GUC, and fixes nested-field, WHERE, ORDER BY, and unsafe join-pushdown cases. Upstream dropped PostgreSQL 12 support in this line.
Notes
- BSON only supports UTF-8; ensure PostgreSQL database uses UTF-8 encoding
- Column names with uppercase letters or dots (for nested documents) require double-quoting
- PostgreSQL limits column names to 63 characters by default