目录：
基础操作之___使用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)
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)
3、安装扩展插件
（1）数据库中安装插件
mydb=# create extension pg_stat_statements;
CREATE EXTENSION
查验当前数据库扩展插件
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)
（2）更改参数文件
vi $PGDATA/postgresql.conf
将新安装的插件名添加到shared_preload_libraries参数中
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
说明：
源码安装PG时，插件所需文件需要手工编译，并将相关文件拷贝到指定位置，才能安装成功
相关提示及操作如下：
- 编译:
cd <源码包解压目录>/postgresql-12.3/contrib/pgstattuple make
将编译后目录下所有内容拷贝到
/usr/pgsql12/share/extension/将pgstattuple.so 拷贝到
/usr/pgsql12/lib/create extension注： 其中
/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
注意： 并非所有插件安装后都需要重启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=#
（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;
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=#
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=#
（2）更改参数文件
将要删除插件名从shared_preload_libraries参数中移除，否则PG重启在做预加载时发现找不到对应插件就会报错
vi $PGDATA/postgresql.conf
shared_preload_libraries = '' # (change requires restart)
（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
扫一扫
2679
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
