PostgreSQL日志分析工具 PostgreSQL日志分析工具

plugs=`echo -e "Text-CSV_XS-1.39
pgbadger-10.3"`;
baseDir=`pwd`; 
for plug in ${plugs}; 
do 
    tar zxf ${plug}.*; 
    cd ${baseDir}/${plug}; 
    perl Makefile.PL && make && make install; 
    cd ${baseDir};
done;

# 指定csv格式日志
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%w.log'
log_file_mode = 0640
log_truncate_on_rotation = off
log_rotation_age = 1d
log_line_prefix = '%t [%r]-[%p]: %l user=%u,db=%d'
log_lock_waits = off
log_checkpoints = off
log_connections = off
log_disconnections = off
log_duration = off
log_min_duration_statement = 0

# 初始化pgbenth依赖表
pgbench -U postgres -d postgres -i -s 5
# 5客户端压力10秒
pgbench -U postgres -d postgres -v -c 5 -T 10

# 开启httpd服务
systemctl start httpd.service
# 创建日志输出目录
mkdir -p /var/www/html/pgbadger/;
# 查找一下pgbadger命令所在目录
which pgbadger
# 指定命令路径 -> 指定日志路径(名称根据log_filename参数来的) -> 指定httpd的服务为输出目录: 增量生成日志
/usr/local/bin/pgbadger -I -q /data/pgdata/pg_log/postgresql-*.csv -O /var/www/html/pgbadger/

# /etc/cron.d目录下创建文件 pgbadger_cron 并添加如下内容
# 凌晨2点补全目录
0 2 * * * root /usr/bin/mkdir -p /var/www/html/pgbadger/
# 凌晨2点10分分析前天统计日志
10 2 * * * root /usr/local/bin/pgbadger -I -q /data/pgdata/pg_log/postgresql-`date -d "now -1 days" "+%w"`.csv -O /var/www/html/pgbadger/


# 重启crond
systemctl restart crond.service