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,
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
