环境说明
本文基于 CentOS Linux release 7.5.1804 (Core) 虚拟机，使用 PostgreSQL 10.5 、MongoDB 4.0.10 和 mongo_fdw (Latest commit 5fe371a on 31 Mar) 搭建的测试验证环境，其中 MongoDB 的驱动使用的是 v1.9.5。
为了更好地解释说明问题，约定下文中示范命令前的 “#” 符号代表以 root 用户执行，而 “$” 符号代表以 postgres 用户执行。
若遵循以下安装搭建流程，需要 wget、unzip 工具和 ssl 的包，可使用如下命令获得；其中 openssl、openssl-devel 是必须的。
# yum install wget -y # yum install unzip -y # yum install openssl -y # 必须 # yum install openssl-devel -y # 必须
安装 PostgreSQL 并启动服务
这个比较简单，可以从 rpm 包或者 yum 源安装启动 PostgreSQL，也可以从官网获取源码自行编译安装；下面是自行编译安装启动的示例。
# ./configure && make && make install $ /usr/local/pgsql/bin/initdb -D ~/data $ /usr/local/pgsql/bin/pg_ctl -D /home/postgres/data -l logfile start
安装 mongo_fdw 插件
以下是正确的流程示范。
# wget https://github.com/EnterpriseDB/mongo_fdw/archive/master.zip # unzip master.zip # cd mongo_fdw-master/ # ./autogen.sh --with-master # export PKG_CONFIG_PATH=mongo-c-driver/src/:mongo-c-driver/src/libbson/src # export PATH=$PATH:/usr/local/pgsql/bin/ # make -f Makefile.meta # make -f Makefile.meta install
注意
必须安装 openssl 和 openssl-devel 包，否则报错。
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fPIC --std=c99 -I/usr/local/include/libmongoc-1.0 -I/usr/local/include/libbson-1.0 -Ijson-c -DMETA_DRIVER -I. -I./ -I/usr/local/pgsql/include/server -I/usr/local/pgsql/include/internal -D_GNU_SOURCE -c -o mongo_wrapper_meta.o mongo_wrapper_meta.c
mongo_wrapper_meta.c: In function ‘MongoConnect’:
mongo_wrapper_meta.c:73:3: error: unknown type name ‘mongoc_ssl_opt_t’
mongoc_ssl_opt_t *ssl_opts = (mongoc_ssl_opt_t*) malloc(sizeof(mongoc_ssl_opt_t));
^
mongo_wrapper_meta.c:73:33: error: ‘mongoc_ssl_opt_t’ undeclared (first use in this function)
mongoc_ssl_opt_t *ssl_opts = (mongoc_ssl_opt_t*) malloc(sizeof(mongoc_ssl_opt_t));
^
mongo_wrapper_meta.c:73:33: note: each undeclared identifier is reported only once for each function it appears in
mongo_wrapper_meta.c:73:50: error: expected expression before ‘)’ token
mongoc_ssl_opt_t *ssl_opts = (mongoc_ssl_opt_t*) malloc(sizeof(mongoc_ssl_opt_t));
^
mongo_wrapper_meta.c:74:11: error: request for member ‘pem_file’ in something not a structure or union
ssl_opts->pem_file = pem_file;
^
mongo_wrapper_meta.c:75:11: error: request for member ‘pem_pwd’ in something not a structure or union
ssl_opts->pem_pwd = pem_pwd;
^
mongo_wrapper_meta.c:76:11: error: request for member ‘ca_file’ in something not a structure or union
ssl_opts->ca_file = ca_file;
^
mongo_wrapper_meta.c:77:11: error: request for member ‘ca_dir’ in something not a structure or union
ssl_opts->ca_dir = ca_dir;
^
mongo_wrapper_meta.c:78:11: error: request for member ‘crl_file’ in something not a structure or union
ssl_opts->crl_file = crl_file;
^
mongo_wrapper_meta.c:79:11: error: request for member ‘weak_cert_validation’ in something not a structure or union
ssl_opts->weak_cert_validation = weak_cert_validation;
安装 MongoDB 并启动服务
命令示范如下所示。
# curl -O https://fastdl.mongodb.org/linux/mongodb-linux-x86_64-4.0.10.tgz # tar -zxvf mongodb-linux-x86_64-4.0.10.tgz # mv mongodb-linux-x86_64-4.0.10/ /usr/local/mongodb # mkdir -p /data/db # chown -R postgres:postgres /data/
启动 MongoDB 服务，并为 db 数据库添加管理员 mongo_user。
# su - postgres
$ /usr/local/mongodb/bin/mongod &
$ /usr/local/mongodb/bin/mongo
> use db
switched to db db
> db.createUser({user:'mongo_user', pwd:'mongo_pass', roles: [ { role: "dbOwner", db: "db" } ]})
Successfully added user: {
"user" : "mongo_user",
"roles" : [
{
"role" : "dbOwner",
"db" : "db"
}
]
}
>
> show users
{
"_id" : "db.mongo_user",
"userId" : UUID("7126df06-931d-4e12-9fd4-b3477b241dec"),
"user" : "mongo_user",
"db" : "db",
"roles" : [
{
"role" : "dbOwner",
"db" : "db"
}
],
"mechanisms" : [
"SCRAM-SHA-1",
"SCRAM-SHA-256"
]
}
杀掉 mongod 进程并使用认证模式启动：
$ /usr/local/mongodb/bin/mongod --auth --bind_ip_all --logpath mongo.log &
启用 mongo_fdw 插件
以 postgress 用户启动 psql，安装 mongo_fdw 插件，创建 MongoDB 服务节点，创建用户映射，创建外部表，插入几条数据，最后可以成功查询出来。
$ /usr/local/pgsql/bin/psql
psql (10.5)
Type "help" for help.
postgres=# CREATE EXTENSION mongo_fdw;
CREATE EXTENSION
postgres=# create server mongo_server foreign data wrapper mongo_fdw options(address '127.0.0.1',port '27017');
CREATE SERVER
postgres=# create user mapping for postgres server mongo_server options(username 'mongo_user', password 'mongo_pass');
CREATE USER MAPPING
postgres=# create foreign table warehouse(_id NAME, warehouse_id int, warehouse_name text, warehouse_created timestamptz) server mongo_server options (database 'db', collection 'warehouse');
CREATE FOREIGN TABLE
postgres=# select * from warehouse;
_id | warehouse_id | warehouse_name | warehouse_created
-----+--------------+----------------+-------------------
(0 rows)
postgres=# INSERT INTO warehouse values (0, 1, 'UPS', '2014-12-12T07:12:10Z');
INSERT 0 1
postgres=# select * from warehouse;
_id | warehouse_id | warehouse_name | warehouse_created
--------------------------+--------------+----------------+------------------------
5d3569a778882ae4687acf82 | 1 | UPS | 2014-12-12 02:12:10-05
(1 row)
postgres=# INSERT INTO warehouse values (0, 1, 'UPS', '2014-12-12T07:12:10Z');
INSERT 0 1
postgres=# select * from warehouse;
_id | warehouse_id | warehouse_name | warehouse_created
--------------------------+--------------+----------------+------------------------
5d3569a778882ae4687acf82 | 1 | UPS | 2014-12-12 02:12:10-05
5d3569ab78882ae4687acf83 | 1 | UPS | 2014-12-12 02:12:10-05
(2 rows)
postgres=#
注意
postgres=# CREATE EXTENSION mongo_fdw; ERROR: could not load library "/usr/local/pgsql/lib/mongo_fdw.so": libmongoc-1.0.so.0: cannot open shared object file: No such file or directory
其实 libmongoc-1.0.so.0 在 /usr/local/lib 目录中，只是没找到而已；在 /etc/ld.so.conf.d/ 下面新建一个 .conf 文件，然后 ldonfig -v 即可。
postgresql 编译 oracle_fdw 安装后报找不到oracle链接库的办法 – 悠远的博客 – CSDN博客
手动连接 MongoDB 验证
在上一节中通过 mongo_fdw 创建了 warehouse 表，并插入了几条数据；现在通过 mongo 接入 MongoDB 验证这几条是否存在。
$ /usr/local/mongodb/bin/mongo -u "mongo_user" --authenticationDatabase "db" -p
MongoDB shell version v4.0.10
Enter password:
connecting to: mongodb://127.0.0.1:27017/?authSource=db&gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("e2cfb45d-9fe5-4fd4-a5a9-5df781aa8336") }
MongoDB server version: 4.0.10
> use db
switched to db db
> db.warehouse.find()
{ "_id" : ObjectId("5d3569a778882ae4687acf82"), "warehouse_id" : 1, "warehouse_name" : "UPS", "warehouse_created" : ISODate("2014-12-12T07:12:10Z") }
{ "_id" : ObjectId("5d3569ab78882ae4687acf83"), "warehouse_id" : 1, "warehouse_name" : "UPS", "warehouse_created" : ISODate("2014-12-12T07:12:10Z") }
>
在 db 数据库的 warehouse 集合中成功查到了刚刚通过 mongo_fdw 插入的数据，证明环境搭建成功。
参考链接
GitHub – EnterpriseDB/mongo_fdw: PostgreSQL foreign data wrapper for MongoDB
分类：PostgreSQL
