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.
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
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)
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 | 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
Ameen Abbas
