Skip site navigation (1) Skip section navigation (2)

Re: postgres 8.2.9 transaction id wraparound failure

From: "Maria L(dot) Wilson" <Maria(dot)L(dot)Wilson-1(at)nasa(dot)gov>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: "Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS APPLICATIONS]" <m(dot)l(dot)wilson(at)nasa(dot)gov>, "jd(at)commandprompt(dot)com" <jd(at)commandprompt(dot)com>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: postgres 8.2.9 transaction id wraparound failure
Date: 2009-04-20 18:43:23
Message-ID: 49ECC24B.5090009@nasa.gov (view raw or flat)
Thread:
Lists: pgsql-admin
ouch!!  ok - this may take me all week!
i'm opening each database when I start up the server ( postgres --single 
-D /data/pg_devices /database1/ )
i run the vacuum - just a plain old vacuum - nothing else...
i am getting output like this with each vacuum...

backend> [postgres(at)n116 ~]$ postgres --single -D /data/pg_devices n015ecs
vacuum;
  2009-04-20 14:40:02.015 EDT  [10171] [] WARNING:  database "postgres" 
must be vacuumed within 981142 transactions
  2009-04-20 14:40:02.015 EDT  [10171] [] HINT:  To avoid a database 
shutdown, execute a full-database VACUUM in "postgres".
  2009-04-20 14:40:02.053 EDT  [10171] [] WARNING:  database "postgres" 
must be vacuumed within 981142 transactions
 2009-04-20 14:40:02.603 EDT  [10171] [] HINT:  To avoid a database 
shutdown, execute a full-database VACUUM in "postgres".
  2009-04-20 14:40:02.603 EDT  [10171] [] WARNING:  oldest xmin is far 
in the past
  2009-04-20 14:40:02.603 EDT  [10171] [] HINT:  Close open transactions 
soon to avoid wraparound problems.
  2009-04-20 14:40:02.671 EDT  [10171] [] WARNING:  database "postgres" 
must be vacuumed within 981131 transactions

it finishes - i then control -D to stop the server and start up with the 
next database.

Alvaro Herrera wrote:
> Maria L. Wilson wrote:
>   
>> I tried just vacuuming the "postgres" database first.  No luck.  Was  
>> still unable to startup the server normally.
>>     
>
> Did the vacuum actually work?  Note that you need to open the database
> you're going to vacuum, on the --single command line.
>
>   
>> Here's is a snipped from the query you suggested....  All the databases  
>> on this machine look similar.....
>>     
>
> So you'll need to vacuum them all ...
>
>   
>> backend> SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY  
>> age(datfrozenxid) DESC
>>  2009-04-20 14:27:52.250 EDT  [10097] [] WARNING:  database "postgres"  
>> must be vacuumed within 981218 transactions
>>  2009-04-20 14:27:52.250 EDT  [10097] [] HINT:  To avoid a database  
>> shutdown, execute a full-database VACUUM in "postgres".
>>     1: datname    (typeid = 19, len = 64, typmod = -1, byval = f)
>>     2: age    (typeid = 23, len = 4, typmod = -1, byval = t)
>>    ----
>>     1: datname = "postgres"    (typeid = 19, len = 64, typmod = -1,  
>> byval = f)
>>     2: age = "2146502429"    (typeid = 23, len = 4, typmod = -1, byval = t)
>>    ----
>>     1: datname = "ange"    (typeid = 19, len = 64, typmod = -1, byval = f)
>>     2: age = "2146502429"    (typeid = 23, len = 4, typmod = -1, byval = t)
>>    ----
>>     
>
> My guess is that autovacuum is failing to vacuum anything for some
> reason.  Maybe it's dying due to an error, which you'd find in the
> server log file.
>
>   

In response to

Responses

pgsql-admin by date

Next:From: Jaime CasanovaDate: 2009-04-20 18:55:12
Subject: Re: postgres 8.2.9 transaction id wraparound failure
Previous:From: Alvaro HerreraDate: 2009-04-20 18:38:52
Subject: Re: postgres 8.2.9 transaction id wraparound failure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group