PostgreSQL 全文检索安装 pg_jieba 中文插件

下载 pg_jieba

git clone https://github.com/jaiminpan/pg_jieba

初始化 pg_jieba

cd pg_jieba

初始化子工程

git submodule update --init --recursive

编译

创建 build 文件夹

mkdir build

进入 build 文件夹

cd build

安装 CMake

macOS可以使用brew安装。

brew install cmake

输出

❯ cmake ..
CMake Deprecation Warning at CMakeLists.txt:1 (cmake_minimum_required):
  Compatibility with CMake < 2.8.12 will be removed from a future version of
  CMake.

  Update the VERSION argument <min> value or use a ...<max> suffix to tell
  CMake that the project does not need compatibility with older versions.


-- The C compiler identification is AppleClang 14.0.3.14030022
-- The CXX compiler identification is AppleClang 14.0.3.14030022
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working C compiler: /Library/Developer/CommandLineTools/usr/bin/cc - skipped
-- Detecting C compile features
-- Detecting C compile features - done
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Check for working CXX compiler: /Library/Developer/CommandLineTools/usr/bin/c++ - skipped
-- Detecting CXX compile features
-- Detecting CXX compile features - done
-- Setting pg_jieba build type -
CMake Error at /opt/homebrew/Cellar/cmake/3.24.3/share/cmake/Modules/FindPackageHandleStandardArgs.cmake:230 (message):
  Could NOT find PostgreSQL (missing: PostgreSQL_LIBRARY
  PostgreSQL_INCLUDE_DIR)
Call Stack (most recent call first):
  /opt/homebrew/Cellar/cmake/3.24.3/share/cmake/Modules/FindPackageHandleStandardArgs.cmake:594 (_FPHSA_FAILURE_MESSAGE)
  /opt/homebrew/Cellar/cmake/3.24.3/share/cmake/Modules/FindPostgreSQL.cmake:272 (find_package_handle_standard_args)
  CMakeLists.txt:11 (find_package)

配置 CMake 参数

查看pg配置

pg_config --bindir --includedir --includedir-server

输出

❯ pg_config --bindir --includedir --includedir-server
/Applications/Postgres.app/Contents/Versions/15/bin
/Applications/Postgres.app/Contents/Versions/15/include
/Applications/Postgres.app/Contents/Versions/15/include/postgresql/server

运行 CMake

-DPostgreSQL_LIBRARY：指定为--bindir路径。

-DPostgreSQL_INCLUDE_DIR：指定为--includedir路径。

-DPostgreSQL_TYPE_INCLUDE_DIR：指定为--includedir-server路径。

cmake -DPostgreSQL_LIBRARY=/Applications/Postgres.app/Contents/Versions/15/bin -DPostgreSQL_INCLUDE_DIR=/Applications/Postgres.app/Contents/Versions/15/include -DPostgreSQL_TYPE_INCLUDE_DIR=/Applications/Postgres.app/Contents/Versions/15/include/postgresql/server ..

输出

❯ cmake -DPostgreSQL_LIBRARY=/Applications/Postgres.app/Contents/Versions/15/bin ..
CMake Deprecation Warning at CMakeLists.txt:1 (cmake_minimum_required):
  Compatibility with CMake < 2.8.12 will be removed from a future version of
  CMake.

  Update the VERSION argument <min> value or use a ...<max> suffix to tell
  CMake that the project does not need compatibility with older versions.


-- Setting pg_jieba build type -
-- Found PostgreSQL: /Applications/Postgres.app/Contents/Versions/15/bin (found version "15.2")
-- POSTGRESQL_EXECUTABLE is /Applications/postgres.app/Contents/MacOS/Postgres
-- Configuring done
-- Generating done
-- Build files have been written to: /Users/zhangb76/github/pg/pg_jieba/build

运行 make

❯ make
[ 33%] Building C object CMakeFiles/pg_jieba.dir/pg_jieba.c.o
[ 66%] Building CXX object CMakeFiles/pg_jieba.dir/jieba.cpp.o
[100%] Linking CXX shared module pg_jieba.so
[100%] Built target pg_jieba

运行 make install

可能出现的错误：权限问题。

❯ make install
Consolidate compiler generated dependencies of target pg_jieba
[100%] Built target pg_jieba
Install the project...
-- Install configuration: ""
-- Installing: /Applications/Postgres.app/Contents/Versions/15/lib/postgresql/pg_jieba.so
CMake Error at cmake_install.cmake:49 (file):
  file INSTALL cannot copy file
  "/Users/fendoudebb/github/pg/pg_jieba/build/pg_jieba.so" to
  "/Applications/Postgres.app/Contents/Versions/15/lib/postgresql/pg_jieba.so":
  Operation not permitted.


make: *** [install] Error 1

对于macOS用户使用iTerm终端可以在设置中开启完全的磁盘访问权限。

路径：设置-隐私与安全性-完全磁盘访问权限-iTerm。

❯ make install
[100%] Built target pg_jieba
Install the project...
-- Install configuration: ""
-- Installing: /Applications/Postgres.app/Contents/Versions/15/lib/postgresql/pg_jieba.so
-- Installing: /Applications/Postgres.app/Contents/Versions/15/share/postgresql/extension/pg_jieba.control
-- Installing: /Applications/Postgres.app/Contents/Versions/15/share/postgresql/extension/pg_jieba--1.1.1.sql
-- Installing: /Applications/Postgres.app/Contents/Versions/15/share/postgresql/tsearch_data/jieba_base.dict
-- Installing: /Applications/Postgres.app/Contents/Versions/15/share/postgresql/tsearch_data/jieba_hmm.model
-- Installing: /Applications/Postgres.app/Contents/Versions/15/share/postgresql/tsearch_data/jieba_user.dict
-- Installing: /Applications/Postgres.app/Contents/Versions/15/share/postgresql/tsearch_data/jieba.stop
-- Installing: /Applications/Postgres.app/Contents/Versions/15/share/postgresql/tsearch_data/jieba.idf

修改配置

不修改配置每个Session首次查询时比较耗时，推荐修改。

启动PostgreSQL后，进入psql，使用alter命令，修改配置。

alter命令修改的配置，会出现在postgresql.auto.conf文件中。（不用污染原始配置文件）

alter system set shared_preload_libraries = pg_stat_statements, "pg_jieba.so";

重启 PostgreSQL

shared_preload_libraries参数，必须重启后生效。

创建插件

create extension if not exists pg_jieba;

查看开启的插件

select * from pg_extension;

输出

z-blog=# select * from pg_extension;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 13823 | plpgsql            |       10 |           11 | f              | 1.0        |           |
 27054 | pg_jieba           |       10 |         2200 | t              | 1.1.1      |           |
 27087 | pg_stat_statements |       10 |         2200 | t              | 1.10       |           |
(3 rows)

官方文档

https://github.com/jaiminpan/pg_jieba