postgresq的日志_postgresql 查看操作日志

postgresql日志类型：

• pg_log --> log:数据库运行日志，如各种error信息，慢查询，服务器与DB的状态信息等，默认关闭，可以通过参数自定义存储位置，格式等
• pg_xlog --> pg_wal:WAL日志，即重做日志，默认大小16MB，强制打开
• pg_clog --> pg_xact:事务提交日志，记录事务的元数据，强制打开

ps：在10.0版本开始，pg_xlog目录被重新命名为pg_wal，pg_clog目录被重命名为pg_xact,pg_log目录被重命名为log

1.log

1）pg_log的开启，修改postgresql.conf相关配置

##开启日志记录
logging_collector = on

##stderr,csvlog,syslog,and eventlog，默认stderr
log_destination = 'stderr'

##日志保存路径
log_directory = '/data/pg_data/log/'

##日志文件格式
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

##日志文件权限
log_file_mode = 0600

##是否开启通过日志覆盖模式
log_truncate_on_rotation = off

##日志输出格式
log_line_prefix = '%t:%r:%u@%d:[%p]:'

##none, ddl, mod, all，需要记录的类型
log_statement = 'ddl'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2.pg_wal

1）WAL（Write-Ahead Logging）

• WAL日志机制保证了事务的持久性和数据完整性，同时又避免了频繁IO对性能的影响
• 从pg9.6版本开始，WAL日志使用动态切换的模式，即单个wal日志写满后才继续写下一个wal日志，直到磁盘剩余空间不足min_wal_size时才会将旧的wal文件回收，以便继续使用
• 作用redo日志

2）WAL日志命名规则

例如：000000010000000000000003

• 24个16进制数字组成
• 前8位：00000001表示timeline（时间线）
• 中间8位：00000000表示logid（LSN高32位）
• 后8位：00000003表示logseg（LSN低32位）

3）WAL日志LSN（log sequence number）编号规则

• 日志序列号，WAL日志唯一的，全局的标识
• 记录WAL日志的写入顺序

-- 查看当前的lsn号
select pg_current_wal_lsn();
0/3E4E6A0

-- 查看的当前lsn的wal日志文件名
select pg_walfile_name(pg_current_wal_lsn());
000000030000000000000003

-- 查看当前lsn的偏移量
select pg_walfile_name_offset(pg_current_wal_lsn());
(000000030000000000000003,15001248)

-- 16进制转换数值
select x'E4E6A0'::int
15001248

-- 手动刷新wal
select pg_switch_wal();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4）WAL流程

a.当数据库中的数据发生变更时：

• change发生时：先要将变更后内容计入wal buffer中，再将变更后的数据写入data buffer
• commit发生时：wal buffer 中数据刷新到磁盘
• checkpoint发送时：将所有data buffer刷新到磁盘

b.触发checkpoint触发场景：

• 手动执行checkpoint命令
• 执行需要检查点的命令，如pg_start_backup,pg_ctl stop|restart等
• 达到检查点配置时间
• max_wal_size已满

c.checkpoit相关配置

-- 查看checkpoint_timeout配置，默认5min
show checkpoint_timeout;

-- 查看max_wal_size配置，默认1GB
show max_wal_size;

-- checkpoint_completion_target:指定检查点完成的目标。默认0.5
-- 可以降低checkpoint对性能的影，但是数值过大时在数据库出现故障时候，会比较危险
show checkpoint_completion_target;
1
2
3
4
5
6
7
8
9

5）当前WAL的信息获取
pg_controldata -D /data/pg_data/data

pg_control version number:            1100
Catalog version number:               201809051
Database system identifier:           7018374463638270579
Database cluster state:               in production
pg_control last modified:             Mon 13 Dec 2021 03:16:58 PM CST
Latest checkpoint location:           0/3E4E5F8
Latest checkpoint's REDO location:    0/3E4E5C0
Latest checkpoint's REDO WAL file:    000000030000000000000003
Latest checkpoint's TimeLineID:       3
Latest checkpoint's PrevTimeLineID:   3
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:710
Latest checkpoint's NextOID:          41209
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        561
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  710
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Mon 13 Dec 2021 03:16:58 PM CST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                on
max_connections setting:              100
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            6aa093cf542692ca29bda2051fdfce33f70da31a756e0e6939746a14bb62cb57
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

6）WAL日志解析工具使用

pg_waldump的使用

• 10.x版本之后，使用pg_waldump解析WAL日志
• 10.x版本之前，使用pg_xlogdump解析WAL日志

pg_waldump使用语法：

pg_waldump [option] … [startseg [endseg]]

常用参数：

pg_waldump /data/pg_data/data/pg_wal/000000010000000000000003

rmgr: Btree       len (rec/tot):     72/    72, tx:        673, lsn: 0/0301A5D0, prev 0/0301A520, desc: INSERT_LEAF off 55, blkref #0: rel 1663/13287/2658 blk 14
rmgr: Btree       len (rec/tot):     64/    64, tx:        673, lsn: 0/0301A618, prev 0/0301A5D0, desc: INSERT_LEAF off 366, blkref #0: rel 1663/13287/2659 blk 9
rmgr: Heap        len (rec/tot):    175/   175, tx:        673, lsn: 0/0301A658, prev 0/0301A618, desc: INSERT off 10, blkref #0: rel 1663/13287/1249 blk 52
rmgr: Btree       len (rec/tot):     72/    72, tx:        673, lsn: 0/0301A708, prev 0/0301A658, desc: INSERT_LEAF off 58, blkref #0: rel 1663/13287/2658 blk 14
rmgr: Btree       len (rec/tot):     64/    64, tx:        673, lsn: 0/0301A750, prev 0/0301A708, desc: INSERT_LEAF off 366, blkref #0: rel 1663/13287/2659 blk 9
rmgr: Heap        len (rec/tot):    175/   175, tx:        673, lsn: 0/0301A790, prev 0/0301A750, desc: INSERT off 11, blkref #0: rel 1663/13287/1249 blk 52
rmgr: Btree       len (rec/tot):     72/    72, tx:        673, lsn: 0/0301A840, prev 0/0301A790, desc: INSERT_LEAF off 55, blkref #0: rel 1663/13287/2658 blk 14
1
2
3
4
5
6
7
8
9