Обсуждение: Restoring fscked up postgres 7.1

Greetings,

Some really strange things happened here. We were using old 7.1
postgres on 2.4 linux and db files living on ext3 partition for some time,
vacuuming regulary and not upgrading only because software didn't worked with 7.2+.
(developers where in progress, but not yet).

Last night no crash, not anything, just @ one moment all records for
last year where gone, all ros created for last year & tables created
while this time - and not in only one database, but all 6 DB's on this
server.

In all DB's there is data like
2005-01-20 19:26:05+02
2005-01-20 19:26:02+02

and then next lines are

2004-04-15 18:19:23+03
2004-04-15 17:41:15+03

Looks like after IT, there is no problemm inserting or anything, just
those records or tables before IT till  2004-04-15 18:19:23 are invisible.
Data files ar there. Database created 3 month ago is displayed, but
with no tables in it.  We movied data directory, created newone, and
restored from last night's dumps. The pain is that murphys law aplied in this case,
and one of db's backup dumps was bad. I was ok some time ago, but it
was failing for some last weeks or so, dump files ar 0, and pg_dump didn't notified
anyhow.. strange

Anyw ideas?  Maybe there is some way to dump something out of raw psql
data files?

--
KRISHA

On Fri, Jan 21, 2005 at 03:33:28PM +0200, Kristaps Armanis wrote:
> Last night no crash, not anything, just @ one moment all records for
> last year where gone, all ros created for last year & tables created
> while this time - and not in only one database, but all 6 DB's on this
> server.

Looks like XID wraparound, you hit the 4 billion transaction mark. This
was fixed in 7.2 IIRC. Unfortunatly I don't know of an easy way to
recover, but this should give you something to search the archives for.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Friday, January 21, 2005, 5:30:07 PM, Jus rakstijat:

MvO> Looks like XID wraparound, you hit the 4 billion transaction mark. This
MvO> was fixed in 7.2 IIRC. Unfortunatly I don't know of an easy way to
MvO> recover, but this should give you something to search the archives for.

Till how sorted that out already.

I hyave copy of old one..
Latest checkpoint's NextXID:          2669533222
Latest checkpoint's NextOID:          26300179
Time of latest checkpoint:            Fri Jan 21 16:00:48 2005
Database block size:                  8192
Blocks per segment of large relation: 131072

--------------
And then it's just:

Latest checkpoint's NextXID:          2747597
Latest checkpoint's NextOID:          282132
Time of latest checkpoint:            Fri Jan 21 03:32:17 2005


Is there any solution?


___________________________________
Kristaps Armanis
Grafton Entertainment :: IT director
Brivibas Str. 214m, Riga LV1039, Latvia
e-mail: krisha@grafton.lv
web: http://www.tvnet.lv
phn: +371 7819577
gsm: +371 9474932
fax: +371 7819578

Kristaps Armanis <krisha@grafton.lv> writes:

> Is there any solution?

I think what you have to do is use pg_resetxlog with -x and a value just shy
of 2^32. then pg_dump and restore into a fresh database. I've never done it
though so perhaps you should wait until someone with more experience speaks
up. I would also suggest taking a cold backup of the bad database before
running pg_resetxlog.

--
greg