Re: Change the csv log to 'key:value' to facilitate the user to understanding and processing of logs

On Tue, Mar 15, 2022 at 09:31:19AM +0800, lupeng wrote:
> Dear Hackers
> When I audit the Postgresql database recently, I found that after
> configuring the log type as csv, the output log content is as follows:
> "database ""lp_db1"" does not exist",,,,,"DROP DATABASE
> lp_db1;",,"dropdb, dbcommands.c:841","","client backend",,0 It is very
> inconvenient to understand the real meaning of each field. And in the
> log content，" is escaped as "", which is not friendly to regular
> expression matching. Therefore, I want to modify the csv log function,
> change its format to key:value, assign the content of the non-existing
> field to NULL, and at the same time, " will be escaped as \" in the
> log content. After the modification, the above log format is as
> follows: Log_time:"2022-03-15 09:17:55.289
> CST",User_name:"postgres",Database_name:"lp_db",Process_id:"17995",Remote_host:"192.168.88.130",Remote_port:"38402",Line_number:
> "622fe941.464b",PS_display:"DROP
> DATABASE",Session_start_timestamp:"2022-03-15 09:17:53
> CST",Virtual_transaction_id:"3/2",Transaction_id:"NULL",Error_severity:"ERROR",SQL_state_code
> :"3D000",Errmessage:"database \"lp_db1\" does not
> exist",Errdetail:"NULL",Errhint:"NULL",Internal_query:"NULL",Internal_pos:"0",Errcontext:"NULL",User_query
> :"DROP DATABASE lp_db1;",Cursorpos:"NULL",File_location:"dropdb,
> dbcommands.c:841",Application_name:"NULL",Backend_type:"client
> backend",Leader_PID:"0",Query_id:"0"

CSV format is well documented
(https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG).

If you want named fields you can wait for pg15 and its jsonlog
(https://www.depesz.com/2022/01/17/waiting-for-postgresql-15-introduce-log_destinationjsonlog/).

I, for one, wouldn't want to have to deal with field names repeated in
every single record.

depesz