Re: postgres 8.2.9 transaction id wraparound failure - Mailing list pgsql-admin

From Maria L. Wilson
Subject Re: postgres 8.2.9 transaction id wraparound failure
Date
Msg-id 49EE2D1F.5080308@nasa.gov
Whole thread Raw
In response to Re: postgres 8.2.9 transaction id wraparound failure  (Alvaro Herrera <alvherre@commandprompt.com>)
List 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=10.10.100.11 port=53704
jboss 10.10.100.11 2009-04-21 16:14:41.781 EDT 0 [10821]
[cluster0809oaid] LOG:  connection authorized: user=jboss
database=cluster0809oaid
jboss 10.10.100.11 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.
>
>

pgsql-admin by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: License Issue
Next
From: "Maria L. Wilson"
Date:
Subject: postgres 8.2.9 can't drop database in single user mode