[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: [ADMIN] postgres 8.2.9 transaction id wraparound failure
From:       "Maria L. Wilson" <Maria.L.Wilson-1 () nasa ! gov>
Date:       2009-04-21 20:31:27
Message-ID: 49EE2D1F.5080308 () nasa ! gov
[Download RAW message or body]

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.
>
>   

-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
[prev in list] [next in list] [prev in thread] [next in thread]