cancel
Showing results for 
Search instead for 
Did you mean: 

Wraparound warnings in the database logs

Whenever you see below warnings in the database logs, there are certain steps that you need to follow as soon as possible to avoid any inconvenience:

 
2019-01-03 07:54:08 GMT WARNING:  oldest xmin is far in the past
2019-01-03 07:54:08 GMT HINT:  Close open transactions soon to avoid wraparound problems.
 
You can use the below query to check the top 10 relations having the oldest age transaction:
 
SELECT oid::regclass, relfrozenxid, age(relfrozenxid), txid_current() FROM pg_class WHERE NOT relfrozenxid = '0' ORDER BY age(relfrozenxid) DESC LIMIT 10; 
 
 
Step1:
 
Check whether there are any prepared transaction open using the following:
 
 
SELECT * FROM pg_prepared_xacts;
 
If there are any prepared transactions found, you need to rollback that. Refer the following link for further information:
 
 
 
Step2:
 
Check whether there are any "IDLE IN TRANSACTION" status sessions from pg_stat_activity open. If so, you need to terminate those using pg_terminate_backend function as it may be possible that those "IDLE IN TRANSACTION" session are holding the XID horizon to move forward.
 
 
Step3:
 
Finally, you need to check if there are any inactive replication slots that are present in the cluster using the following query:
 
select * from pg_replication_slots;
 
If you find any inactive replication slot, you need to drop those as that can be a reason while you run VACUUM afterwards, it will not remove the removable rows giving the following exception(for example):
 

 

2019-01-03 07:52:48 GMT DEBUG:  "pg_statistic": found 30 removable, 566 nonremovable row versions in 24 out of 24 pages

2019-01-03 07:52:48 GMT DETAIL:  82 dead row versions cannot be removed yet.

 

2019-01-03 07:52:48 GMT DEBUG:  "pg_toast_2619": found 0 removable, 29 nonremovable row versions in 6 out of 6 pages

2019-01-03 07:52:48 GMT DETAIL:  1 dead row versions cannot be removed yet.  
 
 
After checking all the above three steps. You need to run a plain VACUUM command on the database. By doing this, it will freeze the transaction xids and you will no more receive the wraparound warnings in the database logs.
Version history
Revision #:
1 of 1
Last update:
2 weeks ago
Updated by:
 
Labels (1)
Contributors