本篇内容介绍了“怎么使用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这几个维度来解析这些参数，本节是第二部分，介绍when to log。

when to log

带有时间属性的参数包括log_timezone、log_duration、log_autovacuum_min_duration、log_min_duration_statement
log_min_messages
可选项从低到高包括：
debug5
debug4
debug3
debug2
debug1
info
notice
warning
error
log
fatal
panic
默认值为warning。
设置为panic，则只有panic级别的日志，设置为debug5则包括所有级别的日志。下面把该参数设置为debug5，重启数据库，检查日志输出。

[test@localhost~]$grep'log_min_'$PGDATA/postgresql.conflog_min_messages=debug5#valuesinorderofdecreasingdetail:#log_min_error_statement=error#valuesinorderofdecreasingdetail:#log_min_duration_statement=-1#-1isdisabled,0logsallstatements[test@localhost~]$[test@localhost~]$pg_ctlrestartwaitingforservertoshutdown....doneserverstoppedwaitingforservertostart....2019-12-2710:25:15.198CST[4441]DEBUG:postgres:PostmasterMain:initialenvironmentdump:2019-12-2710:25:15.198CST[4441]DEBUG:-----------------------------------------2019-12-2710:25:15.198CST[4441]DEBUG:XDG_SESSION_ID=122019-12-2710:25:15.198CST[4441]DEBUG:HOSTNAME=localhost.localdomain2019-12-2710:25:15.198CST[4441]DEBUG:TERM=xterm2019-12-2710:25:15.198CST[4441]DEBUG:SHELL=/bin/bash2019-12-2710:25:15.198CST[4441]DEBUG:HISTSIZE=10002019-12-2710:25:15.198CST[4441]DEBUG:PG_GRANDPARENT_PID=24602019-12-2710:25:15.198CST[4441]DEBUG:USER=test2019-12-2710:25:15.198CST[4441]DEBUG:LS_COLORS=rs=0:di=01;34:ln=01;36:mh=00:pi=40;33:so=01;35:do=01;35:bd=40;33;01:cd=40;33;01:or=40;31;01:mi=01;05;37;41:su=37;41:sg=30;43:ca=30;41:tw=30;42:ow=34;42:st=37;44:ex=01;32:*.tar=01;31:*.tgz=01;31:*.arc=01;31:*.arj=01;31:*.taz=01;31:*.lha=01;31:*.lz4=01;31:*.lzh=01;31:*.lzma=01;31:*.tlz=01;31:*.txz=01;31:*.tzo=01;31:*.t7z=01;31:*.zip=01;31:*.z=01;31:*.Z=01;31:*.dz=01;31:*.gz=01;31:*.lrz=01;31:*.lz=01;31:*.lzo=01;31:*.xz=01;31:*.bz2=01;31:*.bz=01;31:*.tbz=01;31:*.tbz2=01;31:*.tz=01;31:*.deb=01;31:*.rpm=01;31:*.jar=01;31:*.war=01;31:*.ear=01;31:*.sar=01;31:*.rar=01;31:*.alz=01;31:*.ace=01;31:*.zoo=01;31:*.cpio=01;31:*.7z=01;31:*.rz=01;31:*.cab=01;31:*.jpg=01;35:*.jpeg=01;35:*.gif=01;35:*.bmp=01;35:*.pbm=01;35:*.pgm=01;35:*.ppm=01;35:*.tga=01;35:*.xbm=01;35:*.xpm=01;35:*.tif=01;35:*.tiff=01;35:*.png=01;35:*.svg=01;35:*.svgz=01;35:*.mng=01;35:*.pcx=01;35:*.mov=01;35:*.mpg=01;35:*.mpeg=01;35:*.m2v=01;35:*.mkv=01;35:*.webm=01;35:*.ogm=01;35:*.mp4=01;35:*.m4v=01;35:*.mp4v=01;35:*.vob=01;35:*.qt=01;35:*.nuv=01;35:*.wmv=01;35:*.asf=01;35:*.rm=01;35:*.rmvb=01;35:*.flc=01;35:*.avi=01;35:*.fli=01;35:*.flv=01;35:*.gl=01;35:*.dl=01;35:*.xcf=01;35:*.xwd=01;35:*.yuv=01;35:*.cgm=01;35:*.emf=01;35:*.axv=01;35:*.anx=01;35:*.ogv=01;35:*.ogx=01;35:*.aac=01;36:*.au=01;36:*.flac=01;36:*.mid=01;36:*.midi=01;36:*.mka=01;36:*.mp3=01;36:*.mpc=01;36:*.ogg=01;36:*.ra=01;36:*.wav=01;36:*.axa=01;36:*.oga=01;36:*.spx=01;36:*.xspf=01;36:2019-12-2710:25:15.198CST[4441]DEBUG:PGPORT=50002019-12-2710:25:15.198CST[4441]DEBUG:PGDATABASE=testdb2019-12-2710:25:15.198CST[4441]DEBUG:LIBDIR=/appdb/pg12/pg12.1/share/postgresql/extension2019-12-2710:25:15.198CST[4441]DEBUG:PATH=/appdb/pg12/pg12.1/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/test/.local/bin:/home/test/bin2019-12-2710:25:15.198CST[4441]DEBUG:MAIL=/var/spool/mail/test2019-12-2710:25:15.198CST[4441]DEBUG:_=/appdb/pg12/pg12.1/bin/pg_ctl2019-12-2710:25:15.198CST[4441]DEBUG:PWD=/home/test2019-12-2710:25:15.198CST[4441]DEBUG:LANG=en_US.UTF-82019-12-2710:25:15.198CST[4441]DEBUG:PGHOST=/var/run/test2019-12-2710:25:15.198CST[4441]DEBUG:PGSYSCONFDIR=/appdb/pg12/pg12.1/etc/postgresql2019-12-2710:25:15.198CST[4441]DEBUG:HISTCONTROL=ignoredups2019-12-2710:25:15.198CST[4441]DEBUG:HOME=/home/test2019-12-2710:25:15.198CST[4441]DEBUG:SHLVL=12019-12-2710:25:15.198CST[4441]DEBUG:LOGNAME=test2019-12-2710:25:15.198CST[4441]DEBUG:LESSOPEN=||/usr/bin/lesspipe.sh%s2019-12-2710:25:15.198CST[4441]DEBUG:PGDATA=/data/pgsql/tmpdb2019-12-2710:25:15.198CST[4441]DEBUG:LC_COLLATE=en_US.UTF-82019-12-2710:25:15.198CST[4441]DEBUG:LC_CTYPE=en_US.UTF-82019-12-2710:25:15.198CST[4441]DEBUG:LC_MESSAGES=en_US.UTF-82019-12-2710:25:15.198CST[4441]DEBUG:LC_MONETARY=C2019-12-2710:25:15.198CST[4441]DEBUG:LC_NUMERIC=C2019-12-2710:25:15.198CST[4441]DEBUG:LC_TIME=C2019-12-2710:25:15.198CST[4441]DEBUG:-----------------------------------------2019-12-2710:25:15.202CST[4441]DEBUG:registeringbackgroundworker"logicalreplicationlauncher"2019-12-2710:25:15.202CST[4441]LOG:startingPostgreSQL12.1onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2019-12-2710:25:15.202CST[4441]LOG:listeningonIPv6address"::1",port50002019-12-2710:25:15.202CST[4441]LOG:listeningonIPv4address"127.0.0.1",port50002019-12-2710:25:15.206CST[4441]LOG:listeningonUnixsocket"/var/run/test/.s.PGSQL.5000"2019-12-2710:25:15.208CST[4441]DEBUG:invokingIpcMemoryCreate(size=149102592)2019-12-2710:25:15.208CST[4441]DEBUG:mmap(150994944)withMAP_HUGETLBfailed,hugepagesdisabled:Cannotallocatememory2019-12-2710:25:15.218CST[4441]DEBUG:SlruScanDirectoryinvokingcallbackonpg_notify/00002019-12-2710:25:15.218CST[4441]DEBUG:removingfile"pg_notify/0000"2019-12-2710:25:15.218CST[4441]DEBUG:dynamicsharedmemorysystemwillsupport308segments2019-12-2710:25:15.218CST[4441]DEBUG:createddynamicsharedmemorycontrolsegment1237114017(7408bytes)2019-12-2710:25:15.219CST[4441]DEBUG:max_safe_fds=983,usable_fds=1000,already_open=72019-12-2710:25:15.219CST[4441]LOG:redirectinglogoutputtologgingcollectorprocess2019-12-2710:25:15.219CST[4441]HINT:Futurelogoutputwillappearindirectory"log".doneserverstarted[test@localhost~]$

可以看到，在启动阶段输出很多DEBUG日志。
下面执行SQL：

[local:/var/run/test]:5000test@testdb=#select*fromtblorderbyidlimit1;id|c1----+----1|1(1row)[local:/var/run/test]:5000test@testdb=#

日志输出：

...2019-12-2710:47:24.850CST[5763]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/0RELOPTINFO(tbl):rows=200002width=6pathlist:SeqScan(tbl)rows=200002cost=0.00..5540.02cheapestparameterizedpaths:SeqScan(tbl)rows=200002cost=0.00..5540.02cheapeststartuppath:SeqScan(tbl)rows=200002cost=0.00..5540.02cheapesttotalpath:SeqScan(tbl)rows=200002cost=0.00..5540.022019-12-2710:47:24.853CST[5747]DEBUG:registeringbackgroundworker"parallelworkerforPID5763"2019-12-2710:47:24.853CST[5747]DEBUG:registeringbackgroundworker"parallelworkerforPID5763"2019-12-2710:47:24.853CST[5747]DEBUG:startingbackgroundworkerprocess"parallelworkerforPID5763"2019-12-2710:47:24.853CST[5747]DEBUG:startingbackgroundworkerprocess"parallelworkerforPID5763"2019-12-2710:47:24.854CST[5783]DEBUG:InitPostgres2019-12-2710:47:24.854CST[5783]DEBUG:mybackendIDis42019-12-2710:47:24.854CST[5784]DEBUG:InitPostgres2019-12-2710:47:24.854CST[5784]DEBUG:mybackendIDis52019-12-2710:47:24.854CST[5783]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.854CST[5784]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.878CST[5783]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.878CST[5783]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.879CST[5784]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.879CST[5784]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.913CST[5783]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.913CST[5783]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.914CST[5784]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.914CST[5784]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.945CST[5784]DEBUG:CommitTransaction(1)name:unnamed;blockState:PARALLEL_INPROGRESS;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.945CST[5784]DEBUG:shmem_exit(0):1before_shmem_exitcallbackstomake2019-12-2710:47:24.945CST[5784]DEBUG:shmem_exit(0):7on_shmem_exitcallbackstomake2019-12-2710:47:24.945CST[5784]DEBUG:proc_exit(0):2callbackstomake2019-12-2710:47:24.945CST[5784]DEBUG:exit(0)2019-12-2710:47:24.945CST[5783]DEBUG:CommitTransaction(1)name:unnamed;blockState:PARALLEL_INPROGRESS;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:47:24.945CST[5784]DEBUG:shmem_exit(-1):0before_shmem_exitcallbackstomake2019-12-2710:47:24.945CST[5784]DEBUG:shmem_exit(-1):0on_shmem_exitcallbackstomake2019-12-2710:47:24.945CST[5784]DEBUG:proc_exit(-1):0callbackstomake2019-12-2710:47:24.945CST[5783]DEBUG:shmem_exit(0):1before_shmem_exitcallbackstomake2019-12-2710:47:24.945CST[5783]DEBUG:shmem_exit(0):7on_shmem_exitcallbackstomake2019-12-2710:47:24.945CST[5783]DEBUG:proc_exit(0):2callbackstomake2019-12-2710:47:24.945CST[5783]DEBUG:exit(0)2019-12-2710:47:24.945CST[5783]DEBUG:shmem_exit(-1):0before_shmem_exitcallbackstomake2019-12-2710:47:24.945CST[5783]DEBUG:shmem_exit(-1):0on_shmem_exitcallbackstomake2019-12-2710:47:24.945CST[5783]DEBUG:proc_exit(-1):0callbackstomake2019-12-2710:47:24.946CST[5747]DEBUG:reapingdeadprocesses2019-12-2710:47:24.946CST[5747]DEBUG:unregisteringbackgroundworker"parallelworkerforPID5763"2019-12-2710:47:24.946CST[5747]DEBUG:backgroundworker"parallelworker"(PID5784)exitedwithexitcode02019-12-2710:47:24.947CST[5747]DEBUG:reapingdeadprocesses2019-12-2710:47:24.947CST[5747]DEBUG:unregisteringbackgroundworker"parallelworkerforPID5763"2019-12-2710:47:24.947CST[5747]DEBUG:backgroundworker"parallelworker"(PID5783)exitedwithexitcode02019-12-2710:47:24.947CST[5763]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/0

执行insert&update

[local:/var/run/test]:5000test@testdb=#insertintotbl(id)selectgenerate_series(1,100000);INSERT0100000[local:/var/run/test]:5000test@testdb=#updatetblsetid=4;UPDATE500002[local:/var/run/test]:5000test@testdb=#

日志输出

2019-12-2710:49:08.917CST[5763]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/0RELOPTINFO(*SELECT*):rows=100000width=4pathlist:SubqueryScan(*SELECT*)rows=100000cost=0.00..1500.02cheapestparameterizedpaths:SubqueryScan(*SELECT*)rows=100000cost=0.00..1500.02cheapeststartuppath:SubqueryScan(*SELECT*)rows=100000cost=0.00..1500.02cheapesttotalpath:SubqueryScan(*SELECT*)rows=100000cost=0.00..1500.022019-12-2710:49:09.123CST[5763]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:503/1/0(used)2019-12-2710:49:20.592CST[5751]DEBUG:snapshotof0+0runningtransactionids(lsn0/A63FC00oldestxid504latestcomplete503nextxid504)2019-12-2710:49:24.401CST[5763]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/0RELOPTINFO(tbl):rows=400002width=44pathlist:SeqScan(tbl)rows=400002cost=0.00..7540.02cheapestparameterizedpaths:SeqScan(tbl)rows=400002cost=0.00..7540.02cheapeststartuppath:SeqScan(tbl)rows=400002cost=0.00..7540.02cheapesttotalpath:SeqScan(tbl)rows=400002cost=0.00..7540.022019-12-2710:49:26.477CST[5763]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:504/1/0(used)2019-12-2710:49:37.140CST[5751]DEBUG:snapshotof0+0runningtransactionids(lsn0/EF68250oldestxid505latestcomplete504nextxid505)

autovacuum的日志输出

2019-12-2710:49:58.619CST[5755]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:58.619CST[5755]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:58.649CST[5753]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:58.649CST[5753]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:58.649CST[5754]DEBUG:receivedinquiryfordatabase02019-12-2710:49:58.649CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/global.stat"2019-12-2710:49:58.649CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_0.stat"2019-12-2710:49:58.662CST[5923]DEBUG:InitPostgres2019-12-2710:49:58.662CST[5923]DEBUG:mybackendIDis42019-12-2710:49:58.662CST[5923]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:58.663CST[5923]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:58.663CST[5923]DEBUG:autovacuum:processingdatabase"testdb"2019-12-2710:49:58.663CST[5754]DEBUG:receivedinquiryfordatabase163842019-12-2710:49:58.663CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/global.stat"2019-12-2710:49:58.663CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_16384.stat"2019-12-2710:49:58.663CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_0.stat"2019-12-2710:49:58.675CST[5923]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:58.675CST[5923]DEBUG:tbl:vac:500002(threshold80050),anl:600002(threshold40050)2019-12-2710:49:58.675CST[5923]DEBUG:pg_statistic:vac:6(threshold134),anl:8(threshold92)2019-12-2710:49:58.675CST[5923]DEBUG:pg_type:vac:0(threshold131),anl:2(threshold91)2019-12-2710:49:58.675CST[5923]DEBUG:pg_authid:vac:0(threshold52),anl:0(threshold51)2019-12-2710:49:58.675CST[5923]DEBUG:pg_attribute:vac:0(threshold633),anl:8(threshold341)2019-12-2710:49:58.675CST[5923]DEBUG:pg_proc:vac:0(threshold642),anl:0(threshold346)2019-12-2710:49:58.675CST[5923]DEBUG:pg_class:vac:0(threshold129),anl:1(threshold90)2019-12-2710:49:58.675CST[5923]DEBUG:pg_index:vac:0(threshold82),anl:0(threshold66)2019-12-2710:49:58.675CST[5923]DEBUG:pg_operator:vac:0(threshold204),anl:0(threshold127)2019-12-2710:49:58.675CST[5923]DEBUG:pg_opclass:vac:0(threshold76),anl:0(threshold63)2019-12-2710:49:58.675CST[5923]DEBUG:pg_am:vac:0(threshold51),anl:0(threshold51)2019-12-2710:49:58.675CST[5923]DEBUG:pg_amop:vac:0(threshold193),anl:0(threshold122)2019-12-2710:49:58.675CST[5923]DEBUG:pg_amproc:vac:0(threshold139),anl:0(threshold95)2019-12-2710:49:58.675CST[5923]DEBUG:pg_rewrite:vac:0(threshold75),anl:0(threshold63)2019-12-2710:49:58.675CST[5923]DEBUG:pg_cast:vac:0(threshold93),anl:0(threshold72)2019-12-2710:49:58.675CST[5923]DEBUG:pg_namespace:vac:0(threshold51),anl:0(threshold51)2019-12-2710:49:58.675CST[5923]DEBUG:pg_depend:vac:0(threshold1570),anl:3(threshold810)2019-12-2710:49:58.675CST[5923]DEBUG:pg_database:vac:0(threshold50),anl:0(threshold50)2019-12-2710:49:58.675CST[5923]DEBUG:pg_tablespace:vac:0(threshold50),anl:0(threshold50)2019-12-2710:49:58.675CST[5923]DEBUG:pg_shdepend:vac:0(threshold52),anl:0(threshold51)2019-12-2710:49:58.675CST[5923]DEBUG:pg_ts_config:vac:0(threshold54),anl:0(threshold52)2019-12-2710:49:58.675CST[5923]DEBUG:pg_publication:vac:0(threshold50),anl:0(threshold50)2019-12-2710:49:58.676CST[5754]DEBUG:receivedinquiryfordatabase163842019-12-2710:49:58.676CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/global.stat"2019-12-2710:49:58.676CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_16384.stat"2019-12-2710:49:58.676CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_0.stat"2019-12-2710:49:58.686CST[5923]DEBUG:tbl:vac:500002(threshold80050),anl:600002(threshold40050)2019-12-2710:49:58.686CST[5923]DEBUG:autovac_balance_cost(pid=5923db=16384,rel=16385,dobalance=yescost_limit=200,cost_limit_base=200,cost_delay=2)2019-12-2710:49:58.686CST[5923]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:58.686CST[5923]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:58.686CST[5923]DEBUG:vacuuming"public.tbl"2019-12-2710:49:59.087CST[5923]DEBUG:"tbl":removed500002rowversionsin2216pages2019-12-2710:49:59.087CST[5923]DEBUG:"tbl":found500002removable,500002nonremovablerowversionsin4425outof4425pages2019-12-2710:49:59.087CST[5923]DETAIL:0deadrowversionscannotberemovedyet,oldestxmin:505Therewere168unuseditemidentifiers.Skipped0pagesduetobufferpins,0frozenpages.0pagesareentirelyempty.CPU:user:0.22s,system:0.00s,elapsed:0.40s.2019-12-2710:49:59.087CST[5923]LOG:automaticvacuumoftable"testdb.public.tbl":indexscans:0pages:0removed,4425remain,0skippedduetopins,0skippedfrozentuples:500002removed,500002remain,0aredeadbutnotyetremovable,oldestxmin:505bufferusage:8877hits,0misses,1dirtiedavgreadrate:0.000MB/s,avgwriterate:0.019MB/ssystemusage:CPU:user:0.22s,system:0.00s,elapsed:0.40s2019-12-2710:49:59.087CST[5923]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/0(used)2019-12-2710:49:59.087CST[5923]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:59.087CST[5923]DEBUG:analyzing"public.tbl"2019-12-2710:49:59.250CST[5923]DEBUG:"tbl":scanned4425of4425pages,containing500002liverowsand0deadrows;30000rowsinsample,500002estimatedtotalrows2019-12-2710:49:59.258CST[5923]LOG:automaticanalyzeoftable"testdb.public.tbl"systemusage:CPU:user:0.08s,system:0.01s,elapsed:0.17s2019-12-2710:49:59.260CST[5923]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:505/1/0(used)2019-12-2710:49:59.265CST[5923]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:59.265CST[5923]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:49:59.265CST[5923]DEBUG:shmem_exit(0):1before_shmem_exitcallbackstomake2019-12-2710:49:59.265CST[5923]DEBUG:shmem_exit(0):7on_shmem_exitcallbackstomake2019-12-2710:49:59.265CST[5923]DEBUG:proc_exit(0):2callbackstomake2019-12-2710:49:59.265CST[5923]DEBUG:exit(0)2019-12-2710:49:59.265CST[5923]DEBUG:shmem_exit(-1):0before_shmem_exitcallbackstomake2019-12-2710:49:59.265CST[5923]DEBUG:shmem_exit(-1):0on_shmem_exitcallbackstomake2019-12-2710:49:59.265CST[5923]DEBUG:proc_exit(-1):0callbackstomake2019-12-2710:49:59.267CST[5747]DEBUG:reapingdeadprocesses2019-12-2710:49:59.267CST[5747]DEBUG:serverprocess(PID5923)exitedwithexitcode02019-12-2710:50:07.762CST[5751]DEBUG:snapshotof0+0runningtransactionids(lsn0/F1DA880oldestxid506latestcomplete505nextxid506)

我们把语句放在事务中执行：

[local:/var/run/test]:5000test@testdb=#begin;BEGIN[local:/var/run/test]:5000test@testdb=#*insertintotbl(id)selectgenerate_series(1,100000);INSERT0100000[local:/var/run/test]:5000test@testdb=#*updatetblsetid=4;UPDATE600002[local:/var/run/test]:5000test@testdb=#*updatetblsetid=5;UPDATE600002[local:/var/run/test]:5000test@testdb=#*end;COMMIT[local:/var/run/test]:5000test@testdb=#

日志输出：

2019-12-2710:53:36.847CST[5763]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/0RELOPTINFO(*SELECT*):rows=100000width=4pathlist:SubqueryScan(*SELECT*)rows=100000cost=0.00..1500.02cheapestparameterizedpaths:SubqueryScan(*SELECT*)rows=100000cost=0.00..1500.02cheapeststartuppath:SubqueryScan(*SELECT*)rows=100000cost=0.00..1500.02cheapesttotalpath:SubqueryScan(*SELECT*)rows=100000cost=0.00..1500.022019-12-2710:53:52.369CST[5751]DEBUG:snapshotof1+0runningtransactionids(lsn0/F8619B0oldestxid506latestcomplete505nextxid507)RELOPTINFO(tbl):rows=500002width=44pathlist:SeqScan(tbl)rows=500002cost=0.00..9425.02cheapestparameterizedpaths:SeqScan(tbl)rows=500002cost=0.00..9425.02cheapeststartuppath:SeqScan(tbl)rows=500002cost=0.00..9425.02cheapesttotalpath:SeqScan(tbl)rows=500002cost=0.00..9425.022019-12-2710:53:55.368CST[5763]DEBUG:creatingandfillingnewWALfile2019-12-2710:53:55.414CST[5763]DEBUG:donecreatingandfillingnewWALfile2019-12-2710:53:55.901CST[5763]DEBUG:creatingandfillingnewWALfile2019-12-2710:53:55.946CST[5763]DEBUG:donecreatingandfillingnewWALfile2019-12-2710:53:56.398CST[5763]DEBUG:creatingandfillingnewWALfile2019-12-2710:53:56.436CST[5763]DEBUG:donecreatingandfillingnewWALfile2019-12-2710:53:56.891CST[5763]DEBUG:creatingandfillingnewWALfile2019-12-2710:53:56.945CST[5763]DEBUG:donecreatingandfillingnewWALfile2019-12-2710:53:57.410CST[5763]DEBUG:creatingandfillingnewWALfile2019-12-2710:53:57.501CST[5763]DEBUG:donecreatingandfillingnewWALfile2019-12-2710:53:58.128CST[5763]DEBUG:creatingandfillingnewWALfile2019-12-2710:53:58.486CST[5763]DEBUG:donecreatingandfillingnewWALfile2019-12-2710:53:58.854CST[5753]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:53:58.854CST[5753]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:53:58.855CST[5754]DEBUG:receivedinquiryfordatabase02019-12-2710:53:58.855CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/global.stat"2019-12-2710:53:58.855CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_0.stat"2019-12-2710:53:58.869CST[6146]DEBUG:InitPostgres2019-12-2710:53:58.869CST[6146]DEBUG:mybackendIDis42019-12-2710:53:58.874CST[6146]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:53:58.882CST[6146]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:53:58.882CST[6146]DEBUG:autovacuum:processingdatabase"testdb"2019-12-2710:53:58.883CST[5754]DEBUG:receivedinquiryfordatabase163842019-12-2710:53:58.883CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/global.stat"2019-12-2710:53:58.883CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_16384.stat"2019-12-2710:53:58.883CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_0.stat"2019-12-2710:53:58.895CST[6146]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:53:58.896CST[6146]DEBUG:tbl:vac:0(threshold100050),anl:0(threshold50050)2019-12-2710:53:58.896CST[6146]DEBUG:pg_statistic:vac:8(threshold134),anl:10(threshold92)2019-12-2710:53:58.896CST[6146]DEBUG:pg_type:vac:0(threshold131),anl:2(threshold91)2019-12-2710:53:58.896CST[6146]DEBUG:pg_authid:vac:0(threshold52),anl:0(threshold51)2019-12-2710:53:58.896CST[6146]DEBUG:pg_attribute:vac:0(threshold633),anl:8(threshold341)2019-12-2710:53:58.896CST[6146]DEBUG:pg_proc:vac:0(threshold642),anl:0(threshold346)2019-12-2710:53:58.896CST[6146]DEBUG:pg_class:vac:0(threshold129),anl:1(threshold90)2019-12-2710:53:58.896CST[6146]DEBUG:pg_index:vac:0(threshold82),anl:0(threshold66)2019-12-2710:53:58.896CST[6146]DEBUG:pg_operator:vac:0(threshold204),anl:0(threshold127)2019-12-2710:53:58.896CST[6146]DEBUG:pg_opclass:vac:0(threshold76),anl:0(threshold63)2019-12-2710:53:58.896CST[6146]DEBUG:pg_am:vac:0(threshold51),anl:0(threshold51)2019-12-2710:53:58.896CST[6146]DEBUG:pg_amop:vac:0(threshold193),anl:0(threshold122)2019-12-2710:53:58.896CST[6146]DEBUG:pg_amproc:vac:0(threshold139),anl:0(threshold95)2019-12-2710:53:58.896CST[6146]DEBUG:pg_rewrite:vac:0(threshold75),anl:0(threshold63)2019-12-2710:53:58.896CST[6146]DEBUG:pg_cast:vac:0(threshold93),anl:0(threshold72)2019-12-2710:53:58.896CST[6146]DEBUG:pg_namespace:vac:0(threshold51),anl:0(threshold51)2019-12-2710:53:58.896CST[6146]DEBUG:pg_depend:vac:0(threshold1570),anl:3(threshold810)2019-12-2710:53:58.896CST[6146]DEBUG:pg_database:vac:0(threshold50),anl:0(threshold50)2019-12-2710:53:58.896CST[6146]DEBUG:pg_tablespace:vac:0(threshold50),anl:0(threshold50)2019-12-2710:53:58.896CST[6146]DEBUG:pg_shdepend:vac:0(threshold52),anl:0(threshold51)2019-12-2710:53:58.896CST[6146]DEBUG:pg_ts_config:vac:0(threshold54),anl:0(threshold52)2019-12-2710:53:58.896CST[6146]DEBUG:pg_publication:vac:0(threshold50),anl:0(threshold50)2019-12-2710:53:58.897CST[6146]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:53:58.897CST[6146]DEBUG:shmem_exit(0):1before_shmem_exitcallbackstomake2019-12-2710:53:58.897CST[6146]DEBUG:shmem_exit(0):7on_shmem_exitcallbackstomake2019-12-2710:53:58.897CST[6146]DEBUG:proc_exit(0):2callbackstomake2019-12-2710:53:58.897CST[6146]DEBUG:exit(0)2019-12-2710:53:58.897CST[6146]DEBUG:shmem_exit(-1):0before_shmem_exitcallbackstomake2019-12-2710:53:58.897CST[6146]DEBUG:shmem_exit(-1):0on_shmem_exitcallbackstomake2019-12-2710:53:58.897CST[6146]DEBUG:proc_exit(-1):0callbackstomake2019-12-2710:53:58.903CST[5747]DEBUG:reapingdeadprocesses2019-12-2710:53:58.903CST[5747]DEBUG:serverprocess(PID6146)exitedwithexitcode02019-12-2710:54:09.502CST[5751]DEBUG:snapshotof1+0runningtransactionids(lsn0/15954428oldestxid506latestcomplete505nextxid507)2019-12-2710:54:28.097CST[5750]DEBUG:snapshotof1+0runningtransactionids(lsn0/15954460oldestxid506latestcomplete505nextxid507)2019-12-2710:54:28.098CST[5750]DEBUG:attemptingtoremoveWALsegmentsolderthanlogfile00000000000000000000000E2019-12-2710:54:28.098CST[5750]DEBUG:recycledwrite-aheadlogfile"000000010000000000000009"2019-12-2710:54:28.098CST[5750]DEBUG:recycledwrite-aheadlogfile"00000001000000000000000A"2019-12-2710:54:28.099CST[5750]DEBUG:recycledwrite-aheadlogfile"00000001000000000000000B"2019-12-2710:54:28.099CST[5750]DEBUG:recycledwrite-aheadlogfile"00000001000000000000000C"2019-12-2710:54:28.099CST[5750]DEBUG:removingwrite-aheadlogfile"00000001000000000000000D"2019-12-2710:54:28.103CST[5750]DEBUG:removingwrite-aheadlogfile"00000001000000000000000E"2019-12-2710:54:28.108CST[5750]DEBUG:SlruScanDirectoryinvokingcallbackonpg_subtrans/00002019-12-2710:54:29.922CST[5751]DEBUG:snapshotof1+0runningtransactionids(lsn0/15954510oldestxid506latestcomplete505nextxid507)RELOPTINFO(tbl):rows=600115width=44pathlist:SeqScan(tbl)rows=600115cost=0.00..11312.15cheapestparameterizedpaths:SeqScan(tbl)rows=600115cost=0.00..11312.15cheapeststartuppath:SeqScan(tbl)rows=600115cost=0.00..11312.15cheapesttotalpath:SeqScan(tbl)rows=600115cost=0.00..11312.152019-12-2710:54:35.594CST[5763]DEBUG:creatingandfillingnewWALfile2019-12-2710:54:35.638CST[5763]DEBUG:donecreatingandfillingnewWALfile2019-12-2710:54:46.496CST[5751]DEBUG:snapshotof1+0runningtransactionids(lsn0/1A760D38oldestxid506latestcomplete505nextxid507)2019-12-2710:54:49.705CST[5763]DEBUG:CommitTransaction(1)name:unnamed;blockState:END;state:INPROGRESS,xid/subid/cid:506/1/32019-12-2710:54:58.913CST[5753]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:54:58.913CST[5753]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:54:58.913CST[5754]DEBUG:receivedinquiryfordatabase02019-12-2710:54:58.913CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/global.stat"2019-12-2710:54:58.914CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_0.stat"2019-12-2710:54:58.926CST[6200]DEBUG:InitPostgres2019-12-2710:54:58.926CST[6200]DEBUG:mybackendIDis42019-12-2710:54:58.930CST[6200]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:54:58.938CST[6200]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:54:58.938CST[6200]DEBUG:autovacuum:processingdatabase"testdb"2019-12-2710:54:58.939CST[5754]DEBUG:receivedinquiryfordatabase163842019-12-2710:54:58.939CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/global.stat"2019-12-2710:54:58.939CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_16384.stat"2019-12-2710:54:58.939CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_0.stat"2019-12-2710:54:58.950CST[6200]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:54:58.951CST[6200]DEBUG:tbl:vac:1200004(threshold100050),anl:1300004(threshold50050)2019-12-2710:54:58.951CST[6200]DEBUG:pg_statistic:vac:8(threshold134),anl:10(threshold92)2019-12-2710:54:58.951CST[6200]DEBUG:pg_type:vac:0(threshold131),anl:2(threshold91)2019-12-2710:54:58.951CST[6200]DEBUG:pg_authid:vac:0(threshold52),anl:0(threshold51)2019-12-2710:54:58.951CST[6200]DEBUG:pg_attribute:vac:0(threshold633),anl:8(threshold341)2019-12-2710:54:58.951CST[6200]DEBUG:pg_proc:vac:0(threshold642),anl:0(threshold346)2019-12-2710:54:58.951CST[6200]DEBUG:pg_class:vac:0(threshold129),anl:1(threshold90)2019-12-2710:54:58.951CST[6200]DEBUG:pg_index:vac:0(threshold82),anl:0(threshold66)2019-12-2710:54:58.951CST[6200]DEBUG:pg_operator:vac:0(threshold204),anl:0(threshold127)2019-12-2710:54:58.951CST[6200]DEBUG:pg_opclass:vac:0(threshold76),anl:0(threshold63)2019-12-2710:54:58.951CST[6200]DEBUG:pg_am:vac:0(threshold51),anl:0(threshold51)2019-12-2710:54:58.951CST[6200]DEBUG:pg_amop:vac:0(threshold193),anl:0(threshold122)2019-12-2710:54:58.951CST[6200]DEBUG:pg_amproc:vac:0(threshold139),anl:0(threshold95)2019-12-2710:54:58.951CST[6200]DEBUG:pg_rewrite:vac:0(threshold75),anl:0(threshold63)2019-12-2710:54:58.951CST[6200]DEBUG:pg_cast:vac:0(threshold93),anl:0(threshold72)2019-12-2710:54:58.951CST[6200]DEBUG:pg_namespace:vac:0(threshold51),anl:0(threshold51)2019-12-2710:54:58.951CST[6200]DEBUG:pg_depend:vac:0(threshold1570),anl:3(threshold810)2019-12-2710:54:58.951CST[6200]DEBUG:pg_database:vac:0(threshold50),anl:0(threshold50)2019-12-2710:54:58.951CST[6200]DEBUG:pg_tablespace:vac:0(threshold50),anl:0(threshold50)2019-12-2710:54:58.951CST[6200]DEBUG:pg_shdepend:vac:0(threshold52),anl:0(threshold51)2019-12-2710:54:58.951CST[6200]DEBUG:pg_ts_config:vac:0(threshold54),anl:0(threshold52)2019-12-2710:54:58.951CST[6200]DEBUG:pg_publication:vac:0(threshold50),anl:0(threshold50)2019-12-2710:54:58.951CST[5754]DEBUG:receivedinquiryfordatabase163842019-12-2710:54:58.951CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/global.stat"2019-12-2710:54:58.951CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_16384.stat"2019-12-2710:54:58.951CST[5754]DEBUG:writingstatsfile"pg_stat_tmp/db_0.stat"2019-12-2710:54:58.962CST[6200]DEBUG:tbl:vac:1200004(threshold100050),anl:1300004(threshold50050)2019-12-2710:54:58.962CST[6200]DEBUG:autovac_balance_cost(pid=6200db=16384,rel=16385,dobalance=yescost_limit=200,cost_limit_base=200,cost_delay=2)2019-12-2710:54:58.962CST[6200]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:54:58.962CST[6200]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:54:58.962CST[6200]DEBUG:vacuuming"public.tbl"2019-12-2710:55:00.192CST[6200]DEBUG:"tbl":removed1200004rowversionsin5311pages2019-12-2710:55:00.192CST[6200]DEBUG:"tbl":found1200004removable,600002nonremovablerowversionsin7965outof7965pages2019-12-2710:55:00.192CST[6200]DETAIL:0deadrowversionscannotberemovedyet,oldestxmin:507Therewere420unuseditemidentifiers.Skipped0pagesduetobufferpins,0frozenpages.0pagesareentirelyempty.CPU:user:0.64s,system:0.02s,elapsed:1.23s.2019-12-2710:55:00.192CST[6200]LOG:automaticvacuumoftable"testdb.public.tbl":indexscans:0pages:0removed,7965remain,0skippedduetopins,0skippedfrozentuples:1200004removed,600002remain,0aredeadbutnotyetremovable,oldestxmin:507bufferusage:15957hits,0misses,890dirtiedavgreadrate:0.000MB/s,avgwriterate:5.651MB/ssystemusage:CPU:user:0.64s,system:0.02s,elapsed:1.23s2019-12-2710:55:00.192CST[6200]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/0(used)2019-12-2710:55:00.193CST[6200]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:55:00.193CST[6200]DEBUG:analyzing"public.tbl"2019-12-2710:55:00.511CST[6200]DEBUG:"tbl":scanned7965of7965pages,containing600002liverowsand0deadrows;30000rowsinsample,600002estimatedtotalrows2019-12-2710:55:00.525CST[6200]LOG:automaticanalyzeoftable"testdb.public.tbl"systemusage:CPU:user:0.11s,system:0.06s,elapsed:0.33s2019-12-2710:55:00.526CST[6200]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:507/1/0(used)2019-12-2710:55:00.526CST[6200]DEBUG:StartTransaction(1)name:unnamed;blockState:DEFAULT;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:55:00.526CST[6200]DEBUG:CommitTransaction(1)name:unnamed;blockState:STARTED;state:INPROGRESS,xid/subid/cid:0/1/02019-12-2710:55:00.526CST[6200]DEBUG:shmem_exit(0):1before_shmem_exitcallbackstomake2019-12-2710:55:00.526CST[6200]DEBUG:shmem_exit(0):7on_shmem_exitcallbackstomake2019-12-2710:55:00.526CST[6200]DEBUG:proc_exit(0):2callbackstomake2019-12-2710:55:00.526CST[6200]DEBUG:exit(0)2019-12-2710:55:00.527CST[6200]DEBUG:shmem_exit(-1):0before_shmem_exitcallbackstomake2019-12-2710:55:00.527CST[6200]DEBUG:shmem_exit(-1):0on_shmem_exitcallbackstomake2019-12-2710:55:00.527CST[6200]DEBUG:proc_exit(-1):0callbackstomake2019-12-2710:55:00.531CST[5747]DEBUG:reapingdeadprocesses2019-12-2710:55:00.531CST[5747]DEBUG:serverprocess(PID6200)exitedwithexitcode02019-12-2710:55:06.901CST[5751]DEBUG:snapshotof0+0runningtransactionids(lsn0/1AD14BB0oldestxid508latestcomplete507nextxid508)

对内核实现感兴趣的，可以仔细研究下：）
其他级别的Level：

INFO – as far as I can tell PostgreSQL itself uses it only in some cases with notify/listen
NOTICE – well, this are used for example when you create a table, with PRIMARY KEY, and it notifies you about index creation for the table
WARNING – all kinds of warnings, the most common one is about using escapes (\ character) in strings
ERROR – all cases which cause Pg to break transaction. Deadlocks, bad values, constraint violations and so on
LOG – used to log information about special event (checkpoint, connections)
FATAL – errors which cause Pg session that caused it to abort. For example – bad password given when authenticating
PANIC – errors which cause all Pg sessions to be aborted. You don’t want this.

log_min_error_statement
可选项从低到高包括：
debug5
debug4
debug3
debug2
debug1
info
notice
warning
error
log
fatal
panic
默认值为error。
下面我们设置该值为debug5

[test@localhosttmpdb]$pg_ctlrestartwaitingforservertoshutdown....doneserverstoppedwaitingforservertostart....2019-12-2711:51:01.267CST[9410]LOG:startingPostgreSQL12.1onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2019-12-2711:51:01.268CST[9410]LOG:listeningonIPv6address"::1",port50002019-12-2711:51:01.268CST[9410]LOG:listeningonIPv4address"127.0.0.1",port50002019-12-2711:51:01.272CST[9410]LOG:listeningonUnixsocket"/var/run/test/.s.PGSQL.5000"2019-12-2711:51:01.286CST[9410]LOG:redirectinglogoutputtologgingcollectorprocess2019-12-2711:51:01.286CST[9410]HINT:Futurelogoutputwillappearindirectory"log".doneserverstarted[test@localhosttmpdb]$grep'log_min_error'postgresql.conflog_min_error_statement=debug5#valuesinorderofdecreasingdetail:[test@localhosttmpdb]$

执行常规的SQL和出错的SQL

###SQL[local:/var/run/test]:5000test@testdb=#select*fromttt;ERROR:relation"ttt"doesnotexistLINE1:select*fromttt;^[local:/var/run/test]:5000test@testdb=#select1/0;ERROR:divisionbyzero[local:/var/run/test]:5000test@testdb=####日志输出2019-12-2711:52:19.052CST[9430]ERROR:relation"ttt"doesnotexistatcharacter152019-12-2711:52:19.052CST[9430]STATEMENT:select*fromttt;2019-12-2711:52:34.043CST[9430]ERROR:divisionbyzero2019-12-2711:52:34.043CST[9430]STATEMENT:select1/0;

调整为panic，执行SQL

[test@localhosttmpdb]$pg_ctlrestartwaitingforservertoshutdown....doneserverstopped...[test@localhosttmpdb]$grep'log_min_error'postgresql.conflog_min_error_statement=panic#valuesinorderofdecreasingdetail:[test@localhosttmpdb]$###[local:/var/run/test]:5000test@testdb=#select*fromttt;ERROR:relation"ttt"doesnotexistLINE1:select*fromttt;^[local:/var/run/test]:5000test@testdb=#select1/0;ERROR:divisionbyzero[local:/var/run/test]:5000test@testdb=####2019-12-2712:03:49.939CST[10151]ERROR:relation"ttt"doesnotexistatcharacter152019-12-2712:03:54.470CST[10151]ERROR:divisionbyzero

日志输出只有错误信息，没有SQL语句。

log_min_duration_statement
整型，单位为ms毫秒。SQL运行时长超过此设定的，将记录在日志中。需要注意的是，并不是超过该时长马上记录，而是在SQL执行结束后才会记录。

[test@localhosttmpdb]$greplog_min_duration_statementpostgresql.conflog_min_duration_statement=100#-1isdisabled,0logsallstatements[test@localhosttmpdb]$pg_ctlrestartwaitingforservertoshutdown....doneserverstoppedwaitingforservertostart....2019-12-2716:01:11.343CST[23662]LOG:startingPostgreSQL12.1onx86_64-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),64-bit2019-12-2716:01:11.347CST[23662]LOG:listeningonIPv6address"::1",port50002019-12-2716:01:11.347CST[23662]LOG:listeningonIPv4address"127.0.0.1",port50002019-12-2716:01:11.358CST[23662]LOG:listeningonUnixsocket"/var/run/test/.s.PGSQL.5000"2019-12-2716:01:11.382CST[23662]LOG:redirectinglogoutputtologgingcollectorprocess2019-12-2716:01:11.382CST[23662]HINT:Futurelogoutputwillappearindirectory"log".doneserverstarted[test@localhosttmpdb]$

执行SQL：

[local:/var/run/test]:5000test@testdb=#selectpg_sleep(0.01);pg_sleep----------(1row)[local:/var/run/test]:5000test@testdb=#selectpg_sleep(1);pg_sleep----------(1row)[local:/var/run/test]:5000test@testdb=#

日志输出（SQL执行完才会记录在日志中）

...2019-12-2716:02:02.066CST[23733]LOG:duration:1001.115msstatement:selectpg_sleep(1);

log_transaction_sample_rate
该参数在PG 12中引入，解释如下：

Log all statements from a sample of transactions
This is useful to obtain a view of the different transaction types in an
application, regardless of the durations of the statements each runs.

对按此参数设定的比例对事务进行采样，对APP很有用。

“怎么使用PostgreSQL与日志相关的配置参数log_XXX”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！