The following bug has been logged on the website:
Bug reference: 17543
Logged by: Nathan Ballance
Email address: nathanfballance@gmail.com
PostgreSQL version: 14.4
Operating system: Linux/UNIX
Description:
Postgresql server with csvlog log_destination enabled will have malformed
CSV upon a disk space error. This causes any loading of the malformed *.csv
log file to error
Steps to Reproduce:
*Applies to all postgresql versions. Tested using git mirror -
https://github.com/postgres/postgres
Modify 'postgresql.conf' for the following parameter changes:
log_destination = 'stderr,csvlog'
logging_collector = on
Cause system to run out of disk space
postgresql-2022-XX-XX_XXXXXX.csv is now in malformed format
Returns error when executing \copy postgres_log FROM
postgresql-2022-XX-XX_XXXXXX.csv csv
Testing example:
template1=# \copy postgres_log FROM
'/home/ec2-user/postgres/test_postgres/log/postgresql-2022-07-08_012958.csv'
csv
ERROR: unterminated CSV quoted field
CONTEXT: COPY postgres_log, line 107: "2022-07-08 01:41:59.246
UTC,,,12154,,62c78b67.2f7a,1,,2022-07-08 01:41:59
UTC,4/17,0,ERROR,53100,"co..."
template1=#
In postgresql-2022-XX-XX_XXXXXX.csv the line will not terminate in proper
CSV format
Log snippet from postgresql-2022-07-08_012958.csv
=================
2022-07-08 01:41:49.969
UTC,"ec2-user","template1",12152,"[local]",62c78b2f.2f78,1,"idle",2022-07-08
01:41:03 UTC,3/51,0,ERROR,42601,"syntax error at or near ""IF""",,,,,,"D
ROP TABLE test_5 IF EXISTS;",19,,"psql","client backend",,0
2022-07-08 01:41:59.246 UTC,,,12154,,62c78b67.2f7a,1,,2022-07-08 01:41:59
UTC,4/17,0,ERROR,53100,"could not extend file ""base/1/16389_vm"": No space
left on device",,"Check
free disk space.",,,"while scanning block 32672 of relation
""public.test_5""
automatic vacuum of table ""template1.public.test_5""",,,,"","autovacuum
wor2022-07-08 01:42:44.035 UTC,,,12073,,62c78896.2f29,31,,2022-07-08
01:29:58 UTC,,0,LOG,00000,"chec
kpoint complete: wrote 4169 buffers (25.4%); 0 WAL file(s) added, 0 removed,
33 recycled; write=269.903 s, sync=0.002 s, total=269.972 s; sync files=2,
longest=0.002 s, aver
age=0.001 s; distance=538440 kB, estimate=540448 kB; lsn=1/7E026E90, redo
lsn=1/6C1D0148",,,,,,,,,"","checkpointer",,0
2022-07-08 01:43:14.040 UTC,,,12073,,62c78896.2f29,32,,2022-07-08 01:29:58
UTC,,0,LOG,00000,"checkpoint starting: time",,,,,,,,,"","checkpointer",,0
=================
From the above the malformed log line is:
autovacuum wor2022-07-08 01:42:44.035 UTC
This thread is writing 'autovacuum worker' but terminates but does not fully
write to CSV file and next log line is started. This is malformed.
Please note, when testing that the exact location when this terminates is
not repeatable, however, the CSV will always be malformed upon a disk space
error
ASK: Can the CSV file be written to in a safer way which ensures proper
format even upon disk error?