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

Re: postgres 8.2.9 transaction id wraparound failure

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: "Maria L(dot) Wilson" <Maria(dot)L(dot)Wilson-1(at)nasa(dot)gov>
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 19:03:37
Message-ID: 20090420190337.GG10358@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-admin
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.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

In response to

Responses

pgsql-admin by date

Next:From: dx k9Date: 2009-04-21 13:31:13
Subject: slpd restart restarted postgres
Previous:From: Maria L. WilsonDate: 2009-04-20 19:00:18
Subject: Re: postgres 8.2.9 transaction id wraparound failure

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