Re: postgres 8.2.9 transaction id wraparound failure

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.