-
PostgreSQL插件的安装使用与删除
基础操作之___使用PG插件
PG很多功能是通过插件形式提供的，所谓安装插件就是执行sql脚本
1、查看当前数据库扩展插件
mydb=# \dx List of installed extensions Name | Version | Schema | Description ---------+---------+------------+------------------------------ plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (1 row)- 1
- 2
- 3
- 4
- 5
- 6
2、查看数据库可用扩展插件
mydb=# select * from pg_available_extensions; name | default_version | installed_version | comment --------------------+-----------------+-------------------+---------------------------------------------------------------------- plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language adminpack | 2.0 | | administrative functions for PostgreSQL amcheck | 1.2 | | functions for verifying relation integrity bloom | 1.0 | | bloom access method - signature file based index btree_gin | 1.3 | | support for indexing common datatypes in GIN btree_gist | 1.5 | | support for indexing common datatypes in GiST citext | 1.6 | | data type for case-insensitive character strings cube | 1.4 | | data type for multidimensional cubes dblink | 1.2 | | connect to other PostgreSQL databases from within a database dict_int | 1.0 | | text search dictionary template for integers dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing earthdistance | 1.1 | | calculate great-circle distances on the surface of the Earth file_fdw | 1.0 | | foreign-data wrapper for flat file access fuzzystrmatch | 1.1 | | determine similarities and distance between strings hstore | 1.6 | | data type for storing sets of (key, value) pairs intagg | 1.1 | | integer aggregator and enumerator (obsolete) intarray | 1.2 | | functions, operators, and index support for 1-D arrays of integers isn | 1.2 | | data types for international product numbering standards lo | 1.1 | | Large Object maintenance ltree | 1.1 | | data type for hierarchical tree-like structures pageinspect | 1.7 | | inspect the contents of database pages at a low level pg_buffercache | 1.3 | | examine the shared buffer cache pg_freespacemap | 1.2 | | examine the free space map (FSM) pg_prewarm | 1.2 | | prewarm relation data pg_stat_statements | 1.7 | | track execution statistics of all SQL statements executed pg_trgm | 1.4 | | text similarity measurement and index searching based on trigrams pgcrypto | 1.3 | | cryptographic functions pgrowlocks | 1.2 | | show row-level locking information pgstattuple | 1.5 | | show tuple-level statistics pg_visibility | 1.2 | | examine the visibility map (VM) and page-level visibility info postgres_fdw | 1.0 | | foreign-data wrapper for remote PostgreSQL servers seg | 1.3 | | data type for representing line segments or floating-point intervals autoinc | 1.0 | | functions for autoincrementing fields insert_username | 1.0 | | functions for tracking who changed a table moddatetime | 1.0 | | functions for tracking last modification time refint | 1.0 | | functions for implementing referential integrity (obsolete) tablefunc | 1.0 | | functions that manipulate whole tables, including crosstab tcn | 1.0 | | Triggered change notifications tsm_system_rows | 1.0 | | TABLESAMPLE method which accepts number of rows as a limit tsm_system_time | 1.0 | | TABLESAMPLE method which accepts time in milliseconds as a limit unaccent | 1.1 | | text search dictionary that removes accents (41 rows)- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
3、安装扩展插件
（1）数据库中安装插件
mydb=# create extension pg_stat_statements; CREATE EXTENSION- 1
- 2
查验当前数据库扩展插件
mydb=# \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+----------------------------------------------------------- pg_stat_statements | 1.7 | public | track execution statistics of all SQL statements executed plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows)- 1
- 2
- 3
- 4
- 5
- 6
- 7
（2）更改参数文件
vi $PGDATA/postgresql.conf- 1
将新安装的插件名添加到
shared_preload_libraries参数中shared_preload_libraries = 'pg_stat_statements' # (change requires restart)- 1
说明：
源码安装PG时，插件所需文件需要手工编译，并将相关文件拷贝到指定位置，才能安装成功
相关提示及操作如下：
- 编译:
cd <源码包解压目录>/postgresql-12.3/contrib/pgstattuple make- 1
- 2
- 3
-
将编译后目录下所有内容拷贝到
/usr/pgsql12/share/extension/ -
将pgstattuple.so 拷贝到
/usr/pgsql12/lib/ -
create extension- 1
- 2
注： 其中
/usr/pgsql12是PG安装目录（3）重启PG使参数生效
$ pg_ctl restart -D /pgdata12 waiting for server to shut down.... done server stopped waiting for server to start....2022-08-24 17:29:32.887 CST [20011] LOG: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit 2022-08-24 17:29:32.891 CST [20011] LOG: listening on IPv4 address "0.0.0.0", port 5432 2022-08-24 17:29:32.891 CST [20011] LOG: listening on IPv6 address "::", port 5432 2022-08-24 17:29:32.894 CST [20011] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2022-08-24 17:29:32.945 CST [20011] LOG: redirecting log output to logging collector process 2022-08-24 17:29:32.945 CST [20011] HINT: Future log output will appear in directory "log". done server started- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
注意： 并非所有插件安装后都需要重启PG，插件是与库相联系的，未安装的库是看不到的
4、查看扩展插件功能及作用
两种方式：
（1）使用\dx + <插件名>
mydb=# \x Expanded display is on. mydb=# \dx pg_stat_statements List of installed extensions -[ RECORD 1 ]---------------------------------------------------------- Name | pg_stat_statements Version | 1.7 Schema | public Description | track execution statistics of all SQL statements executed mydb=#- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
（2）直接查看sql脚本
$ pwd /usr/pgsql12/share/extension $ cat pg_stat_statements--1.4.sql /* contrib/pg_stat_statements/pg_stat_statements--1.4.sql */ -- complain if script is sourced in psql, rather than via CREATE EXTENSION \echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit -- Register functions. CREATE FUNCTION pg_stat_statements_reset() RETURNS void AS 'MODULE_PATHNAME' LANGUAGE C PARALLEL SAFE; CREATE FUNCTION pg_stat_statements(IN showtext boolean, OUT userid oid, OUT dbid oid, OUT queryid bigint, OUT query text, OUT calls int8, OUT total_time float8, OUT min_time float8, OUT max_time float8, OUT mean_time float8, OUT stddev_time float8, OUT rows int8, OUT shared_blks_hit int8, OUT shared_blks_read int8, OUT shared_blks_dirtied int8, OUT shared_blks_written int8, OUT local_blks_hit int8, OUT local_blks_read int8, OUT local_blks_dirtied int8, OUT local_blks_written int8, OUT temp_blks_read int8, OUT temp_blks_written int8, OUT blk_read_time float8, OUT blk_write_time float8 ) RETURNS SETOF record AS 'MODULE_PATHNAME', 'pg_stat_statements_1_3' LANGUAGE C STRICT VOLATILE PARALLEL SAFE; -- Register a view on the function for ease of use. CREATE VIEW pg_stat_statements AS SELECT * FROM pg_stat_statements(true); GRANT SELECT ON pg_stat_statements TO PUBLIC; -- Don't want this to be available to non-superusers. REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
5、使用扩展插件
mydb=# \c You are now connected to database "mydb" as user "postgres". mydb=# \x Expanded display is on. mydb=# \dx pg_stat_statements List of installed extensions -[ RECORD 1 ]---------------------------------------------------------- Name | pg_stat_statements Version | 1.7 Schema | public Description | track execution statistics of all SQL statements executed mydb=# select * from pg_stat_statements; -[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ userid | 10 dbid | 16428 queryid | -7980196795949952804 query | SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description" + | FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON c.objoid = e.oid AND c.classoid = $1::pg_catalog.regclass+ | WHERE e.extname OPERATOR(pg_catalog.~) $2 COLLATE pg_catalog.default + | ORDER BY 1 calls | 1 total_time | 0.44118 min_time | 0.44118 max_time | 0.44118 mean_time | 0.44118 stddev_time | 0 rows | 1 shared_blks_hit | 4 shared_blks_read | 4 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 mydb=# select now(); -[ RECORD 1 ]---------------------- now | 2022-08-25 15:09:30.241603+08 mydb=# select * from pg_stat_statements; -[ RECORD 1 ]-------+---------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 16428 queryid | -4121204046855525566 query | select * from pg_stat_statements calls | 1 total_time | 0.05042 min_time | 0.05042 max_time | 0.05042 mean_time | 0.05042 stddev_time | 0 rows | 1 shared_blks_hit | 0 shared_blks_read | 0 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 2 ]-------+---------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 16428 queryid | -1144474632245246520 query | select now() calls | 1 total_time | 0.011551 min_time | 0.011551 max_time | 0.011551 mean_time | 0.011551 stddev_time | 0 rows | 1 shared_blks_hit | 0 shared_blks_read | 0 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 -[ RECORD 3 ]-------+---------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- userid | 10 dbid | 16428 queryid | -7980196795949952804 query | SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description" + | FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catal og.pg_description c ON c.objoid = e.oid AND c.classoid = $1::pg_catalog.regclass+ | WHERE e.extname OPERATOR(pg_catalog.~) $2 COLLATE pg_catalog.default + | ORDER BY 1 calls | 1 total_time | 0.44118 min_time | 0.44118 max_time | 0.44118 mean_time | 0.44118 stddev_time | 0 rows | 1 shared_blks_hit | 4 shared_blks_read | 4 shared_blks_dirtied | 0 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0 mydb=#- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
6、删除扩展插件
（1）删除扩展插件
mydb=# \x Expanded display is on. mydb=# \dx List of installed extensions -[ RECORD 1 ]---------------------------------------------------------- Name | pg_stat_statements Version | 1.7 Schema | public Description | track execution statistics of all SQL statements executed -[ RECORD 2 ]---------------------------------------------------------- Name | plpgsql Version | 1.0 Schema | pg_catalog Description | PL/pgSQL procedural language mydb=# drop extension pg_stat_statements; DROP EXTENSION mydb=# \dx List of installed extensions -[ RECORD 1 ]----------------------------- Name | plpgsql Version | 1.0 Schema | pg_catalog Description | PL/pgSQL procedural language mydb=#- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
（2）更改参数文件
将要删除插件名从
shared_preload_libraries参数中移除，否则PG重启在做预加载时发现找不到对应插件就会报错vi $PGDATA/postgresql.conf- 1
shared_preload_libraries = '' # (change requires restart)- 1
（3）重启PG使参数生效
$ pg_ctl restart -D /pgdata12 waiting for server to shut down.... done server stopped waiting for server to start....2022-08-25 15:23:47.701 CST [10053] LOG: starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit 2022-08-25 15:23:47.702 CST [10053] LOG: listening on IPv4 address "0.0.0.0", port 5432 2022-08-25 15:23:47.702 CST [10053] LOG: listening on IPv6 address "::", port 5432 2022-08-25 15:23:47.703 CST [10053] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2022-08-25 15:23:47.717 CST [10053] LOG: redirecting log output to logging collector process 2022-08-25 15:23:47.717 CST [10053] HINT: Future log output will appear in directory "log". done server started- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
-
相关阅读:
100个Java工具类之61：队列类Queue
C＃中从一个路径复制SQLite数据库并将其粘贴到另一路径
自学黑客/网络安全工具软件大全100套
英语教师杂志英语教师杂志社英语教师编辑部2022年第16期目录
Vue——动态组件、缓存组件keep-alive、异步组件
求直角三角形第三点的坐标
深度学习_1_基本语法
【SLAM】5相机&图像
LeetCode第20题——有效的括号
公司项目JAVA开发规范总结（四）——knife4j使用规范
- 原文地址：https://blog.csdn.net/ly7472712/article/details/126826956
