本篇内容主要讲解“分析PostgreSQL日志相关的配置参数log_XXX”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“分析PostgreSQL日志相关的配置参数log_XXX”吧!

在新initdb的数据库上查询pg_settings，可查询PostgreSQL中与log的参数包括：

[test@localhost~]$psqlExpandeddisplayisusedautomatically.psql(12.1)Type"help"forhelp.[local:/var/run/test]:5000test@testdb=#selectcategory,name,settingfrompg_settingswherenamelike'log%'orderbycategory;category|name|setting--------------------------------------+-----------------------------+--------------------------------ReportingandLogging/WhattoLog|log_lock_waits|offReportingandLogging/WhattoLog|log_checkpoints|offReportingandLogging/WhattoLog|log_connections|offReportingandLogging/WhattoLog|log_timezone|PRCReportingandLogging/WhattoLog|log_temp_files|-1ReportingandLogging/WhattoLog|log_disconnections|offReportingandLogging/WhattoLog|log_duration|offReportingandLogging/WhattoLog|log_error_verbosity|defaultReportingandLogging/WhattoLog|log_statement|noneReportingandLogging/WhattoLog|log_replication_commands|offReportingandLogging/WhattoLog|log_autovacuum_min_duration|-1ReportingandLogging/WhattoLog|log_hostname|offReportingandLogging/WhattoLog|log_line_prefix|%m[%p]ReportingandLogging/WhentoLog|log_min_duration_statement|-1ReportingandLogging/WhentoLog|log_min_error_statement|errorReportingandLogging/WhentoLog|log_min_messages|warningReportingandLogging/WhentoLog|log_transaction_sample_rate|0ReportingandLogging/WheretoLog|log_destination|stderrReportingandLogging/WheretoLog|log_filename|postgresql-%Y-%m-%d_%H%M%S.logReportingandLogging/WheretoLog|logging_collector|offReportingandLogging/WheretoLog|log_truncate_on_rotation|offReportingandLogging/WheretoLog|log_rotation_size|10240ReportingandLogging/WheretoLog|log_file_mode|0600ReportingandLogging/WheretoLog|log_rotation_age|1440ReportingandLogging/WheretoLog|log_directory|logStatistics/Monitoring|log_statement_stats|offStatistics/Monitoring|log_planner_stats|offStatistics/Monitoring|log_executor_stats|offStatistics/Monitoring|log_parser_stats|off(29rows)[local:/var/run/test]:5000test@testdb=#

log_打头的参数有29个，下面从where、when、what这几个维度来解析这些参数，本节是第三部分，介绍what to log。

debug_print_parse
是否打印分析阶段的查询树，默认为off-不打印

debug_print_rewritten
是否打印查询重写阶段的查询树，默认为off-不打印

debug_print_plan
是否打印计划阶段的计划树，默认为off-不打印

debug_pretty_print
是否以优雅的方式打印？默认为on

log_checkpoints
是否记录checkpoint信息，默认为off。
在checkpoint发生时，PG会记录相关的信息

checkpoint_timeout=1min#range30s-1dcheckpoint_completion_target=0.5#checkpointtargetduration,0.0-1.0#checkpoint_flush_after=256kB#measuredinpages,0disables#checkpoint_warning=30s#0disableslog_checkpoints=on

插入数据，在检查点发生时，可以看到检查点的相关信息输出

2019-12-3014:27:03.618CST[2224]LOG:duration:106.374msstatement:insertintotblselectx,'c1'||xfromgenerate_series(1,10000)x;2019-12-3014:28:00.428CST[2166]LOG:checkpointstarting:time2019-12-3014:28:06.387CST[2166]LOG:checkpointcomplete:wrote59buffers(0.4%);0WALfile(s)added,0removed,0recycled;write=5.949s,sync=0.001s,total=5.958s;syncfiles=3,longest=0.001s,average=0.000s;distance=764kB,estimate=764kB

log_connections
记录登录信息，包括什么时候有连接请求，哪个用户连接的是哪个数据库；如连接失败，也会记录相关信息。

###修改配置信息[test@localhost~]$grep'log_connections'$PGDATA/postgresql.conf#"postgres-clog_connections=on".Someparameterscanbechangedatruntimelog_connections=on[test@localhost~]$pg_ctlreloadserversignaled[test@localhost~]$###日志输出2019-12-3014:33:02.527CST[2634]LOG:connectionreceived:host=[local]2019-12-3014:33:02.531CST[2634]LOG:connectionauthorized:user=testdatabase=testdbapplication_name=psql

log_disconnections
记录连接断开登录信息

###[test@localhost~]$grep'log_disconnections'$PGDATA/postgresql.conflog_disconnections=on[test@localhost~]$###日志输出2019-12-3014:34:57.646CST[2734]LOG:disconnection:sessiontime:0:00:04.885user=testdatabase=testdbhost=[local]

log_duration
记录执行时间，仅记录执行时间，没有其他多余的信息。默认为on。

2019-12-3014:36:49.149CST[2224]LOG:duration:12.178ms

log_error_verbosity
出现错误时的日志诊断信息级别，可选项包括：terse, default, or verbose，默认值为default
使用verbose，可以看到哪个源文件的哪一行，方便诊断

###创建测试对象[local:/data/run/test]:5000test@testdb=#createviewvw_tblasselect*fromtbl;CREATEVIEW[local:/data/run/test]:5000test@testdb=####terse2019-12-3014:41:30.395CST[2163]LOG:parameter"log_error_verbosity"changedto"terse"2019-12-3014:41:39.689CST[2224]ERROR:cannotdroptabletblbecauseotherobjectsdependonit###default2019-12-3014:42:27.106CST[2163]LOG:parameter"log_error_verbosity"changedto"default"2019-12-3014:42:33.287CST[2224]ERROR:cannotdroptabletblbecauseotherobjectsdependonit2019-12-3014:42:33.287CST[2224]DETAIL:viewvw_tbldependsontabletbl2019-12-3014:42:33.287CST[2224]HINT:UseDROP...CASCADEtodropthedependentobjectstoo.###verbose2019-12-3014:43:29.654CST[2163]LOG:00000:parameter"log_error_verbosity"changedto"verbose"2019-12-3014:43:29.654CST[2163]LOCATION:ProcessConfigFileInternal,guc-file.l:4562019-12-3014:43:32.790CST[2224]ERROR:2BP01:cannotdroptabletblbecauseotherobjectsdependonit2019-12-3014:43:32.790CST[2224]DETAIL:viewvw_tbldependsontabletbl2019-12-3014:43:32.790CST[2224]HINT:UseDROP...CASCADEtodropthedependentobjectstoo.2019-12-3014:43:32.790CST[2224]LOCATION:reportDependentObjects,dependency.c:1196

log_hostname
是否记录主机名称，默认为off。

log_line_prefix
每一行日志前的前缀。默认为’%m [%p] ‘，可使用的通配符包括：

%a – application name
%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
%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
%% – ‘%’

把该参数修改为’%m %u@%d %p %r’，reload后的日志输出：

2019-12-3015:20:31.707CST@[2163]LOG:00000:parameter"log_line_prefix"changedto"%m%u@%d[%p]%r"2019-12-3015:20:31.707CST@[2163]LOCATION:ProcessConfigFileInternal,guc-file.l:4562019-12-3015:20:40.148CSTtest@testdb[2224][local]ERROR:2BP01:cannotdroptabletblbecauseotherobjectsdependonit2019-12-3015:20:40.148CSTtest@testdb[2224][local]DETAIL:viewvw_tbldependsontabletbl2019-12-3015:20:40.148CSTtest@testdb[2224][local]HINT:UseDROP...CASCADEtodropthedependentobjectstoo.2019-12-3015:20:40.148CSTtest@testdb[2224][local]LOCATION:reportDependentObjects,dependency.c:1196

log_lock_waits
记录等待时间超过deadlock_timeout（默认为1s）的lock。

2019-12-3015:27:06.637CST@[2163]LOG:00000:parameter"log_lock_waits"changedto"on"2019-12-3015:27:06.637CST@[2163]LOCATION:ProcessConfigFileInternal,guc-file.l:4562019-12-3015:27:18.462CSTtest@testdb[2224][local]LOG:00000:duration:0.542ms2019-12-3015:27:18.462CSTtest@testdb[2224][local]LOCATION:exec_simple_query,postgres.c:12892019-12-3015:27:36.559CSTtest@testdb[5749][local]LOG:00000:duration:0.639ms2019-12-3015:27:36.559CSTtest@testdb[5749][local]LOCATION:exec_simple_query,postgres.c:12892019-12-3015:27:44.470CSTtest@testdb[2224][local]LOG:00000:duration:3.592ms2019-12-3015:27:44.470CSTtest@testdb[2224][local]LOCATION:exec_simple_query,postgres.c:12892019-12-3015:27:58.024CSTtest@testdb[5749][local]LOG:00000:process5749stillwaitingforAccessShareLockonrelation16385ofdatabase16384after1000.615msatcharacter222019-12-3015:27:58.024CSTtest@testdb[5749][local]DETAIL:Processholdingthelock:2224.Waitqueue:5749.2019-12-3015:27:58.024CSTtest@testdb[5749][local]LOCATION:ProcSleep,proc.c:1493...

log_statement
记录哪些语句，可选项包括none, ddl, mod, all。
设置该参数为all，而log_min_duration_statement设置为600
执行SQL

[local:/data/run/test]:5000test@testdb=#select1;?column?----------1(1row)[local:/data/run/test]:5000test@testdb=#selectpg_sleep(1);pg_sleep----------(1row)

虽然select 1;执行得很快，没有超过600ms，但由于设置了该参数为all，因此也会在日志中出现

2019-12-3015:32:41.934CST@[2163]LOG:00000:receivedSIGHUP,reloadingconfigurationfiles2019-12-3015:32:41.934CST@[2163]LOCATION:SIGHUP_handler,postmaster.c:26352019-12-3015:32:41.939CST@[2163]LOG:00000:parameter"log_min_duration_statement"changedto"600"2019-12-3015:32:41.939CST@[2163]LOCATION:ProcessConfigFileInternal,guc-file.l:4562019-12-3015:32:41.939CST@[2163]LOG:00000:parameter"log_statement"changedto"all"2019-12-3015:32:41.939CST@[2163]LOCATION:ProcessConfigFileInternal,guc-file.l:4562019-12-3015:32:51.932CSTtest@testdb[2224][local]LOG:00000:statement:select1;2019-12-3015:32:51.932CSTtest@testdb[2224][local]LOCATION:exec_simple_query,postgres.c:10452019-12-3015:32:51.932CSTtest@testdb[2224][local]LOG:00000:duration:0.375ms2019-12-3015:32:51.932CSTtest@testdb[2224][local]LOCATION:exec_simple_query,postgres.c:12892019-12-3015:33:02.686CSTtest@testdb[2224][local]LOG:00000:statement:selectpg_sleep(1);2019-12-3015:33:02.686CSTtest@testdb[2224][local]LOCATION:exec_simple_query,postgres.c:10452019-12-3015:33:03.691CSTtest@testdb[2224][local]LOG:00000:duration:1005.297ms2019-12-3015:33:03.691CSTtest@testdb[2224][local]LOCATION:exec_simple_query,postgres.c:1289

把该参数设置为none，执行同样的SQL，日志输出中只有select pg_sleep(1);

2019-12-3015:35:06.740CST@[2163]LOG:00000:receivedSIGHUP,reloadingconfigurationfiles2019-12-3015:35:06.740CST@[2163]LOCATION:SIGHUP_handler,postmaster.c:26352019-12-3015:35:06.743CST@[2163]LOG:00000:parameter"log_statement"changedto"none"2019-12-3015:35:06.743CST@[2163]LOCATION:ProcessConfigFileInternal,guc-file.l:4562019-12-3015:35:09.995CSTtest@testdb[2224][local]LOG:00000:duration:0.325ms2019-12-3015:35:09.995CSTtest@testdb[2224][local]LOCATION:exec_simple_query,postgres.c:12892019-12-3015:35:12.441CSTtest@testdb[2224][local]LOG:00000:duration:1001.645msstatement:selectpg_sleep(1);2019-12-3015:35:12.441CSTtest@testdb[2224][local]LOCATION:exec_simple_query,postgres.c:1296

log_replication_commands
是否记录复制命令。

log_temp_files
是否记录大小超过该参数配置大小的临时文件。在执行大批量数据排序或者使用临时表时可以用于诊断。

2019-12-3015:40:37.992CST@[2163]LOG:00000:receivedSIGHUP,reloadingconfigurationfiles2019-12-3015:40:37.992CST@[2163]LOCATION:SIGHUP_handler,postmaster.c:26352019-12-3015:40:37.993CST@[2163]LOG:00000:parameter"log_replication_commands"changedto"on"2019-12-3015:40:37.993CST@[2163]LOCATION:ProcessConfigFileInternal,guc-file.l:4562019-12-3015:40:37.993CST@[2163]LOG:00000:parameter"log_temp_files"changedto"1024"2019-12-3015:40:37.993CST@[2163]LOCATION:ProcessConfigFileInternal,guc-file.l:456###日志输出2019-12-3015:42:07.897CSTtest@testdb[2224][local]LOG:00000:temporaryfile:path"base/pgsql_tmp/pgsql_tmp2224.4",size295075842019-12-3015:42:07.897CSTtest@testdb[2224][local]LOCATION:ReportTemporaryFileUsage,fd.c:1285

log_timezone
是否记录时区信息。

到此，相信大家对“分析PostgreSQL日志相关的配置参数log_XXX”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！