Configuring the MongoDB Foreign Data Wrapper v5.2.8
Before using the MongoDB Foreign Data Wrapper, you must:
- Use the CREATE EXTENSION command to create the MongoDB Foreign Data Wrapper extension on the Postgres host.
- Use the CREATE SERVER command to define a connection to the MongoDB server.
- Use the CREATE USER MAPPING command to define a mapping that associates a Postgres role with the server.
- 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 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:
IF NOT EXISTS
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.
Optionally specify the name of the schema in which to install the extension's objects.
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 command to define a connection to a foreign server. The syntax is:
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.
server_name to specify a name for the foreign server. The server name must be unique within the database.
FOREIGN_DATA_WRAPPER clause to specify that the server should use the
mongo_fdw foreign data wrapper when connecting to the cluster.
OPTIONS clause of the
CREATE SERVER command to specify connection information for the foreign server object. You can include:
|address||The address or hostname of the Mongo server. The default value is |
|port||The port number of the Mongo Server. Valid range is 0 to 65535. The default value is |
|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 |
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
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 command to define a mapping that associates a Postgres role with a foreign server:
You must be the owner of the foreign server to create a user mapping for that server.
role_name to specify the role that will be associated with the foreign server.
server_name to specify the name of the server that defines a connection to the MongoDB cluster.
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.
The following command creates a user mapping for a role named
enterprisedb; the mapping is associated with a server named
CREATE USER MAPPING FOR enterprisedb SERVER mongo_server;
If the database host uses secure authentication, provide connection credentials when creating the user mapping:
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
For detailed information about the
CREATE USER MAPPING command, see:
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:
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
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.
Specifies the name of a column in the new table; each column should correspond to a column described on the MongoDB server.
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 clause to assign a collation to the column; if not specified, the column data type's default collation is used.
INHERITS (parent_table [, ... ])
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.
Specify an optional name for a column or table constraint; if not specified, the server will generate a constraint name.
NOT NULL keywords to indicate that the column is not allowed to contain null values.
NULL keywords to indicate that the column is allowed to contain null values. This is the default.
CHECK (expr) [NO INHERIT]
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.
CHECK expression cannot contain subqueries or refer to variables other than columns of the current row.
NO INHERIT keywords to specify that a constraint should not propagate to child tables.
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.
OPTIONS clause to specify the following
options and their corresponding values:
|database||The name of the database to query. The default value is |
|collection||The name of the collection to query. The default value is the foreign table name.|
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:
You should execute a command on the Postgres server that creates a comparable table on the Postgres server:
The first column of the table must be
_id of the type
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:
MongoDB foreign data wrapper supports the write capability feature.
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|