【PgSQL日常管理】 日志配置wal、pg_log

日志有三种：

pg_xlog目录pg_wal  在线重做日志

pg_clog目录pg_xact  事务日志文件，记录哪些事务已完成

pg_log 数据库日志， 记录host,db,慢SQL信息

归档或者流复制发生异常的时候，事务日志会不断生成，有可能会造成磁盘空间被塞满，最终导致DB挂掉或者起不来。

遇到这种情况，可以先关闭归档或者流复制功能，备份pg_xlog日志到其他地方，不要删除。然后删除较早时间的的pg_xlog，有一定空间后再试着启动Postgres。 

 

postgresql.log只保留一周的日志，进行循环覆盖

logging_collector = on#启动日志

log_destination = 'csvlog'#日志格式，值为stderr,csvlog,syslog,and eventlog之一

log_filename = ‘postgresql-%a.log’
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0

wal日志增长量过大的问题处理，已经配置优化

$ du -sh pg_wal
395G pg_wal

$ pg_controldata
pg_control version number: 1201
Catalog version number: 201909212
Database system identifier: 6831009729171842643
Database cluster state: in production
pg_control last modified: Mon 19 Apr 2021 02:19:49 PM CST
Latest checkpoint location: 341/A0000060
Latest checkpoint's REDO location: 341/A0000028
Latest checkpoint's REDO WAL file: 0000000100000341000000A0

检查点 341/A0000028已经执行，对应wal日志文件为0000000100000341000000A0，在这之前的日志可清理

1、mv清理到其他盘  2、pg_archivecleanup

例如：

$ ll /data/pgsql_5432/data/pg_wal/ |grep -C 10 0000000100000341000000A0
-rw------- 1 postgres postgres 16777216 Apr 19 14:11 000000010000034100000096
-rw------- 1 postgres postgres 16777216 Apr 19 14:12 000000010000034100000097
-rw------- 1 postgres postgres 16777216 Apr 19 14:13 000000010000034100000098
-rw------- 1 postgres postgres 16777216 Apr 19 14:14 000000010000034100000099
-rw------- 1 postgres postgres 16777216 Apr 19 14:15 00000001000003410000009A
-rw------- 1 postgres postgres 16777216 Apr 19 14:16 00000001000003410000009B
-rw------- 1 postgres postgres 16777216 Apr 19 14:17 00000001000003410000009C
-rw------- 1 postgres postgres 16777216 Apr 19 14:18 00000001000003410000009D
-rw------- 1 postgres postgres 16777216 Apr 19 14:19 00000001000003410000009E
-rw------- 1 postgres postgres 16777216 Apr 19 14:19 00000001000003410000009F
-rw------- 1 postgres postgres 16777216 Apr 19 14:20 0000000100000341000000A0
-rw------- 1 postgres postgres 353 Apr 19 14:56 0000000100000341000000A0.00000028.backup
-rw------- 1 postgres postgres 16777216 Apr 19 14:21 0000000100000341000000A1
-rw------- 1 postgres postgres 16777216 Apr 19 14:22 0000000100000341000000A2
-rw------- 1 postgres postgres 16777216 Apr 19 14:23 0000000100000341000000A3
-rw------- 1 postgres postgres 16777216 Apr 19 14:24 0000000100000341000000A4
-rw------- 1 postgres postgres 16777216 Apr 19 14:25 0000000100000341000000A5
-rw------- 1 postgres postgres 16777216 Apr 19 14:26 0000000100000341000000A6
-rw------- 1 postgres postgres 16777216 Apr 19 14:27 0000000100000341000000A7
-rw------- 1 postgres postgres 16777216 Apr 19 14:28 0000000100000341000000A8
-rw------- 1 postgres postgres 16777216 Apr 19 14:29 0000000100000341000000A9
-rw------- 1 postgres postgres 16777216 Apr 19 14:30 0000000100000341000000AA

0000000100000341000000A0之前的日志，可以进行清理

 pg_archivecleanup 0000000100000341000000A0

wal优化配置

shared_buffers = 32GB
checkpoint_completion_target = 0.1
checkpoint_timeout = 60min
min_wal_size = 4GB
max_wal_size = 64GB
wal_log_hints = on
wal_level = replica
wal_keep_segments = 1000

并开启wal压缩，

wal_compression = on

参考阅读:
http://www.postgres.cn/index.php/news/viewone/1/273