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-21 20:31:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
ok - i am still having the same errors after removing the majority of 
the databases on this server - leaving only 46 left.  I have gone into 
single user mode and vacuumed the rest.
any other ideas would be greatly appreciated!!!

log file.....
 2009-04-21 16:14:36.688 EDT  [10757] [] LOG:  database system is ready
  2009-04-21 16:14:37.754 EDT  [10757] [] WARNING:  database "postgres" 
must be vacuumed within 976347 transactions
  2009-04-21 16:14:37.754 EDT  [10757] [] HINT:  To avoid a database 
shutdown, execute a full-database VACUUM in "postgres".
  2009-04-21 16:14:37.811 EDT  [10819] [] ERROR:  database is not 
accepting commands to avoid wraparound data loss in database "postgres"
  2009-04-21 16:14:37.811 EDT  [10819] [] HINT:  Stop the postmaster and 
use a standalone backend to vacuum database "postgres".
[unknown]  2009-04-21 16:14:41.767 EDT 0 [10821] [[unknown]] LOG:  
connection received: host= port=53704
jboss 2009-04-21 16:14:41.781 EDT 0 [10821] 
[cluster0809oaid] LOG:  connection authorized: user=jboss 
jboss 2009-04-21 16:14:41.785 EDT 0 [10821] 
[cluster0809oaid] FATAL:  database "cluster0809oaid" does not exist

thanks....  Maria Wilson

Alvaro Herrera wrote:
> Maria L. Wilson wrote:
>> 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...
> Heh.  You can actually script this, you know.  You need to provide a
> list of databases, then do something like (maybe bash-specific):
> for db in database1 database2 database3 ...; do
>   echo "vacuum $db" | postgres --single -D /data/pg_devices $db 
> done
> To produce the list of databases you could connect to any one of them
> and do "SELECT datname FROM pg_database", redirect this to a file, and
> then fix up the resulting file so that there's a single database name
> per line, then the above loop could look like this:
> for db in $(cat file-with-database-names); do
>   echo "vacuum $db" | postgres --single -D /data/pg_devices $db 
> done
> If there are names with funny characteres in them (spaces, uppercase,
> symbols) then some quoting is called for.  You'd be also wise to check
> the output for errors in case something goes ill.
> Note that something *will* go wrong at some point.  Some database
> somewhere will throw you an error and it'll tell you that it cannot be
> vacuumed.  That is, after all, the reason that autovacuum has not being
> doing this automatically for you.

In response to

pgsql-admin by date

Next:From: Chris BrowneDate: 2009-04-21 20:51:23
Subject: Re: [ADMIN] License Issue
Previous:From: Tom LaneDate: 2009-04-21 18:43:37
Subject: Re: [ADMIN] License Issue

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