oldest xmin is far in the past: PG 11

From: Ameen Abbas <abbas(dot)dba(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: oldest xmin is far in the past: PG 11
Date: 2020-01-22 08:01:29
Message-ID: CAHq+KHJAXtbZNTWu9MotVEubAFdzvBKDMT8xPD1EK-kEbZ0Hzw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello Team,

We are facing "oldest xmin is far in the past" in PostgreSQL 11 in logs,
and we don't have any halted/aborted open transactions, and even no
replication slots and no prepared transactions, we had ran vacuum verbose
on the db multiple times still the below warning message not gone.

Warning while running vacuum verbose;

WARNING: oldest xmin is far in the past
HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or
drop stale replication slots.
INFO: aggressively vacuuming "tab1"
INFO: "tab1": found 0 removable, 1034 nonremovable row versions in 8 out
of 8 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin:
*3931622719*
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.

FYI...

SELECT datname, age(datfrozenxid) FROM pg_database;
datname | age
--------------+-----------
postgres | 290160616
template0 | 290160616
template1 | 290160616
prod_db | 290160616

select * from pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | temporary | active |
active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)

select * from pg_prepared_statements;
name | statement | prepare_time | parameter_types | from_sql
------+-----------+--------------+-----------------+----------
(0 rows)

select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)

name | setting
-----------------------------------+-------------------------------------------------
vacuum_cleanup_index_scale_factor | 0.1
vacuum_cost_delay | 0
vacuum_cost_limit | 800
vacuum_cost_page_dirty | 20
vacuum_cost_page_hit | 1
vacuum_cost_page_miss | 10
vacuum_defer_cleanup_age | 10000
vacuum_freeze_min_age | 50000000
vacuum_freeze_table_age | 150000000
vacuum_multixact_freeze_min_age | 5000000
vacuum_multixact_freeze_table_age | 150000000

Best Regards,
Ameen Abbas

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2020-01-22 10:33:33 Re: oldest xmin is far in the past: PG 11
Previous Message Bruce Momjian 2020-01-21 15:29:53 Re: pg_upgrade from 9.5 to 12 fails due to plpythonu2