Hi,
we have an error happening on a catalog table on one of the dbs in the
instance.
This is the error report form:
A description of what you are trying to achieve and what results you
expect.:
Any kind of vacuum fails on pg_authid table, I would expect it to succeed.
This is occasionaly blocking autovacuums, so we must resort to manual
vacuum of busy tables, which is bearable but inconvenient.
PostgreSQL version number you are running:
PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-28), 64-bit
How you installed PostgreSQL:
>From the pgdg yum repositories.
Changes made to the settings in the postgresql.conf file: see Server
Configuration for a quick way to list them all.
name |
current_setting | source
------------------------------+------------------------------------------------------------------------------+----------------------
application_name | psql
| client
archive_command | rsync -a %p
barman@x.x.x.x:/data/backup/barman/ubipgsql-10/incoming/%f
| configuration file
archive_mode | on
| configuration file
checkpoint_completion_target | 0.9
| configuration file
checkpoint_timeout | 25min
| configuration file
client_encoding | UTF8
| client
DateStyle | ISO, MDY
| configuration file
default_text_search_config | pg_catalog.english
| configuration file
dynamic_shared_memory_type | posix
| configuration file
effective_cache_size | 64GB
| configuration file
hot_standby | on
| configuration file
hot_standby_feedback | on
| configuration file
lc_messages | en_US.UTF-8
| configuration file
lc_monetary | en_US.UTF-8
| configuration file
lc_numeric | en_US.UTF-8
| configuration file
lc_time | en_US.UTF-8
| configuration file
listen_addresses | *
| configuration file
log_autovacuum_min_duration | 1s
| configuration file
log_checkpoints | on
| configuration file
log_destination | syslog
| configuration file
log_directory | log
| configuration file
log_filename | postgresql-%a.log
| configuration file
log_line_prefix | user=%u,db=%d,client=%h
| configuration file
log_lock_waits | on
| configuration file
log_min_duration_statement | 1s
| configuration file
log_min_messages | info
| configuration file
log_rotation_age | 1d
| configuration file
log_rotation_size | 0
| configuration file
log_statement | ddl
| configuration file
log_temp_files | 1MB
| configuration file
log_timezone | Europe/Rome
| configuration file
log_truncate_on_rotation | on
| configuration file
logging_collector | on
| configuration file
maintenance_work_mem | 1GB
| configuration file
max_connections | 1000
| configuration file
max_stack_depth | 2MB
| environment variable
max_wal_size | 12GB
| configuration file
min_wal_size | 80MB
| configuration file
password_encryption | scram-sha-256
| configuration file
pg_stat_statements.max | 10000
| configuration file
pg_stat_statements.track | all
| configuration file
shared_buffers | 32GB
| configuration file
shared_preload_libraries | pg_stat_statements
| configuration file
syslog_facility | local0
| configuration file
syslog_ident | postgres
| configuration file
TimeZone | Europe/Rome
| configuration file
track_activity_query_size | 2048
| configuration file
track_functions | all
| configuration file
track_io_timing | on
| configuration file
wal_keep_segments | 200
| configuration file
wal_level | logical
| configuration file
(51 rows)
Operating system and version:
CentOS Linux release 7.4.1708 (Core)
Linux xx.xx.com 3.10.0-693.11.1.el7.x86_64 #1 SMP Mon Dec 4 23:52:40 UTC
2017 x86_64 x86_64 x86_64 GNU/Linux
What program you're using to connect to PostgreSQL:
psql, jdbc driver. It's not an application side error.
Is there anything relevant or unusual in the PostgreSQL server logs?:
Yes, we see these kind of errors:
May 20 16:06:04 ubipgsql01 postgres[26739]: [1380-1] user=,db=,client=
ERROR: found xmin 2889675859 from before relfrozenxid 400011439
May 20 16:06:04 ubipgsql01 postgres[26739]: [1380-2] user=,db=,client=
CONTEXT: automatic vacuum of table "postgres.pg_catalog.pg_authid"
When these errors pop up, autovacuum repeatedly fails on this very same
table, and we have to resort to manual vacuums
For questions about any kind of error:
What you were doing when the error happened / how to cause the error:
The first time we saw the error, we found an idle transaction was left open
for several days. The user was not monitored for long running transactions.
We killed the session, deleted the offending row (the one with the xmin
value reported) and then vacuumed the table.
However, teh same error pops up every time we try to add a new user. We
would like to fix these error once and for all, so we can add new users.
The EXACT TEXT of the error message you're getting, if there is one: (Copy
and paste the message to the email, do not send a screenshot)
This is the error:
psql (10.4)
Type "help" for help.
postgres=# vacuum pg_authid;
ERROR: found xmin 3031994631 from before relfrozenxid 400011439
Some datas:
postgres=# select xmin from pg_authid ;
xmin
------------
1
1
1
1
1
557
7216348
110077819
110511334
3031994631
3032044199
3032044199
3032044199
3032070282
(14 rows)
postgres=# select relfrozenxid from pg_class where relname='pg_authid';
relfrozenxid
--------------
400011439
(1 row)
postgres=#
Is this a sympthom of data corruption or transaction wraparound due to the
long running transaction that we killed weeks ago? This is the only table
in the whole cluster that has this error. We are monitoring transactions
wraparound with the the check_postgres.pl script, the check is still
running fine and no alert was given at all since the cluster has been
running.
Thank you in advance for any answer.
Paolo Crosato
