cancel
Showing results for 
Search instead for 
Did you mean: 

Wraparound warnings in the database logs

Whenever you see the 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;


Step 1

Check whether there are any prepared transactions open using the following:

 

SELECT * FROM pg_prepared_xacts;

 

If there are any prepared transactions found, you need to issue a rollback those. Visit the following link for further information:

https://www.postgresql.org/docs/9.6/sql-rollback-prepared.html

Step 2

Check whether there are any “IDLE IN TRANSACTION” status sessions open in from pg_stat_activity. If so, you need to terminate those using the pg_terminate_backend() function as it may be possible that those “IDLE IN TRANSACTION” session are holding the XID horizon to move forward.


Step 3

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 slots, you need to drop those as that can prevent VACUUM from cleaning up rows, which would show up in the log like as follows:

 

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 #:
4 of 5
Last update:
‎07-10-2019 07:40 PM
Updated by:
 
Labels (1)