This morning I noticed this error in my PG CSV log file.
2022-05-25 23:59:17.776 JST,,,117110,,628cf2c5.1c976,1,,2022-05-25 23:59:17 JST,9/611296,0,ERROR,XX001,"uncommitted xmin 16395356 from before xid cutoff 144683296 needs to be frozen",,,,,"while scanning block 19267 of relation ""relation_name""
This was a data corruption error that I resolved by truncating the table and reloading it. However, I was curious why this message was not sent to my syslog.
My logging configuration in the postgresql.conf is as follows:
# Logging settings
# ----------------
log_destination = 'csvlog,syslog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql_%Y%m%d.log'
log_truncate_on_rotation = off
log_rotation_age = 1h
log_rotation_size = 0
log_timezone = 'Japan'
log_line_prefix = '%t [%p]: [%l-1] %h:%u@%d:[PG]:CODE:%e '
log_statement = 'all'
log_min_messages = info # DEBUG5
log_min_error_statement = info # DEBUG5
log_error_verbosity = default
log_checkpoints = on
log_lock_waits = on
log_temp_files = 0
log_connections = on
log_disconnections = on
log_duration = off
log_min_duration_statement = 1000
log_autovacuum_min_duration = 3000ms
The OS info for the server on which this DB is running is as follows:
LSB Version: :core-4.1-amd64:core-4.1-noarch
Distributor ID: CentOS
Description: CentOS Linux release 7.9.2009 (Core)
Release: 7.9.2009
Codename: Core
And there is a filter defined for the rsyslog messages as follows:
if $programname == 'postgres' and \
($msg contains 'CODE:28000' or \
$msg contains 'CODE:28P01' or \
$msg contains 'CODE:3D000' or \
$msg contains 'CODE:08006' or \
$msg contains 'CODE:42501' \
) then /xxxx/xxxx/xxx/pg_log/postgres_filter.log
if $programname == 'postgres' and \
( \
not ($msg contains '[PG]') or \
$msg contains 'CODE:00000' or \
$msg contains 'CODE:28000' or \
$msg contains 'CODE:28P01' or \
$msg contains 'CODE:3D000' or \
$msg contains 'CODE:42501' or \
$msg contains 'CODE:42601' or \
$msg contains 'CODE:42P01' or \
$msg contains 'CODE:42P02' or \
$msg contains 'CODE:08006' or \
$msg contains 'CODE:42703' \
) then stop
syslog_ident and syslog_facility is as follows:
postgres=# show syslog_ident ;
syslog_ident
--------------
postgres
(1 row)
postgres=# show syslog_facility ;
syslog_facility
-----------------
local0
(1 row)
I was thinking that such an error message would be sent to the /var/log/message, from where it would be picked, but this wasn't the case. Could someone explain why this is so and what can I change for these messages to be sent to syslog?
/var/log/messages2022-05-26T16:33:12.680318+09:00 node03 postgres[2157]: [12-1] 2022-05-26 16:33:12 JST [2157]: [5-1] [local]:postgres@testdb:[PG]:CODE:58P01ERROR: could not open file "base/16384/16387": No such file or directoryOn the mailing list too my question is that when the error code XX001 doesn't match the string, why is it skipped? Also, is there a way to reproduce the XX001 error? I can try it on the testdb and print it on the syslog, and then add the filters.