PostgreSQL10基础（4）插件安装

安装环境

数据库：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