PostgreSQL的Logger进程

PostgreSQL的Logger进程

基础信息
OS版本：Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本：16.2
pg软件目录：/home/pg16/soft
pg数据目录：/home/pg16/data
端口：5777

在PostgreSQL中，Logger进程通常指的是日志收集器（logging collector）。这是一个后台进程，负责捕获发送到标准错误流（stderr）的日志消息，并将它们重定向到日志文件中。通过启用日志收集器，PostgreSQL可以将日志输出到文件，而不是默认的标准错误流，这有助于更好地管理和分析日志信息。

要启用日志收集器，可以在postgresql.conf文件中设置logging_collector参数为on。例如：

logging_collector = on

启用后，可以通过查询log_destination参数来确认日志输出的目的地，默认情况下，日志会输出到stderr，csvlog和jsonlog需要打开logging_collector。。例如：

postgres=# SHOW log_destination;
 log_destination 
-----------------
 stderr
(1 row)

postgres=# show log_directory;
 log_directory 
---------------
 log
(1 row)

log 表示在$PG_DATA/log 目录下

示例1：设置为stderr

日志输出：

2024-08-07 14:59:02.690 PDT [8211] LOG:  starting PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-08-07 14:59:02.690 PDT [8211] LOG:  listening on IPv4 address "0.0.0.0", port 5777
2024-08-07 14:59:02.690 PDT [8211] LOG:  listening on IPv6 address "::", port 5777
2024-08-07 14:59:02.697 PDT [8211] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5777"
2024-08-07 14:59:02.700 PDT [8215] LOG:  database system was shut down at 2024-08-07 14:59:02 PDT
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  checkpoint record is at 0/7E000028
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  redo record is at 0/7E000028; shutdown true
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  next transaction ID: 268808; next OID: 17037
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  next MultiXactId: 1; next MultiXactOffset: 0
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  oldest unfrozen transaction ID: 722, in database 4
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  oldest MultiXactId: 1, in database 16535
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  commit timestamp Xid oldest/newest: 0/0
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  transaction ID wrap limit is 2147484369, limited by database with OID 4
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 16535
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  starting up replication slots
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  xmin required by slots: data 0, catalog 0
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  MultiXactId wrap limit is 2147483648, limited by database with OID 16535
2024-08-07 14:59:02.700 PDT [8215] DEBUG:  MultiXact member stop limit is now 4294914944 based on MultiXact 1
2024-08-07 14:59:02.702 PDT [8211] DEBUG:  starting background worker process "logical replication launcher"
2024-08-07 14:59:02.702 PDT [8217] DEBUG:  autovacuum launcher started
2024-08-07 14:59:02.702 PDT [8211] LOG:  database system is ready to accept connections
2024-08-07 14:59:02.703 PDT [8219] DEBUG:  logical replication launcher started

示例2：设置为stderr,csvlog

日志输出：

2024-08-07 14:53:14.996 PDT,,,7755,,66b3ecca.1e4b,10,,2024-08-07 14:53:14 PDT,,0,DEBUG,00000,"MultiXactId wrap limit is 2147483648, limited by database with OID 16535",,,,,,,,,"","startup",,0
2024-08-07 14:53:14.996 PDT,,,7755,,66b3ecca.1e4b,11,,2024-08-07 14:53:14 PDT,,0,DEBUG,00000,"starting up replication slots",,,,,,,,,"","startup",,0
2024-08-07 14:53:14.996 PDT,,,7755,,66b3ecca.1e4b,12,,2024-08-07 14:53:14 PDT,,0,DEBUG,00000,"xmin required by slots: data 0, catalog 0",,,,,,,,,"","startup",,0
2024-08-07 14:53:14.996 PDT,,,7755,,66b3ecca.1e4b,13,,2024-08-07 14:53:14 PDT,,0,DEBUG,00000,"MultiXactId wrap limit is 2147483648, limited by database with OID 16535",,,,,,,,,"","startup",,0
2024-08-07 14:53:14.996 PDT,,,7755,,66b3ecca.1e4b,14,,2024-08-07 14:53:14 PDT,,0,DEBUG,00000,"MultiXact member stop limit is now 4294914944 based on MultiXact 1",,,,,,,,,"","startup",,0
2024-08-07 14:53:14.998 PDT,,,7751,,66b3ecca.1e47,5,,2024-08-07 14:53:14 PDT,,0,DEBUG,00000,"starting background worker process ""logical replication launcher""",,,,,,,,,"","postmaster",,0
2024-08-07 14:53:14.998 PDT,,,7751,,66b3ecca.1e47,6,,2024-08-07 14:53:14 PDT,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,"","postmaster",,0
2024-08-07 14:53:14.998 PDT,,,7757,,66b3ecca.1e4d,1,,2024-08-07 14:53:14 PDT,,0,DEBUG,00000,"autovacuum launcher started",,,,,,,,,"","autovacuum launcher",,0
2024-08-07 14:53:14.998 PDT,,,7759,,66b3ecca.1e4f,1,,2024-08-07 14:53:14 PDT,,0,DEBUG,00000,"logical replication launcher started",,,,,,,,,"","logical replication launcher",,0
2024-08-07 14:54:15.005 PDT,,,7923,,66b3ed07.1ef3,1,,2024-08-07 14:54:15 PDT,3/0,0,DEBUG,00000,"autovacuum: processing database ""postgres""",,,,,,,,,"","autovacuum worker",,0
2024-08-07 14:55:15.048 PDT,,,7983,,66b3ed43.1f2f,1,,2024-08-07 14:55:15 PDT,3/0,0,DEBUG,00000,"autovacuum: processing database ""postgres""",,,,,,,,,"","autovacuum worker",,0
2024-08-07 14:55:53.283 PDT,,,8016,"[local]",66b3ed69.1f50,1,"",2024-08-07 14:55:53 PDT,,0,LOG,00000,"connection received: host=[local]",,,,,,,,,"","not initialized",,0
2024-08-07 14:55:53.289 PDT,"postgres","postgres",8016,"[local]",66b3ed69.1f50,2,"authentication",2024-08-07 14:55:53 PDT,3/5,0,LOG,00000,"connection authorized: user=postgres database=postgres application_name=psql",,,,,,,,,"","client backend",,0
2024-08-07 14:56:15.108 PDT,,,8042,,66b3ed7f.1f6a,1,,2024-08-07 14:56:15 PDT,4/0,0,DEBUG,00000,"autovacuum: processing database ""postgres""",,,,,,,,,"","autovacuum worker",,0

示例3：设置为stderr,jsonlog

日志输出：

{"timestamp":"2024-08-07 15:00:20.888 PDT","pid":8322,"session_id":"66b3ee74.2082","line_num":10,"session_start":"2024-08-07 15:00:20 PDT","txid":0,"error_severity":"DEBUG","message":"MultiXactId wrap limit is 2147483648, limited by database with OID 16535","backend_type":"startup","query_id":0}
{"timestamp":"2024-08-07 15:00:20.888 PDT","pid":8322,"session_id":"66b3ee74.2082","line_num":11,"session_start":"2024-08-07 15:00:20 PDT","txid":0,"error_severity":"DEBUG","message":"starting up replication slots","backend_type":"startup","query_id":0}
{"timestamp":"2024-08-07 15:00:20.888 PDT","pid":8322,"session_id":"66b3ee74.2082","line_num":12,"session_start":"2024-08-07 15:00:20 PDT","txid":0,"error_severity":"DEBUG","message":"xmin required by slots: data 0, catalog 0","backend_type":"startup","query_id":0}
{"timestamp":"2024-08-07 15:00:20.888 PDT","pid":8322,"session_id":"66b3ee74.2082","line_num":13,"session_start":"2024-08-07 15:00:20 PDT","txid":0,"error_severity":"DEBUG","message":"MultiXactId wrap limit is 2147483648, limited by database with OID 16535","backend_type":"startup","query_id":0}
{"timestamp":"2024-08-07 15:00:20.888 PDT","pid":8322,"session_id":"66b3ee74.2082","line_num":14,"session_start":"2024-08-07 15:00:20 PDT","txid":0,"error_severity":"DEBUG","message":"MultiXact member stop limit is now 4294914944 based on MultiXact 1","backend_type":"startup","query_id":0}
{"timestamp":"2024-08-07 15:00:20.890 PDT","pid":8318,"session_id":"66b3ee74.207e","line_num":5,"session_start":"2024-08-07 15:00:20 PDT","txid":0,"error_severity":"DEBUG","message":"starting background worker process \"logical replication launcher\"","backend_type":"postmaster","query_id":0}
{"timestamp":"2024-08-07 15:00:20.890 PDT","pid":8318,"session_id":"66b3ee74.207e","line_num":6,"session_start":"2024-08-07 15:00:20 PDT","txid":0,"error_severity":"LOG","message":"database system is ready to accept connections","backend_type":"postmaster","query_id":0}
{"timestamp":"2024-08-07 15:00:20.890 PDT","pid":8324,"session_id":"66b3ee74.2084","line_num":1,"session_start":"2024-08-07 15:00:20 PDT","txid":0,"error_severity":"DEBUG","message":"autovacuum launcher started","backend_type":"autovacuum launcher","query_id":0}
{"timestamp":"2024-08-07 15:00:20.891 PDT","pid":8326,"session_id":"66b3ee74.2086","line_num":1,"session_start":"2024-08-07 15:00:20 PDT","txid":0,"error_severity":"DEBUG","message":"logical replication launcher started","backend_type":"logical replication launcher","query_id":0}

通过配置日志收集器，可以更好地管理和分析PostgreSQL的日志信息，从而提高数据库的运维效率。