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
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>20.8. Error Reporting and Logging</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="runtime-config-query.html" title="20.7. Query Planning" /><link rel="next" href="runtime-config-statistics.html" title="20.9. Run-time Statistics" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">20.8. Error Reporting and Logging</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="runtime-config-query.html" title="20.7. Query Planning">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><th width="60%" align="center">Chapter 20. Server Configuration</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.6 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="runtime-config-statistics.html" title="20.9. Run-time Statistics">Next</a></td></tr></table><hr /></div><div class="sect1" id="RUNTIME-CONFIG-LOGGING"><div class="titlepage"><div><div><h2 class="title" style="clear: both">20.8. Error Reporting and Logging</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE">20.8.1. Where to Log</a></span></dt><dt><span class="sect2"><a href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN">20.8.2. When to Log</a></span></dt><dt><span class="sect2"><a href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT">20.8.3. What to Log</a></span></dt><dt><span class="sect2"><a href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG">20.8.4. Using CSV-Format Log Output</a></span></dt><dt><span class="sect2"><a href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-JSONLOG">20.8.5. Using JSON-Format Log Output</a></span></dt><dt><span class="sect2"><a href="runtime-config-logging.html#id-1.6.7.11.8">20.8.6. Process Title</a></span></dt></dl></div><a id="id-1.6.7.11.2" class="indexterm"></a><div class="sect2" id="RUNTIME-CONFIG-LOGGING-WHERE"><div class="titlepage"><div><div><h3 class="title">20.8.1. Where to Log</h3></div></div></div><a id="id-1.6.7.11.3.2" class="indexterm"></a><a id="id-1.6.7.11.3.3" class="indexterm"></a><div class="variablelist"><dl class="variablelist"><dt id="GUC-LOG-DESTINATION"><span class="term"><code class="varname">log_destination</code> (<code class="type">string</code>)
<a id="id-1.6.7.11.3.4.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
<span class="productname">PostgreSQL</span> supports several methods
for logging server messages, including
<span class="systemitem">stderr</span>, <span class="systemitem">csvlog</span>,
<span class="systemitem">jsonlog</span>, and
<span class="systemitem">syslog</span>. On Windows,
<span class="systemitem">eventlog</span> is also supported. Set this
parameter to a list of desired log destinations separated by
commas. The default is to log to <span class="systemitem">stderr</span>
only.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p><p>
If <span class="systemitem">csvlog</span> is included in <code class="varname">log_destination</code>,
log entries are output in <span class="quote">“<span class="quote">comma separated
value</span>”</span> (<acronym class="acronym">CSV</acronym>) format, which is convenient for
loading logs into programs.
See <a class="xref" href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG" title="20.8.4. Using CSV-Format Log Output">Section 20.8.4</a> for details.
<a class="xref" href="runtime-config-logging.html#GUC-LOGGING-COLLECTOR">logging_collector</a> must be enabled to generate
CSV-format log output.
</p><p>
If <span class="systemitem">jsonlog</span> is included in
<code class="varname">log_destination</code>, log entries are output in
<acronym class="acronym">JSON</acronym> format, which is convenient for loading logs
into programs.
See <a class="xref" href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-JSONLOG" title="20.8.5. Using JSON-Format Log Output">Section 20.8.5</a> for details.
<a class="xref" href="runtime-config-logging.html#GUC-LOGGING-COLLECTOR">logging_collector</a> must be enabled to generate
JSON-format log output.
</p><p>
When either <span class="systemitem">stderr</span>,
<span class="systemitem">csvlog</span> or <span class="systemitem">jsonlog</span> are
included, the file <code class="filename">current_logfiles</code> is created to
record the location of the log file(s) currently in use by the logging
collector and the associated logging destination. This provides a
convenient way to find the logs currently in use by the instance. Here
is an example of this file's content:
</p><pre class="programlisting">
stderr log/postgresql.log
csvlog log/postgresql.csv
jsonlog log/postgresql.json
</pre><p>
<code class="filename">current_logfiles</code> is recreated when a new log file
is created as an effect of rotation, and
when <code class="varname">log_destination</code> is reloaded. It is removed when
none of <span class="systemitem">stderr</span>,
<span class="systemitem">csvlog</span> or <span class="systemitem">jsonlog</span> are
included in <code class="varname">log_destination</code>, and when the logging
collector is disabled.
</p><div class="note"><h3 class="title">Note</h3><p>
On most Unix systems, you will need to alter the configuration of
your system's <span class="application">syslog</span> daemon in order
to make use of the <span class="systemitem">syslog</span> option for
<code class="varname">log_destination</code>. <span class="productname">PostgreSQL</span>
can log to <span class="application">syslog</span> facilities
<code class="literal">LOCAL0</code> through <code class="literal">LOCAL7</code> (see <a class="xref" href="runtime-config-logging.html#GUC-SYSLOG-FACILITY">syslog_facility</a>), but the default
<span class="application">syslog</span> configuration on most platforms
will discard all such messages. You will need to add something like:
</p><pre class="programlisting">
local0.* /var/log/postgresql
</pre><p>
to the <span class="application">syslog</span> daemon's configuration file
to make it work.
</p><p>
On Windows, when you use the <code class="literal">eventlog</code>
option for <code class="varname">log_destination</code>, you should
register an event source and its library with the operating
system so that the Windows Event Viewer can display event
log messages cleanly.
See <a class="xref" href="event-log-registration.html" title="19.12. Registering Event Log on Windows">Section 19.12</a> for details.
</p></div></dd><dt id="GUC-LOGGING-COLLECTOR"><span class="term"><code class="varname">logging_collector</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.3.4.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
This parameter enables the <em class="firstterm">logging collector</em>, which
is a background process that captures log messages
sent to <span class="systemitem">stderr</span> and redirects them into log files.
This approach is often more useful than
logging to <span class="application">syslog</span>, since some types of messages
might not appear in <span class="application">syslog</span> output. (One common
example is dynamic-linker failure messages; another is error messages
produced by scripts such as <code class="varname">archive_command</code>.)
This parameter can only be set at server start.
</p><div class="note"><h3 class="title">Note</h3><p>
It is possible to log to <span class="systemitem">stderr</span> without using the
logging collector; the log messages will just go to wherever the
server's <span class="systemitem">stderr</span> is directed. However, that method is
only suitable for low log volumes, since it provides no convenient
way to rotate log files. Also, on some platforms not using the
logging collector can result in lost or garbled log output, because
multiple processes writing concurrently to the same log file can
overwrite each other's output.
</p></div><div class="note"><h3 class="title">Note</h3><p>
The logging collector is designed to never lose messages. This means
that in case of extremely high load, server processes could be
blocked while trying to send additional log messages when the
collector has fallen behind. In contrast, <span class="application">syslog</span>
prefers to drop messages if it cannot write them, which means it
may fail to log some messages in such cases but it will not block
the rest of the system.
</p></div></dd><dt id="GUC-LOG-DIRECTORY"><span class="term"><code class="varname">log_directory</code> (<code class="type">string</code>)
<a id="id-1.6.7.11.3.4.3.1.3" class="indexterm"></a>
</span></dt><dd><p>
When <code class="varname">logging_collector</code> is enabled,
this parameter determines the directory in which log files will be created.
It can be specified as an absolute path, or relative to the
cluster data directory.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
The default is <code class="literal">log</code>.
</p></dd><dt id="GUC-LOG-FILENAME"><span class="term"><code class="varname">log_filename</code> (<code class="type">string</code>)
<a id="id-1.6.7.11.3.4.4.1.3" class="indexterm"></a>
</span></dt><dd><p>
When <code class="varname">logging_collector</code> is enabled,
this parameter sets the file names of the created log files. The value
is treated as a <code class="function">strftime</code> pattern,
so <code class="literal">%</code>-escapes can be used to specify time-varying
file names. (Note that if there are
any time-zone-dependent <code class="literal">%</code>-escapes, the computation
is done in the zone specified
by <a class="xref" href="runtime-config-logging.html#GUC-LOG-TIMEZONE">log_timezone</a>.)
The supported <code class="literal">%</code>-escapes are similar to those
listed in the Open Group's <a class="ulink" href="https://pubs.opengroup.org/onlinepubs/009695399/functions/strftime.html" target="_top">strftime
</a> specification.
Note that the system's <code class="function">strftime</code> is not used
directly, so platform-specific (nonstandard) extensions do not work.
The default is <code class="literal">postgresql-%Y-%m-%d_%H%M%S.log</code>.
</p><p>
If you specify a file name without escapes, you should plan to
use a log rotation utility to avoid eventually filling the
entire disk. In releases prior to 8.4, if
no <code class="literal">%</code> escapes were
present, <span class="productname">PostgreSQL</span> would append
the epoch of the new log file's creation time, but this is no
longer the case.
</p><p>
If CSV-format output is enabled in <code class="varname">log_destination</code>,
<code class="literal">.csv</code> will be appended to the timestamped
log file name to create the file name for CSV-format output.
(If <code class="varname">log_filename</code> ends in <code class="literal">.log</code>, the suffix is
replaced instead.)
</p><p>
If JSON-format output is enabled in <code class="varname">log_destination</code>,
<code class="literal">.json</code> will be appended to the timestamped
log file name to create the file name for JSON-format output.
(If <code class="varname">log_filename</code> ends in <code class="literal">.log</code>, the suffix is
replaced instead.)
</p><p>
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-LOG-FILE-MODE"><span class="term"><code class="varname">log_file_mode</code> (<code class="type">integer</code>)
<a id="id-1.6.7.11.3.4.5.1.3" class="indexterm"></a>
</span></dt><dd><p>
On Unix systems this parameter sets the permissions for log files
when <code class="varname">logging_collector</code> is enabled. (On Microsoft
Windows this parameter is ignored.)
The parameter value is expected to be a numeric mode
specified in the format accepted by the
<code class="function">chmod</code> and <code class="function">umask</code>
system calls. (To use the customary octal format the number
must start with a <code class="literal">0</code> (zero).)
</p><p>
The default permissions are <code class="literal">0600</code>, meaning only the
server owner can read or write the log files. The other commonly
useful setting is <code class="literal">0640</code>, allowing members of the owner's
group to read the files. Note however that to make use of such a
setting, you'll need to alter <a class="xref" href="runtime-config-logging.html#GUC-LOG-DIRECTORY">log_directory</a> to
store the files somewhere outside the cluster data directory. In
any case, it's unwise to make the log files world-readable, since
they might contain sensitive data.
</p><p>
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-LOG-ROTATION-AGE"><span class="term"><code class="varname">log_rotation_age</code> (<code class="type">integer</code>)
<a id="id-1.6.7.11.3.4.6.1.3" class="indexterm"></a>
</span></dt><dd><p>
When <code class="varname">logging_collector</code> is enabled,
this parameter determines the maximum amount of time to use an
individual log file, after which a new log file will be created.
If this value is specified without units, it is taken as minutes.
The default is 24 hours.
Set to zero to disable time-based creation of new log files.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-LOG-ROTATION-SIZE"><span class="term"><code class="varname">log_rotation_size</code> (<code class="type">integer</code>)
<a id="id-1.6.7.11.3.4.7.1.3" class="indexterm"></a>
</span></dt><dd><p>
When <code class="varname">logging_collector</code> is enabled,
this parameter determines the maximum size of an individual log file.
After this amount of data has been emitted into a log file,
a new log file will be created.
If this value is specified without units, it is taken as kilobytes.
The default is 10 megabytes.
Set to zero to disable size-based creation of new log files.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-LOG-TRUNCATE-ON-ROTATION"><span class="term"><code class="varname">log_truncate_on_rotation</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.3.4.8.1.3" class="indexterm"></a>
</span></dt><dd><p>
When <code class="varname">logging_collector</code> is enabled,
this parameter will cause <span class="productname">PostgreSQL</span> to truncate (overwrite),
rather than append to, any existing log file of the same name.
However, truncation will occur only when a new file is being opened
due to time-based rotation, not during server startup or size-based
rotation. When off, pre-existing files will be appended to in
all cases. For example, using this setting in combination with
a <code class="varname">log_filename</code> like <code class="literal">postgresql-%H.log</code>
would result in generating twenty-four hourly log files and then
cyclically overwriting them.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p><p>
Example: To keep 7 days of logs, one log file per day named
<code class="literal">server_log.Mon</code>, <code class="literal">server_log.Tue</code>,
etc., and automatically overwrite last week's log with this week's log,
set <code class="varname">log_filename</code> to <code class="literal">server_log.%a</code>,
<code class="varname">log_truncate_on_rotation</code> to <code class="literal">on</code>, and
<code class="varname">log_rotation_age</code> to <code class="literal">1440</code>.
</p><p>
Example: To keep 24 hours of logs, one log file per hour, but
also rotate sooner if the log file size exceeds 1GB, set
<code class="varname">log_filename</code> to <code class="literal">server_log.%H%M</code>,
<code class="varname">log_truncate_on_rotation</code> to <code class="literal">on</code>,
<code class="varname">log_rotation_age</code> to <code class="literal">60</code>, and
<code class="varname">log_rotation_size</code> to <code class="literal">1000000</code>.
Including <code class="literal">%M</code> in <code class="varname">log_filename</code> allows
any size-driven rotations that might occur to select a file name
different from the hour's initial file name.
</p></dd><dt id="GUC-SYSLOG-FACILITY"><span class="term"><code class="varname">syslog_facility</code> (<code class="type">enum</code>)
<a id="id-1.6.7.11.3.4.9.1.3" class="indexterm"></a>
</span></dt><dd><p>
When logging to <span class="application">syslog</span> is enabled, this parameter
determines the <span class="application">syslog</span>
<span class="quote">“<span class="quote">facility</span>”</span> to be used. You can choose
from <code class="literal">LOCAL0</code>, <code class="literal">LOCAL1</code>,
<code class="literal">LOCAL2</code>, <code class="literal">LOCAL3</code>, <code class="literal">LOCAL4</code>,
<code class="literal">LOCAL5</code>, <code class="literal">LOCAL6</code>, <code class="literal">LOCAL7</code>;
the default is <code class="literal">LOCAL0</code>. See also the
documentation of your system's
<span class="application">syslog</span> daemon.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-SYSLOG-IDENT"><span class="term"><code class="varname">syslog_ident</code> (<code class="type">string</code>)
<a id="id-1.6.7.11.3.4.10.1.3" class="indexterm"></a>
</span></dt><dd><p>
When logging to <span class="application">syslog</span> is enabled, this parameter
determines the program name used to identify
<span class="productname">PostgreSQL</span> messages in
<span class="application">syslog</span> logs. The default is
<code class="literal">postgres</code>.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-SYSLOG-SEQUENCE-NUMBERS"><span class="term"><code class="varname">syslog_sequence_numbers</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.3.4.11.1.3" class="indexterm"></a>
</span></dt><dd><p>
When logging to <span class="application">syslog</span> and this is on (the
default), then each message will be prefixed by an increasing
sequence number (such as <code class="literal">[2]</code>). This circumvents
the <span class="quote">“<span class="quote">--- last message repeated N times ---</span>”</span> suppression
that many syslog implementations perform by default. In more modern
syslog implementations, repeated message suppression can be configured
(for example, <code class="literal">$RepeatedMsgReduction</code>
in <span class="productname">rsyslog</span>), so this might not be
necessary. Also, you could turn this off if you actually want to
suppress repeated messages.
</p><p>
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-SYSLOG-SPLIT-MESSAGES"><span class="term"><code class="varname">syslog_split_messages</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.3.4.12.1.3" class="indexterm"></a>
</span></dt><dd><p>
When logging to <span class="application">syslog</span> is enabled, this parameter
determines how messages are delivered to syslog. When on (the
default), messages are split by lines, and long lines are split so
that they will fit into 1024 bytes, which is a typical size limit for
traditional syslog implementations. When off, PostgreSQL server log
messages are delivered to the syslog service as is, and it is up to
the syslog service to cope with the potentially bulky messages.
</p><p>
If syslog is ultimately logging to a text file, then the effect will
be the same either way, and it is best to leave the setting on, since
most syslog implementations either cannot handle large messages or
would need to be specially configured to handle them. But if syslog
is ultimately writing into some other medium, it might be necessary or
more useful to keep messages logically together.
</p><p>
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-EVENT-SOURCE"><span class="term"><code class="varname">event_source</code> (<code class="type">string</code>)
<a id="id-1.6.7.11.3.4.13.1.3" class="indexterm"></a>
</span></dt><dd><p>
When logging to <span class="application">event log</span> is enabled, this parameter
determines the program name used to identify
<span class="productname">PostgreSQL</span> messages in
the log. The default is <code class="literal">PostgreSQL</code>.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-LOGGING-WHEN"><div class="titlepage"><div><div><h3 class="title">20.8.2. When to Log</h3></div></div></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-LOG-MIN-MESSAGES"><span class="term"><code class="varname">log_min_messages</code> (<code class="type">enum</code>)
<a id="id-1.6.7.11.4.2.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
Controls which <a class="link" href="runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS" title="Table 20.2. Message Severity Levels">message
levels</a> are written to the server log.
Valid values are <code class="literal">DEBUG5</code>, <code class="literal">DEBUG4</code>,
<code class="literal">DEBUG3</code>, <code class="literal">DEBUG2</code>, <code class="literal">DEBUG1</code>,
<code class="literal">INFO</code>, <code class="literal">NOTICE</code>, <code class="literal">WARNING</code>,
<code class="literal">ERROR</code>, <code class="literal">LOG</code>, <code class="literal">FATAL</code>, and
<code class="literal">PANIC</code>. Each level includes all the levels that
follow it. The later the level, the fewer messages are sent
to the log. The default is <code class="literal">WARNING</code>. Note that
<code class="literal">LOG</code> has a different rank here than in
<a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a>.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p></dd><dt id="GUC-LOG-MIN-ERROR-STATEMENT"><span class="term"><code class="varname">log_min_error_statement</code> (<code class="type">enum</code>)
<a id="id-1.6.7.11.4.2.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
Controls which SQL statements that cause an error
condition are recorded in the server log. The current
SQL statement is included in the log entry for any message of
the specified
<a class="link" href="runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS" title="Table 20.2. Message Severity Levels">severity</a>
or higher.
Valid values are <code class="literal">DEBUG5</code>,
<code class="literal">DEBUG4</code>, <code class="literal">DEBUG3</code>,
<code class="literal">DEBUG2</code>, <code class="literal">DEBUG1</code>,
<code class="literal">INFO</code>, <code class="literal">NOTICE</code>,
<code class="literal">WARNING</code>, <code class="literal">ERROR</code>,
<code class="literal">LOG</code>,
<code class="literal">FATAL</code>, and <code class="literal">PANIC</code>.
The default is <code class="literal">ERROR</code>, which means statements
causing errors, log messages, fatal errors, or panics will be logged.
To effectively turn off logging of failing statements,
set this parameter to <code class="literal">PANIC</code>.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p></dd><dt id="GUC-LOG-MIN-DURATION-STATEMENT"><span class="term"><code class="varname">log_min_duration_statement</code> (<code class="type">integer</code>)
<a id="id-1.6.7.11.4.2.3.1.3" class="indexterm"></a>
</span></dt><dd><p>
Causes the duration of each completed statement to be logged
if the statement ran for at least the specified amount of time.
For example, if you set it to <code class="literal">250ms</code>
then all SQL statements that run 250ms or longer will be
logged. Enabling this parameter can be helpful in tracking down
unoptimized queries in your applications.
If this value is specified without units, it is taken as milliseconds.
Setting this to zero prints all statement durations.
<code class="literal">-1</code> (the default) disables logging statement
durations.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p><p>
This overrides <a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-DURATION-SAMPLE">log_min_duration_sample</a>,
meaning that queries with duration exceeding this setting are not
subject to sampling and are always logged.
</p><p>
For clients using extended query protocol, durations of the Parse,
Bind, and Execute steps are logged independently.
</p><div class="note"><h3 class="title">Note</h3><p>
When using this option together with
<a class="xref" href="runtime-config-logging.html#GUC-LOG-STATEMENT">log_statement</a>,
the text of statements that are logged because of
<code class="varname">log_statement</code> will not be repeated in the
duration log message.
If you are not using <span class="application">syslog</span>, it is recommended
that you log the PID or session ID using
<a class="xref" href="runtime-config-logging.html#GUC-LOG-LINE-PREFIX">log_line_prefix</a>
so that you can link the statement message to the later
duration message using the process ID or session ID.
</p></div></dd><dt id="GUC-LOG-MIN-DURATION-SAMPLE"><span class="term"><code class="varname">log_min_duration_sample</code> (<code class="type">integer</code>)
<a id="id-1.6.7.11.4.2.4.1.3" class="indexterm"></a>
</span></dt><dd><p>
Allows sampling the duration of completed statements that ran for
at least the specified amount of time. This produces the same
kind of log entries as
<a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT">log_min_duration_statement</a>, but only for a
subset of the executed statements, with sample rate controlled by
<a class="xref" href="runtime-config-logging.html#GUC-LOG-STATEMENT-SAMPLE-RATE">log_statement_sample_rate</a>.
For example, if you set it to <code class="literal">100ms</code> then all
SQL statements that run 100ms or longer will be considered for
sampling. Enabling this parameter can be helpful when the
traffic is too high to log all queries.
If this value is specified without units, it is taken as milliseconds.
Setting this to zero samples all statement durations.
<code class="literal">-1</code> (the default) disables sampling statement
durations.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p><p>
This setting has lower priority
than <code class="varname">log_min_duration_statement</code>, meaning that
statements with durations
exceeding <code class="varname">log_min_duration_statement</code> are not
subject to sampling and are always logged.
</p><p>
Other notes for <code class="varname">log_min_duration_statement</code>
apply also to this setting.
</p></dd><dt id="GUC-LOG-STATEMENT-SAMPLE-RATE"><span class="term"><code class="varname">log_statement_sample_rate</code> (<code class="type">floating point</code>)
<a id="id-1.6.7.11.4.2.5.1.3" class="indexterm"></a>
</span></dt><dd><p>
Determines the fraction of statements with duration exceeding
<a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-DURATION-SAMPLE">log_min_duration_sample</a> that will be logged.
Sampling is stochastic, for example <code class="literal">0.5</code> means
there is statistically one chance in two that any given statement
will be logged.
The default is <code class="literal">1.0</code>, meaning to log all sampled
statements.
Setting this to zero disables sampled statement-duration logging,
the same as setting
<code class="varname">log_min_duration_sample</code> to
<code class="literal">-1</code>.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p></dd><dt id="GUC-LOG-TRANSACTION-SAMPLE-RATE"><span class="term"><code class="varname">log_transaction_sample_rate</code> (<code class="type">floating point</code>)
<a id="id-1.6.7.11.4.2.6.1.3" class="indexterm"></a>
</span></dt><dd><p>
Sets the fraction of transactions whose statements are all logged,
in addition to statements logged for other reasons. It applies to
each new transaction regardless of its statements' durations.
Sampling is stochastic, for example <code class="literal">0.1</code> means
there is statistically one chance in ten that any given transaction
will be logged.
<code class="varname">log_transaction_sample_rate</code> can be helpful to
construct a sample of transactions.
The default is <code class="literal">0</code>, meaning not to log
statements from any additional transactions. Setting this
to <code class="literal">1</code> logs all statements of all transactions.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p><div class="note"><h3 class="title">Note</h3><p>
Like all statement-logging options, this option can add significant
overhead.
</p></div></dd><dt id="GUC-LOG-STARTUP-PROGRESS-INTERVAL"><span class="term"><code class="varname">log_startup_progress_interval</code> (<code class="type">integer</code>)
<a id="id-1.6.7.11.4.2.7.1.3" class="indexterm"></a>
</span></dt><dd><p>
Sets the amount of time after which the startup process will log
a message about a long-running operation that is still in progress,
as well as the interval between further progress messages for that
operation. The default is 10 seconds. A setting of <code class="literal">0</code>
disables the feature. If this value is specified without units,
it is taken as milliseconds. This setting is applied separately to
each operation.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p><p>
For example, if syncing the data directory takes 25 seconds and
thereafter resetting unlogged relations takes 8 seconds, and if this
setting has the default value of 10 seconds, then a messages will be
logged for syncing the data directory after it has been in progress
for 10 seconds and again after it has been in progress for 20 seconds,
but nothing will be logged for resetting unlogged relations.
</p></dd></dl></div><p>
<a class="xref" href="runtime-config-logging.html#RUNTIME-CONFIG-SEVERITY-LEVELS" title="Table 20.2. Message Severity Levels">Table 20.2</a> explains the message
severity levels used by <span class="productname">PostgreSQL</span>. If logging output
is sent to <span class="systemitem">syslog</span> or Windows'
<span class="systemitem">eventlog</span>, the severity levels are translated
as shown in the table.
</p><div class="table" id="RUNTIME-CONFIG-SEVERITY-LEVELS"><p class="title"><strong>Table 20.2. Message Severity Levels</strong></p><div class="table-contents"><table class="table" summary="Message Severity Levels" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /><col class="col4" /></colgroup><thead><tr><th>Severity</th><th>Usage</th><th><span class="systemitem">syslog</span></th><th><span class="systemitem">eventlog</span></th></tr></thead><tbody><tr><td><code class="literal">DEBUG1 .. DEBUG5</code></td><td>Provides successively-more-detailed information for use by
developers.</td><td><code class="literal">DEBUG</code></td><td><code class="literal">INFORMATION</code></td></tr><tr><td><code class="literal">INFO</code></td><td>Provides information implicitly requested by the user,
e.g., output from <code class="command">VACUUM VERBOSE</code>.</td><td><code class="literal">INFO</code></td><td><code class="literal">INFORMATION</code></td></tr><tr><td><code class="literal">NOTICE</code></td><td>Provides information that might be helpful to users, e.g.,
notice of truncation of long identifiers.</td><td><code class="literal">NOTICE</code></td><td><code class="literal">INFORMATION</code></td></tr><tr><td><code class="literal">WARNING</code></td><td>Provides warnings of likely problems, e.g., <code class="command">COMMIT</code>
outside a transaction block.</td><td><code class="literal">NOTICE</code></td><td><code class="literal">WARNING</code></td></tr><tr><td><code class="literal">ERROR</code></td><td>Reports an error that caused the current command to
abort.</td><td><code class="literal">WARNING</code></td><td><code class="literal">ERROR</code></td></tr><tr><td><code class="literal">LOG</code></td><td>Reports information of interest to administrators, e.g.,
checkpoint activity.</td><td><code class="literal">INFO</code></td><td><code class="literal">INFORMATION</code></td></tr><tr><td><code class="literal">FATAL</code></td><td>Reports an error that caused the current session to
abort.</td><td><code class="literal">ERR</code></td><td><code class="literal">ERROR</code></td></tr><tr><td><code class="literal">PANIC</code></td><td>Reports an error that caused all database sessions to abort.</td><td><code class="literal">CRIT</code></td><td><code class="literal">ERROR</code></td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="RUNTIME-CONFIG-LOGGING-WHAT"><div class="titlepage"><div><div><h3 class="title">20.8.3. What to Log</h3></div></div></div><div class="note"><h3 class="title">Note</h3><p>
What you choose to log can have security implications; see
<a class="xref" href="logfile-maintenance.html" title="25.3. Log File Maintenance">Section 25.3</a>.
</p></div><div class="variablelist"><dl class="variablelist"><dt id="GUC-APPLICATION-NAME"><span class="term"><code class="varname">application_name</code> (<code class="type">string</code>)
<a id="id-1.6.7.11.5.3.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
The <code class="varname">application_name</code> can be any string of less than
<code class="symbol">NAMEDATALEN</code> characters (64 characters in a standard build).
It is typically set by an application upon connection to the server.
The name will be displayed in the <code class="structname">pg_stat_activity</code> view
and included in CSV log entries. It can also be included in regular
log entries via the <a class="xref" href="runtime-config-logging.html#GUC-LOG-LINE-PREFIX">log_line_prefix</a> parameter.
Only printable ASCII characters may be used in the
<code class="varname">application_name</code> value. Other characters will be
replaced with question marks (<code class="literal">?</code>).
</p></dd><dt><span class="term"><code class="varname">debug_print_parse</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.2.1.3" class="indexterm"></a>
<br /></span><span class="term"><code class="varname">debug_print_rewritten</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.2.2.3" class="indexterm"></a>
<br /></span><span class="term"><code class="varname">debug_print_plan</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.2.3.3" class="indexterm"></a>
</span></dt><dd><p>
These parameters enable various debugging output to be emitted.
When set, they print the resulting parse tree, the query rewriter
output, or the execution plan for each executed query.
These messages are emitted at <code class="literal">LOG</code> message level, so by
default they will appear in the server log but will not be sent to the
client. You can change that by adjusting
<a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> and/or
<a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-MESSAGES">log_min_messages</a>.
These parameters are off by default.
</p></dd><dt><span class="term"><code class="varname">debug_pretty_print</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.3.1.3" class="indexterm"></a>
</span></dt><dd><p>
When set, <code class="varname">debug_pretty_print</code> indents the messages
produced by <code class="varname">debug_print_parse</code>,
<code class="varname">debug_print_rewritten</code>, or
<code class="varname">debug_print_plan</code>. This results in more readable
but much longer output than the <span class="quote">“<span class="quote">compact</span>”</span> format used when
it is off. It is on by default.
</p></dd><dt id="GUC-LOG-AUTOVACUUM-MIN-DURATION"><span class="term"><code class="varname">log_autovacuum_min_duration</code> (<code class="type">integer</code>)
<a id="id-1.6.7.11.5.3.4.1.3" class="indexterm"></a>
</span></dt><dd><p>
Causes each action executed by autovacuum to be logged if it ran for at
least the specified amount of time. Setting this to zero logs
all autovacuum actions. <code class="literal">-1</code> disables logging autovacuum
actions. If this value is specified without units, it is taken as milliseconds.
For example, if you set this to
<code class="literal">250ms</code> then all automatic vacuums and analyzes that run
250ms or longer will be logged. In addition, when this parameter is
set to any value other than <code class="literal">-1</code>, a message will be
logged if an autovacuum action is skipped due to a conflicting lock or a
concurrently dropped relation. The default is <code class="literal">10min</code>.
Enabling this parameter can be helpful in tracking autovacuum activity.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line; but the setting can be overridden for
individual tables by changing table storage parameters.
</p></dd><dt id="GUC-LOG-CHECKPOINTS"><span class="term"><code class="varname">log_checkpoints</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.5.1.3" class="indexterm"></a>
</span></dt><dd><p>
Causes checkpoints and restartpoints to be logged in the server log.
Some statistics are included in the log messages, including the number
of buffers written and the time spent writing them.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line. The default is on.
</p></dd><dt id="GUC-LOG-CONNECTIONS"><span class="term"><code class="varname">log_connections</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.6.1.3" class="indexterm"></a>
</span></dt><dd><p>
Causes each attempted connection to the server to be logged,
as well as successful completion of both client authentication (if
necessary) and authorization.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this parameter at session start,
and it cannot be changed at all within a session.
The default is <code class="literal">off</code>.
</p><div class="note"><h3 class="title">Note</h3><p>
Some client programs, like <span class="application">psql</span>, attempt
to connect twice while determining if a password is required, so
duplicate <span class="quote">“<span class="quote">connection received</span>”</span> messages do not
necessarily indicate a problem.
</p></div></dd><dt id="GUC-LOG-DISCONNECTIONS"><span class="term"><code class="varname">log_disconnections</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.7.1.3" class="indexterm"></a>
</span></dt><dd><p>
Causes session terminations to be logged. The log output
provides information similar to <code class="varname">log_connections</code>,
plus the duration of the session.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this parameter at session start,
and it cannot be changed at all within a session.
The default is <code class="literal">off</code>.
</p></dd><dt id="GUC-LOG-DURATION"><span class="term"><code class="varname">log_duration</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.8.1.3" class="indexterm"></a>
</span></dt><dd><p>
Causes the duration of every completed statement to be logged.
The default is <code class="literal">off</code>.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p><p>
For clients using extended query protocol, durations of the Parse,
Bind, and Execute steps are logged independently.
</p><div class="note"><h3 class="title">Note</h3><p>
The difference between enabling <code class="varname">log_duration</code> and setting
<a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT">log_min_duration_statement</a> to zero is that
exceeding <code class="varname">log_min_duration_statement</code> forces the text of
the query to be logged, but this option doesn't. Thus, if
<code class="varname">log_duration</code> is <code class="literal">on</code> and
<code class="varname">log_min_duration_statement</code> has a positive value, all
durations are logged but the query text is included only for
statements exceeding the threshold. This behavior can be useful for
gathering statistics in high-load installations.
</p></div></dd><dt id="GUC-LOG-ERROR-VERBOSITY"><span class="term"><code class="varname">log_error_verbosity</code> (<code class="type">enum</code>)
<a id="id-1.6.7.11.5.3.9.1.3" class="indexterm"></a>
</span></dt><dd><p>
Controls the amount of detail written in the server log for each
message that is logged. Valid values are <code class="literal">TERSE</code>,
<code class="literal">DEFAULT</code>, and <code class="literal">VERBOSE</code>, each adding more
fields to displayed messages. <code class="literal">TERSE</code> excludes
the logging of <code class="literal">DETAIL</code>, <code class="literal">HINT</code>,
<code class="literal">QUERY</code>, and <code class="literal">CONTEXT</code> error information.
<code class="literal">VERBOSE</code> output includes the <code class="symbol">SQLSTATE</code> error
code (see also <a class="xref" href="errcodes-appendix.html" title="Appendix A. PostgreSQL Error Codes">Appendix A</a>) and the source code file name, function name,
and line number that generated the error.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p></dd><dt id="GUC-LOG-HOSTNAME"><span class="term"><code class="varname">log_hostname</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.10.1.3" class="indexterm"></a>
</span></dt><dd><p>
By default, connection log messages only show the IP address of the
connecting host. Turning this parameter on causes logging of the
host name as well. Note that depending on your host name resolution
setup this might impose a non-negligible performance penalty.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd><dt id="GUC-LOG-LINE-PREFIX"><span class="term"><code class="varname">log_line_prefix</code> (<code class="type">string</code>)
<a id="id-1.6.7.11.5.3.11.1.3" class="indexterm"></a>
</span></dt><dd><p>
This is a <code class="function">printf</code>-style string that is output at the
beginning of each log line.
<code class="literal">%</code> characters begin <span class="quote">“<span class="quote">escape sequences</span>”</span>
that are replaced with status information as outlined below.
Unrecognized escapes are ignored. Other
characters are copied straight to the log line. Some escapes are
only recognized by session processes, and will be treated as empty by
background processes such as the main server process. Status
information may be aligned either left or right by specifying a
numeric literal after the % and before the option. A negative
value will cause the status information to be padded on the
right with spaces to give it a minimum width, whereas a positive
value will pad on the left. Padding can be useful to aid human
readability in log files.
</p><p>
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line. The default is
<code class="literal">'%m [%p] '</code> which logs a time stamp and the process ID.
</p><div class="informaltable"><table class="informaltable" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Escape</th><th>Effect</th><th>Session only</th></tr></thead><tbody><tr><td><code class="literal">%a</code></td><td>Application name</td><td>yes</td></tr><tr><td><code class="literal">%u</code></td><td>User name</td><td>yes</td></tr><tr><td><code class="literal">%d</code></td><td>Database name</td><td>yes</td></tr><tr><td><code class="literal">%r</code></td><td>Remote host name or IP address, and remote port</td><td>yes</td></tr><tr><td><code class="literal">%h</code></td><td>Remote host name or IP address</td><td>yes</td></tr><tr><td><code class="literal">%b</code></td><td>Backend type</td><td>no</td></tr><tr><td><code class="literal">%p</code></td><td>Process ID</td><td>no</td></tr><tr><td><code class="literal">%P</code></td><td>Process ID of the parallel group leader, if this process
is a parallel query worker</td><td>no</td></tr><tr><td><code class="literal">%t</code></td><td>Time stamp without milliseconds</td><td>no</td></tr><tr><td><code class="literal">%m</code></td><td>Time stamp with milliseconds</td><td>no</td></tr><tr><td><code class="literal">%n</code></td><td>Time stamp with milliseconds (as a Unix epoch)</td><td>no</td></tr><tr><td><code class="literal">%i</code></td><td>Command tag: type of session's current command</td><td>yes</td></tr><tr><td><code class="literal">%e</code></td><td>SQLSTATE error code</td><td>no</td></tr><tr><td><code class="literal">%c</code></td><td>Session ID: see below</td><td>no</td></tr><tr><td><code class="literal">%l</code></td><td>Number of the log line for each session or process, starting at 1</td><td>no</td></tr><tr><td><code class="literal">%s</code></td><td>Process start time stamp</td><td>no</td></tr><tr><td><code class="literal">%v</code></td><td>Virtual transaction ID (backendID/localXID)</td><td>no</td></tr><tr><td><code class="literal">%x</code></td><td>Transaction ID (0 if none is assigned)</td><td>no</td></tr><tr><td><code class="literal">%q</code></td><td>Produces no output, but tells non-session
processes to stop at this point in the string; ignored by
session processes</td><td>no</td></tr><tr><td><code class="literal">%Q</code></td><td>Query identifier of the current query. Query
identifiers are not computed by default, so this field
will be zero unless <a class="xref" href="runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID">compute_query_id</a>
parameter is enabled or a third-party module that computes
query identifiers is configured.</td><td>yes</td></tr><tr><td><code class="literal">%%</code></td><td>Literal <code class="literal">%</code></td><td>no</td></tr></tbody></table></div><p>
The backend type corresponds to the column
<code class="structfield">backend_type</code> in the view
<a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" title="28.2.3. pg_stat_activity">
<code class="structname">pg_stat_activity</code></a>,
but additional types can appear
in the log that don't show in that view.
</p><p>
The <code class="literal">%c</code> escape prints a quasi-unique session identifier,
consisting of two 4-byte hexadecimal numbers (without leading zeros)
separated by a dot. The numbers are the process start time and the
process ID, so <code class="literal">%c</code> can also be used as a space saving way
of printing those items. For example, to generate the session
identifier from <code class="literal">pg_stat_activity</code>, use this query:
</p><pre class="programlisting">
SELECT to_hex(trunc(EXTRACT(EPOCH FROM backend_start))::integer) || '.' ||
to_hex(pid)
FROM pg_stat_activity;
</pre><p>
</p><div class="tip"><h3 class="title">Tip</h3><p>
If you set a nonempty value for <code class="varname">log_line_prefix</code>,
you should usually make its last character be a space, to provide
visual separation from the rest of the log line. A punctuation
character can be used too.
</p></div><div class="tip"><h3 class="title">Tip</h3><p>
<span class="application">Syslog</span> produces its own
time stamp and process ID information, so you probably do not want to
include those escapes if you are logging to <span class="application">syslog</span>.
</p></div><div class="tip"><h3 class="title">Tip</h3><p>
The <code class="literal">%q</code> escape is useful when including information that is
only available in session (backend) context like user or database
name. For example:
</p><pre class="programlisting">
log_line_prefix = '%m [%p] %q%u@%d/%a '
</pre><p>
</p></div><div class="note"><h3 class="title">Note</h3><p>
The <code class="literal">%Q</code> escape always reports a zero identifier
for lines output by <a class="xref" href="runtime-config-logging.html#GUC-LOG-STATEMENT">log_statement</a> because
<code class="varname">log_statement</code> generates output before an
identifier can be calculated, including invalid statements for
which an identifier cannot be calculated.
</p></div></dd><dt id="GUC-LOG-LOCK-WAITS"><span class="term"><code class="varname">log_lock_waits</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.12.1.3" class="indexterm"></a>
</span></dt><dd><p>
Controls whether a log message is produced when a session waits
longer than <a class="xref" href="runtime-config-locks.html#GUC-DEADLOCK-TIMEOUT">deadlock_timeout</a> to acquire a
lock. This is useful in determining if lock waits are causing
poor performance. The default is <code class="literal">off</code>.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p></dd><dt id="GUC-LOG-RECOVERY-CONFLICT-WAITS"><span class="term"><code class="varname">log_recovery_conflict_waits</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.13.1.3" class="indexterm"></a>
</span></dt><dd><p>
Controls whether a log message is produced when the startup process
waits longer than <code class="varname">deadlock_timeout</code>
for recovery conflicts. This is useful in determining if recovery
conflicts prevent the recovery from applying WAL.
</p><p>
The default is <code class="literal">off</code>. This parameter can only be set
in the <code class="filename">postgresql.conf</code> file or on the server
command line.
</p></dd><dt id="GUC-LOG-PARAMETER-MAX-LENGTH"><span class="term"><code class="varname">log_parameter_max_length</code> (<code class="type">integer</code>)
<a id="id-1.6.7.11.5.3.14.1.3" class="indexterm"></a>
</span></dt><dd><p>
If greater than zero, each bind parameter value logged with a
non-error statement-logging message is trimmed to this many bytes.
Zero disables logging of bind parameters for non-error statement logs.
<code class="literal">-1</code> (the default) allows bind parameters to be
logged in full.
If this value is specified without units, it is taken as bytes.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p><p>
This setting only affects log messages printed as a result of
<a class="xref" href="runtime-config-logging.html#GUC-LOG-STATEMENT">log_statement</a>,
<a class="xref" href="runtime-config-logging.html#GUC-LOG-DURATION">log_duration</a>, and related settings. Non-zero
values of this setting add some overhead, particularly if parameters
are sent in binary form, since then conversion to text is required.
</p></dd><dt id="GUC-LOG-PARAMETER-MAX-LENGTH-ON-ERROR"><span class="term"><code class="varname">log_parameter_max_length_on_error</code> (<code class="type">integer</code>)
<a id="id-1.6.7.11.5.3.15.1.3" class="indexterm"></a>
</span></dt><dd><p>
If greater than zero, each bind parameter value reported in error
messages is trimmed to this many bytes.
Zero (the default) disables including bind parameters in error
messages.
<code class="literal">-1</code> allows bind parameters to be printed in full.
If this value is specified without units, it is taken as bytes.
</p><p>
Non-zero values of this setting add overhead, as
<span class="productname">PostgreSQL</span> will need to store textual
representations of parameter values in memory at the start of each
statement, whether or not an error eventually occurs. The overhead
is greater when bind parameters are sent in binary form than when
they are sent as text, since the former case requires data
conversion while the latter only requires copying the string.
</p></dd><dt id="GUC-LOG-STATEMENT"><span class="term"><code class="varname">log_statement</code> (<code class="type">enum</code>)
<a id="id-1.6.7.11.5.3.16.1.3" class="indexterm"></a>
</span></dt><dd><p>
Controls which SQL statements are logged. Valid values are
<code class="literal">none</code> (off), <code class="literal">ddl</code>, <code class="literal">mod</code>, and
<code class="literal">all</code> (all statements). <code class="literal">ddl</code> logs all data definition
statements, such as <code class="command">CREATE</code>, <code class="command">ALTER</code>, and
<code class="command">DROP</code> statements. <code class="literal">mod</code> logs all
<code class="literal">ddl</code> statements, plus data-modifying statements
such as <code class="command">INSERT</code>,
<code class="command">UPDATE</code>, <code class="command">DELETE</code>, <code class="command">TRUNCATE</code>,
and <code class="command">COPY FROM</code>.
<code class="command">PREPARE</code>, <code class="command">EXECUTE</code>, and
<code class="command">EXPLAIN ANALYZE</code> statements are also logged if their
contained command is of an appropriate type. For clients using
extended query protocol, logging occurs when an Execute message
is received, and values of the Bind parameters are included
(with any embedded single-quote marks doubled).
</p><p>
The default is <code class="literal">none</code>.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p><div class="note"><h3 class="title">Note</h3><p>
Statements that contain simple syntax errors are not logged
even by the <code class="varname">log_statement</code> = <code class="literal">all</code> setting,
because the log message is emitted only after basic parsing has
been done to determine the statement type. In the case of extended
query protocol, this setting likewise does not log statements that
fail before the Execute phase (i.e., during parse analysis or
planning). Set <code class="varname">log_min_error_statement</code> to
<code class="literal">ERROR</code> (or lower) to log such statements.
</p><p>
Logged statements might reveal sensitive data and even contain
plaintext passwords.
</p></div></dd><dt id="GUC-LOG-REPLICATION-COMMANDS"><span class="term"><code class="varname">log_replication_commands</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.5.3.17.1.3" class="indexterm"></a>
</span></dt><dd><p>
Causes each replication command to be logged in the server log.
See <a class="xref" href="protocol-replication.html" title="55.4. Streaming Replication Protocol">Section 55.4</a> for more information about
replication command. The default value is <code class="literal">off</code>.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p></dd><dt id="GUC-LOG-TEMP-FILES"><span class="term"><code class="varname">log_temp_files</code> (<code class="type">integer</code>)
<a id="id-1.6.7.11.5.3.18.1.3" class="indexterm"></a>
</span></dt><dd><p>
Controls logging of temporary file names and sizes.
Temporary files can be
created for sorts, hashes, and temporary query results.
If enabled by this setting, a log entry is emitted for each
temporary file when it is deleted.
A value of zero logs all temporary file information, while positive
values log only files whose size is greater than or equal to
the specified amount of data.
If this value is specified without units, it is taken as kilobytes.
The default setting is -1, which disables such logging.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p></dd><dt id="GUC-LOG-TIMEZONE"><span class="term"><code class="varname">log_timezone</code> (<code class="type">string</code>)
<a id="id-1.6.7.11.5.3.19.1.3" class="indexterm"></a>
</span></dt><dd><p>
Sets the time zone used for timestamps written in the server log.
Unlike <a class="xref" href="runtime-config-client.html#GUC-TIMEZONE">TimeZone</a>, this value is cluster-wide,
so that all sessions will report timestamps consistently.
The built-in default is <code class="literal">GMT</code>, but that is typically
overridden in <code class="filename">postgresql.conf</code>; <span class="application">initdb</span>
will install a setting there corresponding to its system environment.
See <a class="xref" href="datatype-datetime.html#DATATYPE-TIMEZONES" title="8.5.3. Time Zones">Section 8.5.3</a> for more information.
This parameter can only be set in the <code class="filename">postgresql.conf</code>
file or on the server command line.
</p></dd></dl></div></div><div class="sect2" id="RUNTIME-CONFIG-LOGGING-CSVLOG"><div class="titlepage"><div><div><h3 class="title">20.8.4. Using CSV-Format Log Output</h3></div></div></div><p>
Including <code class="literal">csvlog</code> in the <code class="varname">log_destination</code> list
provides a convenient way to import log files into a database table.
This option emits log lines in comma-separated-values
(<acronym class="acronym">CSV</acronym>) format,
with these columns:
time stamp with milliseconds,
user name,
database name,
process ID,
client host:port number,
session ID,
per-session line number,
command tag,
session start time,
virtual transaction ID,
regular transaction ID,
error severity,
SQLSTATE code,
error message,
error message detail,
hint,
internal query that led to the error (if any),
character count of the error position therein,
error context,
user query that led to the error (if any and enabled by
<code class="varname">log_min_error_statement</code>),
character count of the error position therein,
location of the error in the PostgreSQL source code
(if <code class="varname">log_error_verbosity</code> is set to <code class="literal">verbose</code>),
application name, backend type, process ID of parallel group leader,
and query id.
Here is a sample table definition for storing CSV-format log output:
</p><pre class="programlisting">
CREATE TABLE postgres_log
(
log_time timestamp(3) with time zone,
user_name text,
database_name text,
process_id integer,
connection_from text,
session_id text,
session_line_num bigint,
command_tag text,
session_start_time timestamp with time zone,
virtual_transaction_id text,
transaction_id bigint,
error_severity text,
sql_state_code text,
message text,
detail text,
hint text,
internal_query text,
internal_query_pos integer,
context text,
query text,
query_pos integer,
location text,
application_name text,
backend_type text,
leader_pid integer,
query_id bigint,
PRIMARY KEY (session_id, session_line_num)
);
</pre><p>
</p><p>
To import a log file into this table, use the <code class="command">COPY FROM</code>
command:
</p><pre class="programlisting">
COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
</pre><p>
It is also possible to access the file as a foreign table, using
the supplied <a class="xref" href="file-fdw.html" title="F.16. file_fdw">file_fdw</a> module.
</p><p>
There are a few things you need to do to simplify importing CSV log
files:
</p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
Set <code class="varname">log_filename</code> and
<code class="varname">log_rotation_age</code> to provide a consistent,
predictable naming scheme for your log files. This lets you
predict what the file name will be and know when an individual log
file is complete and therefore ready to be imported.
</p></li><li class="listitem"><p>
Set <code class="varname">log_rotation_size</code> to 0 to disable
size-based log rotation, as it makes the log file name difficult
to predict.
</p></li><li class="listitem"><p>
Set <code class="varname">log_truncate_on_rotation</code> to <code class="literal">on</code> so
that old log data isn't mixed with the new in the same file.
</p></li><li class="listitem"><p>
The table definition above includes a primary key specification.
This is useful to protect against accidentally importing the same
information twice. The <code class="command">COPY</code> command commits all of the
data it imports at one time, so any error will cause the entire
import to fail. If you import a partial log file and later import
the file again when it is complete, the primary key violation will
cause the import to fail. Wait until the log is complete and
closed before importing. This procedure will also protect against
accidentally importing a partial line that hasn't been completely
written, which would also cause <code class="command">COPY</code> to fail.
</p></li></ol></div><p>
</p></div><div class="sect2" id="RUNTIME-CONFIG-LOGGING-JSONLOG"><div class="titlepage"><div><div><h3 class="title">20.8.5. Using JSON-Format Log Output</h3></div></div></div><p>
Including <code class="literal">jsonlog</code> in the
<code class="varname">log_destination</code> list provides a convenient way to
import log files into many different programs. This option emits log
lines in <acronym class="acronym">JSON</acronym> format.
</p><p>
String fields with null values are excluded from output.
Additional fields may be added in the future. User applications that
process <code class="literal">jsonlog</code> output should ignore unknown fields.
</p><p>
Each log line is serialized as a JSON object with the set of keys and
their associated values shown in <a class="xref" href="runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-JSONLOG-KEYS-VALUES" title="Table 20.3. Keys and Values of JSON Log Entries">Table 20.3</a>.
</p><div class="table" id="RUNTIME-CONFIG-LOGGING-JSONLOG-KEYS-VALUES"><p class="title"><strong>Table 20.3. Keys and Values of JSON Log Entries</strong></p><div class="table-contents"><table class="table" summary="Keys and Values of JSON Log Entries" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Key name</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">timestamp</code></td><td>string</td><td>Time stamp with milliseconds</td></tr><tr><td><code class="literal">user</code></td><td>string</td><td>User name</td></tr><tr><td><code class="literal">dbname</code></td><td>string</td><td>Database name</td></tr><tr><td><code class="literal">pid</code></td><td>number</td><td>Process ID</td></tr><tr><td><code class="literal">remote_host</code></td><td>string</td><td>Client host</td></tr><tr><td><code class="literal">remote_port</code></td><td>number</td><td>Client port</td></tr><tr><td><code class="literal">session_id</code></td><td>string</td><td>Session ID</td></tr><tr><td><code class="literal">line_num</code></td><td>number</td><td>Per-session line number</td></tr><tr><td><code class="literal">ps</code></td><td>string</td><td>Current ps display</td></tr><tr><td><code class="literal">session_start</code></td><td>string</td><td>Session start time</td></tr><tr><td><code class="literal">vxid</code></td><td>string</td><td>Virtual transaction ID</td></tr><tr><td><code class="literal">txid</code></td><td>string</td><td>Regular transaction ID</td></tr><tr><td><code class="literal">error_severity</code></td><td>string</td><td>Error severity</td></tr><tr><td><code class="literal">state_code</code></td><td>string</td><td>SQLSTATE code</td></tr><tr><td><code class="literal">message</code></td><td>string</td><td>Error message</td></tr><tr><td><code class="literal">detail</code></td><td>string</td><td>Error message detail</td></tr><tr><td><code class="literal">hint</code></td><td>string</td><td>Error message hint</td></tr><tr><td><code class="literal">internal_query</code></td><td>string</td><td>Internal query that led to the error</td></tr><tr><td><code class="literal">internal_position</code></td><td>number</td><td>Cursor index into internal query</td></tr><tr><td><code class="literal">context</code></td><td>string</td><td>Error context</td></tr><tr><td><code class="literal">statement</code></td><td>string</td><td>Client-supplied query string</td></tr><tr><td><code class="literal">cursor_position</code></td><td>number</td><td>Cursor index into query string</td></tr><tr><td><code class="literal">func_name</code></td><td>string</td><td>Error location function name</td></tr><tr><td><code class="literal">file_name</code></td><td>string</td><td>File name of error location</td></tr><tr><td><code class="literal">file_line_num</code></td><td>number</td><td>File line number of the error location</td></tr><tr><td><code class="literal">application_name</code></td><td>string</td><td>Client application name</td></tr><tr><td><code class="literal">backend_type</code></td><td>string</td><td>Type of backend</td></tr><tr><td><code class="literal">leader_pid</code></td><td>number</td><td>Process ID of leader for active parallel workers</td></tr><tr><td><code class="literal">query_id</code></td><td>number</td><td>Query ID</td></tr></tbody></table></div></div><br class="table-break" /></div><div class="sect2" id="id-1.6.7.11.8"><div class="titlepage"><div><div><h3 class="title">20.8.6. Process Title</h3></div></div></div><p>
These settings control how process titles of server processes are
modified. Process titles are typically viewed using programs like
<span class="application">ps</span> or, on Windows, <span class="application">Process Explorer</span>.
See <a class="xref" href="monitoring-ps.html" title="28.1. Standard Unix Tools">Section 28.1</a> for details.
</p><div class="variablelist"><dl class="variablelist"><dt id="GUC-CLUSTER-NAME"><span class="term"><code class="varname">cluster_name</code> (<code class="type">string</code>)
<a id="id-1.6.7.11.8.3.1.1.3" class="indexterm"></a>
</span></dt><dd><p>
Sets a name that identifies this database cluster (instance) for
various purposes. The cluster name appears in the process title for
all server processes in this cluster. Moreover, it is the default
application name for a standby connection (see <a class="xref" href="runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES">synchronous_standby_names</a>.)
</p><p>
The name can be any string of less
than <code class="symbol">NAMEDATALEN</code> characters (64 characters in a standard
build). Only printable ASCII characters may be used in the
<code class="varname">cluster_name</code> value. Other characters will be
replaced with question marks (<code class="literal">?</code>). No name is shown
if this parameter is set to the empty string <code class="literal">''</code> (which is
the default). This parameter can only be set at server start.
</p></dd><dt id="GUC-UPDATE-PROCESS-TITLE"><span class="term"><code class="varname">update_process_title</code> (<code class="type">boolean</code>)
<a id="id-1.6.7.11.8.3.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
Enables updating of the process title every time a new SQL command
is received by the server.
This setting defaults to <code class="literal">on</code> on most platforms, but it
defaults to <code class="literal">off</code> on Windows due to that platform's larger
overhead for updating the process title.
Only superusers and users with the appropriate <code class="literal">SET</code>
privilege can change this setting.
</p></dd></dl></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="runtime-config-query.html" title="20.7. Query Planning">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="runtime-config.html" title="Chapter 20. Server Configuration">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="runtime-config-statistics.html" title="20.9. Run-time Statistics">Next</a></td></tr><tr><td width="40%" align="left" valign="top">20.7. Query Planning </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 20.9. Run-time Statistics</td></tr></table></div></body></html>
|
