Re: Please HELP - URGENT - transaction wraparound error

Martin, thanks for the feedback.

I had a look around and couldn't see any data loss (but wasn't really
sure where to start looking).

I decided to switch over to the slave which is now our live database.
the old master with the problem has already been re-inited (although I
have a cold backup of the data dir), plus dump files that I can restore
from.

I checked pg_database (on the new master) and I don't really understand
what it is saying. Is the datvacuumxid vs 3553547043 the significant
information? I see in our new database:

datname | datdba | encoding | datistemplate
--------------+--------+----------+---------------
bp_live | 1 | 6 | f
bp_test | 1 | 6 | f
template1 | 1 | 0 | t
template0 | 1 | 0 | t

datname | datallowconn | datlastsysoid | datvacuumxid
--------------+--------------+---------------+--------------
bp_live | t | 17140 | 332321570
bp_test | t | 17140 | 332265474
template1 | t | 17140 | 332241177
template0 | f | 17140 | 464

datname | datfrozenxid | datpath | datconfig
--------------+--------------+---------+-----------
bp_live | 3553547043 | |
bp_test | 3553490947 | |
template1 | 3553466653 | |
template0 | 464 | |

datname | datacl
--------------+--------------------------
bp_live |
bp_test |
template1 | {postgres=C*T*/postgres}
template0 | {postgres=C*T*/postgres}

Are we going to get the same problem with this database?

What's also worrying me is that the warning message is in fact
misleading!!??

2005-10-28 05:56:58 WARNING: some databases have not been vacuumed in
over 2 billion transactions
DETAIL: You may have already suffered transaction-wraparound data loss.

And I'm wondering if I have in fact destroyed a perfectly good database
and data set...

I read the link you gave (before) but found it hard to work out what you
actually need to do to protect yourself.

We DID vacuum the databases nightly, and template1 once a week. So I
still don't understand why we got this error. Can someone explain in
simple language?

Can someone also give me a detailed "you need to do this, and this and
this..." explanation to prevent this happening again (either on our
master or slave databases).

For example, must you do a vacuum full instead of a vacuum analyze on a
7.4.x database to prevent wraparound issues?

BTW, for those not using **Slony** - you should check it out. It has
saved my bacon three times this year! Due to:

1) server failure - hardware crash, needed BIOS flash, complete OS
reinstall etc
2) disk full - corrupted pg data
3) oid wraparound (today's problem)

Any further help that anyone can give is much appreciated.

Thanks

John

Martijn van Oosterhout wrote:
> On Sun, Oct 30, 2005 at 08:50:18AM +0000, John Sidney-Woollett wrote:
>
>>Oh my god!....
>>
>>DB is pg 7.4.6 on linux
>
>
> Firstly, check pg_database, it should tell you which databases need to
> be vacuumed. Any database you regularly vacuumed is fine so maybe the
> corruption is in some other database you don't remember?
>
>
>>1) Why do have we data corruption? I thought we were doing everything we
>>needed to stop any wraparound... Are the pg docs inadequate, or did I
>>misunderstand what needed to be done?
>
>
> You *may* have corruption. Anything you vacuumed recently should be
> fine.
>
>
>>2) What can I do to recover the data?
>
>
> Check whether anything is lost first.
>
>
>>How do I stop this EVER happening again??!!!
>
>
> Have you read this:
>
> http://www.postgresql.org/docs/7.4/static/maintenance.html#VACUUM-FOR-WRAPAROUND
>
> Hope this helps,