db corruption/recovery help

On Monday June 6 2005 2:16 pm, Ed L. wrote:

Someone flipped a breaker switch, and evidently triggered
corruption in one of our major clusters:

BTW, this is a 7.3.4 cluster ...

Ed

On Mon, 2005-06-06 at 15:16, Ed L. wrote:

Someone flipped a breaker switch, and evidently triggered
corruption in one of our major clusters:

$ cat server_log.Mon
postmaster successfully started
2005-06-06 14:31:11.950 [20124] LOG: database system was interrupted being in recovery at 2005-06-06 14:29:01 EDT
This probably means that some data blocks are corrupted
and you will have to use the last backup for recovery.
2005-06-06 14:31:11.950 [20124] LOG: checkpoint record is at EF/EBB7AFC8
2005-06-06 14:31:11.950 [20124] LOG: redo record is at EF/EBA91EF0; undo record is at 0/0; shutdown FALSE
2005-06-06 14:31:11.950 [20124] LOG: next transaction id: 577477594; next oid: 89750885
2005-06-06 14:31:11.951 [20124] LOG: database system was not properly shut down; automatic recovery in progress
2005-06-06 14:31:11.952 [20124] LOG: redo starts at EF/EBA91EF0
2005-06-06 14:31:11.984 [20124] PANIC: Invalid page header in block 22376 of 79189398
2005-06-06 14:31:12.275 [20121] LOG: startup process (pid 20124) was terminated by signal 6
2005-06-06 14:31:12.275 [20121] LOG: aborting startup due to startup process failure

We have backups from 10 hours earlier, but the obvious
question: what, if anything, can I do now to salvage those
10 hours of data from this?

I guess I could zero the block? I'm a little uncertain since
I don't know what I'm zeroing (pg_database? pg_class?), and
can't start up to see what that relfilenode maps to...

Going to look at it with pg_filedump, maybe oid2filename or
whatever that utility is...

OK, if postgresql is running on hardware that doe NOT lie about
fsyncing, and it is set to fsync, this should NEVER happen.

The fact that it happened means either A: the hardware is lying / broken
(like default IDE drives or a RAID controller with a non-battery backed
cache) or B: the database was set to not fsync. Since the default IS to
fsync, this would require effort on your part to turn it off.

Now, once the database comes up like this, your data may or may not be
salvagable, and may or may not be coherent. I.e. you may have orphaned
records where you shouldn't, or duplicate entries in some unique index
or something like that.

Look for pg_resetxlog. There's a man page for it on my machine, but I'm
not sure about 7.3.x

Before doing anything, make a file system level backup of all your data
so you have a pristine set to play about with.

On Monday June 6 2005 3:17 pm, Scott Marlowe wrote:

On Mon, 2005-06-06 at 15:16, Ed L. wrote:

Someone flipped a breaker switch, and evidently triggered
corruption in one of our major clusters:

OK, if postgresql is running on hardware that doe NOT lie
about fsyncing, and it is set to fsync, this should NEVER
happen.

This is 7.3.4 running on an HP-UX 11.00 9000/800 PA-RISC box with
fsync = TRUE, built with gcc 3.2.2. Database is entirely on a
SAN.

We got very lucky: the corrupted database was expendable
(essentially a log database). I was able to just move the
data/base/NNNN directory off to the side, restart, drop the
corrupted db, and recreate schema...

Thanks,
Ed

On Monday June 6 2005 3:29 pm, Ed L. wrote:

On Monday June 6 2005 3:17 pm, Scott Marlowe wrote:

On Mon, 2005-06-06 at 15:16, Ed L. wrote:

Someone flipped a breaker switch, and evidently triggered
corruption in one of our major clusters:

OK, if postgresql is running on hardware that doe NOT lie
about fsyncing, and it is set to fsync, this should NEVER
happen.

This is 7.3.4 running on an HP-UX 11.00 9000/800 PA-RISC box
with fsync = TRUE, built with gcc 3.2.2. Database is entirely
on a SAN.

We got very lucky: the corrupted database was expendable
(essentially a log database). I was able to just move the
data/base/NNNN directory off to the side, restart, drop the
corrupted db, and recreate schema...

The SAN never lost power, only the system itself. I'd really
like to chase this to the root if possible. Ideas?

Ed

On Mon, 2005-06-06 at 16:39, Ed L. wrote:

On Monday June 6 2005 3:29 pm, Ed L. wrote:

On Monday June 6 2005 3:17 pm, Scott Marlowe wrote:

On Mon, 2005-06-06 at 15:16, Ed L. wrote:

Someone flipped a breaker switch, and evidently triggered
corruption in one of our major clusters:

OK, if postgresql is running on hardware that doe NOT lie
about fsyncing, and it is set to fsync, this should NEVER
happen.

This is 7.3.4 running on an HP-UX 11.00 9000/800 PA-RISC box
with fsync = TRUE, built with gcc 3.2.2. Database is entirely
on a SAN.

We got very lucky: the corrupted database was expendable
(essentially a log database). I was able to just move the
data/base/NNNN directory off to the side, restart, drop the
corrupted db, and recreate schema...

The SAN never lost power, only the system itself. I'd really
like to chase this to the root if possible. Ideas?

It sounds like somewhere between postgresql and the SAN connector going
out the back, something is lying about fsync. I'm not that familiar with
lots of different SAN setups, so you might want to describe how things
are set up and see if anyone else knows more about them than me.

"Ed L." <pgsql@bluepolka.net> writes:

Someone flipped a breaker switch, and evidently triggered
corruption in one of our major clusters:

2005-06-06 14:31:11.984 [20124] PANIC: Invalid page header in block 22376 of 79189398

It's possible that you are one minor release short of having dodged this
problem, as I see in the 7.3.5 CVS log

2003-12-01 11:53 tgl

* src/backend/storage/buffer/bufmgr.c (REL7_3_STABLE): Force
zero_damaged_pages to be effectively ON during recovery from WAL,
since there is no need to worry about damaged pages when we are
going to overwrite them anyway from the WAL. Per recent
discussion.

This doesn't really address the question of how the page header got
clobbered in the first place, though. Did you by any chance make a dump
to see what data was in there?

regards, tom lane

On Monday June 6 2005 11:15 pm, Tom Lane wrote:

It's possible that you are one minor release short of having
dodged this problem, as I see in the 7.3.5 CVS log

* src/backend/storage/buffer/bufmgr.c (REL7_3_STABLE): Force
zero_damaged_pages to be effectively ON during recovery from
WAL, since there is no need to worry about damaged pages when
we are going to overwrite them anyway from the WAL. Per
recent discussion.

I remember all too well.

This doesn't really address the question of how the page
header got clobbered in the first place, though. Did you by
any chance make a dump to see what data was in there?

Well, I do have a copy of the corrupted database/file, if that's
what you mean. I rebuilt 7.3.4 source (which I don't normally
delete) so I could build pg_filedump 2.0, but then pg_filedump
wouldn't build. Not sure why; I didn't have the luxury of
digging deeper. Maybe I can find a pg_filedump laying around
somewhere here, or sort out the build issue...

Ed

On Monday June 6 2005 11:15 pm, Tom Lane wrote:

This doesn't really address the question of how the page
header got clobbered in the first place, though.  Did you by
any chance make a dump to see what data was in there?

I couldn't start the postmaster at all with that data in the
cluster, so no dump. But I do have the corrupted file. Haven't
gotten free yet to setup a pg_filedump on it.

The hardware setup is an HP RP5470 4-way running 11.00, with dual
HP Tachyon XL2 Fibre Channel Mass Storage Adapters going into
dual Cisco 9509 SAN switches to dual EVA 5000 disk arrays,
CPQswsp A.3.0B.01F.00F Patch upgrade for Sanworks Secure Path
Device Driver and utilities. Not sure that reveals much of the
problem...

Ed