Postgres Pro
Downloads
Home   >   mailing lists

Thread: oldest xmin is far in the past: PG 11

oldest xmin is far in the past: PG 11

From
Ameen Abbas
Date:
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

Re: oldest xmin is far in the past: PG 11

From
Laurenz Albe
Date:
On Wed, 2020-01-22 at 13:31 +0530, Ameen Abbas wrote:
> 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.

Ok, there are no prepared transactions and no replication slots.

Check for old transactions:

SELECT pid, age(current_timestamp, xact_start)
FROM pg_stat_activity
WHERE state <> 'idle';

Also, are there standby servers with "hot_standby_feedback = on"?
They might have a long running transaction as well.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com