PostgreSQL之扩展包的用法

原创沈陽²⁰²² ᯤ⁶ᴳ All China Database Union 2024-06-25

1159

一．PostgreSql扩展包介绍

1.1、扩展包功能介绍：

PostgreSQL部署完成后，是一个纯净的数据库管理系统，需要其他功能时，需要安装扩展包。扩展包是一种用于扩展数据库功能的机制，它允许用户添加新的数据类型、函数、操作符和索引方法等。

1.2、扩展包功能图解：

说明：
上图的扩展文件目录，是本项目所在目录，实际情况要根据安装目录确定。

二、扩展包安装流程

2.1、确认扩展包

说明：
1、在安装扩展包前，需要确认要安装的扩展包是否存在于PostgreSql安装目录。/postgres/server/share/extension
2、补充：源码包的扩展包所在位置：/xx/postgresql-17beta1/contrib。

代码如下：
1、检查安装目录所在位置：

 
[root@PostgreSQLBeta ~]# ll /postgres/server/share/extension/
总用量 8
-rw-r--r--. 1 postgres postgres 658 6月  19 10:19 plpgsql--1.0.sql
-rw-r--r--. 1 postgres postgres 193 6月  19 10:19 plpgsql.control
[root@PostgreSQLBeta ~]#

2、检查源码的扩展包所在位置：

 
[root@PostgreSQLBeta ~]# ll /install/postgresql-17beta1/contrib/
总用量 88
drwxrwxr-x. 5 postgres postgres 4096 5月  22 00:54 amcheck
drwxrwxr-x. 2 postgres postgres   61 5月  22 00:54 auth_delay
drwxrwxr-x. 3 postgres postgres   90 5月  22 00:54 auto_explain
drwxrwxr-x. 3 postgres postgres   97 5月  22 00:54 basebackup_to_shell
drwxrwxr-x. 4 postgres postgres  135 5月  22 00:54 basic_archive
drwxrwxr-x. 5 postgres postgres  258 5月  22 00:54 bloom
drwxrwxr-x. 4 postgres postgres  219 5月  22 00:54 bool_plperl
drwxrwxr-x. 4 postgres postgres  249 5月  22 00:54 btree_gin
drwxrwxr-x. 5 postgres postgres 4096 5月  22 00:54 btree_gist
drwxrwxr-x. 4 postgres postgres 4096 5月  22 00:54 citext
-rw-rw-r--. 1 postgres postgres   85 5月  22 00:54 contrib-global.mk
drwxrwxr-x. 5 postgres postgres 4096 5月  22 00:54 cube
drwxrwxr-x. 4 postgres postgres  226 5月  22 00:54 dblink
drwxrwxr-x. 4 postgres postgres  153 5月  22 00:54 dict_int
drwxrwxr-x. 4 postgres postgres  181 5月  22 00:54 dict_xsyn
drwxrwxr-x. 4 postgres postgres  203 5月  22 00:54 earthdistance
drwxrwxr-x. 5 postgres postgres  165 5月  22 00:54 file_fdw
drwxrwxr-x. 4 postgres postgres 4096 5月  22 00:54 fuzzystrmatch
drwxrwxr-x. 5 postgres postgres 4096 5月  22 00:54 hstore
drwxrwxr-x. 4 postgres postgres  229 5月  22 00:54 hstore_plperl
drwxrwxr-x. 4 postgres postgres  178 5月  22 00:54 hstore_plpython
drwxrwxr-x. 2 postgres postgres  114 5月  22 00:54 intagg
drwxrwxr-x. 6 postgres postgres 4096 5月  22 00:54 intarray
drwxrwxr-x. 4 postgres postgres  271 5月  22 00:54 isn
drwxrwxr-x. 4 postgres postgres  224 5月  22 00:54 jsonb_plperl
drwxrwxr-x. 4 postgres postgres  175 5月  22 00:54 jsonb_plpython
drwxrwxr-x. 4 postgres postgres  178 5月  22 00:54 lo
drwxrwxr-x. 5 postgres postgres 4096 5月  22 00:54 ltree
drwxrwxr-x. 4 postgres postgres  175 5月  22 00:54 ltree_plpython
-rw-rw-r--. 1 postgres postgres 1512 5月  22 00:54 Makefile
-rw-rw-r--. 1 postgres postgres 1489 5月  22 00:54 meson.build
drwxrwxr-x. 3 postgres postgres   86 5月  22 00:54 oid2name
drwxrwxr-x. 4 postgres postgres 4096 5月  22 00:54 pageinspect
drwxrwxr-x. 4 postgres postgres  109 5月  22 00:54 passwordcheck
drwxrwxr-x. 4 postgres postgres 4096 5月  22 00:54 pg_buffercache
drwxrwxr-x. 4 postgres postgres 4096 5月  22 00:54 pgcrypto
drwxrwxr-x. 4 postgres postgres  276 5月  22 00:54 pg_freespacemap
drwxrwxr-x. 3 postgres postgres  226 5月  22 00:54 pg_prewarm
drwxrwxr-x. 4 postgres postgres  225 5月  22 00:54 pgrowlocks
drwxrwxr-x. 5 postgres postgres 4096 5月  22 00:54 pg_stat_statements
drwxrwxr-x. 4 postgres postgres 4096 5月  22 00:54 pgstattuple
drwxrwxr-x. 4 postgres postgres  161 5月  22 00:54 pg_surgery
drwxrwxr-x. 5 postgres postgres 4096 5月  22 00:54 pg_trgm
drwxrwxr-x. 5 postgres postgres  247 5月  22 00:54 pg_visibility
drwxrwxr-x. 4 postgres postgres  226 5月  22 00:54 pg_walinspect
drwxrwxr-x. 4 postgres postgres 4096 5月  22 00:54 postgres_fdw
-rw-rw-r--. 1 postgres postgres 1131 5月  22 00:54 README
drwxrwxr-x. 5 postgres postgres 4096 5月  22 00:54 seg
drwxrwxr-x. 4 postgres postgres 4096 5月  22 00:54 sepgsql
drwxrwxr-x. 2 postgres postgres 4096 6月  19 10:19 spi
drwxrwxr-x. 2 postgres postgres  163 5月  22 00:54 sslinfo
drwxrwxr-x. 3 postgres postgres   47 5月  22 00:54 start-scripts
drwxrwxr-x. 5 postgres postgres  187 5月  22 00:54 tablefunc
drwxrwxr-x. 4 postgres postgres  140 5月  22 00:54 tcn
drwxrwxr-x. 6 postgres postgres  151 5月  22 00:54 test_decoding
drwxrwxr-x. 4 postgres postgres  174 5月  22 00:54 tsm_system_rows
drwxrwxr-x. 4 postgres postgres  174 5月  22 00:54 tsm_system_time
drwxrwxr-x. 4 postgres postgres  239 5月  22 00:54 unaccent
drwxrwxr-x. 4 postgres postgres  187 5月  22 00:54 uuid-ossp
drwxrwxr-x. 3 postgres postgres   86 5月  22 00:54 vacuumlo
drwxrwxr-x. 4 postgres postgres  187 5月  22 00:54 xml2
[root@PostgreSQLBeta ~]#

2.2、创建扩展包

说明：使用CREATE EXTENSION命令在数据库中创建扩展。

例如：要创建名为pg_stat_statements的扩展：

1、切换到源文件中的扩展目录

代码如下：

 
[postgres@PostgreSQLBeta ~]$ cd /install/postgresql-17beta1/contrib/pg_stat_statements
[postgres@PostgreSQLBeta pg_stat_statements]$ make
make -C ../../src/backend generated-headers
make[1]: 进入目录“/install/postgresql-17beta1/src/backend”
make -C ../include/catalog generated-headers
make[2]: 进入目录“/install/postgresql-17beta1/src/include/catalog”
make[2]: 对“generated-headers”无需做任何事。
make[2]: 离开目录“/install/postgresql-17beta1/src/include/catalog”
make -C nodes generated-header-symlinks
make[2]: 进入目录“/install/postgresql-17beta1/src/backend/nodes”
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-17beta1/src/backend/nodes”
make -C utils generated-header-symlinks
make[2]: 进入目录“/install/postgresql-17beta1/src/backend/utils”
make -C adt jsonpath_gram.h
make[3]: 进入目录“/install/postgresql-17beta1/src/backend/utils/adt”
make[3]: “jsonpath_gram.h”已是最新。
make[3]: 离开目录“/install/postgresql-17beta1/src/backend/utils/adt”
make[2]: 离开目录“/install/postgresql-17beta1/src/backend/utils”
make[1]: 离开目录“/install/postgresql-17beta1/src/backend”
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I. -I. -I../../src/include  -D_GNU_SOURCE   -c -o pg_stat_statements.o pg_stat_statements.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o pg_stat_statements.so  pg_stat_statements.o -L../../src/port -L../../src/common    -Wl,--as-needed -Wl,-rpath,'/postgres/server/lib',--enable-new-dtags -lm  -fvisibility=hidden 
[postgres@PostgreSQLBeta pg_stat_statements]$ make install
make -C ../../src/backend generated-headers
make[1]: 进入目录“/install/postgresql-17beta1/src/backend”
make -C ../include/catalog generated-headers
make[2]: 进入目录“/install/postgresql-17beta1/src/include/catalog”
make[2]: 对“generated-headers”无需做任何事。
make[2]: 离开目录“/install/postgresql-17beta1/src/include/catalog”
make -C nodes generated-header-symlinks
make[2]: 进入目录“/install/postgresql-17beta1/src/backend/nodes”
make[2]: 对“generated-header-symlinks”无需做任何事。
make[2]: 离开目录“/install/postgresql-17beta1/src/backend/nodes”
make -C utils generated-header-symlinks
make[2]: 进入目录“/install/postgresql-17beta1/src/backend/utils”
make -C adt jsonpath_gram.h
make[3]: 进入目录“/install/postgresql-17beta1/src/backend/utils/adt”
make[3]: “jsonpath_gram.h”已是最新。
make[3]: 离开目录“/install/postgresql-17beta1/src/backend/utils/adt”
make[2]: 离开目录“/install/postgresql-17beta1/src/backend/utils”
make[1]: 离开目录“/install/postgresql-17beta1/src/backend”
/usr/bin/mkdir -p '/postgres/server/lib'
/usr/bin/mkdir -p '/postgres/server/share/extension'
/usr/bin/mkdir -p '/postgres/server/share/extension'
/usr/bin/install -c -m 755  pg_stat_statements.so '/postgres/server/lib/pg_stat_statements.so'
/usr/bin/install -c -m 644 ./pg_stat_statements.control '/postgres/server/share/extension/'
/usr/bin/install -c -m 644 ./pg_stat_statements--1.4.sql ./pg_stat_statements--1.10--1.11.sql ./pg_stat_statements--1.9--1.10.sql ./pg_stat_statements--1.8--1.9.sql ./pg_stat_statements--1.7--1.8.sql ./pg_stat_statements--1.6--1.7.sql ./pg_stat_statements--1.5--1.6.sql ./pg_stat_statements--1.4--1.5.sql ./pg_stat_statements--1.3--1.4.sql ./pg_stat_statements--1.2--1.3.sql ./pg_stat_statements--1.1--1.2.sql ./pg_stat_statements--1.0--1.1.sql  '/postgres/server/share/extension/'
[postgres@PostgreSQLBeta pg_stat_statements]$

2、在数据库中执行以下命令

代码如下：

 
[postgres@PostgreSQLBeta pg_stat_statements]$ psql
psql (17beta1)
Type "help" for help.

postgres=# 
postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
postgres=#

2.3、查看已安装的扩展包

说明：在数据库执行pg_extension系统表，查看是否已成功安装pg_stat_statements插件。

方法1：

代码如下：

 
postgres=# 
postgres=# SELECT * FROM pg_extension;
  oid  |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+--------------------+----------+--------------+----------------+------------+-----------+--------------
 13547 | plpgsql            |       10 |           11 | f              | 1.0        |           | 
 17394 | pg_stat_statements |       10 |         2200 | t              | 1.11       |           | 
(2 rows)

postgres=#

方法2：

代码如下：

 
postgres=# 
postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description      
                         
--------------------+---------+------------+-----------------------------------------------
-------------------------
 pg_stat_statements | 1.11    | public     | track planning and execution statistics of all
 SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=#

2.4、更新或删除扩展包

说明：如果您需要更新或删除已安装的扩展包，可以使用ALTER EXTENSION命令或DROP EXTENSION命令。

代码如下：

2.4.1、修改扩展命令：

 
postgres=# alter extension pg_stat_statements add TABLE t4 ;
ALTER EXTENSION
postgres=#

2.4.2、删除扩展命令：

 
postgres=# drop extension pg_stat_statements ;
DROP EXTENSION
postgres=#

三、常见问题

问题1：安装目录没有pg_stat_statements.control

错误信息：

 
postgres=# create extension pg_stat_statements;
2024-06-24 22:44:36.478 CST [121353] ERROR:  extension "pg_stat_statements" is not available
2024-06-24 22:44:36.478 CST [121353] DETAIL:  Could not open extension control file "/postgres/server/share/extension/pg_stat_statements.control": 没有那个文件或目录.
2024-06-24 22:44:36.478 CST [121353] HINT:  The extension must first be installed on the system where PostgreSQL is running.
2024-06-24 22:44:36.478 CST [121353] STATEMENT:  create extension pg_stat_statements;
ERROR:  extension "pg_stat_statements" is not available
DETAIL:  Could not open extension control file "/postgres/server/share/extension/pg_stat_statements.control": 没有那个文件或目录.
HINT:  The extension must first be installed on the system where PostgreSQL is running.
postgres=# 
postgres=# \q
[postgres@PostgreSQLBeta ~]$

解决方案：

编译安装pg_stat_statements，如流程2.2。

问题2：查询扩展pg_stat_statements报错

错误信息：

 
postgres=# select * from pg_stat_statements;
2024-06-24 23:13:45.663 CST [180530] ERROR:  pg_stat_statements must be loaded via "shared_preload_libraries"
2024-06-24 23:13:45.663 CST [180530] STATEMENT:  select * from pg_stat_statements;
ERROR:  pg_stat_statements must be loaded via "shared_preload_libraries"
postgres=#

解决方案：

 
vi $PGDATA/postgresql.conf

#local_preload_libraries = ''
#session_preload_libraries = ''
shared_preload_libraries = 'pg_stat_statements'         # (change requires restart)
#jit_provider = 'llvmjit'               # JIT library to use

测试查询pg_stat_statements：

代码如下：

 
postgres=# 
postgres=# select userid,dbid,toplevel,queryid,query from pg_stat_statements limit  1;
 userid | dbid | toplevel |       queryid        |              query               
--------+------+----------+----------------------+----------------------------------
     10 |    5 | t        | -8705732074374607414 | select * from pg_stat_statements
(1 row)

postgres=#

四、总结

4.1、在PostgreSQL中卸载扩展包时，需要注意以下几点：

检查依赖关系：在卸载扩展包之前，应该检查该扩展包是否有其他对象（如函数、触发器、视图等）依赖于它。如果有依赖关系，直接卸载可能会导致错误。在这种情况下，可以使用DROP EXTENSION name CASCADE;命令来强制删除依赖对象。

备份重要数据：在卸载扩展包之前，应该备份所有重要数据。因为卸载扩展包可能会影响到依赖于该扩展包的数据，一旦发生数据丢失，备份可以作为恢复的依据。

检查插件版本：在卸载扩展包之前，应该检查该扩展包的版本是否与当前PostgreSQL版本兼容。如果版本不兼容，可能会导致PostgreSQL无法正常工作。

重启数据库服务：在某些情况下，卸载扩展包可能需要重启数据库服务才能完全卸载。例如，如果扩展包涉及到共享预加载库，那么卸载后需要重启数据库服务才能使更改生效。

注意安全风险：在卸载扩展包之前，应该确保该扩展包不会引入安全风险。如果扩展包存在安全漏洞，卸载后可能会暴露出安全隐患。

4.2、PostgreSQL扩展包的常见用途

PostgreSQL扩展包是一类可以增强数据库功能的软件组件，它们通常提供了额外的数据类型、索引类型、函数、操作符等。以下是一些常见的PostgreSQL扩展包及其用途：

pg_repack：用于在线重组维护表和索引，以减少碎片并回收空间，而无需锁定读写操作。这对于要求高可用性的生产数据库非常有用。

pg_stat_statements：用于收集和报告执行的SQL语句和它们的性能数据，对于优化查询、分析系统负载及识别性能瓶颈非常有帮助。

auto_explain：可以自动记录执行计划，尤其是对于执行时间超过预定阈值的语句，对于性能调优和监控慢查询非常有用。

pg_stat_kcache：提供了内核级缓存和I/O统计信息，帮助分析SQL查询性能。

pgBackRest：虽然不是PostgreSQL内置扩展，但它是一个备份和恢复工具，支持全备、增量备份和点对点恢复，是PostgreSQL数据库备份和灾难恢复的重要工具。

这些扩展包可以显著提高PostgreSQL数据库的维护效率及性能，并且可以根据具体需求对数据库进行定制化扩展。

最后修改时间：2024-06-25 14:19:18

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

信创数据库学习中心（共80篇）

信创数据库学习中心

PostgreSQL之扩展包的用法

一．PostgreSql扩展包介绍

1.1、扩展包功能介绍：

1.2、扩展包功能图解：

二、扩展包安装流程

2.1、确认扩展包

2.2、创建扩展包

1、切换到源文件中的扩展目录

2、在数据库中执行以下命令

2.3、查看已安装的扩展包

2.4、更新或删除扩展包

三、常见问题

四、总结

4.1、在PostgreSQL中卸载扩展包时，需要注意以下几点：

4.2、PostgreSQL扩展包的常见用途

这些扩展包可以显著提高PostgreSQL数据库的维护效率及性能，并且可以根据具体需求对数据库进行定制化扩展。

文章被以下合辑收录

评论