前言
之前写了两篇关于 PostgreSQL 日志最佳实践的相关文章
日志的重要性不言而喻，作为故障诊断的绝佳利器，再加之 pgbadger (其愿景是 "Make your reports in seconds!")，可以以图表的形式展现数据库的各项活动和状态信息。但是，再怎么全面还是要人肉去看去捞，需要从日志中分析潜在危害。过往经验告诉我们，如果日志中出现了 FATAL、ERROR 等字眼，那么大概率你的数据库已经处于亚健康的状态了，更不用说 PANIC 了。既然如此，除了传统的监控告警方式，还有没有其他方式可以让我们实时知晓数据库的风吹草动呢？比如出现了 PANIC，我便立马可以接收到信息？Sure！
tail_n_mail
起因是我看到了这样一则需求——需要审计数据库上的 GRANT 和 REVOKE 等操作，并实时发出告警
One of our customers was looking for a custom solution to audit like "Grant" and "Revoke" performed on a database and alert in near realtime to the concerned authorities
愣是琢磨了好一会儿，也没有想到足够简单的解决方案。让我们看看参考答案：
pgaudit 的作用不用多说，自然是为了捕捉更详细的 GRANT/REVOKE 等行为，那 tail_n_mail 是什么玩意？原来 tail_n_mail 是一个纯 Perl 脚本，搭配定时任务，便可以自动从日志中捞取相关信息，然后发送邮件。
tail_n_mail (sometimes abbreviated TNM or tnm) is a Perl script for automatically detecting interesting items that appear in log files and mailing them out to interested parties. It is primarily aimed at Postgres log files but can be used for any files. It was originally developed at End Point Corporation by Greg Sabino Mullane.
其使用也很简单，默认使用 sendmail。配置完 EMAIL，以及在 INCLUDE 中指定所关心的指标便完成了，当然你也可以写各种各样的正则来捕获你关心的内容。此处就参照网上的一些例子：
## Config file for the tail_n_mail.pl program
## This file is automatically updated
EMAIL: greg@endpoint.com, postgres@endpoint.com
FILE: /var/log/pg_log/postgres-%Y-%m-%d.log
INCLUDE: FATAL:
INCLUDE: PANIC:
EXCLUDE: database ".+" does not exist
EXCLUDE: database "template0" is not currently accepting connections
MAILSUBJECT: HOST Postgres fatal errors (FILE)
然后便可以接收到实时的日志告警了
Date: Fri Mar 19 09:52:30 2010
Host: hyperion.example.com
Unique items: 4
Total matches: 134
Matches from [A] /var/log/apps/2010/hyperion/03/19/05/pgsql-warn.log: 60
Matches from [B] /var/log/apps/2010/hyperion/03/19/06/pgsql-warn.log: 8
Matches from [C] /var/log/apps/2010/hyperion/03/19/07/pgsql-warn.log: 16
Matches from [D] /var/log/apps/2010/hyperion/03/19/08/pgsql-warn.log: 31
Matches from [E] /var/log/apps/2010/hyperion/03/19/09/pgsql-warn.log: 19
[1] From files B to E Count: 102
First: [B] 2010-03-19T06:18:58-06:00 hyperion postgres[27317]
Last: [E] 2010-03-19T09:22:50-06:00 hyperion postgres[12493]
ERROR: syntax error at end of input at character 47 STATEMENT: select count(*) from sometable where = 34
[2] From files A to E Count: 30
First: [A] 2010-03-19T05:10:01-06:00 hyperion postgres[9917]
Last: [E] 2010-03-19T09:50:01-06:00 hyperion postgres[30464]
ERROR: insert or update on table "reindeer" violates foreign key constraint "reindeer_ref_antlers"
DETAIL: Key (antlers)=(green) is not present in table "antlers". STATEMENT: INSERT INTO reindeer
(age, gender, antler, notes) VALUES ($1,$2,$3,$4)
[3] From file B
2010-03-19T06:10:50-06:00 hyperion postgres[12868]: [31-1] ERROR: invalid input syntax for type
timestamp: " 00:00:00" STATEMENT: SELECT DISTINCT color FROM antler WHERE creation_date
BETWEEN ' 00:00:00' AND ' 23:59:59'
[4] From file D
2010-03-19T06:10:50-06:00 hyperion postgres[12868]: [33-1] FATAL: role "nosuchuser" does not exist
可以看到，FATAL 和 PANIC 都囊括在其中。
更多使用方式，请参照官网：https://bucardo.org/tail_n_mail/
小结
有了 tail_n_mail，我们可以做到很多事情：
流复制断了？没事，日志会提示 ERROR：WAL has been removed 数据库崩了？没事，日志会提示 FATAL：the database system is starting up 年龄快用完了？没事，日志会提示 WARNING: database "postgres" must be vacuumed within 9763669 transactions 逻辑复制断了？ ...
正如大管家所说，这还不给 Postgres 安排一个？
Few people stare at their mobile phones waiting for something to happen — they configure their phones to notify them when something important happens. Why not do that for your Postgres clusters? That's what check_postgres and tail_n_mail are meant to do.
参考
https://www.endpointdev.com/blog/2017/10/using-tailnmail-after-hours/
