进阶数据库系列（二十四）：PostgreSQL 数据库日志与日常巡检

前面介绍了 PostgreSQL 基于 pgpool 实现读写分离实践、数据库备份与恢复、主从数据目录同步工具 pg_rewind、数据库作业调度工具、性能优化等相关的知识点，今天我将详细的为大家介绍 PostgreSQL 数据库日志与日常巡检相关知识，希望大家能够从中收获多多！如有帮助，请点在看、转发支持一波！！！

PostgreSQL 服务器日志

开启审计日志

编辑 $PGDATA/postgresql.conf文件

vi $PGDATA/postgresql.conf
# 做以下修改，下面2部分未提及的全部备注掉
# - Where to Log -
log_destination = 'csvlog' # 日志输出格式
logging_collector = on # 日志收集器，打开后某些不会出现在审计日志中的日志会被重定向到审计日志
log_directory = 'pg_log' # 相对于 $PGDATA 的相对路径，全路径即为 $PGDATA/pg_log
# 保留近7天的审计日志，轮询替换
log_filename = 'postgresql.%a' #日志名称
log_file_mode = 0600 # 只有postgres有读写权限
log_truncate_on_rotation = on # 覆盖同名日志
log_rotation_size = 0 # 不限制日志大小

log_min_messages = warning # 控制哪些消息级别被写入到审计日志
log_min_error_statement = error # 控制哪些导致一个错误情况的 SQL 语句被记录在服务器日志中
log_min_duration_statement = 0 # 记录所有sql运行时长，可以查慢sql

# - What to Log -
log_duration = on # 导致每一个完成的语句的持续时间被记录
log_lock_waits = on # 等锁超时记录日志，超时时间参数 deadlock_timeout
log_statement = 'mod' # mod记录所有ddl语句，外加数据修改语句例如INSERT, UPDATE、DELETE、TRUNCATE, 和COPY FROM
log_replication_commands = off # 不记录流复制命令
log_timezone = 'Asia/Shanghai' # 时区，查看操作系统时区 timedatectl | grep "Time zone"

重启数据库

pg_ctl restart -mf

更多关于 PostgreSQL 系列的学习文章，请参阅：PostgreSQL 数据库，本系列持续更新中。

sql直接读取日志

安装 file_fdw 插件

create extension file_fdw;

创建外部表

drop foreign table if exists pg_log_mon;
create foreign table pg_log_mon(
     log_time timestamp
    ,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
    ,virtual_transaction_id text
    ,transaction_id bigint
    ,error_severity text
    ,sql_state_code text
    ,message text
    ,detail text
    ,hint text
    ,internal_query text
    ,internal_query_pos integer
    ,context text
    ,query text
    ,query_pos integer
    ,location text
    ,application_name text 
) server pg_file_server options(
     filename '/data/pgdata/pg_log/postgresql.Mon.csv'
    ,format 'csv'
    ,header 'false'
    ,delimiter ','
    ,quote '"'
    ,escape '"'
);
comment on foreign table pg_log_mon is '每周一当天审计日志';
comment on column pg_log_mon.log_time is '日志时间';
comment on column pg_log_mon.user_name is '用户名';
comment on column pg_log_mon.database_name is '数据库名';
comment on column pg_log_mon.process_id is '进程id';
comment on column pg_log_mon.connection_from is '客户端ip:端口';
comment on column pg_log_mon.session_id is '会话id';
comment on column pg_log_mon.session_line_num is '当前会话的第几次查询';
comment on column pg_log_mon.command_tag is '命令类型';
comment on column pg_log_mon.session_start_time is '会话开始时间';
comment on column pg_log_mon.virtual_transaction_id is '虚拟事务id';
comment on column pg_log_mon.transaction_id is '事务id';
comment on column pg_log_mon.error_severity is '错误级别';
comment on column pg_log_mon.sql_state_code is 'sql状态代码';
comment on column pg_log_mon.message is '信息';
comment on column pg_log_mon.detail is '详细信息';
comment on column pg_log_mon.hint is '提示信息';
comment on column pg_log_mon.query is '查询的sql';
comment on column pg_log_mon.application_name is '应用名（客户端名）';

drop foreign table if exists pg_log_tue;
create foreign table pg_log_tue(
     log_time timestamp
    ,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
    ,virtual_transaction_id text
    ,transaction_id bigint
    ,error_severity text
    ,sql_state_code text
    ,message text
    ,detail text
    ,hint text
    ,internal_query text
    ,internal_query_pos integer
    ,context text
    ,query text
    ,query_pos integer
    ,location text
    ,application_name text 
) server pg_file_server options(
     filename '/data/pgdata/pg_log/postgresql.Tue.csv'
    ,format 'csv'
    ,header 'false'
    ,delimiter ','
    ,quote '"'
    ,escape '"'
);
comment on foreign table pg_log_tue is '每周二当天审计日志';
comment on column pg_log_tue.log_time is '日志时间';
comment on column pg_log_tue.user_name is '用户名';
comment on column pg_log_tue.database_name is '数据库名';
comment on column pg_log_tue.process_id is '进程id';
comment on column pg_log_tue.connection_from is '客户端ip:端口';
comment on column pg_log_tue.session_id is '会话id';
comment on column pg_log_tue.session_line_num is '当前会话的第几次查询';
comment on column pg_log_tue.command_tag is '命令类型';
comment on column pg_log_tue.session_start_time is '会话开始时间';
comment on column pg_log_tue.virtual_transaction_id is '虚拟事务id';
comment on column pg_log_tue.transaction_id is '事务id';
comment on column pg_log_tue.error_severity is '错误级别';
comment on column pg_log_tue.sql_state_code is 'sql状态代码';
comment on column pg_log_tue.message is '信息';
comment on column pg_log_tue.detail is '详细信息';
comment on column pg_log_tue.hint is '提示信息';
comment on column pg_log_tue.query is '查询的sql';
comment on column pg_log_tue.application_name is '应用名（客户端名）';

drop foreign table if exists pg_log_wed;
create foreign table pg_log_wed(
     log_time timestamp
    ,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
    ,virtual_transaction_id text
    ,transaction_id bigint
    ,error_severity text
    ,sql_state_code text
    ,message text
    ,detail text
    ,hint text
    ,internal_query text
    ,internal_query_pos integer
    ,context text
    ,query text
    ,query_pos integer
    ,location text
    ,application_name text 
) server pg_file_server options(
     filename '/data/pgdata/pg_log/postgresql.Wed.csv'
    ,format 'csv'
    ,header 'false'
    ,delimiter ','
    ,quote '"'
    ,escape '"'
);
comment on foreign table pg_log_wed is '每周三当天审计日志';
comment on column pg_log_wed.log_time is '日志时间';
comment on column pg_log_wed.user_name is '用户名';
comment on column pg_log_wed.database_name is '数据库名';
comment on column pg_log_wed.process_id is '进程id';
comment on column pg_log_wed.connection_from&nb