Re: recovery from xid wraparound

Martijn,

Thanks,

>Just make sure you've really covered *all* the system tables. If they
go you
>get really weird results.

I've been under the impression system tables get done first, then
user(me)-created tables after - which means my previous [aborted]
attempts at vacuuming them would have covered it, unless I'm missing
something?

(db was created by initdb, then pg_restore to load data into it,
database was then vacuumed before production work began)

I've looked at the list of catalog tables from 7.4's docs (URL below),
and all 28 have been processed in this vacuum, so presumably same order
for previous attempts:
http://www.postgresql.org/docs/7.4/static/catalogs.html

Checked with 'grep vacuuming vacuum.log | grep pg_ | grep toast -v |
sort | uniq'

Does this sound like a fair assumption?

(it is on the first database in the cluster, these aren't coming up from
other databases)

Many thanks for your help!

S

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog(at)svana(dot)org]
Sent: 24 October 2006 11:50
To: Shane Wright
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] recovery from xid wraparound

On Tue, Oct 24, 2006 at 11:06:01AM +0100, Shane Wright wrote:
> If I was to abort this vacuum, given that all other tables are
> vacuumed (including system catalog tables), what's the worst case
> scenario? - given that more transactions are happening on the database

Only tables that havn't been vacuumed in the last billion transactions
are at risk. It's possible that if you've vacuumed that large table
recently by itself that all the data is actually safe, just the system
doesn't realise it.

Just make sure you've really covered *all* the system tables. If they go
you get really wierd results.

> If I understand correctly, it would be that some rows could disappear
> from this large unvacuumed table if their xid was too old - but no
> other consequence?

The VACUUM would make them reappear. To truly disappear they would have
to be 3 billion transactions old. That leaves the unique index issue I
mentioned.

> (fully aware that a db-wide vacuum is needed, but if it can [safely]
> wait for the weekend that would be preferable)

That's risk-management. For example, all the really old tuples are
possibly near the beginning of the table, thus this current vacuum will
have fixed them already. But to get a handle on that you need to analyse
your tuple turnover and usage ratio.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability
> to litigate.