8000 Provide for logfiles in machine readable CSV format. In consequence, … · postgres/postgres@fd801f4 · GitHub
[go: up one dir, main page]
Skip to content

Commit fd801f4

Browse files
adunstanadunstan
committed
Provide for logfiles in machine readable CSV format. In consequence, rename
redirect_stderr to logging_collector. Original patch from Arul Shaji, subsequently modified by Greg Smith, and then heavily modified by me.
1 parent e53a548 commit fd801f4

File tree

12 files changed

+663
-127
lines changed

12 files changed

+663
-127
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 122 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.137 2007/08/13 19:27:11 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.138 2007/08/19 01:41:23 adunstan Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -2261,7 +2261,7 @@ SELECT * FROM parent WHERE key = 2400;
22612261
<para>
22622262
<productname>PostgreSQL</productname> supports several methods
22632263
for logging server messages, including
2264-
<systemitem>stderr</systemitem> and
2264+
<systemitem>stderr</systemitem>, <systemitem>csvlog</systemitem> and
22652265
<systemitem>syslog</systemitem>. On Windows,
22662266
<systemitem>eventlog</systemitem> is also supported. Set this
22672267
parameter to a list of desired log destinations separated by
@@ -2270,24 +2270,33 @@ SELECT * FROM parent WHERE key = 2400;
22702270
This parameter can only be set in the <filename>postgresql.conf</>
22712271
file or on the server command line.
22722272
</para>
2273+
<para> If <varname>log_destination</> is set to <systemitem>csvlog</systemitem>,
2274+
the log is output as comma seperated values. The format is:
2275+
timestamp with milliseconds, username, database name, session id, host:port number,
2276+
process id, per process line number, command tag, session start time, transaction id,
2277+
error severity, SQL state code, statement/error message.
2278+
</para>
22732279
</listitem>
22742280
</varlistentry>
22752281

2276-
<varlistentry id="guc-redirect-stderr" xreflabel="redirect_stderr">
2277-
<term><varname>redirect_stderr</varname> (<type>boolean</type>)</term>
2282+
<varlistentry id="guc-logging-collector" xreflabel="logging_collector">
2283+
<term><varname>logging_collector</varname> (<type>boolean</type>)</term>
22782284
<indexterm>
2279-
<primary><varname>redirect_stderr</> configuration parameter</primary>
2285+
<primary><varname>logging_collector</> configuration parameter</primary>
22802286
</indexterm>
22812287
<listitem>
22822288
<para>
2283-
This parameter allows messages sent to <application>stderr</> to be
2289+
This parameter allows messages sent to <application>stderr</>,
2290+
and CSV logs, to be
22842291
captured and redirected into log files.
22852292
This method, in combination with logging to <application>stderr</>,
22862293
is often more useful than
22872294
logging to <application>syslog</>, since some types of messages
22882295
might not appear in <application>syslog</> output (a common example
22892296
is dynamic-linker failure messages).
22902297
This parameter can only be set at server start.
2298+
<varname>start_log_collector</varname> must be enabled to generate
2299+
CSV logs.
22912300
</para>
22922301
</listitem>
22932302
</varlistentry>
@@ -2299,8 +2308,8 @@ SELECT * FROM parent WHERE key = 2400;
22992308
</indexterm>
23002309
<listitem>
23012310
<para>
2302-
When <varname>redirect_stderr</> is enabled, this parameter
2303-
determines the directory in which log files will be created.
2311+
When <varname>logging_collector</> is enabled,
2312+
this parameter determines the directory in which log files will be created.
23042313
It can be specified as an absolute path, or relative to the
23052314
cluster data directory.
23062315
This parameter can only be set in the <filename>postgresql.conf</>
@@ -2316,8 +2325,8 @@ SELECT * FROM parent WHERE key = 2400;
23162325
</indexterm>
23172326
<listitem>
23182327
<para>
2319-
When <varname>redirect_stderr</varname> is enabled, this parameter
2320-
sets the file names of the created log files. The value
2328+
When <varname>logging_collector</varname> is enabled,
2329+
this parameter sets the file names of the created log files. The value
23212330
is treated as a <systemitem>strftime</systemitem> pattern,
23222331
so <literal>%</literal>-escapes can be used to specify time-varying
23232332
file names. (Note that if there are
@@ -2332,6 +2341,14 @@ SELECT * FROM parent WHERE key = 2400;
23322341
This parameter can only be set in the <filename>postgresql.conf</>
23332342
file or on the server command line.
23342343
</para>
2344+
<para>
2345+
If <varname>log_destination</> is set to <systemitem>csvlog</>,
2346+
<literal>.csv</> will be appended to the timestamped
2347+
<varname>log_filename</> to create the final log file name.
2348+
(If log_filename ends in <literal>.log</>, the suffix is overwritten.)
2349+
In the case of the example above, the
2350+
file name will be <literal>server_log.1093827753.csv</literal>
2351+
</para>
23352352
</listitem>
23362353
</varlistentry>
23372354

@@ -2342,8 +2359,8 @@ SELECT * FROM parent WHERE key = 2400;
23422359
</indexterm>
23432360
<listitem>
23442361
<para>
2345-
When <varname>redirect_stderr</varname> is enabled, this parameter
2346-
determines the maximum lifetime of an individual log file.
2362+
When <varname>logging_collector</varname> is enabled,
2363+
this parameter determines the maximum lifetime of an individual log file.
23472364
After this many minutes have elapsed, a new log file will
23482365
be created. Set to zero to disable time-based creation of
23492366
new log files.
@@ -2360,8 +2377,8 @@ SELECT * FROM parent WHERE key = 2400;
23602377
</indexterm>
23612378
<listitem>
23622379
<para>
2363-
When <varname>redirect_stderr</varname> is enabled, this parameter
2364-
determines the maximum size of an individual log file.
2380+
When <varname>logging_collector</varname> is enabled,
2381+
this parameter determines the maximum size of an individual log file.
23652382
After this many kilobytes have been emitted into a log file,
23662383
a new log file will be created. Set to zero to disable size-based
23672384
creation of new log files.
@@ -2378,8 +2395,8 @@ SELECT * FROM parent WHERE key = 2400;
23782395
</indexterm>
23792396
<listitem>
23802397
<para>
2381-
When <varname>redirect_stderr</varname> is enabled, this parameter will cause
2382-
<productname>PostgreSQL</productname> to truncate (overwrite),
2398+
When <varname>logging_collector</varname> is enabled,
2399+
this parameter will cause <productname>PostgreSQL</productname> to truncate (overwrite),
23832400
rather than append to, any existing log file of the same name.
23842401
However, truncation will occur only when a new file is being opened
23852402
due to time-based rotation, not during server startup or size-based
@@ -2599,7 +2616,7 @@ SELECT * FROM parent WHERE key = 2400;
25992616
The server's standard output and standard error are redirected
26002617
to <literal>/dev/null</>, so any messages sent to them will be lost.
26012618
Unless <application>syslog</> logging is selected or
2602-
<varname>redirect_stderr</> is enabled, using this parameter
2619+
<varname>start_log_collector</> is enabled, using this parameter
26032620
is discouraged because it makes it impossible to see error messages.
26042621
This parameter can only be set at server start.
26052622
</para>
@@ -3066,6 +3083,94 @@ SELECT * FROM parent WHERE key = 2400;
30663083
</varlistentry>
30673084

30683085
</variablelist>
3086+
</sect2>
3087+
<sect2 id="runtime-config-logging-csvlog">
3088+
<title>Using the csvlog</title>
3089+
3090+
<para>
3091+
Including <literal>csvlog</> in the <varname>log_destination</> list
3092+
provides a convenient way to import log files into a database table.
3093+
Here is a sample table definition for storing csvlog output:
3094+
</para>
3095+
3096+
<programlisting>
3097+
CREATE TABLE postgres_log
3098+
(
3099+
log_time timestamp,
3100+
username text,
3101+
database_name text,
3102+
sessionid text not null,
3103+
connection_from text,
3104+
process_id text,
3105+
process_line_num int not null,
3106+
command_tag text,
3107+
session_start_time timestamp,
3108+
transaction_id int,
3109+
error_severity text,
3110+
sql_state_code text,
3111+
statement text,
3112+
PRIMARY KEY (sessionid, process_line_num)
3113+
);
3114+
</programlisting>
3115+
3116+
3117+
<para>
3118+
In order to import into this table, use the COPY FROM command:
3119+
</para>
3120+
3121+
<programlisting>
3122+
COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv;
3123+
</programlisting>
3124+
3125+
<para>
3126+
There are a few things you need to import csvlog files easily and
3127+
automatically:
3128+
3129+
<orderedlist>
3130+
<listitem>
3131+
<para>
3132+
Use a consistant, predictable naming scheme for your log files
3133+
with <varname>log_filename</varname>. This lets you predict what
3134+
the file name will be when it is ready to be imported.
3135+
guess what
3136+
the file name will be and know when an individual log file is
3137+
complete and therefore ready to be imported.
3138+
</para>
3139+
</listitem>
3140+
3141+
<listitem>
3142+
<para>
3143+
Set <varname>log_rotation_size</varname> to 0 to disable
3144+
size-based log rotation, as it makes the log filename difficult
3145+
to predict.
3146+
</para>
3147+
</listitem>
3148+
3149+
<listitem>
3150+
<para>
3151+
Set <varname>log_truncate_on_rotate</varname> = on so that old
3152+
log data isn't mixed with the new in the same file.
3153+
</para>
3154+
</listitem>
3155+
3156+
<listitem>
3157+
<para>
3158+
The example above includes a useful primary key on the log
3159+
file data, which will protect against accidentally importing
3160+
the same information twice. The COPY command commits all of
3161+
the data it imports at one time, and any single error will
3162+
cause the entire import to fail.
3163+
If you import a partial log file and later import the file again
3164+
when it is complete, the primary key violation will cause the
3165+
import to fail. Wait until the log is complete and closed before
3166+
import. This will also protect against accidently importing a
3167+
partial line that hasn't been completely written, which would
3168+
also cause the COPY to fail.
3169+
</para>
3170+
</listitem>
3171+
</orderedlist>
3172+
</para>
3173+
30693174
</sect2>
30703175
</sect1>
30713176

‎doc/src/sgml/func.sgml

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.385 2007/08/13 01:18:47 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.386 2007/08/19 01:41:23 adunstan Exp $ -->
22

33
<chapter id="functions">
44
<title>Functions and Operators</title>
@@ -11254,9 +11254,9 @@ SELECT set_config('log_statement_stats', 'off', false);
1125411254

1125511255
<para>
1125611256
<function>pg_rotate_logfile</> signals the log-file manager to switch
11257-
to a new output file immediately. This works only when
11258-
<varname>redirect_stderr</> is used for logging, since otherwise there
11259-
is no log-file manager subprocess.
11257+
to a new output file immediately. This works only when the built-in
11258+
log collector if running, since otherwise there is no log-file manager
11259+
subprocess.
1126011260
</para>
1126111261

1126211262
<indexterm zone="functions-admin">

‎doc/src/sgml/maintenance.sgml

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.77 2007/07/23 17:22:00 alvherre Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/maintenance.sgml,v 1.78 2007/08/19 01:41:24 adunstan Exp $ -->
22

33
<chapter id="maintenance">
44
<title>Routine Database Maintenance Tasks</title>
@@ -691,10 +691,11 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu
691691
A better approach is to send the server's
692692
<systemitem>stderr</> output to some type of log rotation program.
693693
There is a built-in log rotation program, which you can use by
694-
setting the configuration parameter <literal>redirect_stderr</> to
694+
setting the configuration parameter <literal>logging_collector</> to
695695
<literal>true</> in <filename>postgresql.conf</>. The control
696696
parameters for this program are described in <xref
697-
linkend="runtime-config-logging-where">.
697+
linkend="runtime-config-logging-where">. You can also use this approach
698+
to capture the log data in machine readable CSV format.
698699
</para>
699700

700701
<para>

‎src/backend/postmaster/postmaster.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
*
3838
*
3939
* IDENTIFICATION
40-
* $PostgreSQL: pgsql/src/backend/postmaster/postmaster.c,v 1.540 2007/08/09 01:18:43 tgl Exp $
40+
* $PostgreSQL: pgsql/src/backend/postmaster/postmaster.c,v 1.541 2007/08/19 01:41:24 adunstan Exp $
4141
*
4242
* NOTES
4343
*
@@ -1282,8 +1282,8 @@ ServerLoop(void)
12821282
}
12831283
}
12841284

1285-
/* If we have lost the system logger, try to start a new one */
1286-
if (SysLoggerPID == 0 && Redirect_stderr)
1285+
/* If we have lost the log collector, try to start a new one */
1286+
if (SysLoggerPID == 0 && Logging_collector)
12871287
SysLoggerPID = SysLogger_Start();
12881288

12891289
/*

0 commit comments

Comments
 (0)
0