From fd801f4faa8e0f00bc314b16549e3d8e8aa1b653 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Sun, 19 Aug 2007 01:41:25 +0000 Subject: 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. --- doc/src/sgml/config.sgml | 139 ++++++++++++++++++++++++++++++++++++------ doc/src/sgml/func.sgml | 8 +-- doc/src/sgml/maintenance.sgml | 7 ++- 3 files changed, 130 insertions(+), 24 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 0259e4c31f0..1f0e2005c0f 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1,4 +1,4 @@ - + Server Configuration @@ -2261,7 +2261,7 @@ SELECT * FROM parent WHERE key = 2400; PostgreSQL supports several methods for logging server messages, including - stderr and + stderr, csvlog and syslog. On Windows, eventlog is also supported. Set this parameter to a list of desired log destinations separated by @@ -2270,17 +2270,24 @@ SELECT * FROM parent WHERE key = 2400; This parameter can only be set in the postgresql.conf file or on the server command line. + If log_destination is set to csvlog, + 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. + - - redirect_stderr (boolean) + + logging_collector (boolean) - redirect_stderr configuration parameter + logging_collector configuration parameter - This parameter allows messages sent to stderr to be + This parameter allows messages sent to stderr, + and CSV logs, to be captured and redirected into log files. This method, in combination with logging to stderr, is often more useful than @@ -2288,6 +2295,8 @@ SELECT * FROM parent WHERE key = 2400; might not appear in syslog output (a common example is dynamic-linker failure messages). This parameter can only be set at server start. + start_log_collector must be enabled to generate + CSV logs. @@ -2299,8 +2308,8 @@ SELECT * FROM parent WHERE key = 2400; - When redirect_stderr is enabled, this parameter - determines the directory in which log files will be created. + When logging_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 postgresql.conf @@ -2316,8 +2325,8 @@ SELECT * FROM parent WHERE key = 2400; - When redirect_stderr is enabled, this parameter - sets the file names of the created log files. The value + When logging_collector is enabled, + this parameter sets the file names of the created log files. The value is treated as a strftime pattern, so %-escapes can be used to specify time-varying file names. (Note that if there are @@ -2332,6 +2341,14 @@ SELECT * FROM parent WHERE key = 2400; This parameter can only be set in the postgresql.conf file or on the server command line. + + If log_destination is set to csvlog, + .csv will be appended to the timestamped + log_filename to create the final log file name. + (If log_filename ends in .log, the suffix is overwritten.) + In the case of the example above, the + file name will be server_log.1093827753.csv + @@ -2342,8 +2359,8 @@ SELECT * FROM parent WHERE key = 2400; - When redirect_stderr is enabled, this parameter - determines the maximum lifetime of an individual log file. + When logging_collector is enabled, + this parameter determines the maximum lifetime of an individual log file. After this many minutes have elapsed, a new log file will be created. Set to zero to disable time-based creation of new log files. @@ -2360,8 +2377,8 @@ SELECT * FROM parent WHERE key = 2400; - When redirect_stderr is enabled, this parameter - determines the maximum size of an individual log file. + When logging_collector is enabled, + this parameter determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created. Set to zero to disable size-based creation of new log files. @@ -2378,8 +2395,8 @@ SELECT * FROM parent WHERE key = 2400; - When redirect_stderr is enabled, this parameter will cause - PostgreSQL to truncate (overwrite), + When logging_collector is enabled, + this parameter will cause PostgreSQL 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 @@ -2599,7 +2616,7 @@ SELECT * FROM parent WHERE key = 2400; The server's standard output and standard error are redirected to /dev/null, so any messages sent to them will be lost. Unless syslog logging is selected or - redirect_stderr is enabled, using this parameter + start_log_collector is enabled, using this parameter is discouraged because it makes it impossible to see error messages. This parameter can only be set at server start. @@ -3066,6 +3083,94 @@ SELECT * FROM parent WHERE key = 2400; + + + Using the csvlog + + + Including csvlog in the log_destination list + provides a convenient way to import log files into a database table. + Here is a sample table definition for storing csvlog output: + + + +CREATE TABLE postgres_log +( + log_time timestamp, + username text, + database_name text, + sessionid text not null, + connection_from text, + process_id text, + process_line_num int not null, + command_tag text, + session_start_time timestamp, + transaction_id int, + error_severity text, + sql_state_code text, + statement text, + PRIMARY KEY (sessionid, process_line_num) +); + + + + + In order to import into this table, use the COPY FROM command: + + + +COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; + + + + There are a few things you need to import csvlog files easily and + automatically: + + + + + Use a consistant, predictable naming scheme for your log files + with log_filename. This lets you predict what +the file name will be when it is ready to be imported. +guess what + the file name will be and know when an individual log file is + complete and therefore ready to be imported. + + + + + + Set log_rotation_size to 0 to disable + size-based log rotation, as it makes the log filename difficult + to predict. + + + + + + Set log_truncate_on_rotate = on so that old + log data isn't mixed with the new in the same file. + + + + + + The example above includes a useful primary key on the log + file data, which will protect against accidentally importing + the same information twice. The COPY command commits all of + the data it imports at one time, and any single 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 + import. This will also protect against accidently importing a + partial line that hasn't been completely written, which would + also cause the COPY to fail. + + + + + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 52858efc600..97b7160113e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -11254,9 +11254,9 @@ SELECT set_config('log_statement_stats', 'off', false); pg_rotate_logfile signals the log-file manager to switch - to a new output file immediately. This works only when - redirect_stderr is used for logging, since otherwise there - is no log-file manager subprocess. + to a new output file immediately. This works only when the built-in + log collector if running, since otherwise there is no log-file manager + subprocess. diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 40e9527d0cf..8090bbe8387 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -1,4 +1,4 @@ - + Routine Database Maintenance Tasks @@ -691,10 +691,11 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu A better approach is to send the server's stderr output to some type of log rotation program. There is a built-in log rotation program, which you can use by - setting the configuration parameter redirect_stderr to + setting the configuration parameter logging_collector to true in postgresql.conf. The control parameters for this program are described in . + linkend="runtime-config-logging-where">. You can also use this approach + to capture the log data in machine readable CSV format. -- cgit v1.2.3