Re: [PATCHES] final CSVlog patch
Looks like a nice feature for 8.3! I've looked over what I could. A typo and a couple of wording suggestions below. On Aug 10, 2007, at 21:21 , Andrew Dunstan wrote: *** *** 2280,2285 --- 2287,2293 might not appear in applicationsyslog/ output (a common example is dynamic-linker failure messages). This parameter can only be set at server start. +It is required to be on if CSV logs are to be generated. varnamestart_log_collector/varname must be on to generate CSV logs. *** *** 2324,2329 --- 2332,2344 This parameter can only be set in the filenamepostgresql.conf/ file or on the server command line. /para +para + If varnamelog_destination/varname is set to systemitemcsvlog/systemitem, + literal.csv/literal will be appended to the file name, to obtain the lofile s/lofile/logfile/ If log_destination ... just overwritten. might be rewritten as If varnamelog_destination/varname is set to systemitemcsvlog/ systemitem, literal.csv/literal will be appended to the timestamped varnamelog_filename/varname to create the final log file name. (If log_filename ends in .log, the .log suffix is overwritten.) +para + Putting csvlog into the log_destination list gives an + easy way to import the log files into a database table. + Here is a sample table that csvlog output could be + copied into: +/para Including csvlog in the log_destination list provides an convenient way to import log files into a database table. Here is a sample table to store csvlog output. + CREATE TABLE pglog CREATE TABLE postgres_log (pglog might be confused with the pg_ prefixed system catalogs.) + programlisting + COPY pglog FROM '/full/path/to/logfile.csv' WITH csv; COPY postgres_log FROM /full/path/to/logfile.csv' WITH csv; + /programlisting + +para +There are a few things you will need to do in order for +csvlog importing to produced files that you can import +easily and automatically: There are a few things you need to import csvlog files easily and automatically. + +orderedlist + listitem +para + Use a consistant, predictable naming scheme for your log files + with varnamelog_filename/varname. This lets you guess what + the file name will be and know when an individual log file is + complete and therefore ready to be imported. This lets you predict what the file name will be when it is ready to be imported. (*When* the log file is ready would be controlled by log_rotation_age and perhaps other factors and not by the log_filename, wouldn't it?) + listitem +para + Set varnamelog_rotation_size/varname = 0 to disable size-based + log rotation. That feature makes it more difficult to predict what + the log file name you need to import will be. Set varnamelog_rotation_size/varname to 0 to disable size-based log rotation, as it makes the log filename difficult to predict. Therefore, if you import +a partial log file, then later import the entire thing, +the unique index violation will cause the import to fail. +You need to wait until the log file is complete and closed +before trying to import the whole thing. Operating that way +will also protect you from accidentally trying to import +a partial line that hasn't been completely written yet, which +will also cause the COPY to fail. If you import a partial log file and later import the file again when its complete, the primary key violation will cause the import to fail. Wait until the log is complete and closed before import. This will also protect against accidently importing an partial line that hasn't been completely written, which would also cause the COPY to fail. Index: doc/src/sgml/func.sgml --- 11244,11251 para functionpg_rotate_logfile/ signals the log-file manager to switch ! to a new output file immediately. This works only when the built-in ! log collector, is used for logging, since otherwise there is no log-file manager subprocess. functionpg_rotate_logifle/ signals the log-file manager to switch immediately switch to a new output file. This works only when using the built-in log collector, as otherwise there is no log-file manager subprocess. *** src/include/postmaster/syslogger.h 25 Jul 2007 12:22:53 - 1.10 --- src/include/postmaster/syslogger.h 11 Aug 2007 02:01:20 - *** *** 24,32 * also cope with non-protocol data coming down the pipe, though we cannot * guarantee long strings won't get split apart. * ! * We use 't' or 'f' instead of a bool for is_last to
[PATCHES] final CSVlog patch
I think this is ready to be committed now.It's been a long and tiresome road ;-) Last-minute comments welcome. cheers andrew Index: doc/src/sgml/config.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.136 diff -c -r1.136 config.sgml *** doc/src/sgml/config.sgml 4 Aug 2007 01:26:53 - 1.136 --- doc/src/sgml/config.sgml 11 Aug 2007 02:00:58 - *** *** 2253,2259 para productnamePostgreSQL/productname supports several methods for logging server messages, including ! systemitemstderr/systemitem and systemitemsyslog/systemitem. On Windows, systemitemeventlog/systemitem is also supported. Set this parameter to a list of desired log destinations separated by --- 2253,2259 para productnamePostgreSQL/productname supports several methods for logging server messages, including ! systemitemstderr/systemitem, systemitemcsvlog/systemitem and systemitemsyslog/systemitem. On Windows, systemitemeventlog/systemitem is also supported. Set this parameter to a list of desired log destinations separated by *** *** 2262,2278 This parameter can only be set in the filenamepostgresql.conf/ file or on the server command line. /para /listitem /varlistentry ! varlistentry id=guc-redirect-stderr xreflabel=redirect_stderr ! termvarnameredirect_stderr/varname (typeboolean/type)/term indexterm !primaryvarnameredirect_stderr/ configuration parameter/primary /indexterm listitem para ! This parameter allows messages sent to applicationstderr/ to be captured and redirected into log files. This method, in combination with logging to applicationstderr/, is often more useful than --- 2262,2285 This parameter can only be set in the filenamepostgresql.conf/ file or on the server command line. /para +para If varnamelog_destination/ is set to systemitemcsvlog/systemitem, + the log is output as comma seperated values. The format is: + timestamp with milliseconds, username, database name, session id, host:port number, + process id, per process line number, command tag, session start time, transaction id, + error severity, SQL state code, statement/error message. +/para /listitem /varlistentry ! varlistentry id=guc-start-log-collector xreflabel=start_log_collector ! termvarnamestart_log-collector/varname (typeboolean/type)/term indexterm !primaryvarnamestart_log_collector/ configuration parameter/primary /indexterm listitem para ! This parameter allows messages sent to applicationstderr/, ! and CSV logs, to be captured and redirected into log files. This method, in combination with logging to applicationstderr/, is often more useful than *** *** 2280,2285 --- 2287,2293 might not appear in applicationsyslog/ output (a common example is dynamic-linker failure messages). This parameter can only be set at server start. + It is required to be on if CSV logs are to be generated. /para /listitem /varlistentry *** *** 2291,2298 /indexterm listitem para ! When varnameredirect_stderr/ 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 filenamepostgresql.conf/ --- 2299,2306 /indexterm listitem para ! When varnamestart_log_collector/ 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 filenamepostgresql.conf/ *** *** 2308,2315 /indexterm listitem para ! When varnameredirect_stderr/varname is enabled, this parameter ! sets the file names of the created log files. The value is treated as a systemitemstrftime/systemitem pattern, so literal%/literal-escapes can be used to specify time-varying file names. (Note that if there are --- 2316,2323 /indexterm listitem para ! When varnamestart_log_collector/varname is enabled, ! this parameter sets the file names of the created log files. The value is treated as a
