postgres 8.2.9 transaction id wraparound failure

running on a linux box

i've inherited this test machine getting the following errors in the
logs....

scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181]
[scoogan_ange_1.2] FATAL: database is not accepting commands to avoid
wraparound data loss in database "postgres"
scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181]
[scoogan_ange_1.2] HINT: Stop the postmaster and use a standalone
backend to vacuum database "postgres".

I stopped the server and went into single user mode as the docs
suggested....
postgres --single -D /data/pg_devices


then i am issuing vacuum full analyze; it returns.....

2009-04-20 11:37:47.377 EDT [8513] [] WARNING: database "postgres"
must be vacuumed within 982038 transactions
2009-04-20 11:37:47.377 EDT [8513] [] HINT: To avoid a database
shutdown, execute a full-database VACUUM in "postgres".

trying to restart the server - I still am getting the same initial
errors....

Am i missing something??

thanks!! Maria Wilson



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

Re: postgres 8.2.9 transaction id wraparound failure

On Mon, 2009-04-20 at 13:49 -0400, Maria L. Wilson wrote:
> running on a linux box
>
> i've inherited this test machine getting the following errors in the
> logs....
>
> scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181]
> [scoogan_ange_1.2] FATAL: database is not accepting commands to avoid
> wraparound data loss in database "postgres"
> scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181]
> [scoogan_ange_1.2] HINT: Stop the postmaster and use a standalone
> backend to vacuum database "postgres".
>
> I stopped the server and went into single user mode as the docs
> suggested....
> postgres --single -D /data/pg_devices
>
>
> then i am issuing vacuum full analyze; it returns.....
>
> 2009-04-20 11:37:47.377 EDT [8513] [] WARNING: database "postgres"
> must be vacuumed within 982038 transactions
> 2009-04-20 11:37:47.377 EDT [8513] [] HINT: To avoid a database
> shutdown, execute a full-database VACUUM in "postgres".
>
> trying to restart the server - I still am getting the same initial
> errors....

You have to vacuum each database.

Joshua D. Drake


>
> Am i missing something??
>
> thanks!! Maria Wilson
>
>
>
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997


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

Re: postgres 8.2.9 transaction id wraparound failure

I was afraid you'd say that - any quick way to vacuum all 315 databases
on this machine?
thanks......

Joshua D. Drake wrote:
> On Mon, 2009-04-20 at 13:49 -0400, Maria L. Wilson wrote:
>
>> running on a linux box
>>
>> i've inherited this test machine getting the following errors in the
>> logs....
>>
>> scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181]
>> [scoogan_ange_1.2] FATAL: database is not accepting commands to avoid
>> wraparound data loss in database "postgres"
>> scoogan 198.119.134.228 2009-04-20 11:42:48.762 EDT 0 [9181]
>> [scoogan_ange_1.2] HINT: Stop the postmaster and use a standalone
>> backend to vacuum database "postgres".
>>
>> I stopped the server and went into single user mode as the docs
>> suggested....
>> postgres --single -D /data/pg_devices
>>
>>
>> then i am issuing vacuum full analyze; it returns.....
>>
>> 2009-04-20 11:37:47.377 EDT [8513] [] WARNING: database "postgres"
>> must be vacuumed within 982038 transactions
>> 2009-04-20 11:37:47.377 EDT [8513] [] HINT: To avoid a database
>> shutdown, execute a full-database VACUUM in "postgres".
>>
>> trying to restart the server - I still am getting the same initial
>> errors....
>>
>
> You have to vacuum each database.
>
> Joshua D. Drake
>
>
>
>> Am i missing something??
>>
>> thanks!! Maria Wilson
>>
>>
>>
>>

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

Re: postgres 8.2.9 transaction id wraparound failure

Maria L. Wilson wrote:
> I was afraid you'd say that - any quick way to vacuum all 315 databases
> on this machine?

Actually you only need to vacuum the database being complained about --
"postgres" in this case. You can issue
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC
to find whether any other database is going to need vacuuming as well.

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

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

Re: postgres 8.2.9 transaction id wraparound failure

I tried just vacuuming the "postgres" database first. No luck. Was
still unable to startup the server normally.

Here's is a snipped from the query you suggested.... All the databases
on this machine look similar.....

backend> SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY
age(datfrozenxid) DESC
2009-04-20 14:27:52.250 EDT [10097] [] WARNING: database "postgres"
must be vacuumed within 981218 transactions
2009-04-20 14:27:52.250 EDT [10097] [] HINT: To avoid a database
shutdown, execute a full-database VACUUM in "postgres".
1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
2: age (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "postgres" (typeid = 19, len = 64, typmod = -1,
byval = f)
2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t)
----
1: datname = "ange" (typeid = 19, len = 64, typmod = -1, byval = f)
2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t)
----


Alvaro Herrera wrote:
> Maria L. Wilson wrote:
>
>> I was afraid you'd say that - any quick way to vacuum all 315 databases
>> on this machine?
>>
>
> Actually you only need to vacuum the database being complained about --
> "postgres" in this case. You can issue
> SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY age(datfrozenxid) DESC
> to find whether any other database is going to need vacuuming as well.
>
>

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

Re: postgres 8.2.9 transaction id wraparound failure

Maria L. Wilson wrote:
> I tried just vacuuming the "postgres" database first. No luck. Was
> still unable to startup the server normally.

Did the vacuum actually work? Note that you need to open the database
you're going to vacuum, on the --single command line.

> Here's is a snipped from the query you suggested.... All the databases
> on this machine look similar.....

So you'll need to vacuum them all ...

> backend> SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY
> age(datfrozenxid) DESC
> 2009-04-20 14:27:52.250 EDT [10097] [] WARNING: database "postgres"
> must be vacuumed within 981218 transactions
> 2009-04-20 14:27:52.250 EDT [10097] [] HINT: To avoid a database
> shutdown, execute a full-database VACUUM in "postgres".
> 1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
> 2: age (typeid = 23, len = 4, typmod = -1, byval = t)
> ----
> 1: datname = "postgres" (typeid = 19, len = 64, typmod = -1,
> byval = f)
> 2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t)
> ----
> 1: datname = "ange" (typeid = 19, len = 64, typmod = -1, byval = f)
> 2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t)
> ----

My guess is that autovacuum is failing to vacuum anything for some
reason. Maybe it's dying due to an error, which you'd find in the
server log file.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: postgres 8.2.9 transaction id wraparound failure

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

backend> [postgres@n116 ~]$ postgres --single -D /data/pg_devices n015ecs
vacuum;
2009-04-20 14:40:02.015 EDT [10171] [] WARNING: database "postgres"
must be vacuumed within 981142 transactions
2009-04-20 14:40:02.015 EDT [10171] [] HINT: To avoid a database
shutdown, execute a full-database VACUUM in "postgres".
2009-04-20 14:40:02.053 EDT [10171] [] WARNING: database "postgres"
must be vacuumed within 981142 transactions
2009-04-20 14:40:02.603 EDT [10171] [] HINT: To avoid a database
shutdown, execute a full-database VACUUM in "postgres".
2009-04-20 14:40:02.603 EDT [10171] [] WARNING: oldest xmin is far
in the past
2009-04-20 14:40:02.603 EDT [10171] [] HINT: Close open transactions
soon to avoid wraparound problems.
2009-04-20 14:40:02.671 EDT [10171] [] WARNING: database "postgres"
must be vacuumed within 981131 transactions

it finishes - i then control -D to stop the server and start up with the
next database.

Alvaro Herrera wrote:
> Maria L. Wilson wrote:
>
>> I tried just vacuuming the "postgres" database first. No luck. Was
>> still unable to startup the server normally.
>>
>
> Did the vacuum actually work? Note that you need to open the database
> you're going to vacuum, on the --single command line.
>
>
>> Here's is a snipped from the query you suggested.... All the databases
>> on this machine look similar.....
>>
>
> So you'll need to vacuum them all ...
>
>
>> backend> SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY
>> age(datfrozenxid) DESC
>> 2009-04-20 14:27:52.250 EDT [10097] [] WARNING: database "postgres"
>> must be vacuumed within 981218 transactions
>> 2009-04-20 14:27:52.250 EDT [10097] [] HINT: To avoid a database
>> shutdown, execute a full-database VACUUM in "postgres".
>> 1: datname (typeid = 19, len = 64, typmod = -1, byval = f)
>> 2: age (typeid = 23, len = 4, typmod = -1, byval = t)
>> ----
>> 1: datname = "postgres" (typeid = 19, len = 64, typmod = -1,
>> byval = f)
>> 2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t)
>> ----
>> 1: datname = "ange" (typeid = 19, len = 64, typmod = -1, byval = f)
>> 2: age = "2146502429" (typeid = 23, len = 4, typmod = -1, byval = t)
>> ----
>>
>
> My guess is that autovacuum is failing to vacuum anything for some
> reason. Maybe it's dying due to an error, which you'd find in the
> server log file.
>
>

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

Re: postgres 8.2.9 transaction id wraparound failure

On Mon, Apr 20, 2009 at 1:43 PM, Maria L. Wilson
wrote:
> ouch!! =A0ok - this may take me all week!
> i'm opening each database when I start up the server ( postgres --single =
-D
> /data/pg_devices /database1/ )

is database1 the directory where you initialized the server?

> i run the vacuum - just a plain old vacuum - nothing else...
> i am getting output like this with each vacuum...
>
> backend> [postgres@n116 ~]$ postgres --single -D /data/pg_devices n015ecs

can't understand this one... you have initialized several directories?
so you have variuos instances of postgresql running? if so, you only
have to vacuum the one that is giving you troubles...

> vacuum;

you execute this inside psql... rigth?

--=20
Atentamente,
Jaime Casanova
Soporte y capacitaci=F3n de PostgreSQL
Asesor=EDa y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres 8.2.9 transaction id wraparound failure

On Mon, Apr 20, 2009 at 12:43 PM, Maria L. Wilson
wrote:
> ouch!! =A0ok - this may take me all week!
> i'm opening each database when I start up the server ( postgres --single =
-D
> /data/pg_devices /database1/ )

This is not specifying the db to connect to. You need:

postgres --single -D /data/pg_devices /database1/ postgres

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: postgres 8.2.9 transaction id wraparound failure

sorry for the confusion - from what the docs - i am starting out the
single user like this.....

postgres --single -D /data/pg_devices postgres

*that connects me to the "postgres" database

postgres --single -D /data/pg_devices n001acs

*that connects me to the "n001acs" database.......

then I am issuing a vacuum command with each session..... Sound okay?

Scott Marlowe wrote:
> On Mon, Apr 20, 2009 at 12:43 PM, 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/ )
>>
>
> This is not specifying the db to connect to. You need:
>
> postgres --single -D /data/pg_devices /database1/ postgres
>

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

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.

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

Re: postgres 8.2.9 transaction id wraparound failure

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

Free Space after vacuuming

All,

How does one tell how much space is available (probably by table), after
a=20
"vacuum verbose analyze" has completed. As I understand it, the emptied

space will still be only available to the table that originally housed
it.

We are running Postgresql 8.3 in various Solaris and Linux environments.

Thanks,
Naomi



--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: Free Space after vacuuming

"Naomi Walker" wrote:

> How does one tell how much space is available (probably by table),
> after a "vacuum verbose analyze" has completed.

Personally, I mostly pay attention to these lines:

NNNNN pages contain useful free space.

(where NNNNN is a large number).

We grep for large numbers in the file where we've redirected the
nightly VACUUM ANALYZE VERBOSE output, and if we find something we
email our DBA team.

-Kevin

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