Hey,
I'm trying to help a guy that is using pg9.6 but I'm not so familiar with the error message :
ERROR: found xmin 16804535 from before relfrozenxid 90126924
CONTEXT: automatic vacuum of table db1.public.table_1"
It seems that the error has started appearing two weeks ago. Data that I collected :
-all the autovacuum params are set to default
-SELECT relname, age(relfrozenxid) as xid_age,
pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class
WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 4;
relname | xid_age | table_size
-------------------------------+-----------+------------
table_1 | 180850538 | 10 GB
table_2 | 163557812 | 10 GB
table_3 | 143732477 | 1270 MB
table_4 | 70464685 | 3376 MB
pg_controldata :
Latest checkpoint's NextXID: 0:270977386
Latest checkpoint's NextOID: 25567991
Latest checkpoint's NextMultiXactId: 1079168
Latest checkpoint's NextMultiOffset: 68355
Latest checkpoint's oldestXID: 77980003
Latest checkpoint's oldestXID's DB: 16403
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1047846
Latest checkpoint's oldestMulti's DB: 16403
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
It seems that the autovacuum cant vacuum table_1 and it has alot of dead_tuples. Moreover, it seems that the indexes are bloated.
| schemaname | relname | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum | last_autovacuum | last_analyze |
| public | table_1 | 0 | 5422370 | 0 | 382222 | 109582923 | 10760701 |
I tried to vacuum the table (full,freeze) but it didnt help.
I read about the wrap that can happen but to be honest I'm not sure that I understood id.
What can I do to vacuum the table ? Can some one explain the logic behind the error message ?
Thanks.
