PostgreSQL 管理PG 的 4个 自制小脚本

最近问问题的同学挺多的，也有问有没有群的，实在是忙没有建群，所以问的人多了，想想还是建一个群，但本人写文章不懒，其他的比较懒，因为问POLARDB 的问题的多，所以建立了一个 POLARDB 和 PG 以及文章问题的讨论群。希望能帮助自己也帮助大家共同提高，要进群的，可以添加微信 liuaustin3 ,来申请加群。

转到本期的主体,POSTGRESQL 自制小脚本管理PG，这个问题是最近自己没有办法的办法，主要在于云上的POSTGRESQL，需要更快速和更专业的快速响应，当然线下的PG 也可以用这些脚本。主要的思想是，通过批量的灵活的脚本，最终产生在上端产生一个管理这些脚本和使用这些脚本的WEB 程序，集中快速的管理PG 数据库。

整体的脚本全部用PG 的函数撰写，后期可能用存储过程撰写，有人会问，为什么不用 GO ,PYTHON ，哪怕SHELL 撰写。这里面是有原因的，主要是云上的数据库本身限制多，并且连接起来，处理起来，如果通过上面的程序也不是不可以，但个人感觉不灵活，并且如果将这些程序集中在数据库内的话，对于一些其他的无法通过SHELL 连接的数据库，也可以进行管理，设置我进入到数据库内部，执行命令，就可以立即解决问题，整体的思维模式是，可上可下，可以结构化，也可以零散化，组织起来就是一个管理程序组，分散起来，可以专项对某些问题快速解决，部署简单，也考虑了传参的模式。

目前写了4 个小的脚本，下面简单的说说功能

1 pg_stat_activity 表信息的收集工具

这里主要解决的问题是，在发生系统CPU 或内存等系统资源告急的情况下，一般都需要对当前的连接的信息进行收集，发现问题，但存在如下问题

1连接需要时间

2 连接后信息怎么留存

3 如何控制频度，或者定时收集信息

4 信息收集到哪里，收集什么

基于上面的问题，撰写了下面的脚本

主要的功能和使用方法如下：

通过执行脚本后，在函数存在的数据库中，执行 select info_collect(n,a,b);完成如下功能

功能：收集POSTGRESQL 主机的运行过程的主机pg_stat_activity 信息，包含三个参数

n 控制搜取的次数，最大为60次，最小为1次

a 控制过滤搜取语句自上一次执行query_start后，截止到搜取的时间的长度，无限制，但不允许为负数，如为负数，程序退出，单位秒

b 在循环搜取的数据的过程中，间隔多少秒搜寻一次，最小为1秒 最大为10秒，超过范围程序退出

程序会在插入数据后，程序退出前将表改名为dba_query_log_当时修改表名的时间为新的表名。

其中存储的信息主要有PID ， 用户名，进程启动的时间，查询开始的时间，等待的类型，等待的事件，查询开始到目前的时间单位秒， 查询语句等信息,以及信息插入的时间。

演示：

我们在数据库中执行一个select pg_sleep(30); 在另一个进程里面执行 select pg_sleep(20);

然后开另外一个进程，执行我们的函数，来收集当前的数据库的pg_stat_activity;

执行后的结果：这里解释一下select info_collect(1,1,20) 的意思是

至少执行循环2次，语句执行时间据查询他的时间至少超过1秒，并且等20秒后，在记录一次信息。但实际上从截图中看到，没有20秒，而是10秒一次，因为程序中做的判断，如果设置的间隔时间超过10秒，则默认为10秒一次轮询。

也就是说这个程序最大可以执行的时间为360 * 10 = 3600百秒，也就是一个小时，一个小时每10秒进行一个数据的收集。

2 pg_stat_user_table的信息收集

这里面PG 的管理中牵扯很多细节问题，如我们后期要编写一个动态只能调整表的 autovacuum 配置参数的程序，而这个程序的判断数据的来源，就来自于 pg_stat_user_table 表，通过这个表中对各个表的状态收集，来产生一个表对于触发 autovacuum 的基础数据，并通过另一个程序（未编制）来进行大表的参数动态修改，避免在业务高峰期，产生 autovacuum 并且在业务低峰期，积极触发autovacuum 的智能化PG 管理程序。

工具功能：工具主要为分析PG 表中的死元组，数据的插入，查询，以及数据中是否有集中的全表扫描的问题，autovacuum工作情况，最后一次工作的时间等，等进行数据的分析收集数据使用

参数

n 参数控制执行的次数 执行最大次数为360次，负数或小于1则程序直接退出

a 控制间隔的时间，至少30秒操作一次获取数据，最大1小时获取一次数据

ar 表中的实际的数据必须大于1万，否则不统计

dr 表中的死元组必须大于 1万，否则不统计

举例：

下方为收集 lsy_5877 库中的用户表信息，其中可以看到，第一个2 是执行至少2次的数据获取，第二个2为2秒一次，但基于程序和保护数据库的特性，这里如果填写的间隔秒数低于30秒，则自动切换为30秒，此事可以看到结果，两次的数据收集，间隔了30秒，第三和第四个参数的意义是，表中的活跃的行数和 死元组的行数必须大于10万，如果小于10万的就不做统计了。

3 kill session 的PG 程序包

代码如下：

功能：

KILL SESSION 必须是在无法控制应用程序对数据库的使用的情况下，进行，操作过程中，必须在对应的物理数据库上添加此函数

然后执行 select kill_session(1) 其中杀死线程最大 30个，最小1个，输入1以下的，默认为1，超过30的，默认为30个。

程序中包含一个核心的地方，就是删除的进程前，会保留要清理的进程，因为在清理进程后，会有相关的人员，问，你到底清理了那些进程，此时如果手动，估计你也说不行，而这个命令的特殊性就在于，清理前会记录，可以进行查询。

杀死进程中有一个参数，就是你一次希望KILL多少进程，这里做了保护，如果你填写的进程数超过30 ，则最大清理30个连接。清理的规则是清理query_start 开始最久的连接清理。（后续可能会开发一个让用户可以选择的参数是最久的还是最近的）

4 kill_sql 清理超时的SQL 的脚本

目的：通过脚本，控制查杀多少慢SQL ，自定义多长时间的SQL 被查杀，

参数 a 控制一次性查杀多少SQL ，最大不能超过 30个

参数 b 控制多长时间的SQL 为超时

另只查杀慢SQL 语句，不会终结进程，更不会查杀idel 的进程

会记录查杀的SQL 的详细信息 包含 数据库名，用户名 ，应用名，事务开始时间，事务等待类型， 查询语句 ，已经消耗的查询时间 以及何时查杀的时间

功能与查杀连接雷同，但是增加了判断正在运行的SQL的时间，并且必须是 active 的语句，保证杀死的都是超时的，而不是idel 的连接。

因为功能和上面的雷同，这里就不做演示了。

如果有需要的同学可以加群，获取相关的脚本，并帮助我们改进，感谢！