开发者学堂课程【PostgreSQL 快速入门：24PostgreSQL 日常维护和巡检】学习笔记（二），与课程紧密连接，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/16/detail/83
24 PostgreSQL 日常维护和巡检
内容介绍：
一、简介
二、日常维护
三、日常巡检
四、总结
三、日常巡检
1.日志文件
日常会产生很多日志文件。日志文件比如放在下图所示地方，有很多组件，日积月累之后，日志文件就越来越大。
需要把老的日志打包，比如把一月前的日志打包保留，删掉。自己去定制一些后台操作就可以。
还有日志内容的检查。要去检查这里面有一些错误。比如 error 或者 fault 这种错误，因为库里面没有任何错误，不用去查。
还有是常识口。比如我这里配置generation。输入log min_duration_statement。如果配置负一，表示不记录。打个比方，输入10毫秒，即行时间超过10毫秒，都会把它记录。
还有锁等待。锁等待比如超过一秒钟，也是自己定义的时间，超过一秒钟就记录日志。
从字面去关注一些数据库的健康的状态。是否需要做优化或者业务逻辑是否调整，是否有被攻击的这种现象产生等等。
直接去查看文件、查询日志可能不太方便。postgres提供比较有效的，可以创建文件级别的外部表。文件级别的外部表，其实这里使用的是CSVlog，所以它的格式还是比较固定的。就可以通过创建外部表，把数据导进来去做查询，或者是直接使用外部表。使用外部表，不需要数据导进数据。
比如创建外部表。
CREATE TABLE postgres_log
log time timestamp(3) with time zone,
user name text
database name text process_id integer, connection from text
session id text
session_line_num bigint, command_tag text
session_start_time timestamp with time zone, virtual transaction id text. transaction id bigint, error_severity text. sql_state_code text, message text, detail text,
比如要查它的日志级别、错误，错误范围，查看比较方便。然后查完之后，直接out外部表的选项。输入all the following table, 然后options set 修改原链接，然后就可以查下一个外部表。这样去做比较简单。
2.数据库环境
还有如果数据库是HA的环境，或者是配置 standby 用流复制配置 standby ，还需要去检查standby延迟。
通过查看视图，视图里面就 sent_location，通过对比发送的 location和当前的 x log 的位置。比如当前是去比对值之间的差异。差异看 diff 函数 pg_log_location_diff。diff 函数去获取返回差异，
来判断 standby 延迟。
write _location指的是x log 接收到之后，已经写到磁盘里的意思。flush_location 是指从OScatch 已经flash 到磁盘。
这些都指的是standby 的节点。如果有很多个standby 节点，每节点上面都会有这种信息。比如A节点发送的很快，B节点可能是因为网络堵塞的原因，它的location 跟当前的location 差别比较大。
replay指的是stand by还原的速率。比如还原位置和sent的速率是一样，表示性能还不错，接触到x log之后，马上就可以做还原。standby的相当于跟primary节点完全达到一致。
3.数据库巡检模板
那最后要提的是日常的数据库巡检的模板。数据库在巡检的时候关注一些东西。
（1）基本信息
一般数据库的基本信息要记录。比如安装数据库的场景，程序和内核版本数据库的版本。比如安装9.3.3版本，在巡检的时候，如果数据库已经打很多个补丁到9.3.6。那么可能要考虑数据库是不是也要打补丁升级。所以巡检的时候需要关注它的版本，然后安装第三方插件。比如数据库里安装pgfincore，安装第三方插件要记录。因为第三方插件版本可能需要升级。还有数据库最后编译的参数，
使用的参数也是需要去记下来。比如指定的WL的process。在另外数据库建立standby，配置必须要完全一致。如果不一样，不能做复制的环境。
包括下面一些东西。
（2）postgres配置
那么接下来要记录postgres配置。比如md5值主要是如果配置时间或者集群环境，两个cosplay的配置文件一致，很容易的去做比对。
（3）非默认内容
那么把这种非默认的内容打印出来，通过grep “^[a-z] postgresq.conf打印。比如可以连到目录下面执行自然命令，把它的非默认值打印出来，然后记录。
（4）语法
接着检查这里的语法是不是正确，检测语法的合法性。
（5）HA环境
如果流复制环境是HA环境，确保主备环境md5值一致，同时STANDBY确保执行reload。因为在做HA切换的时候，如果STANDBY没有做reload，比如提改修改参数值或者修改pg hba.config文件,那么其实是老的配置，所以也要做reload。每一次修改的时候STANDBY确保执行reload。
其它的配置上的一些优化建议，可以去参考这一篇文章：http://blog.163.com/digoal@126/blog/static/163877040201221382150858/
配置合法性，hba文件、recovery等都要检测。
（6）csvlog
接下来要检测的是csvlog，上一次检测以来，比如检测二月份,检测二月份有哪些错误,能够输出一些错误代码。错误代码查后，到错误代码里面去看，这是一些类型的错误。
比如违反唯一约束的类型，错常非常多，要检查业务上的数据,给出合理的建议。
（7）定时任务
定时任务，比如数据库里面有没有定时任务。巡检的时候去检查定时任务是否正常运行或者有必要运行，这次巡检以来数据库的增长。比如去年以来记录数据库输入的大小，通过这条命令就能够记录当前数据库的大小。如果有上一次记录，可以比对数据库的增长的情况。
查看增长是不是稳定，是否是突发性增长，要关注大表，还有一些垃圾数据。
（8）top 10 SQL
然后接下来要看的是top 10 SQL，业务运行的时候，查看SQL语句耗时、调用次数、语句的命中率等，都需要去通过创建插件去完善。
创建插件。可以去查最近以来的统计信息，比如哪些语句的命中率比较低，哪些语句消耗的时间长多等等，都可以统计出来。
top 10 SQL巡检应该是重中之重。其实在日常的时候，每天都要去关注数据库运行情况和波动情况，及时做一些调整。
（9）数据库状态
接下来要巡检的是数据库的状态。
比如剩余连接数，如果连接数不够会导致数据库新建连接无法新建。然后是年龄大于巡检，可以自定义年龄>多少亿，因为最多分数只有20亿的点。blog里面已经指出来，有详细的解释。
如果年龄到达一定数值，要去手工vacuum freeze降低年龄。
还有是大表，连接各个数据库去查询比如大于10G的表以及表的年龄。如果年龄大于10亿，建议做vacuum freeze。大表建议去做数据库的分区。因为大表去做vacuum freeze的时候，可能不能降低年龄。
比如做vacuum freeze之前，在整个数据库集群里面，只要有任何事物是repeatedly read或者是COR iful级别，只要事物在vacuum freez结束前还没有结束，vacuum freez无法回收垃圾数据，无法降低年龄。详细了解可以去参考上文blog。
（10）索引
超过4的表，要检查索引是否合理的。因为索引越多，d上操作带来负面影响越大。而且每一个索引都无法确保在发挥作用。查看索引是否使用，可以到系统里面去查看。
如果index和下面几个都是零，表示索引从上次统计信息被清除以来，到现在没有任何一次扫描，比如索引根本没有使用，可以考虑把索引删除。
接着检查上一次巡检以来，使用较少的索引。
（11）各库分区表
检查各库分区表的时间，分区表的权限、触发。比如是以时间来分析的申请表，现在已经三月份，马上就四月份，如果四月份的分表还没有创建，要创建。否则到下个月的时候触发器可能报错，四月份的表不存在。
（12）膨胀检查
还要检查膨胀。连接每个库进行查询，是否有表已经膨胀，是否要去做重组的操作。前面提到重组使用PG reorganize这样插件去做这种重组。
还有垃圾数据的巡检，已经提交过。存储剩余空间和表空间巡检，还有数据库所在服务器的负载，以及它的IO群体。
（13）回滚比例
还要巡检的是否开启归档和自动垃圾回收，回滚比例、命中比例是样的情况。
回滚比例查看pg_stat_database。
这里回滚比较少。如果发现回滚跟提交比例相差小，比如回滚提交1000万次，但回滚500万次，可以关注业务上的问题。因为回滚也会产生垃圾数据。比如是收藏一条记录，回滚掉。这条记录实际上已经存在，它变成data tup，会带来额外的垃圾回收的负担。
还有长事务和长SQL也要关注，都可以通过log_min_duration_ statement去csvlog里面去统计。
（14）序列
还是序列是否正常，序列可能很容易被忽视的。序列在使用的时候，比如表是int 4 类型，但是序列是int 8类型。
当序列值大到超过int 4时，就会报错。还有序列如果没有开启循环使用，比如序列最大值是1000万，最小值是1，并且没有设置sql。
序列用到1000万的时候也会报错，序列已经用完，所以也需要去检查。
还有备份、容灾、HA状态的检查。
四、总结
1.清除工作
那么在巡检结束之后，要把统计信息清除。清除计数器的信息，不是pg_stats。比如表插、删除操作次数，下次再去巡检的时候，看到的就是从上一次训练到现在为止的状态。不清除相当于是整个历史里面的状态。
2.参考模板
巡检报告里面用到的每一项的查询。比如如何查询垃圾数据，因为这里blogg字数限制的原因没有写出。可以去把文档下载来查看到。
每一个SQL都已经标注过。比如历史的数据库增长、耗时都有标注出来，可以去下载模板，方便巡检的时候使用。也可以把它设置成脚本去做。
