PostgreSQL10基础（4）插件安装

本是古典何须时尚 2023-07-10 12:44 20阅读 0赞

安装环境

数据库：PostgreSQL10.4（已安装）  
操作系统：Centos7  
安装步骤

1.  安装

1.1 有网安装

\#安装源  
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86\_64/pgdg-centos10-10-2.noarch.rpm  
\#安装PG插件  
yum install postgresql10-contrib  
1.2 无网安装

在有网络的服务器下运行

\#安装源  
yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86\_64/pgdg-centos10-10-2.noarch.rpm  
\#下载PG 插件RPM到tmp文件夹下  
yum install --downloadonly --downloaddir=/tmp/ postgresql10-contrib

将tmp下rpm文件的内容拷贝到待安装服务器下并安装

rpm -i \*.rpm  
插件

常用插件列表

插件 用途  
auth\_delay 服务器在身份验证失败之前暂停，以使对数据库密码的暴力攻击更加困难  
auto\_explain 自动记录慢语句执行计划的方法，无需手工运行EXPLAIN  
chkpass 专为存储加密密码而设计的数据类型，每次输入密码都会自动转换为加密形式，并且始终以加密方式存储  
DBLINK 建立和远程数据库的连接  
file\_fdw 可用于访问服务器文件系统中的数据文件，或者在服务器上执行程序并读取其输出。数据文件或程序输出必须采用可读取的格式 COPY FROM  
passwordcheck 检查用户的密码,如果密码被认为太弱，它将被拒绝并且命令将以错误终止  
pgcrypto 提供加密函数  
pgrowlocks 显示指定表的行锁定信息  
pg\_stat\_statements 提供了跟踪服务器执行的所有SQL语句的执行统计信息的方法  
UUID-OSSP 提供使用几种标准算法之一生成通用唯一标识符（UUID）的功能  
UUID-OSSP

安装

\#切换到postgres用户  
su postgres  
\#进入psql  
psql  
\#安装插件  
CREATE EXTENSION “uuid-ossp”;  
使用

https://www.postgresql.org/docs/10/static/uuid-ossp.html

常用函数 说明  
uuid\_generate\_v1() 使用计算机的MAC地址和时间戳生成，不适用于某些对安全敏感的应用程序  
uuid\_generate\_v1mc() 使用随机多播MAC地址而不是计算机的真实MAC地址生成  
uuid\_generate\_v4() UUID完全来自随机数  
pg\_stat\_statements

安装

\#切换到postgres用户  
su postgres  
\#进入psql  
psql  
\#安装插件  
CREATE EXTENSION “pg\_stat\_statements”;  
修改postgresql.conf，添加pg\_stat\_statements

\#如果有其他预加载模块，以逗号间隔  
shared\_preload\_libraries = ‘pg\_stat\_statements’  
重新启动数据库

权限

当pg\_stat\_statements被加载，它跟踪整个服务器的所有数据库的统计数据，为了访问和操作这些统计数据，该模块提供了一个视图 pg\_stat\_statements，以及实用功能pg\_stat\_statements\_reset 和pg\_stat\_statements

视图

pg\_stat\_statements

名称 类型 参考 描述  
userid oid pg\_authid.oid 执行语句的用户的OID  
dbid oid pg\_database.oid 执行语句的数据库的OID  
queryid bigint 内部散列码，根据语句的分析树计算得出  
query text 有代表性的SQL文本  
calls bigint 执行的次数  
total\_time double precision 总时间，以毫秒为单位  
min\_time double precision 最短时间，以毫秒为单位  
max\_time double precision 最长时间，以毫秒为单位  
mean\_time double precision 平均时间，以毫秒为单位  
stddev\_time double precision 花费时间的标准差，以毫秒为单位  
rows bigint 该语句检索或影响的总行数  
shared\_blks\_hit bigint 语句共享块缓存命中总数  
shared\_blks\_read bigint 语句读取的共享块的总数  
shared\_blks\_dirtied bigint 由语句弄脏的共享块的总数  
shared\_blks\_written bigint 语句写入的共享块的总数  
local\_blks\_hit bigint 语句的本地块缓存命中总数  
local\_blks\_read bigint 语句读取的本地块总数  
local\_blks\_dirtied bigint 由语句弄脏的本地块的总数  
local\_blks\_written bigint 语句写入的本地块总数  
temp\_blks\_read bigint 语句读取的临时块总数  
temp\_blks\_written bigint 语句写入的临时块总数  
blk\_read\_time double precision 语句花费读取块的总时间（以毫秒为单位）（如果track\_io\_timing 已启用，否则为零）  
blk\_write\_time double precision 语句写入块的总时间（以毫秒为单位）（如果 已启用track\_io\_timing，否则为零）  
出于安全原因，只有超级用户和pg\_read\_all\_stats角色成员 才能看到SQL文本和queryid其他用户执行的查询。但是，其他用户可以查看统计信息，如果视图已经安装在他们的数据库中。

常用查询

–总耗时Top100(平均时间大于0.5秒)  
select u.rolname,d.datname,s.\* from pg\_stat\_statements s left join pg\_database d on s.dbid=d.oid  
left join pg\_roles u on s.userid = u.oid  
where rolname!=‘postgres’ and mean\_time>500 order by total\_time desc limit 100

–最大Top100(平均时间大于0.5秒)  
select u.rolname,d.datname,s.\* from pg\_stat\_statements s left join pg\_database d on s.dbid=d.oid  
left join pg\_roles u on s.userid = u.oid  
where rolname!=‘postgres’ and mean\_time>500 order by max\_time desc limit 100

–平均耗时Top100(平均时间大于0.5秒)  
select u.rolname,d.datname,s.\* from pg\_stat\_statements s left join pg\_database d on s.dbid=d.oid  
left join pg\_roles u on s.userid = u.oid  
where rolname!=‘postgres’ and mean\_time>500 order by mean\_time desc limit 100

–清除统计记录  
select pg\_stat\_statements\_reset()  
函数

函数 说明  
pg\_stat\_statements\_reset() returns void 丢弃到目前为止收集的所有统计数据pg\_stat\_statements。默认情况下，该功能只能由超级用户执行  
pg\_stat\_statements(showtext boolean) returns setof record 该pg\_stat\_statements视图是根据一个也被命名的函数定义的pg\_stat\_statements。客户端可以pg\_stat\_statements直接调用该函数，并通过指定showtext := false查询文本被忽略（也就是说OUT，与视图query列相对应的参数 将返回空值）。此功能旨在支持可能希望避免重复检索长度不确定的查询文本的开销的外部工具。这些工具可以替代地缓存为每个条目本身观察到的第一个查询文本，因为这 pg\_stat\_statements本身就是自己做的，然后只根据需要检索查询文本。由于服务器将查询文本存储在文件中，因此此方法可能会减少重复检查pg\_stat\_statements数据的物理I / O。  
配置参数

postgresql.conf中配置

参数 说明  
pg\_stat\_statements.max（integer） 跟踪的语句的最大数量（即pg\_stat\_statements视图中的最大行数）。如果观察到比这更明显的语句，则丢弃关于最少执行的语句的信息。默认值为5000.此参数只能在服务器启动时设置。  
pg\_stat\_statements.track（enum） 控制哪些语句由模块计数。指定top跟踪顶级语句（由客户端直接发布的语句），all还可以跟踪嵌套语句（例如在函数中调用的语句），或者none禁用语句统计信息收集。默认值是top。只有超级用户可以更改此设置。  
pg\_stat\_statements.track\_utility（boolean） 是否跟踪SELECT，INSERT， UPDATE和DELETE外的其他命令。默认值是on。只有超级用户可以更改此设置。  
pg\_stat\_statements.save（boolean） 指定是否跨服务器关闭保存语句统计信息。如果是，off那么统计数据不会在关机时保存，也不会在服务器启动时重新加载。默认值是 on