Thread: Re: [GENERAL] ERROR: cannot read block 1452 from data (HELP!!!! I'MDESPERATE!)

Hi there!

Well, I've fixed the problem now ... I read a lot in the mailingslists
around VACUUM things and I got some ideas ...

Since the datafiles are build up by 8KB blocks I wanted to calculate
how many blocks it contained.  The filesize was 11.901.952 bytes ...
divided by 8192 (8KB) I got 1452,875 blocks ... Knowing this, I guessed
that the block count began at 1 (one) since it complained about block
1453.  Doing 1452,875/1453 ~~ 1 ==> Ok, then I was missing 1/8 in the
last block, and 1/8 of a block is 1KB.

The solution to try was simple:

alpha-centauri> dd bs=1024 count=1 >> data

I did a VACUUM ANALYZE on the table and it said it found some
uninitialized pages in the table, and it worked again.

I don't believe that I missed any data, because the total count seems
to what I estimated, maybe some records - but hey!  I can handle a loss
of 100 records when I saved almost 9000.

The only thing that I don't know is if the file was supposed to be
larger, more than 1453 bloks ...

Before I did this dd thing I tried

  #>SELECT * INTO TABLE restored FROM DATA LIMIT xxx

and I found a 'xxx' value where it began to complain about block
1453 ... and after doing a VACUUM ANALYZE on both the restored table
and the data table, I registered that the filesize was identical.

I also used a lot of time to analyze the restored data (with the dd
command) and I couldn't find any corrupted fields in the records.

But we're setting up a new production server (it's not ready yet, but
will be soon) and that one's running v7.0.1 at the moment, but I'll
upgrade it before I'll put it in action.

The problem with upgrading from v6.5.x to v7.x.x is that the v7.x.x
don't handle datetime functions with fields "containing" null.  So we
had to create our own datetime convertion variant to support this.


David S.
dazo@netcom.no

(Able to sleep at night at least!)

----- Original Message -----
From: Justin Clift <jclift@iprimus.com.au>
Date: Sunday, April 8, 2001 11:28 am
Subject: Re: [GENERAL] ERROR: cannot read block 1452 from data
(HELP!!!!  I'MDESPERATE!)

> Very ouch.
>
> Um... after you somehow sort out the mess you've got yourself in,
> you'llprobably want to upgrade from version 6.x to version 7.0.3
> or 7.1 asap.
>
> Apparently version 6.x.x has got some nasty bugs in it that the later
> versions have fixed.
>
> Regards and best wishes,
>
> Justin Clift
>
> dazo@netcom.no wrote:
> >
> > Hi ya'll!
> >
> > I hope some of you can help me with this emergency!
> >
> > One of my data tables has crashed and I'm not able to dump the
> table or
> > vacuum it ... it just gives me the same story each time: Cannot read
> > block 1452 from data
> >
> > I've done filesystems check, everything ok (both e2fsck and
> badblocks)> I've dropped all indexes
> > I've vacuumed all other tables in the database
> > I've restarted the postmaster
> > I've booted the server
> > ... BUT NOTHING HELPS!
> >
> > I'm running PostgreSQL v6.5.2 on a Linux 2.0.36 kernel (SMP) - Dual
> > Pentium II with 256 MB of RAM.
> >
> > We use the database to handle our card production and the table
> which> crashed is of cource the most important one, which changes
> every hour -
> > which makes backup almost imposible. We do regulary vacuums (every
> > night) ... but this problem is killing me ... our production is
> halted> now because of this problem.
> >
> > Ok, more details:
> >
> > This is what psql gives me:
> > bankdb=> vacuum verbose analyze data;
> > NOTICE: --Relation data--
> > ERROR: cannot read block 1452 of data
> > bankdb=> vacuum verbose analyze data;
> > NOTICE: CreatePortal: portal <vacuum> already exists
> > NOTICE: --Relation data--
> > pqReadData() -- backend closed the channel unexpectedly.
> >         This probably means the backend terminated abnormally
> >         before or while processing the request.
> > We have lost the connection to the backend, so further
> processing is
> > impossible.
> >   Terminating.
> >
> > And I connected gdb to the postgres backend and the 'bt'
> commands gives
> > me this ...
> >
> > Attaching to program `/usr/local/pgsql/bin/postmaster', process 1069
> > Reading symbols from /lib/libdl.so.1...done.
> > Reading symbols from /lib/libm.so.5...done.
> > Reading symbols from /lib/libtermcap.so.2...done.
> > Reading symbols from /lib/libncurses.so.3.4...done.
> > Reading symbols from /lib/libc.so.5...done.
> > Reading symbols from /lib/ld-linux.so.1...done.
> > 0x400bf074 in recv ()
> > (gdb) cont
> > Continuing.
> >
> > Program received signal SIGABRT, Aborted.
> > 0x400c2a89 in __kill ()
> > (gdb) bt
> > #0 0x400c2a89 in __kill ()
> > #1 0x400918c9 in gsignal ()
> > #2 0x80d6a8e in s_lock ()
> > #3 0x80d515b in BufferAlloc ()
> > #4 0x80d4d1c in ReadBufferWithBufferLock ()
> > #5 0x80d4caa in ReadBuffer ()
> > #6 0x8086d30 in vc_scanheap ()
> > #7 0x8086a9d in vc_vacone ()
> > #8 0x8086465 in vc_vacuum ()
> > #9 0x8086353 in vacuum ()
> > #10 0x80e2923 in ProcessUtility ()
> > #11 0x80df70a in pg_exec_query_dest ()
> > #12 0x80df62c in pg_exec_query ()
> > #13 0x80e0888 in PostgresMain ()
> > #14 0x80ca0f4 in DoBackend ()
> > #15 0x80c9c43 in BackendStartup ()
> > #16 0x80c93ae in ServerLoop ()
> > #17 0x80c8f89 in PostmasterMain ()
> > #18 0x80a05a4 in main ()
> > #19 0x806081e in _start ()
> >
> > Is there anyone who can help me ... I do not want to drop the
> table, it
> > contains almost 7000 records with production data (thats 1,5 day of
> > work for our production machines)
> >
> > I need help! And if you're looking on the case, tell me! Any ideas
> > are welcome! I'm willing to do anything (well, except selling my
> soul)> as long as I can restore the my data. And this has to be
> done before
> > 11:00 GMT+1 ...
> >
> > I'll just pray that some of you may be able help me ...
> >
> > David Sommerseth
> > dazo@netcom.no
> >
> > ---------------------------(end of broadcast)--------------------
> -------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
>     - Indira Gandhi
>

<dazo@netcom.no> writes:
> The problem with upgrading from v6.5.x to v7.x.x is that the v7.x.x
> don't handle datetime functions with fields "containing" null.  So we
> had to create our own datetime convertion variant to support this.

This is fixed in 7.1 --- you might want to forget 7.0.* and go straight
to 7.1.

            regards, tom lane