环境说明
本文基于 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