Re: Serious Crash last Friday

On Tue, Jul 09, 2002 at 05:33:58PM +0200, Henrik Steffen wrote:

pg_dump: ERROR: cannot open segment 1 of relation pg_toast_16584
(target block 1048595): No such file or directory

Last time I saw a similarly unhappy message, I had a fried hard
drive. Are you sure you don't have hardware trouble? Because that's
the first thing I'd look for.

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

hello,

I allready have a script that drops and recreates all user-indexes on a
daily basis using DROP and CREATE INDEX.

Now I started the database in single user mode and did a
REINDEX DATABASE kunden FORCE, but this didn't help either.

I will do an fsck tonight to see if something with the hardware is wrong.

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

On Wed, Jul 10, 2002 at 08:44:25AM +0200, Henrik Steffen wrote:

I will do an fsck tonight to see if something with the hardware is wrong.

That won't guarantee you don't have bad hardware. Are you seeing
anything in the syslog? If you do a non-destructive badblocks (or
whatever) test, what happens? I'd expect that, at least, to show up
any hardware problem.

A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110

excuse me, but what is a "non-destructive badblocks (or
whatever) test" - and how can I do this?

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Andrew Sullivan" <andrew@libertyrms.info>
To: "pg" <pgsql-general@postgresql.org>
Sent: Wednesday, July 10, 2002 2:20 PM
Subject: Re: [GENERAL] Serious Crash last Friday

On Wed, Jul 10, 2002 at 08:44:25AM +0200, Henrik Steffen wrote:

I will do an fsck tonight to see if something with the hardware is

wrong.

On Wed, Jul 10, 2002 at 02:51:00PM +0200, Henrik Steffen wrote:

excuse me, but what is a "non-destructive badblocks (or
whatever) test" - and how can I do this?

There is a badblocks program which will check for badblocks. I'd also
recommend memtest86 (www.memtest86.com).
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Wed, Jul 10, 2002 at 02:51:00PM +0200, Henrik Steffen wrote:

excuse me, but what is a "non-destructive badblocks (or
whatever) test" - and how can I do this?

If you're using linux, try "man badblocks".

On Solaris, a similar command is diskscan.

Other systems will have other names. I can't recall how to do this
on BSD, and I can't find anything in my local man db at the moment.
I'll bet someone else here knows.

You should check for other hardware faults, too. A bad memory module
can provide for all sorts of strange errors on your system (although
if it never locks up or crashes, I'd look elsewhere for the problem
first). In recent releases, PostgreSQL has proven to be very stable,
but you are having a lot of trouble, and many others are not
reporting similar problems. That leads one to suspect that you have
faulty hardware; it certainly requires, at least, that you eliminate
hardware as a factor.

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Hi,

thanks for the information...

the badblocks read-only test did not report any problems,
do you think i should run the "read-write" test, too?

i did the last initdb 3 weeks ago and created every table new from dumps.
i am recreating all user-indexes every day, i am vacuuming everything
everyday.

the server has only been running for 4 months, it's brand new hardware
(Intel PIII,
900 MHz, 2 x 60 GB SCSI-Raid 0 disks) ... it has been up and running for 42
days
without reset now (last reset was due to work at powerswitch).

tonight I will have the memory checked by memtest86 ...

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Andrew Sullivan" <andrew@libertyrms.info>
To: "pg" <pgsql-general@postgresql.org>
Sent: Wednesday, July 10, 2002 4:38 PM
Subject: Re: [GENERAL] Serious Crash last Friday

Henrik Steffen wrote:

Hi,

thanks for the information...

[...]

Did you have any OS crash or the like since the last rebuild of the
database? Are there any files in lost+found of the filesystem, your data
directory is on?

The fact that the backend cannot open a segment of a TOAST table raises
the question "how did it get removed?".

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

ok -- now this is what I did now:

I tried to figure out, where exactly the problem was:

The error occured while trying to COPY table seiten to STDOUT

Now I did "SELECT * FROM seiten;" ---> same problem

table seiten looks as follows:

lfdseitenr char(9)
absatznr smallint
absatz text
bildtyp smallint
bildtext text
richt boolean

there are 22409 rows - and there is an index on lfdseitenr

using LIKE I tried to figure out which rows where affected

SELECT * FROM seiten WHERE lfdseitenr LIKE '08%';

finally I found out that it was only ONE single row,
lfdseitenr = 081400023 AND absatznr=3

SELECT lfdseitenr, absatznr, bildtyp, bildtext, richt FROM seiten WHERE
lfdseitenr = '081400023' AND absatznr=3;

so only ONE field in ONE particular row was destroyed (pg_toast - error
message -
no such file or directory)

I now created a temp table selecting EVERYTHING but the affected row and
renamed
the table. So the problem is solved now, but this should actually never
happen.

I have the temp-table left on my system. Is it possible that someone entered
invalid
characters or something into this particular text-fild "absatz" ? Or what
else could
cause this error? Maybe anyone of the postgres-'gurus' wants to have a look
on my
system? Maybe there is a bug anywhere in postgres? Doesn't look like a
hardware-problem
or what do you guys think?

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Henrik Steffen" <steffen@city-map.de>
To: "Andrew Sullivan" <andrew@libertyrms.info>
Cc: "pg" <pgsql-general@postgresql.org>
Sent: Wednesday, July 10, 2002 5:19 PM
Subject: Re: [GENERAL] Serious Crash last Friday

Hi,

thanks for the information...

the badblocks read-only test did not report any problems,
do you think i should run the "read-write" test, too?

i did the last initdb 3 weeks ago and created every table new from dumps.
i am recreating all user-indexes every day, i am vacuuming everything
everyday.

the server has only been running for 4 months, it's brand new hardware
(Intel PIII,
900 MHz, 2 x 60 GB SCSI-Raid 0 disks) ... it has been up and running for

42

no, no crash for more than 42 days

last initdb was 3 weeks ago

all lost+found folders on the system are empty.

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Jan Wieck" <JanWieck@Yahoo.com>
To: "Henrik Steffen" <steffen@city-map.de>
Cc: "Andrew Sullivan" <andrew@libertyrms.info>; "pg"
<pgsql-general@postgresql.org>
Sent: Wednesday, July 10, 2002 5:40 PM
Subject: Re: [GENERAL] Serious Crash last Friday

On Wed, Jul 10, 2002 at 05:19:47PM +0200, Henrik Steffen wrote:

Hi,

thanks for the information...

the badblocks read-only test did not report any problems,
do you think i should run the "read-write" test, too?

Well, if you do it'll destoy the data, so although it's the only way
to be sure, I wouldn't unless absolutely pushed to do so. A
read-write badblocks test on a big partition can take many hours.

tonight I will have the memory checked by memtest86 ...

Yes, that seems a good idea. Brand new hardware doesn't guarantee
anything, particularly when memory is so fast these days (I've had
DIMMs fail a couple of months after they were new).

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Jan Wieck <JanWieck@Yahoo.com> writes:

The fact that the backend cannot open a segment of a TOAST table raises
the question "how did it get removed?".

I am guessing that the segment never existed, and that the real problem
is corruption of an entry in the TOAST table's index. A bad record
number in an index tuple's heap pointer could produce this symptom.

It might be worth dumping the TOAST index with pg_filedump or some such
tool and looking for silly block numbers. Examining the pattern of
corruption on the bad page, once found, might give us some hint how it
happened.

regards, tom lane

On Wed, 10 Jul 2002, Andrew Sullivan wrote:

On Wed, Jul 10, 2002 at 05:19:47PM +0200, Henrik Steffen wrote:

Hi,

thanks for the information...

the badblocks read-only test did not report any problems,
do you think i should run the "read-write" test, too?

Well, if you do it'll destoy the data, so although it's the only way
to be sure, I wouldn't unless absolutely pushed to do so. A
read-write badblocks test on a big partition can take many hours.

This isn't entirely true. According to bad blocks' man page:

-n Use non-destructive read-write mode. By default
only a non-destructive read-only test is done.
This option must not be combined with the -w
option, as they are mutually exclusive.

So, with the -n switch, badblocks will save a sector, do a write / read
test, then restore the sector.

Note that this is pretty slow, as I've tested it before.

tonight I will have the memory checked by memtest86 ...

Yes, that seems a good idea. Brand new hardware doesn't guarantee
anything, particularly when memory is so fast these days (I've had
DIMMs fail a couple of months after they were new).

Also, another REALLY good test for bad memory is to build postgresql from
source a couple dozen times, especially with a -j switch set to about 6 or
so.

On Wed, Jul 10, 2002 at 02:16:53PM -0600, scott.marlowe wrote:

This isn't entirely true. According to bad blocks' man page:

-n Use non-destructive read-write mode. By default

Ah, yes, I forgot about that. Takes days ;-)

Also, another REALLY good test for bad memory is to build postgresql from
source a couple dozen times, especially with a -j switch set to about 6 or
so.

I've had extremely good luck with memtestx86, though. Usually, by
the 3rd or 4th test it's spotted something, if something is wrong.

This raises another issue, by the way. Even big, expensive hardware
can have faulty memory. But the big, expensive hardware frequently
has ECC RAM, which saves your hide. I _know_ it's expensive, but
it's worth every penny. If you're building a database server for
production use, and you haven't specified ECC memory, go back and
think again. It might save you hours of work some day.

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Andrew Sullivan wrote:

On Wed, Jul 10, 2002 at 02:51:00PM +0200, Henrik Steffen wrote:

excuse me, but what is a "non-destructive badblocks (or
whatever) test" - and how can I do this?

If you're using linux, try "man badblocks".

On Solaris, a similar command is diskscan.

Other systems will have other names. I can't recall how to do this
on BSD, and I can't find anything in my local man db at the moment.
I'll bet someone else here knows.

My Buslogic/Mylex SCSI controller card beeps when it hits a bad SCSI
block. I didn't know what that sound was until about the 10th time.
:-)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Hello Tom,

I now did a "pg_filedump -R 1048595 16584" on the file
/var/lib/pgsql/data/base/16556/16584

It delivered the following output. Can you read anything from it?

*****************************************************************
* PostgreSQL File/Block Formatted Dump Utility
*
* File: 16584
* Options used: -R 1048595
*
* Dump created on: Thu Jul 11 08:13:47 2002
*****************************************************************

Block 1048595 ******************************************************
<Header> -----
Block Offset: 0x00026000 Offsets: Lower 156 (0x009c)
Block Size: 8192 Upper 200 (0x00c8)
LSN: logid 0 recoff 0x00982b4c Special 8192 (0x2000)
Items: 34 Free Space: 44
Length (including item array): 160

<Data> ------
Item 1 -- Length: 613 Offset: 7576 (0x1d98) Flags: USED
Item 2 -- Length: 61 Offset: 7512 (0x1d58) Flags: USED
Item 3 -- Length: 83 Offset: 7428 (0x1d04) Flags: USED
Item 4 -- Length: 109 Offset: 7316 (0x1c94) Flags: USED
Item 5 -- Length: 405 Offset: 6908 (0x1afc) Flags: USED
Item 6 -- Length: 61 Offset: 6844 (0x1abc) Flags: USED
Item 7 -- Length: 397 Offset: 6444 (0x192c) Flags: USED
Item 8 -- Length: 61 Offset: 6380 (0x18ec) Flags: USED
Item 9 -- Length: 729 Offset: 5648 (0x1610) Flags: USED
Item 10 -- Length: 61 Offset: 5584 (0x15d0) Flags: USED
Item 11 -- Length: 881 Offset: 4700 (0x125c) Flags: USED
Item 12 -- Length: 457 Offset: 4240 (0x1090) Flags: USED
Item 13 -- Length: 61 Offset: 4176 (0x1050) Flags: USED
Item 14 -- Length: 345 Offset: 3828 (0x0ef4) Flags: USED
Item 15 -- Length: 61 Offset: 3764 (0x0eb4) Flags: USED
Item 16 -- Length: 329 Offset: 3432 (0x0d68) Flags: USED
Item 17 -- Length: 61 Offset: 3368 (0x0d28) Flags: USED
Item 18 -- Length: 457 Offset: 2908 (0x0b5c) Flags: USED
Item 19 -- Length: 61 Offset: 2844 (0x0b1c) Flags: USED
Item 20 -- Length: 485 Offset: 2356 (0x0934) Flags: USED
Item 21 -- Length: 61 Offset: 2292 (0x08f4) Flags: USED
Item 22 -- Length: 61 Offset: 2228 (0x08b4) Flags: USED
Item 23 -- Length: 61 Offset: 2164 (0x0874) Flags: USED
Item 24 -- Length: 61 Offset: 2100 (0x0834) Flags: USED
Item 25 -- Length: 321 Offset: 1776 (0x06f0) Flags: USED
Item 26 -- Length: 61 Offset: 1712 (0x06b0) Flags: USED
Item 27 -- Length: 168 Offset: 1544 (0x0608) Flags: USED
Item 28 -- Length: 88 Offset: 1456 (0x05b0) Flags: USED
Item 29 -- Length: 97 Offset: 1356 (0x054c) Flags: USED
Item 30 -- Length: 61 Offset: 1292 (0x050c) Flags: USED
Item 31 -- Length: 157 Offset: 1132 (0x046c) Flags: USED
Item 32 -- Length: 801 Offset: 328 (0x0148) Flags: USED
Item 33 -- Length: 61 Offset: 264 (0x0108) Flags: USED
Item 34 -- Length: 61 Offset: 200 (0x00c8) Flags: USED

*** End of Requested Range Encountered. Last Block Read: 1048595 ***

I still get the following error, even on the old renamed temp-table:
ERROR: cannot open segment 1 of relation pg_toast_16584 (target block
1048595): No such file or directory

Why is that? The file 16584 exists. The target block exists, though it seems
to be the last
existing block in this particular file.

I even tried the -d option on pg_filedump ... it delivers a 36 MB file...
I can send it to you privately if you want.

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

"Henrik Steffen" <steffen@city-map.de> writes:

I now did a "pg_filedump -R 1048595 16584" on the file
/var/lib/pgsql/data/base/16556/16584

What file is that? Doesn't look like it is an index ...

It delivered the following output. Can you read anything from it?

Not a lot. I'd suggest "pg_filedump -i FILENAME" where FILENAME is the
OID of the toast-table index for your problem table. (Look at
pg_class.reltoastidxid if you're not sure.) That should produce a ton
of output along the lines of

Item 155 -- Length: 12 Offset: 4720 (0x1270) Flags: USED
Block Id: 4 linp Index: 39 Size: 12
Has Nulls: 0 Has Varlenas: 0

What you want to look for is outrageously large values in the "Block Id"
field. Once you find 'em, a "pg_filedump -i -f" of just the block
containing the broken item(s) would be worth studying.

regards, tom lane

Hi once more,

I found the damaged section in the >30 MB file:

0940: d0930100 00000000 00000000 02000000 ................
0950: 00000000 00005b02 10000600 0e092000 ......[....... .
0960: 0d000000 30383134 30303032 33000300 ....081400023...
0970: 140000c0 f3170000 a10f0000 d1930100 ................
0980: ca400000 0000726f 04000000 01000000 .@....ro........
0990: cf930100 00000000 00000000 02000000 ................

this is table "altseiten" where lfdseitenr=081400023 and absatznr=3
what's wrong here?

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

16584 was mentioned in the error message
ERROR: cannot open segment 1 of relation pg_toast_16584

so I did a find in the /var/lib/pgsql folder and found only this particular
file.
I would say, it contains table information/content of the table 'altseiten'
with
the corrupted field.

select reltoastidxid from pg_class where relname='altseiten'
delivers: 0 ???

Mit freundlichem Gru�

Henrik Steffen
Gesch�ftsf�hrer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com Tel. +49 4141 991230
mail: steffen@topconcepts.com Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------