将PG数据库中的表数据导出到MongoDB

Export data from postgresql to mongodb

将PG数据库中的表数据导出到MongoDB
Page content

1. PG sql

使用row_to_json()函数将数据转为json格式 使用COPY命令将数据写到标准输出 注意:使用COPY命令直接将表数据写到某个文件中需要superuser权限

1 
> select regextappid as appid, 
         bindings->'devid' as devid, 
         bindings->'wechat' as wechat 
  from user_info;

2. 
> SELECT row_to_json(sso_bindings) FROM (
    select regextappid as appid, 
           bindings->'devid' as devid, 
           bindings->'wechat' as wechat 
    from user_info
) sso_bindings;

3.
> COPY ( SELECT row_to_json(sso_bindings) FROM ( 
    select regextappid as appid,
           bindings->'devid' as devid, 
           bindings->'wechat' as wechat 
    from 
          user_info 
    where ctime > '2020-09-07 14:00:09') sso_bindings) 
TO STDOUT; 

2. 将导出的数据存储到文件中

~$ /usr/local/pgxl/bin/psql -h HOST -p 21001 -U USER -d DB -c "COPY ( SELECT row_to_json(sso_bindings) FROM ( select regextappid as _id, regextappid as appid, bindings->'devid' as devid, bindings->'wechat' as wechat from user_info) sso_bindings) TO STDOUT;" > sso_bindings.raw 2>&1

~$ cat sso_bindings.raw
{"appid" : "55555", "devid" : "aString", "wechat" : "aString" }
{"appid" : "666666", "devid" : "aString", "wechat" : null }
......

3. 导入MongoDB

  • 数据放入新集合中:sso_bindings
  • mongo数据库集合的字段:
_id 默认生成
"appid": appid
"devid": devid
"wechat": wechat
  • MongoDB host:
cat sso_bindings.raw | awk '{ print "db.sso_bindings.insert("$0")" }' | mongo --quiet --host 172.29.97.201 --port 20000 icake_android
  • appid 和 devid 建立索引
mongos> db.sso_bindings.createIndex({"appid": 1})
mongos> db.sso_bindings.createIndex({"devid": 1})