PostgreSQL日志管理

PostgreSQL日志分类：

运行日志：$PGDATA/log 默认未开启，需在配置文件中关掉注释开启
在线重做日志：$PGDATA/pg_wal
事务提交日志：$PGDATA/pg_xact
服务器日志：启动服务时指定日志文件生成的日志

运行日志

# 运行日志默认未开启，修改配置文开启日志
[root@Rocky8-2 ~]# vim /pgsql/data/postgresql.conf 
logging_collector = on                  # Enable capturing of stderr, jsonlog,
                                        # and csvlog into log files. Required
                                        # to be on for csvlogs and jsonlogs.
                                        # (change requires restart)
[root@Rocky8-2 ~]# systemctl restart postgresql.service 
[root@Rocky8-2 ~]# ll $PGDATA/log
total 4
-rw------- 1 postgres postgres 919 Jan 16 14:24 postgresql-2024-01-16_141938.log

# 主要记录数据库的运行状况和监听端口
[root@Rocky8-2 ~]# tail $PGDATA/log/postgresql-2024-01-16_141938.log 
2024-01-16 14:19:38.032 CST [2980] LOG:  starting PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit
2024-01-16 14:19:38.035 CST [2980] LOG:  listening on IPv6 address "::1", port 5432
2024-01-16 14:19:38.035 CST [2980] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2024-01-16 14:19:38.042 CST [2980] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2024-01-16 14:19:38.061 CST [2984] LOG:  database system was shut down at 2024-01-16 14:19:37 CST
2024-01-16 14:19:38.078 CST [2980] LOG:  database system is ready to accept connections
2024-01-16 14:24:38.073 CST [2982] LOG:  checkpoint starting: time
2024-01-16 14:24:38.092 CST [2982] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.004 s, total=0.020 s; sync files=2, longest=0.003 s, average=0.002 s; distance=0 kB, estimate=0 kB

# 开启系统日志后，多了一个logger进程
[root@Rocky8-2 ~]# ps aux |grep postgres
postgres    2980  1.0  0.8 175808 17760 ?        Ss   14:19   0:00 /apps/pgsql/bin/postgres -D /pgsql/data
postgres    2981  0.0  0.1  27684  2856 ?        Ss   14:19   0:00 postgres: logger 
postgres    2982  0.0  0.1 175808  2744 ?        Ss   14:19   0:00 postgres: checkpointer 
postgres    2983  0.0  0.2 175948  4248 ?        Ss   14:19   0:00 postgres: background writer 
postgres    2985  0.0  0.4 175808  8116 ?        Ss   14:19   0:00 postgres: walwriter 
postgres    2986  0.0  0.2 177396  5244 ?        Ss   14:19   0:00 postgres: autovacuum launcher 
postgres    2987  0.0  0.2 177376  5104 ?        Ss   14:19   0:00 postgres: logical replication launcher 
root        2991  0.0  0.0  12144  1200 pts/5    S+   14:19   0:00 grep --color=auto postgres

在线WAL日志

WAL日志是为了保证数据库崩溃后的数据安全，如果系统崩溃，可以“重放”从最后一次检查以来的日志项来恢复数据库的一致性。

WAL日志文件存放在$PGDATA/pg_wal下

[root@Rocky8-2 ~]# ll $PGDATA/pg_wal
total 16384
-rw------- 1 postgres postgres 16777216 Jan 16 14:24 000000010000000000000001
drwx------ 2 postgres postgres        6 Aug  7 14:06 archive_status

WAL日志相关配置

# 日志文件总大小（默认值）
[root@Rocky8-2 ~]# vim /pgsql/data/postgresql.conf 
max_wal_size = 1GB
min_wal_size = 80MB

LSN:Log Sequence Number 用于记录WAL文件当前的位置，是WAL日志的唯一的、全局的标识。

# 查看当前LSN号：
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/18D6FD8
(1 row)

# 查看光前LSN对应的WAL日志文件
postgres=# select pg_walfile_name(pg_current_wal_lsn());
     pg_walfile_name      
--------------------------
 000000010000000000000001
(1 row)

# 查看当前事务ID
postgres=# select txid_current();
 txid_current 
--------------
          749
(1 row)

# 查看当前WAL日志偏移量
postgres=# select pg_walfile_NAME_OFFSET(pg_current_wal_lsn());
       pg_walfile_name_offset       
------------------------------------
 (000000010000000000000001,9269536)
(1 row)

# 切换wal日志（默认文件达到16M时会自动切换wal日志）
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/18D7138
(1 row)

# 按时间顺序显示wal文件
postgres=# select * from pg_ls_waldir() order by modification asc;
           name           |   size   |      modification      
--------------------------+----------+------------------------
 000000010000000000000003 | 16777216 | 2024-01-22 11:39:38+08
 000000010000000000000002 | 16777216 | 2024-01-22 11:41:10+08
(2 rows)

使用命令pg_waldump查看wal日志内容，执行结果中 tx: 后面的数字即事务id,同一个事务的id是相同的

[root@Rocky8-2 ~]# pg_waldump /pgsql/data/pg_wal/000000010000000000000002
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/02000028, prev 0/018D7120, desc: RUNNING_XACTS nextXid 750 latestCompletedXid 749 oldestRunningXid 750
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/02000060, prev 0/02000028, desc: RUNNING_XACTS nextXid 750 latestCompletedXid 749 oldestRunningXid 750
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/02000098, prev 0/02000060, desc: CHECKPOINT_ONLINE redo 0/2000060; tli 1; prev tli 1; fpw true; xid 0:750; oid 16426; multi 1; offset 0; oldest xid 717 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 750; online
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/02000110, prev 0/02000098, desc: RUNNING_XACTS nextXid 750 latestCompletedXid 749 oldestRunningXid 750

创建恢复点（相当于快照，可用于还原）

postgres=# select pg_create_restore_point('test-restore-point');
 pg_create_restore_point 
-------------------------
 0/20001B0
(1 row)

归档wal日志

归档日志记录的是checkpoint前的WAL日志，即数据的历史日志，把pg_wal里面的在线日志备份出来，相当于mysql的二进制日志。为了保证数据高可用性，需要开启归档，当系统故障后可以通过归档的日志文件恢复数据。

# 配置wal归档

# 开启日志级别，建议使用默认replica级别（10版本以上）
wal_level = replica                     # minimal, replica, or logical  日志建议使用默认replica级别（10版本以上）
                                        # (change requires restart)
# 开启归档，默认值为off                                        
archive_mode = on               		# enables archiving; off, on, or always
                                		# (change requires restart)
                                		
# 配置archive_command,可以是一个命令，此命令把日志文档拷贝到其它地方
archive_command = ''
# archive_command = ''           # command to use to archive a logfile segment
                                 # placeholders: %p = path of file to archive  
                                 # %p 表示包含完整路径信息的文件名
                                 #               %f = file name only
                                 # %f 表示不包含路径信息的文件名
                                 # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

archive_command 配置示例：

1、本地归档

[root@Rocky8-2 ~]# mkdir /archive
[root@Rocky8-2 ~]# chown -R postgres. /archive
[root@Rocky8-2 ~]# ll -d /archive
drwxr-xr-x 2 postgres postgres 6 Jan 22 13:32 /archive

[root@Rocky8-2 ~]# vim /pgsql/data/postgresql.conf 
archive_mode = on
archive_command = 'DIR=/archive/`date +%F`; [ -d $DIR ] || mkdir -p $DIR; cp %p $DIR/%f'

[root@Rocky8-2 ~]# systemctl restart postgresql.service 
# 插入数据
postgres=#  \c testdb1
testdb1=# create table t1(id int);
testdb1=# insert into t1 values(generate_series(1,100000));
testdb1=# select ctid,* from t1;

# 触发检查点，切换日志，自动触发归档
testdb1=# checkpoint;
CHECKPOINT
testdb1=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/92046D0
(1 row)

# 检查归档文件
[root@Rocky8-2 ~]# tree /archive/
/archive/
└── 2024-01-22
    └── 00000001000000000000000B

1 directory, 1 file

2、远程归档（在10.0.0.13保存归档）

# 创建归档保存目录，并授权postgres
[root@Rocky8-3 ~]# mkdir -p /pgsql/backup
[root@Rocky8-3 ~]# chown postgres. /pgsql/backup/
[root@Rocky8-3 ~]# ll -d /pgsql/backup/
drwxr-xr-x 2 postgres postgres 6 Jan 22 14:40 /pgsql/backup/

[postgres@postgres ~]# ssh-keygen 
[postgres@postgres ~]# ssh-copy-id postgres@10.0.0.13

# 修改配置
[root@postgres ~]# vim /pgsql/data/postgresql.conf 
archive_mode = on  
archive_command = 'scp %p 10.0.0.13:/pgspl/backup/%f'

[root@postgres ~]# systemctl restart postgresql.service

PostgreSQL

#Backup #PostgreSQL #pg_dump

PostgreSQL日志管理

https://www.xcjyc.top/2024/01/16/PostgreSQL日志管理/

作者

XCJYC

发布于

2024年1月16日

许可协议

Nginx配置文件nginx.conf 上一篇