Обсуждение: Vaccuuming every db in cluster fails to eliminate "execute a full-database VACUUM in..."

So, I have neglected to do the vacuum on every db for a long time, and
this is a very high volume cluster.  The db stopped executing requests
this morning, and reported:

ERROR:  database is not accepting commands to avoid wraparound data
loss in database "vwuds"

So, I did some googling and found I needed to shut down the
postmaster, enter single user mode, and vacuum each and every db in my
cluster.  I did so (even though only about 4 of the 22 databases
actually get any appreciable use), but am still hampered by this
error.

Using 8.3.7 on CentOS.  I am really in a bind, obviously, as my system
is currently unusable (for what I do with it that is).

Thanks in advance,
r.b.


--
--
Robert W. Burgholzer
http://www.findingfreestyle.com/

So, as I mentioned, I have tried to get this straightened out by
vacuuming all in --single mode, but to no avail.  I executed the
following command, to see which tables were in trouble:

SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';

And found a table listed, that DOES NOT exist any longer.  Do I have
any recourse other than to drop and rebuild all of my tables?  Can I
just dump, drop and recreate my database?

How do I drop a database in --single mode?

Thanks,
r.b.


On Sun, Oct 10, 2010 at 7:21 AM, Robert Burgholzer <rburghol@vt.edu> wrote:
>
> ERROR:  database is not accepting commands to avoid wraparound data
> loss in database "vwuds"
>
> So, I vacuum each and every db in my ... but am still hampered by this
> error.
>
--
--
Robert W. Burgholzer
http://www.findingfreestyle.com/
Find us on Facebook by searching "Finding Freestyle"

Robert Burgholzer <rburghol@vt.edu> writes:
> So, as I mentioned, I have tried to get this straightened out by
> vacuuming all in --single mode, but to no avail.  I executed the
> following command, to see which tables were in trouble:

> SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';

> And found a table listed, that DOES NOT exist any longer.

Yeah?  What happens if you try to select from that table?

If there is a row in pg_class that for some reason didn't get deleted
when the table was dropped, you could just manually remove that row
(ie, DELETE FROM pg_class WHERE ... as superuser).  You'd still need
another VACUUM to get the database's datfrozenxid updated, but
after that things should be OK.

            regards, tom lane

Tom,
Thanks for getting in touch.  Unfortunately, I thought to myself "why
not drop the db in single mode under database postgres", which I did,
and which worked, and thus, I can no longer produce the error, nor can
I query the "phantom" table as you suggested.  I can say that when I
tried to vacuum the table, it told me:

backend> vacuum annual_data_gwreplace
ERROR:  relation "annual_data_gwreplace" does not exist
STATEMENT:  vacuum annual_data_gwreplace

I restored, and now, I *think* things are going OK.

Thanks again,
r.b.

>If there is a row in pg_class that for some reason didn't get deleted
>when the table was dropped, you could just manually remove that row
>(ie, DELETE FROM pg_class WHERE ... as superuser).  You'd still need
>another VACUUM to get the database's datfrozenxid updated, but
>after that things should be OK.
--
--
Robert W. Burgholzer
http://www.findingfreestyle.com/