SRE文档# 启动日志收集， 这是一个后台进程，抓取发送到stderr的日志消息，并会将他们重定向到日志文件。
logging_collector = on
# 日志输出路径，可以是自定义绝对路径或相对于数据目录 PGDATA 的相对路径
log_directory = 'log'
# 文件名，可以带上格式字符串
log_filename = 'postgresql-%a.log'
# 当生成新的文件时，文件名已存在，则覆盖同名旧文件名
log_truncate_on_rotation = on
#设置日志记录内容，log_statement：none, ddl, mod, and all 默认是 none
# None表示不记录，ddl记录所有数据定义命令，比如CREATE,ALTER,和DROP语句，
# mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE等
# all记录所有执行的语句，将此配置设置为all可跟踪整个数据库执行的SQL语句，但会对数据库性能产生较大影响，生产环境不建议配置此值
log_statement = mod
# 以上设置完成之后，可以记录到 update、drop 等，但是并不会记录 select 语句，加上 log_min_duration_statement = 0 才可以记录到select
log_min_duration_statement=0# 启动日志收集， 这是一个后台进程，抓取发送到stderr的日志消息，并会将他们重定向到日志文件。
logging_collector = on
# 日志输出路径，可以是自定义绝对路径或相对于数据目录 PGDATA 的相对路径
log_directory = 'log'
# 文件名，可以带上格式字符串
log_filename = 'postgresql-%a.log'
# 当生成新的文件时，文件名已存在，则覆盖同名旧文件名
log_truncate_on_rotation = on
#设置日志记录内容，log_statement：none, ddl, mod, and all 默认是 none
# None表示不记录，ddl记录所有数据定义命令，比如CREATE,ALTER,和DROP语句，
# mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE等
# all记录所有执行的语句，将此配置设置为all可跟踪整个数据库执行的SQL语句，但会对数据库性能产生较大影响，生产环境不建议配置此值
log_statement = mod
# 以上设置完成之后，可以记录到 update、drop 等，但是并不会记录 select 语句，加上 log_min_duration_statement = 0 才可以记录到select
log_min_duration_statement=0有关日志配置
| 参数 | 描述 |
|---|---|
| logging_collector = on/off | 是否将日志重定向至文件中，默认是off（修改后需重启DB） |
| log_directory='pg_log' | 日志文件目录，默认是${PGDATA}的相对路径，即${PGDATA}/pg_log，也可以改为绝对路径 |
| log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' | 日志文件命名形式，使用默认即可 |
| log_rotation_age = 1d | 单个日志文件的生存期，默认1天，在日志文件大小没有达到log_rotation_size时，一天只生成一个日志文件 |
| log_rotation_size = 10MB | 单个日志文件的大小，如果超过将新生成一个日志文件 |
| log_truncate_on_rotation = off | 当日志文件已存在时，该配置如果为off，新生成的日志将在文件尾部追加，如果为on，则会覆盖原来的日志 |
| log_lock_waits = off | 控制当一个会话等待时间超过deadlock_timeout而被锁时是否产生一个日志信息。在判断一个锁等待是否会影响性能时是有用的，缺省是off |
| log_statement = 'none' | # none, ddl, mod, all 控制记录哪些SQL语句。none不记录，ddl记录所有数据定义命令，比如CREATE,ALTER,和DROP 语句。mod记录所有ddl语句,加上数据修改语句INSERT,UPDATE等,all记录所有执行的语句，将此配置设置为all可跟踪整个数据库执行的SQL语句 |
| log_duration = off | 记录每条SQL语句执行完成消耗的时间，将此配置设置为on,用于统计哪些SQL语句耗时较长 |
| log_min_duration_statement = -1 | # -1 is disabled, 0 logs all statements and their durations, > 0 logs only statements running at least this number of milliseconds -1表示不可用，0将记录所有SQL语句和它们的耗时，>0只记录那些耗时超过（或等于）这个值（ms）的SQL语句。使用log_statement和log_duration也能够统计SQL语句及耗时，log_min_duration_statement会将SQL语句和耗时在同一行记录，更方便阅读 |
| log_connections = off | 是否记录连接日志 |
| log_disconnections = off | 是否记录连接断开日志 |
| log_line_prefix = '%m %p %u %d %r ' | 日志输出格式（%m,%p实际意义配置文件中有解释）,可根据自己需要设置（能够记录时间，用户名称，数据库名称，客户端IP和端口，方便定位问题） |
| log_timezone = 'Asia/Shanghai' | 日志时区，最好和服务器设置同一个时区，方便问题定位 |
| log_lock_waits = on | 记录sql语句长时间锁，off关闭（控制当一个会话等待时间超过deadlock_timeout而被锁时是否产生一个） |
| log_error_verbosity | 默认为default，verbose表示冗长的 |
| log_connections | 用户session登陆时是否写入日志，默认off |
| log_disconnections | 用户session退出时是否写入日志，默认off |
# %a = application name # （能够记录时间，用户名称，数据库名称，客户端IP和端口，sql语句方便定位问题）
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %n = timestamp with milliseconds (as a Unix epoch)
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'# %a = application name # （能够记录时间，用户名称，数据库名称，客户端IP和端口，sql语句方便定位问题）
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %n = timestamp with milliseconds (as a Unix epoch)
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'记录用户登陆数据库后的各种操作，postgres日志里分成了3类，通过参数pg_statement来控制，默认的pg_statement参数值是none，即不记录，可以设置ddl(记录create,drop和alter)、mod(记录ddl+insert,delete,update和truncate)和all(mod+select)
开启 PostgreSQL 日志
1.配置文件
一般的错误跟踪，只需在配置文件 vipostgresql.conf 简单设置几个参数，当然还有错误级别等要设置
logging_collector = on
log_destination = 'stderr'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1440 #minute,多长时间创建新的文件记录日志。0 表示禁扩展。
log_rotation_size = 10240 #kb,文件多大后创建新的文件记录日志。0 表示禁扩展。
log_truncate_on_rotation = on #可重用同名日志文件
#慢语句
log_statement = all #需设置跟踪所有语句，否则只能跟踪出错信息，有4种类型：none(默认), ddl, mod, all。跟踪所有语句时可设置为 "all"
log_min_duration_statement = 5000 #milliseconds,记录执行5秒及以上的语句
当 log_statement=all 和 log_min_duration_statement 同时设置时，将跟踪所有语句，忽略log_min_duration_statement 设置。所以需按情况设置其中一个或两个值？？？logging_collector = on
log_destination = 'stderr'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1440 #minute,多长时间创建新的文件记录日志。0 表示禁扩展。
log_rotation_size = 10240 #kb,文件多大后创建新的文件记录日志。0 表示禁扩展。
log_truncate_on_rotation = on #可重用同名日志文件
#慢语句
log_statement = all #需设置跟踪所有语句，否则只能跟踪出错信息，有4种类型：none(默认), ddl, mod, all。跟踪所有语句时可设置为 "all"
log_min_duration_statement = 5000 #milliseconds,记录执行5秒及以上的语句
当 log_statement=all 和 log_min_duration_statement 同时设置时，将跟踪所有语句，忽略log_min_duration_statement 设置。所以需按情况设置其中一个或两个值？？？2.查询设置状态
sql
#以管理员查查
SELECT name,setting,vartype,boot_val,reset_val
FROM pg_settings
WHERE name IN ('logging_collector','log_destination','log_directory','log_filename');
name | setting | vartype | boot_val | reset_val
-------------------+----------------------------+---------+--------------------------------+----------------------------
log_destination | csvlog | string | stderr | csvlog
log_directory | log | string | log | log
log_filename | postgresql-%Y-%m-%d_%H%M%S | string | postgresql-%Y-%m-%d_%H%M%S.log | postgresql-%Y-%m-%d_%H%M%S
logging_collector | on | bool | off | on
(4 rows)#以管理员查查
SELECT name,setting,vartype,boot_val,reset_val
FROM pg_settings
WHERE name IN ('logging_collector','log_destination','log_directory','log_filename');
name | setting | vartype | boot_val | reset_val
-------------------+----------------------------+---------+--------------------------------+----------------------------
log_destination | csvlog | string | stderr | csvlog
log_directory | log | string | log | log
log_filename | postgresql-%Y-%m-%d_%H%M%S | string | postgresql-%Y-%m-%d_%H%M%S.log | postgresql-%Y-%m-%d_%H%M%S
logging_collector | on | bool | off | on
(4 rows)3.完整配置
vi /data/pgdata/data/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 100
superuser_reserved_connections = 10
full_page_writes = on
wal_log_hints = off
max_wal_senders = 50
hot_standby = on
unix_socket_directories = '/tmp'
shared_buffers = 512MB
temp_buffers = 16MB
work_mem = 3MB
effective_cache_size = 2GB
maintenance_work_mem = 128MB
#max_stack_depth = 2MB
dynamic_shared_memory_type = posix
## PITR full_page_writes = on
wal_keep_segments = 2048
wal_buffers = 16MB
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /data/pgdata/archivedir/%f && cp %p /data/pgdata/archivedir/%f'
archive_timeout = 60s
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d-%w.log'
log_rotation_age = 1d
log_statement = ddl
log_rotation_size = 10MB
log_timezone = 'PRC'
timezone = 'PRC'
# 记录执行慢的SQL
log_min_duration_statement = 3s
log_checkpoints = on
log_lock_waits = on
deadlock_timeout = 1s
log_connections = off
log_disconnections = off
log_duration = on
log_line_prefix = '%m %p %u %d %r %e'
log_truncate_on_rotation = on
log_error_verbosity = verbosevi /data/pgdata/data/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 100
superuser_reserved_connections = 10
full_page_writes = on
wal_log_hints = off
max_wal_senders = 50
hot_standby = on
unix_socket_directories = '/tmp'
shared_buffers = 512MB
temp_buffers = 16MB
work_mem = 3MB
effective_cache_size = 2GB
maintenance_work_mem = 128MB
#max_stack_depth = 2MB
dynamic_shared_memory_type = posix
## PITR full_page_writes = on
wal_keep_segments = 2048
wal_buffers = 16MB
wal_writer_delay = 200ms
commit_delay = 0
commit_siblings = 5
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /data/pgdata/archivedir/%f && cp %p /data/pgdata/archivedir/%f'
archive_timeout = 60s
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d-%w.log'
log_rotation_age = 1d
log_statement = ddl
log_rotation_size = 10MB
log_timezone = 'PRC'
timezone = 'PRC'
# 记录执行慢的SQL
log_min_duration_statement = 3s
log_checkpoints = on
log_lock_waits = on
deadlock_timeout = 1s
log_connections = off
log_disconnections = off
log_duration = on
log_line_prefix = '%m %p %u %d %r %e'
log_truncate_on_rotation = on
log_error_verbosity = verbose- 过滤慢日志
bash
注意 “duration: 2113.625 ms”
cat postgresql-2014-06-17_215* |grep "duration: [0-9].* ms"| awk -F 'statement:| ms|duration: ' '{b=2000;if($2-b>0) print $0}'注意 “duration: 2113.625 ms”
cat postgresql-2014-06-17_215* |grep "duration: [0-9].* ms"| awk -F 'statement:| ms|duration: ' '{b=2000;if($2-b>0) print $0}'4.保留日志时间
为了让日志文件自动覆盖，达到保留多少日志的目的，可以进行如下设置：
log_truncate_on_rotation = on
log_filename = 'postgresql-%I.log' #最多保存12小时的日志,每小时一个文件
log_filename = 'postgresql-%H.log' #最多保存24小时的日志,每小时一个文件
log_filename = 'postgresql-%w.log' #最多保存一周的日志,每天一个文件
log_filename = 'postgresql-%d.log' #最多保存一个月的日志,每天一个文件
log_filename = 'postgresql-%j.log' #最多保存一年的日志,每天一个文件log_truncate_on_rotation = on
log_filename = 'postgresql-%I.log' #最多保存12小时的日志,每小时一个文件
log_filename = 'postgresql-%H.log' #最多保存24小时的日志,每小时一个文件
log_filename = 'postgresql-%w.log' #最多保存一周的日志,每天一个文件
log_filename = 'postgresql-%d.log' #最多保存一个月的日志,每天一个文件
log_filename = 'postgresql-%j.log' #最多保存一年的日志,每天一个文件二、日志压缩
archive_command 是 postgresql.conf 里的参
restore_command 是 recovery.conf 里的参数
2.1非压缩
archive_command = 'cp %p /data/backup/pgwalarchive/%f && echo %f >> /data/backup/pgwalarchive/archive.list'
restore_command = 'cp /data/backup/pgwalarchive/%f %p'archive_command = 'cp %p /data/backup/pgwalarchive/%f && echo %f >> /data/backup/pgwalarchive/archive.list'
restore_command = 'cp /data/backup/pgwalarchive/%f %p'2.2压缩 gzip
archive_command = 'gzip < %p > /data/backup/pgwalarchive/%f.gz'
restore_command = 'gunzip < /data/backup/pgwalarchive/%f.gz > %p'archive_command = 'gzip < %p > /data/backup/pgwalarchive/%f.gz'
restore_command = 'gunzip < /data/backup/pgwalarchive/%f.gz > %p'2.3压缩 bzip2
主要用户二进制和大文件。经测试，压缩率比 gzip 要好很多
archive_command = 'bzip2 < %p > /data/backup/pgwalarchive/%f.bz2'
restore_command = 'bunzip2 < /data/backup/pgwalarchive/%f.bz2 > %p'archive_command = 'bzip2 < %p > /data/backup/pgwalarchive/%f.bz2'
restore_command = 'bunzip2 < /data/backup/pgwalarchive/%f.bz2 > %p'2.4压缩 lz4
archive_command = 'lz4 -f -q -z %p /data/backup/pgwalarchive/%f.lz4'
restore_command = 'lz4 -f -q -d /data/backup/pgwalarchive/%f.lz4 %p'archive_command = 'lz4 -f -q -z %p /data/backup/pgwalarchive/%f.lz4'
restore_command = 'lz4 -f -q -d /data/backup/pgwalarchive/%f.lz4 %p'三、pgsql共三类日志文件
pg12.2
pg_xlog ---- pg_wal （WAL 日志，即重做日志） 强制开启
pg_clog ---- pg_xact （事务提交日志，记录的是事务的元数据） 内容一般不具有可读性 强制开启
pg_log ---- log （数据库运行日志） 内容可读 默认关闭的，需要设置参数启动
3.1pg_wal（write ahead log）
由文件名可以知道为重做日志，类似mysql和oralce中的undo log，二进制文件，一般WAL机制的引入，可以保证事物的持久性和数据的完整性。同时也解决了写入磁盘的耗时操作
具体步骤：
数据更新或者插入：先写入WAL BUFFER中，再将更新或插入后的数据写入DATA BUFFER中
数据提交后：WAL BUFFER中的内容写入磁盘，DATA BUFFER写入磁盘推迟
CHECKPOINT发生时：将所有DATA BUFFER数据写到磁盘
3.2pg_xact
这个目录下的文件也是事务日志文件，但与pg_wal不同的是它记录的是事务的元数据（metadata），这个日志文件主要功能为记录事务的状态，这个日志文件一般非常小，但很重要，不得随意删除修改,手动直接rm删除导致不能启动数据库
3.3pg_log
pg_log记录各种运行、Error信息，以及服务器和DB的状态信息，定位慢查询SQL，数据库的启动关闭信息类似mysql的catalog和oracle的alter日志。当数据库出现问题时，应该首先查看这个日志文件
该日志有.csv格式和.log。个人建议用前一种，因为一般会按大小和时间自动切割，毕竟查看一个巨大的日志文件比查看不同时间段的多个日志要难得多。另外这种日志是可以被清理删除，压缩打包或者转移，同时并不影响DB的正常运行