Using CSV Log Output
Prerequisites
- The log_destination parameter is set to csvlog.
- The logging_collector parameter is set to on.
Definition of csvlog
Log lines are emitted in comma separated values (CSV) format.
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 | CREATE TABLE postgres_log ( log_time timestamp(3) with time zone, node_name text, user_name text, database_name text, process_id bigint, 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, query_id bigint, module text, 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 ); |
For details, see Table 1.
| Field | Description | Field | Description |
|---|---|---|---|
| log_time | Timestamp in milliseconds | module | Log module |
| node_name | Node name | error_severity | ERRORSTATE code |
| user_name | Username | sql_state_code | SQLSTATE code |
| database_name | Database name | message | Error message |
| process_id | Process ID | detail | Detailed error message |
| connection_from | Port number of the client host | hint | Prompt message |
| session_id | Session ID | internal_query | Internal query (This field is used to query the information leading to errors if any.) |
| session_line_num | Number of lines in each session | internal_query_pos | Pointer for an internal query |
| command_tag | Command tag | context | Environment |
| session_start_time | Start time of a session | query | Character count at the position where errors occur |
| virtual_transaction_id | Regular transaction | query_pos | Pointer at the position where errors occur |
| transaction_id | Transaction ID | location | Position where errors occur in the GaussDB source code if log_error_verbosity is set to verbose |
| query_id | Query ID | application_name | Application name |
1 | COPY postgres_log FROM '/opt/data/pg_log/logfile.csv' WITH csv; |
The log name (logfile.csv) here needs to be replaced with the name of a log generated.
Simplifying Input
Simplify importing CSV log files by performing the following operations:
- Set log_filename and log_rotation_age to provide a consistent, predictable naming solution for log files. By doing this, you can predict when an individual log file is complete and ready to be imported.
- Set log_rotation_size to 0 to disable size-based log rollback, as it makes the log file name difficult to predict.
- Set log_truncate_on_rotation to on so that old log data cannot be mixed with the new one in the same file.
