前面介绍了 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_l