pg_dump备份数据备份不了pg_jieba

efynn commented 5 years ago

创建数据库，安装pg_jieba扩展后，执行：

pg_dump -U postgres -h 127.0.0.1 -d postgres > postgres.bak

执行失败：

pg_dump: [archiver (db)] query failed: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
pg_dump: [archiver (db)] query was: SELECT
  ( SELECT alias FROM pg_catalog.ts_token_type('32776'::pg_catalog.oid) AS t
    WHERE t.tokid = m.maptokentype ) AS tokenname,
  m.mapdict::pg_catalog.regdictionary AS dictname
FROM pg_catalog.pg_ts_config_map AS m
WHERE m.mapcfg = '32780'
ORDER BY m.mapcfg, m.maptokentype, m.mapseqno

把这句sql放在数据库中执行，有时会退出进程

postgres=#  SELECT
  ( SELECT alias FROM pg_catalog.ts_token_type('32776'::pg_catalog.oid) AS t
    WHERE t.tokid = m.maptokentype ) AS tokenname,
  m.mapdict::pg_catalog.regdictionary AS dictname
FROM pg_catalog.pg_ts_config_map AS m
WHERE m.mapcfg = '32780'
ORDER BY m.mapcfg, m.maptokentype, m.mapseqno;
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> 
!> 
!>

有时会得到如下结果：

postgres=#  SELECT
  ( SELECT alias FROM pg_catalog.ts_token_type('32776'::pg_catalog.oid) AS t
    WHERE t.tokid = m.maptokentype ) AS tokenname,
  m.mapdict::pg_catalog.regdictionary AS dictname
FROM pg_catalog.pg_ts_config_map AS m
WHERE m.mapcfg = '32780'
ORDER BY m.mapcfg, m.maptokentype, m.mapseqno;

 tokenname |  dictname  
-----------+------------
 nz        | jieba_syn
 nz        | jieba_stem
 n         | jieba_syn
 n         | jieba_stem
 m         | jieba_syn
 m         | jieba_stem
 i         | jieba_syn
 i         | jieba_stem
 l         | jieba_syn
 l         | jieba_stem
 d         | jieba_syn
 d         | jieba_stem
 s         | jieba_syn
 s         | jieba_stem
 t         | jieba_syn
 t         | jieba_stem
 mq        | jieba_syn
 mq        | jieba_stem
 nr        | jieba_syn
 nr        | jieba_stem
 j         | jieba_syn
 j         | jieba_stem
 a         | jieba_syn
 a         | jieba_stem
 r         | jieba_syn
 r         | jieba_stem
 b         | jieba_syn
 b         | jieba_stem
 f         | jieba_syn
 f         | jieba_stem
 nrt       | jieba_syn
 nrt       | jieba_stem
 v         | jieba_syn
 v         | jieba_stem
 z         | jieba_syn
 z         | jieba_stem
 ns        | jieba_syn
 ns        | jieba_stem
 q         | jieba_syn
 q         | jieba_stem
 vn        | jieba_syn
 vn        | jieba_stem
...

请问您知道这是什么原因吗？

efynn commented 5 years ago

下面是测试环境：

[root@192-168-1-226 opt]# psql -V
psql (PostgreSQL) 11.4
[root@192-168-1-226 opt]# 
[root@192-168-1-226 opt]# uname -a
Linux 192-168-1-226 3.10.0-957.el7.x86_64 #1 SMP Thu Nov 8 23:39:32 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
[root@192-168-1-226 opt]# 
[root@192-168-1-226 opt]# psql postgres postgres
psql (11.4)
Type "help" for help.

postgres=# \dx
                        List of installed extensions
   Name   | Version |   Schema   |               Description                
----------+---------+------------+------------------------------------------
 pg_jieba | 1.1.0   | public     | a parser for full-text search of Chinese
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

jaiminpan commented 5 years ago

是以前某个版本的代码，或者做了其他额外配置？我在当前的master(8995a41872415b489969ef9970517a613040ce28)版本。装在pg11上。多次使用pg_dump和你提供的sql，都没有发现错误。不过我执行的sql显示的内容和你的不一样。

tokenname |  dictname
-----------+------------
 eng       | jieba_stem
 nz        | jieba_stem
 n         | jieba_stem
 m         | jieba_stem
 i         | jieba_stem
 l         | jieba_stem
 d         | jieba_stem
 s         | jieba_stem
 t         | jieba_stem
 mq        | jieba_stem
 nr        | jieba_stem
 j         | jieba_stem
 a         | jieba_stem
 r         | jieba_stem
 b         | jieba_stem
 f         | jieba_stem
 nrt       | jieba_stem
 v         | jieba_stem
 z         | jieba_stem
 ns        | jieba_stem
 q         | jieba_stem
 vn        | jieba_stem
 c         | jieba_stem
 nt        | jieba_stem
 u         | jieba_stem
 o         | jieba_stem
 zg        | jieba_stem
 nrfg      | jieba_stem
 df        | jieba_stem
 p         | jieba_stem
 g         | jieba_stem
 y         | jieba_stem
 ad        | jieba_stem
 vg        | jieba_stem
 ng        | jieba_stem
 x         | jieba_stem
 ul        | jieba_stem
 k         | jieba_stem
 ag        | jieba_stem
 dg        | jieba_stem
 rr        | jieba_stem
 rg        | jieba_stem
 an        | jieba_stem
 vq        | jieba_stem
 e         | jieba_stem
 uv        | jieba_stem
 tg        | jieba_stem
 mg        | jieba_stem
 ud        | jieba_stem
 vi        | jieba_stem
 vd        | jieba_stem
 uj        | jieba_stem
 uz        | jieba_stem
 h         | jieba_stem
 ug        | jieba_stem
 rz        | jieba_stem
(56 rows)

jaiminpan / pg_jieba

pg_dump备份数据备份不了pg_jieba #39