Configuring the MongoDB Foreign Data Wrapper

Before using the MongoDB Foreign Data Wrapper, you must:

  1. Use the CREATE EXTENSION command to create the MongoDB Foreign Data Wrapper extension on the Postgres host.

  2. Use the CREATE SERVER command to define a connection to the MongoDB server.

  3. Use the CREATE USER MAPPING command to define a mapping that associates a Postgres role with the server.

  4. Use the CREATE FOREIGN TABLE command to define a table in the Postgres database that corresponds to a database that resides on the MongoDB cluster.

CREATE EXTENSION

Use the CREATE EXTENSION command to create the mongo_fdw extension. To invoke the command, use your client of choice (for example, psql) to connect to the Postgres database from which you will be querying the MongoDB server, and invoke the command:

CREATE EXTENSION [IF NOT EXISTS] mongo_fdw [WITH] [SCHEMA schema_name];

Parameters

IF NOT EXISTS

Include the IF NOT EXISTS clause to instruct the server to issue a notice instead of throwing an error if an extension with the same name already exists.

schema_name

Optionally specify the name of the schema in which to install the extension’s objects.

Example

The following command installs the MongoDB foreign data wrapper:

CREATE EXTENSION mongo_fdw;

For more information about using the foreign data wrapper CREATE EXTENSION command, see:

CREATE SERVER

Use the CREATE SERVER command to define a connection to a foreign server. The syntax is:

CREATE SERVER server_name FOREIGN DATA WRAPPER mongo_fdw
    [OPTIONS (option 'value' [, ...])]

The role that defines the server is the owner of the server; use the ALTER SERVER command to reassign ownership of a foreign server. To create a foreign server, you must have USAGE privilege on the foreign-data wrapper specified in the CREATE SERVER command.

Parameters

server_name

Use server_name to specify a name for the foreign server. The server name must be unique within the database.

FOREIGN_DATA_WRAPPER

Include the FOREIGN_DATA_WRAPPER clause to specify that the server should use the mongo_fdw foreign data wrapper when connecting to the cluster.

OPTIONS

Use the OPTIONS clause of the CREATE SERVER command to specify connection information for the foreign server object. You can include:

Option

Description

address

The address or hostname of the Mongo server. The default value is 127.0.0.1.

port

The port number of the Mongo Server. Valid range is 0 to 65535. The default value is 27017.

authentication_database

The database against which user will be authenticated. This option is only valid with password based authentication.

replica_set

The replica set the server is member of. If it is set, the driver will auto-connect to correct primary in the replica set when writing.

read_preference

The order of read preference. Options available are: primary [default], secondary, primaryPreferred, secondaryPreferred, and nearest.

ssl

Requests an authenticated, encrypted SSL connection. By default, the value is set to false. Set the value to true to enable ssl. 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

Example

The following command creates a foreign server named mongo_server that uses the mongo_fdw foreign data wrapper to connect to a host with an IP address of 127.0.0.1:

CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (host '127.0.0.1', port '27017');

The foreign server uses the default port (27017) for the connection to the client on the MongoDB cluster.

For more information about using the CREATE SERVER command, see:

CREATE USER MAPPING

Use the CREATE USER MAPPING command to define a mapping that associates a Postgres role with a foreign server:

CREATE USER MAPPING FOR role_name SERVER server_name
       [OPTIONS (option 'value' [, ...])];

You must be the owner of the foreign server to create a user mapping for that server.

Parameters

role_name

Use role_name to specify the role that will be associated with the foreign server.

server_name

Use server_name to specify the name of the server that defines a connection to the MongoDB cluster.

OPTIONS

Use the OPTIONS clause to specify connection information for the foreign server.

username: the name of the user on the MongoDB server.

password: the password associated with the username.

Example

The following command creates a user mapping for a role named enterprisedb; the mapping is associated with a server named mongo_server:

CREATE USER MAPPING FOR enterprisedb SERVER mongo_server;

If the database host uses secure authentication, provide connection credentials when creating the user mapping:

CREATE USER MAPPING FOR enterprisedb SERVER mongo_server OPTIONS (username 'mongo_user', password 'mongo_pass');

The command creates a user mapping for a role named enterprisedb that is associated with a server named mongo_server. When connecting to the MongoDB server, the server will authenticate as mongo_user, and provide a password of mongo_pass.

For detailed information about the CREATE USER MAPPING command, see:

CREATE FOREIGN TABLE

A foreign table is a pointer to a table that resides on the MongoDB host. Before creating a foreign table definition on the Postgres server, connect to the MongoDB server and create a collection; the columns in the table will map to columns in a table on the Postgres server. Then, use the CREATE FOREIGN TABLE command to define a table on the Postgres server with columns that correspond to the collection that resides on the MongoDB host. The syntax is:

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name [ OPTIONS ( option 'value' [, ... ] ) ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expr) [ NO INHERIT ] | DEFAULT default_expr }

and table_constraint is:

[ CONSTRAINT constraint_name ] CHECK (expr) [ NO INHERIT ]

Parameters

table_name

Specifies the name of the foreign table; include a schema name to specify the schema in which the foreign table should reside.

IF NOT EXISTS

Include the IF NOT EXISTS clause to instruct the server to not throw an error if a table with the same name already exists; if a table with the same name exists, the server will issue a notice.

column_name

Specifies the name of a column in the new table; each column should correspond to a column described on the MongoDB server.

data_type

Specifies the data type of the column; when possible, specify the same data type for each column on the Postgres server and the MongoDB server. If a data type with the same name is not available, the Postgres server will attempt to cast the data type to a type compatible with the MongoDB server. If the server cannot identify a compatible data type, it will return an error.

COLLATE collation

Include the COLLATE clause to assign a collation to the column; if not specified, the column data type’s default collation is used.

INHERITS (parent_table [, ... ])

Include the INHERITS clause to specify a list of tables from which the new foreign table automatically inherits all columns. Parent tables can be plain tables or foreign tables.

CONSTRAINT constraint_name

Specify an optional name for a column or table constraint; if not specified, the server will generate a constraint name.

NOT NULL

Include the NOT NULL keywords to indicate that the column is not allowed to contain null values.

NULL

Include the NULL keywords to indicate that the column is allowed to contain null values. This is the default.

CHECK (expr) [NO INHERIT]

Use the CHECK clause to specify an expression that produces a Boolean result that each row in the table must satisfy. A check constraint specified as a column constraint should reference that column’s value only, while an expression appearing in a table constraint can reference multiple columns.

A CHECK expression cannot contain subqueries or refer to variables other than columns of the current row.

Include the NO INHERIT keywords to specify that a constraint should not propagate to child tables.

DEFAULT default_expr

Include the DEFAULT clause to specify a default data value for the column whose column definition it appears within. The data type of the default expression must match the data type of the column.

SERVER server_name [OPTIONS (option 'value' [, ... ] ) ]

To create a foreign table that will allow you to query a table that resides on a MongoDB file system, include the SERVER clause and specify the server_name of the foreign server that uses the MongoDB data adapter.

Use the OPTIONS clause to specify the following options and their corresponding values:

option

value

database

The name of the database to query. The default value is test.

collection

The name of the collection to query. The default value is the foreign table name.

Example

To use data that is stored on MongoDB server, you must create a table on the Postgres host that maps the columns of a MongoDB collection to the columns of a Postgres table. For example, for a MongoDB collection with the following definition:

db.warehouse.find
(
        {
                "warehouse_id" : 1
        }
).pretty()
{
        "_id" : ObjectId("53720b1904864dc1f5a571a0"),
        "warehouse_id" : 1,
        "warehouse_name" : "UPS",
        "warehouse_created" : ISODate("2014-12-12T07:12:10Z")
}

You should execute a command on the Postgres server that creates a comparable table on the Postgres server:

CREATE FOREIGN TABLE warehouse
(
 _id               NAME,
 warehouse_id      INT,
 warehouse_name    TEXT,
 warehouse_created TIMESTAMPZ
)
SERVER mongo_server
OPTIONS (database 'db', collection 'warehouse');

The first column of the table must be _id of the type name.

Include the SERVER clause to specify the name of the database stored on the MongoDB server and the name of the table (warehouse) that corresponds to the table on the Postgres server.

For more information about using the CREATE FOREIGN TABLE command, see:

Note

MongoDB foreign data wrapper supports the write capability feature.

Data Type Mappings

When using the foreign data wrapper, you must create a table on the Postgres server that mirrors the table that resides on the MongoDB server. The MongoDB data wrapper will automatically convert the following MongoDB data types to the target Postgres type:

MongoDB (BSON Type)

Postgres

ARRAY

JSON

BOOL

BOOL

BINARY

BYTEA

DATE_TIME

DATE/TIMESTAMP/TIMESTAMPTZ

DOCUMENT

JSON

DOUBLE

FLOAT/FLOAT4/FLOAT8/DOUBLE PRECISION/NUMERIC

INT32

SMALLINT/INT2/INT/INTEGER/INT4

INT64

BIGINT/INT8

OID

NAME

UTF8

BPCHAR/VARCHAR/CHARCTER VARYING/TEXT