0

I have facing one problem with my Production PostgreSQL and which is related to Wraparound Problem basically for over limit of XID range.

This PostgreSQL Production is running more than 1 year with loads of transaction and bulk insertion.

I searched lot in google but confuse and very scare about this critical error. Right now I am getting this error during any vacuum or auto-vacuum stuff. I have around 250 GB of Postgres Production Database and I have also set auto-vacuum for all tables. 

Error and Warning are:

WARNING:  oldest xmin is far in the past
HINT:  Close open transactions soon to avoid wraparound problems.

I have also checked current open transaction but there is no any long running transaction in Postgres session. 

Current database age of XID :
  Database       Age
"template1";  153163876
"template0";  153163876
"postgres";   153163876
"dispatcher"; 153163876
"qate";       195568300

above result I got using below query :

SELECT datname, age(datfrozenxid) FROM pg_database;

please provide your inputs as soon as possible because very soon, this will really create problem for my production database.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Anvesh
  • 7,103
  • 3
  • 45
  • 43

1 Answers1

4

If you can afford to forcibly close open transactions:

SELECT pg_terminate_backend(procpid)
FROM   pg_stat_activity
WHERE  datname = 'mydb';

Then autovacuum can freeze rows, or you can run VACUUM manually. You may have to prevent new transactions temporarily. Here are detailed instructions:

.. except you don't DROP the db. Just VACUUM it.

Or Postgres will force a shutdown to prevent data loss. Detailed explanation in the manual.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    In my case, the problem processes were idle transactions. Another symptom is that autovacuum worker processes are not running. As soon as I did the pg_terminate_backend calls as per above, the autovacuum workers returned and the pg_log warnings went away. – Robert Casey Aug 02 '17 at 17:39