本文主要介绍pg的log的使用方式和参数说明,部分参考https://www.modb.pro/db/45471

Table of Contents

• 1 不同安装方式日志收集功能区别
• 2 日志参数
• 3 日志使用
• 3.1 开启日志
• 3.2 log 路径
• 3.3 日志开关
• 3.5 日志保留策略
• 3.6 日志前缀使用
• 3.7 慢日志使用
• 3.8 记录DDL和DML操作
• 3.9 日志详细程度
• 3.10 日志统计监控
• 3.11 调试SQL执行计划和过程
• 4 日志插件pgBadger
• 4.1 安装
• 4.2 使用
• 4.3 测试
• 4.3.1 log日志参数改造
• 4.3.2 使用pgBadger来分析
• 4.3.3 查看网页

1 不同安装方式日志收集功能区别

2 日志参数

查询log参数：
postgres@[local]:5432=>        SELECT name,
postgres->                setting
postgres->         FROM pg_settings
postgres->         WHERE name ~ 'log';
              name               |    setting
---------------------------------+---------------
 log_autovacuum_min_duration     | -1
 log_checkpoints                 | off
 log_connections                 | off
 log_destination                 | stderr
 log_disconnections              | off
 log_duration                    | off
 log_error_verbosity             | default
 log_executor_stats              | off
 log_file_mode                   | 0600
 log_hostname                    | off
 log_line_prefix                 | %m [%p]
 log_lock_waits                  | off
 log_min_duration_statement      | -1
 log_min_error_statement         | error
 log_min_messages                | warning
 log_parser_stats                | off
 log_planner_stats               | off
 log_replication_commands        | off
 log_rotation_age                | 1440
 log_rotation_size               | 10240
 log_statement                   | none
 log_statement_stats             | off
 log_temp_files                  | -1
 log_timezone                    | Asia/Shanghai
 log_truncate_on_rotation        | off
 logging_collector               | off
 max_logical_replication_workers | 4
 syslog_facility                 | local0
 syslog_ident                    | postgres
 syslog_sequence_numbers         | on
 syslog_split_messages           | on
 wal_log_hints                   | off
 
 
 postgres@[local]:5432=# SELECT name,
postgres-# setting
postgres-# FROM pg_settings
postgres-# WHERE name like '%debug%';
         name          | setting
-----------------------+---------
 debug_assertions      | off
 debug_pretty_print    | on
 debug_print_parse     | off
 debug_print_plan      | off
 debug_print_rewritten | off
(5 rows)


复制

3 日志使用

3.1 开启日志

默认为off，设置为on则pg可以记录相关日志，建议打开，否则在数据库出现异常时候，没有日志来定位具体问题。
logging_collector

我的实验环境是编译安装
[postgres@PGserver2 ~]$ psql
psql (10.18)
Type "help" for help.

postgres@[local]:5432=#show logging_collector ;
 logging_collector
-------------------
 off
(1 row)

postgres@[local]:5432=#alter system set logging_collector=on;
ALTER SYSTEM
postgres@[local]:5432=#\q
[postgres@PGserver2 ~]$ pg_ctl restart -D $PGDATA -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[postgres@PGserver2 ~]$

postgres@[local]:5432=#show logging_collector
postgres-# ;
 logging_collector
-------------------
 on
(1 row)
复制

3.2 log 路径

--默认的日志路径：
postgres@[local]:5432=#show log_directory ;
 log_directory
---------------
 log
(1 row)
--如果参数不是绝对路径，那么默认的路径在 $PGDATA 目录下。--
postgresql-2021-09-03_095109.log
[postgres@PGserver2 log]$ pwd
/data/pg10.18/pgdata/log

修改日志文件的路径：
[postgres@PGserver2 ~]$ psql
psql (10.18)
Type "help" for help.

postgres@[local]:5432=#show logging_collector
postgres-# ;
 logging_collector
-------------------
 on
(1 row)

postgres@[local]:5432=#show log_directory ;
 log_directory
---------------
 log
(1 row)

postgres@[local]:5432=#alter system set log_directory  = 'pglog';
ALTER SYSTEM
postgres@[local]:5432=#\q
[postgres@PGserver2 ~]$ pg_ctl  restart  -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[postgres@PGserver2 ~]$

[postgres@PGserver2 pglog]$ pwd
/data/pg10.18/pgdata/pglog
[postgres@PGserver2 pglog]$ ls
postgresql-2021-09-03_101728.log


修改日志目录到非 $PGDATA 目录下：
准备把log日志放到/data/log下
mkdir -p /data/log



postgres@[local]:5432=#alter system set log_directory ='/data/log';
ALTER SYSTEM
postgres@[local]:5432=#\q
[postgres@PGserver2 ~]$ pg_ctl  restart  -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started


[postgres@PGserver2 log]$ ls
postgresql-2021-09-03_101929.log
[postgres@PGserver2 log]$ pwd
/data/log
[postgres@PGserver2 log]$ more postgresql-2021-09-03_101929.log
2021-09-03 10:19:29.641 CST [10418] LOG:  database system was shut down at 2021-09-03 10:19:29 CST
2021-09-03 10:19:29.643 CST [10416] LOG:  database system is ready to accept connections
[postgres@PGserver2 log]$

--恢复原状

postgres@[local]:5432=#alter system set log_directory ='log';
ALTER SYSTEM
postgres@[local]:5432=#\q
[postgres@PGserver2 ~]$ pg_ctl  restart  -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started

复制

3.3 日志开关

postgres@[local]:5432=#show log_connections ;
 log_connections
-----------------
 off
(1 row)

postgres@[local]:5432=#show log_disconnections ;
 log_disconnections
--------------------
 off
(1 row)

postgres@[local]:5432=#show log_checkpoints ;
 log_checkpoints
-----------------
 off
(1 row)

postgres@[local]:5432=#show log_duration ;
 log_duration
--------------
 off
(1 row)

postgres@[local]:5432=#show log_hostname ;
 log_hostname
--------------
 off
(1 row)

postgres@[local]:5432=#show log_lock_waits ;
 log_lock_waits
----------------
 off
(1 row)

以上默认都是off

现在针对相关的参数进行测试
log_connections、log_disconnections
因为这2个参数是superuser-backend  ---因为reload后需要重新登陆

postgres@[local]:5432=#alter system set log_connections =on;
ALTER SYSTEM
postgres@[local]:5432=#alter system set log_disconnections =on;
ALTER SYSTEM
postgres@[local]:5432=#select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres@[local]:5432=#show log_disconnections ;
 log_disconnections
--------------------
 off
(1 row)

postgres@[local]:5432=#show log_connections ;
 log_connections
-----------------
 off
(1 row)

postgres@[local]:5432=#^C
postgres@[local]:5432=#\c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres@[local]:5432=#show log_disconnections ;
 log_disconnections
--------------------
 on
(1 row)

postgres@[local]:5432=#show log_connections ;
 log_connections
-----------------
 on
(1 row)

postgres@[local]:5432=#

相关日志信息：
2021-09-03 10:55:51.865 CST [12452] LOG:  disconnection: session time: 0:01:24.175 user=postgres database=postgres host=[local]
2021-09-03 10:56:04.223 CST [12532] LOG:  disconnection: session time: 0:00:12.359 user=postgres database=postgres host=[local]
2021-09-03 10:56:15.302 CST [12554] LOG:  connection received: host=[local]
2021-09-03 10:56:15.303 CST [12554] LOG:  connection authorized: user=postgres database=postgres
以上就是连接和断开连接的日志


==========================================================

log_duration

postgres@[local]:5432=#alter system set log_duration=on;
ALTER SYSTEM
postgres@[local]:5432=#select pg_reload

postgres@[local]:5432=#select pg_reload

postgres@[local]:5432=#select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres@[local]:5432=#show log_duration ;
 log_duration
--------------
 on
(1 row)
2021-09-03 10:58:35.852 CST [12554] LOG:  duration: 1.267 ms
2021-09-03 10:58:37.555 CST [12554] LOG:  duration: 1.187 ms
2021-09-03 10:58:38.478 CST [12554] LOG:  duration: 0.061 ms

==========================================================

log_hostname

postgres@[local]:5432=#alter system set log_hostname=on;
ALTER SYSTEM
postgres@[local]:5432=#select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres@[local]:5432=#show log_hostname ;
 log_hostname
--------------
 on
(1 row)

[postgres@pgserver12 ~]$ psql -h 192.168.245.143 -d postgres -U postgres
Password for user postgres:
psql (12.5, server 10.18)
Type "help" for help.


2021-09-03 11:01:41.680 CST [12868] LOG:  connection authorized: user=postgres database=postgres
2021-09-03 11:02:51.726 CST [12760] LOG:  disconnection: session time: 0:02:53.339 user=postgres database=postgres host=[local]
2021-09-03 11:02:53.292 CST [12939] LOG:  connection received: host=[local]
2021-09-03 11:02:53.292 CST [12939] LOG:  connection authorized: user=postgres database=postgres
2021-09-03 11:03:04.335 CST [12868] LOG:  disconnection: session time: 0:01:24.678 user=postgres database=postgres host=192.168.245.119 port=4217
2021-09-03 11:03:07.405 CST [12950] LOG:  connection received: host=192.168.245.119 port=42176
2021-09-03 11:03:13.096 CST [12976] LOG:  connection received: host=192.168.245.119 port=42178
2021-09-03 11:03:13.097 CST [12976] LOG:  connection authorized: user=postgres database=postgres
2021-09-03 11:08:27.317 CST [12976] LOG:  disconnection: session time: 0:05:16.229 user=postgres database=postgres host=192.168.245.119 port=42178
2021-09-03 11:08:32.356 CST [13299] LOG:  connection received: host=192.168.245.119 port=42180

这个我测试过了 还是只显示IP ?不知道为什么？

========================================================

log_lock_waits

设置参数
log_lock_waits = on
deadlock_timeout = 1s
postgres@[local]:5432=#alter system set log_lock_waits = on;
ALTER SYSTEM
postgres@[local]:5432=#alter system set deadlock_timeout = 1s;
ERROR:  syntax error at or near "s"
LINE 1: alter system set deadlock_timeout = 1s;
                                             ^
postgres@[local]:5432=#alter system set deadlock_timeout = '1s';
ALTER SYSTEM
postgres@[local]:5432=#select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres@[local]:5432=#show deadlock_timeout ;
 deadlock_timeout
------------------
 1s
(1 row)

postgres@[local]:5432=#show log_lock_waits ;
 log_lock_waits
----------------
 on
(1 row)


测试锁：

session a：

postgres@[local]:5432=#create table test2(id int, info text);
CREATE TABLE
postgres@[local]:5432=#insert into test2 values (1,'test');
INSERT 0 1
postgres@[local]:5432=#begin;
BEGIN
postgres@[local]:5432=#update test2 set info='a' where id=1;
UPDATE 1

session b ：
postgres@[local]:5432=#update test2 set info='b' ;
UPDATE 1


日志输出：

2021-09-03 11:12:59.775 CST [13560] DETAIL:  Process holding the lock: 12939. Wait queue: 13560.
2021-09-03 11:12:59.775 CST [13560] CONTEXT:  while updating tuple (0,1) in relation "test2"
2021-09-03 11:12:59.775 CST [13560] STATEMENT:  update test2 set info='b' ;
2021-09-03 11:13:23.031 CST [12939] ERROR:  syntax error at or near "/" at character 5
2021-09-03 11:13:23.031 CST [12939] STATEMENT:  end
        /
        ;
2021-09-03 11:13:23.031 CST [13560] LOG:  process 13560 acquired ShareLock on transaction 930 after 24257.795 ms
2021-09-03 11:13:23.031 CST [13560] CONTEXT:  while updating tuple (0,1) in relation "test2"
2021-09-03 11:13:23.031 CST [13560] STATEMENT:  update test2 set info='b' ;
2021-09-03 11:13:23.031 CST [13560] LOG:  duration: 24258.607 ms

复制

3.5 日志保留策略

如下一共三个配置方案，可根据业务需求进行配置

​
​

复制

3.6 日志前缀使用

PostgreSQL 日志行前缀可以包含除了实际消息本身之外最有价值的信息。Postgres 文档列出了日志事件前缀配置的几个转义字符。在运行时，这些转义序列将替换为各种状态值。像pgBadger这样的应用程序就没有特定的日志行前缀。
我建议在前缀中包含以下信息：

事件的时间(不含毫秒)：％t

远程客户端名称或IP地址：％h

用户名：％u

访问数据库：％d

应用名称：％a

进程ID：％p

终止非会话进程输出：％q

每个会话或进程的日志行号，从1开始：%l


log_line_prefix = 'time=%t, pid=%p %q db=%d, usr=%u, client=%h , app=%a, line=%l'
postgres@[local]:5432=#alter system set log_line_prefix = 'time=%t, pid=%p %q db=%d, usr=%u, client=%h , app=%a, line=%l';
ALTER SYSTEM
postgres@[local]:5432=#show log_line_prefix
postgres-# ;
 log_line_prefix
-----------------
 %m [%p]
(1 row)

postgres@[local]:5432=#select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres@[local]:5432=#show log_line_prefix
postgres-# ;
                        log_line_prefix
---------------------------------------------------------------
 time=%t, pid=%p %q db=%d, usr=%u, client=%h , app=%a, line=%l
(1 row)


复制

3.7 慢日志使用

#执行sql时间为2s以上的sql都会被记录下来
log_min_duration_statement = 2s

“ log_min_duration_statement”参数。设置此参数（不使用任何单位，以毫秒为单位）时，将记录任何等于或大于参数值的语句的持续时间。将此参数值设置为0将记录所有已完成语句的持续时间。将此设置为-1将禁用语句持续时间记录。这是默认值，我建议保持默认值。


这个和mysql慢日志差不多
MySQL 慢查询的相关参数解释：
slow_query_log    ：是否开启慢查询日志，1表示开启，0表示关闭。
log-slow-queries  ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

复制

3.8 记录DDL和DML操作

log_statement = 'off' | 'ddl' | 'mod' | 'all'
将其设置为“ all”以捕获服务器上运行的每个 SQL 语句可能很诱人，但在现实中这并不总是一个好主意。
如果您的PostgreSQL实例适度繁忙（每小时数十次查询），请随时将此参数设置为“ all”。在对运行缓慢的SELECT查询进行故障排除或寻找未经授权的数据访问时，也可以将其临时设置为“all。一些应用程序，例如pgBadger，也希望您将其设置为“ all”。


postgres@[local]:5432=#alter system set log_statement='all';
ALTER SYSTEM
postgres@[local]:5432=#select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres@[local]:5432=#show log_statement;
 log_statement
---------------
 all
(1 row)

postgres@[local]:5432=#select * from pg_authid ;
       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit |
      rolpassword             | rolvaliduntil
----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------
------------------------------+---------------


time=2021-09-03 11:25:27 CST, pid=14081  db=postgres, usr=postgres, client=[local] , app=psql, line=11LOG:  statement: SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,5)='pg_au' AND pg_catalog.pg_table_is_visible(c.oid)
        UNION
        SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,5)='pg_au' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,5) = substring('pg_au',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
        UNION
        SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,5)='pg_au' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,5) = substring('pg_au',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,5) = substring('pg_au',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
        LIMIT 1000
time=2021-09-03 11:25:27 CST, pid=14081  db=postgres, usr=postgres, client=[local] , app=psql, line=12LOG:  duration: 2.639 ms
time=2021-09-03 11:25:29 CST, pid=14081  db=postgres, usr=postgres, client=[local] , app=psql, line=13LOG:  statement: SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(c.relname),1,9)='pg_authid' AND pg_catalog.pg_table_is_visible(c.oid)
        UNION
        SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname) || '.',1,9)='pg_authid' AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,9) = substring('pg_authid',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
        UNION
        SELECT pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND substring(pg_catalog.quote_ident(n.nspname) || '.' || pg_catalog.quote_ident(c.relname),1,9)='pg_authid' AND substring(pg_catalog.quote_ident(n.nspname) || '.',1,9) = substring('pg_authid',1,pg_catalog.length(pg_catalog.quote_ident(n.nspname))+1) AND (SELECT pg_catalog.count(*) FROM pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) || '.',1,9) = substring('pg_authid',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) = 1
        LIMIT 1000
time=2021-09-03 11:25:29 CST, pid=14081  db=postgres, usr=postgres, client=[local] , app=psql, line=14LOG:  duration: 0.781 ms
time=2021-09-03 11:25:29 CST, pid=14081  db=postgres, usr=postgres, client=[local] , app=psql, line=15LOG:  statement: select * from pg_authid ;
time=2021-09-03 11:25:29 CST, pid=14081  db=postgres, usr=postgres, client=[local] , app=psql, line=16LOG:  duration: 0.307 ms

复制

3.9 日志详细程度

每个PostgreSQL事件都有一个关联的消息级别。消息级别可以从详细的DEBUG到简洁的PANIC。级别越低，消息越详细。“ log_min_messages”参数的默认值为“ WARNING”。我建议将其保持在此级别，除非您希望记录更多信息性的消息。
参数“ log_min_error_statement”控制将记录哪些 SQL 语句抛出错误。与“ log _ min _ message”一样，严重程度等于或高于“ log_min_error_statement”中指定值的任何 SQL 语句都将被记录。默认值为“ ERROR”，我建议使用默认值



复制

3.10 日志统计监控

#log_statement_stats为on则会开启log_parser_stats，log_planner_stats，log_executor_stats这三个选项，生产环境不建议开启，建议测试环境开启，用于定位问题。
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
复制

3.11 调试SQL执行计划和过程

4 日志插件pgBadger

pgBadger

​ pgBadger是一个功能强大的PostgreSQL日志分析器，可以让我们从Postgres日志文件中得到非常有用的信息。它是用 Perl 编写的开源工具，在运行它的机器上占用的空间非常小。该工具从命令行运行，并带有大量选项。它将一个或多个日志作为输入，并且可以生成HTML报告，其中包含以下方面的详细统计信息：

使用像pgBadger这样的工具

pgBadger是一个功能强大的PostgreSQL日志分析器，可以让我们从Postgres日志文件中得到非常有用的信息。它是用 Perl 编写的开源工具，在运行它的机器上占用的空间非常小。该工具从命令行运行，并带有大量选项。它将一个或多个日志作为输入，并且可以生成HTML报告，其中包含以下方面的详细统计信息：
 最常见等待的查询。
 查询生成最多的或最大的临时文件
 运行速度最慢的查询
 平均查询持续时间
 最常运行的查询
 查询中最常见的错误
 运行查询的用户和应用程序
 检查点统计信息。
 自动清理和自动分析统计信息。
 锁定统计
 错误事件（panic，fatal，error和warning）。
 连接和会话配置文件（按数据库，用户，应用程序）
 会话配置文件
 查询配置文件（查询类型，按数据库/应用程序查询）
 I O统计
复制

4.1 安装

下载地址：https://github.com/darold/pgbadger/releases
说明文档：https://github.com/darold/pgbadger

perl Makefile.PL
make
make install 

 yum install -y perl perl-devel
 yum install -y php php-devel
 
 
 
[postgres@PGserver2 ~]$ cd pgbadger-master
[postgres@PGserver2 pgbadger-master]$ ls
ChangeLog  CONTRIBUTING.md  doc  HACKING.md  LICENSE  Makefile.PL  MANIFEST  META.yml  pgbadger  README  README.md  resources  t  tools
[postgres@PGserver2 pgbadger-master]$


[root@PGserver2 postgres]# cd pgbadger-master
[root@PGserver2 pgbadger-master]# perl Makefile.PL
Writing Makefile for pgBadger
[root@PGserver2 pgbadger-master]# make && make install
which: no pod2markdown in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
cp pgbadger blib/script/pgbadger
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/pgbadger
echo "=head1 SYNOPSIS" > doc/synopsis.pod
./pgbadger --help >> doc/synopsis.pod
echo "=head1 DESCRIPTION" >> doc/synopsis.pod
sed -i.bak 's/ +$//g' doc/synopsis.pod
rm doc/synopsis.pod.bak
sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod
sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod
rm doc/pgBadger.pod.bak
Manifying blib/man1/pgbadger.1p
rm doc/synopsis.pod
which: no pod2markdown in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
echo "=head1 SYNOPSIS" > doc/synopsis.pod
./pgbadger --help >> doc/synopsis.pod
echo "=head1 DESCRIPTION" >> doc/synopsis.pod
sed -i.bak 's/ +$//g' doc/synopsis.pod
rm doc/synopsis.pod.bak
sed -i.bak '/^=head1 SYNOPSIS/,/^=head1 DESCRIPTION/d' doc/pgBadger.pod
sed -i.bak '4r doc/synopsis.pod' doc/pgBadger.pod
rm doc/pgBadger.pod.bak
Manifying blib/man1/pgbadger.1p
Installing /usr/local/share/man/man1/pgbadger.1p
Installing /usr/local/bin/pgbadger
Appending installation info to /usr/lib64/perl5/perllocal.pod
rm doc/synopsis.pod

复制

4.2 使用

Arguments:

logfile can be a single log file, a list of files, or a shell command
returning a list of files. If you want to pass log content from stdin
use - as filename. Note that input from stdin will not work with csvlog.
Options:

-a | --average minutes : number of minutes to build the average graphs of
                         queries and connections. Default 5 minutes.
-A | --histo-average min: number of minutes to build the histogram graphs
                         of queries. Default 60 minutes.
-b | --begin datetime  : start date/time for the data to be parsed in log
                         (either a timestamp or a time)
-c | --dbclient host   : only report on entries for the given client host.
-C | --nocomment       : remove comments like /* ... */ from queries.
-d | --dbname database : only report on entries for the given database.
-D | --dns-resolv      : client ip addresses are replaced by their DNS name.
                         Be warned that this can really slow down pgBadger.
-e | --end datetime    : end date/time for the data to be parsed in log
                         (either a timestamp or a time)
-E | --explode         : explode the main report by generating one report
                         per database. Global information not related to a
                         database are added to the postgres database report.
-f | --format logtype  : possible values: syslog, syslog2, stderr, jsonlog,
                         cvs, pgbouncer, logplex, rds and redshift. Use this
                         option when pgBadger is not able to detect the log
                         format.
-G | --nograph         : disable graphs on HTML output. Enabled by default.
-h | --help            : show this message and exit.
-H | --html-outdir path: path to directory where HTML report must be written
                         in incremental mode, binary files stay on directory
                         defined with -O, --outdir option.
-i | --ident name      : programname used as syslog ident. Default: postgres
-I | --incremental     : use incremental mode, reports will be generated by
                         days in a separate directory, --outdir must be set.
-j | --jobs number     : number of jobs to run at same time. Run as single
                         by default or when working with csvlog.
-J | --Jobs number     : number of log file to parse in parallel. Process one
                         file at a time by default or when csvlog is used.
-l | --last-parsed file: allow incremental log parsing by registering the
                         last datetime and line parsed. Useful if you want
                         to watch errors since last run or if you want one
                         report per day with a log rotated each week.
-L | --logfile-list file:file containing a list of log file to parse.
-m | --maxlength size  : maximum length of a query, it will be restricted to
                         the given size. Default truncate size is 100000.
-M | --no-multiline    : do not collect multiline statement to avoid garbage
                         especially on errors that generate a huge report.
-n | --nohighlight     : disable SQL code highlighting.
-N | --appname name    : only report on entries for given application name
-o | --outfile filename: define the filename for the output. Default depends
                         on the output format: out.html, out.txt, out.bin,
                         out.json or out.tsung. This option can be used
                         multiple time to output several format. To use json
                         output the Perl module JSON::XS must be installed,
                         To dump output to stdout use - as filename.
-O | --outdir path     : directory where out file must be saved.
-p | --prefix string   : the value of your custom log_line_prefix as
                         defined in your postgresql.conf. Only use it if you
                         aren't using one of the standard prefixes specified
                         in the pgBadger documentation, such as if your
                         prefix includes additional variables like client ip
                         or application name. See examples below.
-P | --no-prettify     : disable SQL queries prettify formatter.
-q | --quiet           : don't print anything to stdout, not even a progress
                         bar.
-Q | --query-numbering : add numbering of queries to the output when using
                         options --dump-all-queries or --normalized-only.
-r | --remote-host ip  : set the host where to execute the cat command on
                         remote logfile to parse locally the file.
-R | --retention N     : number of weeks to keep in incremental mode. Default
                         to 0, disabled. Used to set the number of weeks to
                         keep in output directory. Older weeks and days
                         directory are automatically removed.
-s | --sample number   : number of query samples to store. Default: 3.
-S | --select-only     : only report SELECT queries.
-t | --top number      : number of queries to store/display. Default: 20.
-T | --title string    : change title of the HTML page report.
-u | --dbuser username : only report on entries for the given user.
-U | --exclude-user username : exclude entries for the specified user from
                         report. Can be used multiple time.
-v | --verbose         : enable verbose or debug mode. Disabled by default.
-V | --version         : show pgBadger version and exit.
-w | --watch-mode      : only report errors just like logwatch could do.
-W | --wide-char       : encode html output of queries into UTF8 to avoid
                         Perl message "Wide character in print".
-x | --extension       : output format. Values: text, html, bin, json or
                         tsung. Default: html
-X | --extra-files     : in incremental mode allow pgBadger to write CSS and
                         JS files in the output directory as separate files.
-z | --zcat exec_path  : set the full path to the zcat program. Use it if
                         zcat or bzcat or unzip is not in your path.
-Z | --timezone +/-XX  : Set the number of hours from GMT of the timezone.
                         Use this to adjust date/time in JavaScript graphs.
--pie-limit num        : pie data lower than num% will show a sum instead.
--exclude-query regex  : any query matching the given regex will be excluded
                         from the report. For example: "^(VACUUM|COMMIT)"
                         You can use this option multiple times.
--exclude-file filename: path of the file which contains all the regex to
                         use to exclude queries from the report. One regex
                         per line.
--include-query regex  : any query that does not match the given regex will
                         be excluded from the report. You can use this
                         option multiple times. For example: "(tbl1|tbl2)".
--include-file filename: path of the file which contains all the regex of
                         the queries to include from the report. One regex
                         per line.
--disable-error        : do not generate error report.
--disable-hourly       : do not generate hourly report.
--disable-type         : do not generate report of queries by type, database
                         or user.
--disable-query        : do not generate query reports (slowest, most
                         frequent, queries by users, by database, ...).
--disable-session      : do not generate session report.
--disable-connection   : do not generate connection report.
--disable-lock         : do not generate lock report.
--disable-temporary    : do not generate temporary report.
--disable-checkpoint   : do not generate checkpoint/restartpoint report.
--disable-autovacuum   : do not generate autovacuum report.
--charset              : used to set the HTML charset to be used.
                         Default: utf-8.
--csv-separator        : used to set the CSV field separator, default: ,
--exclude-time  regex  : any timestamp matching the given regex will be
                         excluded from the report. Example: "2013-04-12 .*"
                         You can use this option multiple times.
--include-time  regex  : only timestamps matching the given regex will be
                         included in the report. Example: "2013-04-12 .*"
                         You can use this option multiple times.
--exclude-db name      : exclude entries for the specified database from
                         report. Example: "pg_dump". Can be used multiple
                         time.
--exclude-appname name : exclude entries for the specified application name
                         from report.  Example: "pg_dump".  Can be used
                         multiple time.
--exclude-line regex   : pgBadger will start to exclude any log entry that
                         will match the given regex. Can be used multiple
                         time.
--exclude-client name  : exclude log entries for the specified client ip.
                         Can be used multiple time.
--anonymize            : obscure all literals in queries, useful to hide
                         confidential data.
--noreport             : prevent pgBadger to create reports in incremental
                         mode.
--log-duration         : force pgBadger to associate log entries generated
                         by both log_duration = on and log_statement = 'all'
--enable-checksum      : used to add a md5 sum under each query report.
--journalctl command   : command to use to replace PostgreSQL logfile by
                         a call to journalctl. Basically it might be:
                            journalctl -u postgresql-9.5
--pid-dir path         : set the path where the pid file must be stored.
                         Default /tmp
--pid-file file        : set the name of the pid file to manage concurrent
                         execution of pgBadger. Default: pgbadger.pid
--rebuild              : used to rebuild all html reports in incremental
                         output directories where there's binary data files.
--pgbouncer-only       : only show PgBouncer related menu in the header.
--start-monday         : in incremental mode, calendar's weeks start on
                         a sunday. Use this option to start on a monday.
--iso-week-number      : in incremental mode, calendar's weeks start on a
                         monday and respect the ISO 8601 week number, range
                         01 to 53, where week 1 is the first week that has
                         at least 4 days in the new year.
--normalized-only      : only dump all normalized query to out.txt
--log-timezone +/-XX   : Set the number of hours from GMT of the timezone
                         that must be used to adjust date/time read from
                         log file before beeing parsed. Using this option
                         make more difficult log search with a date/time.
--prettify-json        : use it if you want json output to be prettified.
--month-report YYYY-MM : create a cumulative HTML report over the specified
                         month. Requires incremental output directories and
                         the presence of all necessary binary data files
--noexplain            : do not process lines generated by auto_explain.
--command CMD          : command to execute to retrieve log entries on
                         stdin. pgBadger will open a pipe to the command
                         and parse log entries generated by the command.
--no-week              : inform pgbadger to not build weekly reports in
                         incremental mode. Useful if it takes too much time.
--explain-url URL      : use it to override the url of the graphical explain
                         tool. Default: http://explain.depesz.com/?is_public=0&is_anon=0&plan=
--tempdir DIR          : set directory where temporary files will be written
                         Default: File::Spec->tmpdir() || '/tmp'
--no-process-info      : disable changing process title to help identify
                         pgbadger process, some system do not support it.
--dump-all-queries     : dump all queries found in the log file replacing
                         bind parameters are included in the queries at
                         their respective placeholders position.
--keep-comments        : do not remove comments from normalized queries. It
                         can be useful if you want to distinguish between
                         same normalized queries.
pgBadger is able to parse a remote log file using a passwordless ssh connection. Use the -r or --remote-host to set the host ip address or hostname. There's also some additional options to fully control the ssh connection.

--ssh-program ssh        path to the ssh program to use. Default: ssh.
--ssh-port port          ssh port to use for the connection. Default: 22.
--ssh-user username      connection login name. Default to running user.
--ssh-identity file      path to the identity file to use.
--ssh-timeout second     timeout to ssh connection failure. Default 10 secs.
--ssh-option  options    list of -o options to use for the ssh connection.
                         Options always used:
                             -o ConnectTimeout=$ssh_timeout
                             -o PreferredAuthentications=hostbased,publickey
Log file to parse can also be specified using an URI, supported protocol are http[s] and [s]ftp. The curl command will be used to download the file and the file will be parsed during download. The ssh protocol is also supported and will use the ssh command like with the remote host use. See examples bellow.

Examples:

pgbadger /var/log/postgresql.log
pgbadger /var/log/postgres.log.2.gz /var/log/postgres.log.1.gz /var/log/postgres.log
pgbadger /var/log/postgresql/postgresql-2012-05-*
pgbadger --exclude-query="^(COPY|COMMIT)" /var/log/postgresql.log
pgbadger -b "2012-06-25 10:56:11" -e "2012-06-25 10:59:11" /var/log/postgresql.log
cat /var/log/postgres.log | pgbadger -
# Log prefix with stderr log output
pgbadger --prefix '%t [%p]: user=%u,db=%d,client=%h' /pglog/postgresql-2012-08-21*
pgbadger --prefix '%m %u@%d %p %r %a : ' /pglog/postgresql.log
# Log line prefix with syslog log output
pgbadger --prefix 'user=%u,db=%d,client=%h,appname=%a' /pglog/postgresql-2012-08-21*
# Use my 8 CPUs to parse my 10GB file faster, much faster
pgbadger -j 8 /pglog/postgresql-10.1-main.log
Use URI notation for remote log file:

pgbadger http://172.12.110.1//var/log/postgresql/postgresql-10.1-main.log
pgbadger ftp://username@172.12.110.14/postgresql-10.1-main.log
pgbadger ssh://username@172.12.110.14:2222//var/log/postgresql/postgresql-10.1-main.log*
You can use together a local PostgreSQL log and a remote pgbouncer log file to parse:

pgbadger /var/log/postgresql/postgresql-10.1-main.log ssh://username@172.12.110.14/pgbouncer.log
Generate Tsung sessions XML file with select queries only:

pgbadger -S -o sessions.tsung --prefix '%t [%p]: user=%u,db=%d ' /pglog/postgresql-10.1.log
Reporting errors every week by cron job:

30 23 * * 1 /usr/bin/pgbadger -q -w /var/log/postgresql.log -o /var/reports/pg_errors.html
Generate report every week using incremental behavior:

0 4 * * 1 /usr/bin/pgbadger -q `find /var/log/ -mtime -7 -name "postgresql.log*"` -o /var/reports/pg_errors-`date +\%F`.html -l /var/reports/pgbadger_incremental_file.dat
This supposes that your log file and HTML report are also rotated every week.

Or better, use the auto-generated incremental reports:

0 4 * * * /usr/bin/pgbadger -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/
will generate a report per day and per week.

In incremental mode, you can also specify the number of week to keep in the reports:

/usr/bin/pgbadger --retention 2 -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/
If you have a pg_dump at 23:00 and 13:00 each day during half an hour, you can use pgBadger as follow to exclude these period from the report:

pgbadger --exclude-time "2013-09-.* (23|13):.*" postgresql.log
This will help avoid having COPY statements, as generated by pg_dump, on top of the list of slowest queries. You can also use --exclude-appname "pg_dump" to solve this problem in a simpler way.

You can also parse journalctl output just as if it was a log file:

pgbadger --journalctl 'journalctl -u postgresql-9.5'
or worst, call it from a remote host:

pgbadger -r 192.168.1.159 --journalctl 'journalctl -u postgresql-9.5'
you don't need to specify any log file at command line, but if you have other PostgreSQL log file to parse, you can add them as usual.

To rebuild all incremental html reports after, proceed as follow:

rm /path/to/reports/*.js
rm /path/to/reports/*.css
pgbadger -X -I -O /path/to/reports/ --rebuild
it will also update all resource files (JS and CSS). Use -E or --explode if the reports were built using this option.

pgBadger also support Heroku PostgreSQL logs using logplex format:

heroku logs -p postgres | pgbadger -f logplex -o heroku.html -
this will stream Heroku PostgreSQL log to pgbadger through stdin.

pgBadger can auto detect RDS and cloudwatch PostgreSQL logs using rds format:

pgbadger -f rds -o rds_out.html rds.log
CloudSQL Postgresql logs it's fairly normal PostgreSQL log but encapsulated in JSON format. It is auto detected too by pgBagder but in case you need to force the log format, use `jsonlog`

pgbadger -f jsonlog -o cloudsql_out.html cloudsql.log
This is the same than with the jsonlog extension, the json format is different but pgBadger can parse both format.

To create a cumulative report over a month use command:

pgbadger --month-report 2919-05 /path/to/incremantal/reports/
this will add a link to the month name into the calendar view in incremental reports to look at report for month 2019 May. Use -E or --explode if the reports were built using this option.
复制

4.3 测试

4.3.1 log日志参数改造

log_min_duration_statement = 0
log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 1
log_autovacuum_min_duration = 1
log_error_verbosity = default

postgres@[local]:5432=#alter system set log_min_duration_statement = 0;
ALTER SYSTEM
postgres@[local]:5432=#alter system set log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h ';
ALTER SYSTEM
postgres@[local]:5432=#alter system set log_checkpoints = on;
ALTER SYSTEM
postgres@[local]:5432=#alter system set log_connections = on;
ALTER SYSTEM
postgres@[local]:5432=#alter system set log_disconnections = on;
ALTER SYSTEM
postgres@[local]:5432=#alter system set log_lock_waits = on;
ALTER SYSTEM
postgres@[local]:5432=#alter system set log_temp_files = 1;
ALTER SYSTEM
postgres@[local]:5432=#alter system set log_autovacuum_min_duration = 1;
ALTER SYSTEM
postgres@[local]:5432=#alter system set log_error_verbosity = default;
ALTER SYSTEM
postgres@[local]:5432=#
postgres@[local]:5432=#\q
[postgres@PGserver2 ~]$ pg_ctl restart
.bash_history            .bashrc                  pgbadger-master/         postgresql-10.18/        .psqlrc
.bash_logout             human_resource/          pgbadger-master.zip      postgresql-10.18.tar.gz  userlist
.bash_profile            logfile                  postgres_dba/            .psql_history
[postgres@PGserver2 ~]$ pg_ctl restart -l /tmp/logfile;
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
复制

4.3.2 使用pgBadger来分析

[postgres@PGserver2 log]$ pgbadger --prefix '%t [%p]: user=%u,db=%d,client=%h' /data/pg10.18/pgdata/log/postgresql-* -o 1.html
LOG: Ok, generating html report... 18228 bytes of 18228 (100.00%), queries: 0, events: 0

[postgres@PGserver2 log]$

复制

4.3.3 查看网页

具体说明查看官方网站https://pgbadger.darold.net/