开发者社区> 问答> 正文

pg使用mongo_fdw外部表时,如果外部mongo重启,则pg无法自动重连mongo,pg也必须重启。

pg使用mongo_fdw外部表时,如果外部mongo重启,则pg无法自动重连mongo,pg也必须重启。

mongo重启前,各种外部表查询都正常。

root@937118f50889:~# psql -U postgres -d acmcoder -h 127.0.0.1 -p 5432
Password for user postgres: 
psql (9.3.4)
SSL connection (cipher: DHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

acmcoder=# SELECT * FROM testmongo;
           _id            | id | v 
--------------------------+----+---
 5770ed26a65e4cd5c9bf5ffc |  1 | 1
 5770f0c8b6fc52594ac90541 |  2 | 2
 5770f6a690a120b540fb759e |  6 | 3
(3 rows)

kill mongo服务器进程后,则无法查询,这个正常:

acmcoder=# SELECT * FROM testmongo;
 _id | id | v 
-----+----+---
(0 rows)

关键是mongo服务器重新启动后,查询还不正常:

acmcoder=# SELECT * FROM testmongo;
 _id | id | v 
-----+----+---
(0 rows)

pg的日志如下:

2016-06-28 01:18:33 UTC LOG: database system was interrupted; last known up at 2016-06-27 12:53:32 UTC
2016-06-28 01:18:33 UTC LOG: database system was not properly shut down; automatic recovery in progress
2016-06-28 01:18:33 UTC LOG: record with zero length at 0/3514218
2016-06-28 01:18:33 UTC LOG: redo is not required
2016-06-28 01:18:33 UTC LOG: database system is ready to accept connections
2016-06-28 01:18:33 UTC LOG: autovacuum launcher started
2016-06-28 01:19:23 UTC LOG: could not receive data from client: Connection reset by peer
2016-06-28 01:19:23 UTC LOG: could not receive data from client: Connection reset by peer
2016-06-28 01:19:23 UTC LOG: could not receive data from client: Connection reset by peer
2016-06-28 01:19:29 UTC LOG: could not receive data from client: Connection reset by peer
WARNING: mongo_connect() is deprecated, please use mongo_client()
2016-06-28 01:20:21 UTC LOG: could not receive data from client: Connection reset by peer
2016-06-28 01:20:21 UTC LOG: could not receive data from client: Connection reset by peer

如果此时pg服务器也重启,则查询又正常了:

acmcoder=# SELECT * FROM testmongo;
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset: Succeeded.
acmcoder=# SELECT * FROM testmongo;
           _id            | id | v 
--------------------------+----+---
 5770ed26a65e4cd5c9bf5ffc |  1 | 1
 5770f0c8b6fc52594ac90541 |  2 | 2
 5770f6a690a120b540fb759e |  6 | 3
(3 rows)

acmcoder=#

展开
收起
lifubang 2016-06-28 09:30:41 3712 0
1 条回答
写回答
取消 提交回答
  • 公益是一辈子的事, I am digoal, just do it. 阿里云数据库团队, 擅长PolarDB, PostgreSQL, DuckDB, ADB等, 长期致力于推动开源数据库技术、生态在中国的发展与开源产业人才培养. 曾荣获阿里巴巴麒麟布道师称号、2018届OSCAR开源尖峰人物.

    应该是缓存了链接导致的,看看mongo_fdw的代码能不能改进一下,提供释放链接的函数。

    2019-07-17 19:47:59
    赞同 1 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
SQL Sever迁移PG经验 立即下载
PostgresChina2018_董红禹_SQL_Server迁移PG经验分享 立即下载
MySQL5.7 让优化更轻松 立即下载