☰

将多行数据库日志转换为单行

tag-icon • tag-icon text-processing
将多行数据库日志转换为单行

我有一个 Postgres 数据库日志文件，想要将多行日志转换为单行。多行日志具有回车符、换行符和尾随行制表符，必须将其省略。根据我的研究，有两种可能的方法来解决这个问题，要么基于时间戳，要么使用 Tab 作为枢轴。没有时间戳的每一行中都有一个制表符 ASCII 字符。该选项卡始终位于开头且位于相同位置，这有助于理解日志行来自同一块，否则如果存在新时间戳，则日志块会中断。

这是我的日志文件：

日志档案：

2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;

2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15

2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;

2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement

    ;

2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

           pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

           pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

           d.datcollate as "Collate",

           d.datctype as "Ctype",

           pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"

    FROM pg_catalog.pg_database d

    ORDER BY 1;

2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

           pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

           pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

           d.datcollate as "Collate",

           d.datctype as "Ctype",

           pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"

    FROM pg_catalog.pg_database d

    ORDER BY 1;

2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

         pg_catalog.pg_get_userbyid(d.datdba) as "Owner",

           pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",

           d.datcollate as "Collate",

           d.datctype as "Ctype",

           pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"

    FROM pg_catalog.pg_database d

    ORDER BY 1;

2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1

2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear

    clear

    select

    ;

预期解决方案：

2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;
2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15
2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;
2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate",d.datctype as "Ctype",pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate",d.datctype as "Ctype",pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate",d.datctype as "Ctype",pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1
2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear clear select;

我也尝试了以下方法线 代码 ：

sed -e '$!N;/^\t.*\n/D' -e 'P;D' db_logs.txt > output.txt

它给了我以下输出：

22-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;

2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15

2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;

2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement

2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name",

2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1

2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear

2022-11-03 17:08:01.281 PKT [5221] LOG:  statement: select

2022-11-03 17:11:31.760 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:11:52.884 PKT [5221] LOG:  statement: select

不幸的是，它删除了该行的其余部分。我想要一个基于纯 Linux 工具的解决方案。无法使用外部工具，因此我使用了awk和sed。

答案1

使用任何 POSIX awk：

$ awk -v ORS= '/^[^[:space:]]/{print sep; sep=RS} {sub(/^[[:space:]]+|[[:space:]]+$/," "); print} END{print sep}' file
2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;
2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15
2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;
2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement ;
2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;
2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;
2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1
2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear clear select ;

或者如果由于某种原因您确实希望数据行之间有一个空行：

$ awk -v ORS= '/^[^[:space:]]/{print sep; sep=RS RS} {sub(/^[[:space:]]+|[[:space:]]+$/," "); print} END{print sep}' file
2022-11-03 16:15:54.330 PKT [3421] LOG:  statement: select * from customersabc;

2022-11-03 16:15:54.330 PKT [3421] ERROR:  relation "customersabc" does not exist at character 15

2022-11-03 16:15:54.330 PKT [3421] STATEMENT:  select * from customersabc;

2022-11-03 16:58:26.796 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 16:58:56.674 PKT [5221] LOG:  statement: show log_statement ;

2022-11-03 17:01:47.331 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;

2022-11-03 17:03:08.477 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;

2022-11-03 17:03:29.546 PKT [5221] LOG:  statement: SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner", pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", d.datcollate as "Collate", d.datctype as "Ctype", pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges" FROM pg_catalog.pg_database d ORDER BY 1;

2022-11-03 17:03:36.142 PKT [5221] LOG:  statement: show log_statement;

2022-11-03 17:07:30.703 PKT [5221] ERROR:  syntax error at or near "clear" at character 1

2022-11-03 17:07:30.703 PKT [5221] STATEMENT:  clear clear select ;

答案2

我能够使用以下脚本解决该问题。

awk -v ORS="" '                 
    /^\r?$/ {next}              
    f && !/^\t/ {print "\n"}     
    {
        sub(/\r$/, " ")          
        print                   
        if ($0 ~ /\r/) f = 0    
        else f = 1             
    }
    END {
        print "\n"              
    }
' db_logs.txt > output.txt 
awk 'BEGIN{FS="\t+"; OFS="\t"} {$1=$1; print}' output.txt > out.txt
awk '{ gsub(/ /," "); print }' out.txt > oyt.txt 
awk '{ gsub(/^[ \t]+|[ \t]+$/, ""); print }' oyt.txt > new.txt

相关内容