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_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?