From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: pgsql 8.3.3 "Invalid page header" leads to "FATAL: segment too
	big"
Date: Fri, 23 Apr 2010 11:06:38 +0200
User-Agent: KMail/1.9.7
References: <201004221516.08729.achill@matrix.gatewaynet.com>
	<201004221818.51840.achill@matrix.gatewaynet.com>
	<w2je94e14cd1004220902z1a88290alb6163585300da216@mail.gmail.com>
In-Reply-To: <w2je94e14cd1004220902z1a88290alb6163585300da216@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <201004231206.38741.achill@matrix.gatewaynet.com>

=CE=A3=CF=84=CE=B9=CF=82 Thursday 22 April 2010 19:02:00 =CE=BF/=CE=B7 C=C3=
=A9dric Villemain =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> 2010/4/22 Achilleas Mantzios <achill@matrix.gatewaynet.com>:
> > =CE=A3=CF=84=CE=B9=CF=82 Thursday 22 April 2010 16:53:05 =CE=BF/=CE=B7 =
C=C3=A9dric Villemain =CE=AD=CE=B3=CF=81=CE=B1=CF=88=CE=B5:
> >> 2010/4/22 Achilleas Mantzios <achill@matrix.gatewaynet.com>:
> >> > Hello,
> >> > i have this serious problem in one of our remote vessels. (comm is d=
one by minicom to the remote satelite modem)
> >> > I think that this server was under some sort of constant resets or h=
ardware failures.
> >> > Initially,i had this problem:
> >> > ERROR: =C2=A0invalid page header in block 672720 of relation "pg_toa=
st_125716009"
> >> >
> >> > This toast table corresponds to a table named "mail_message",
> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Table "public.mail_message"
> >> > =C2=A0Column =C2=A0 | =C2=A0 =C2=A0 =C2=A0 Type =C2=A0 =C2=A0 =C2=A0=
 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 Modifiers
> >> > -----------+-------------------+------------------------------------=
=2D----------------------
> >> > =C2=A0msgno =C2=A0 =C2=A0 | mail_msgno_domain | not null default nex=
tval('mail_msgno_sequence'::regclass)
> >> > =C2=A0msgsource | bytea =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
> >> > Indexes:
> >> > =C2=A0 =C2=A0"mail_message_key" PRIMARY KEY, btree (msgno)
> >> >
> >> > (obviously the TOAST table serves the msgsource varlena) the content=
s of which is not of vital importance.
> >> > I tried, REINDEXING, with no success, and after that, i tried resett=
ing the said block on disk as per this
> >> > suggestion by Tom here: http://old.nabble.com/invalid-page-header-td=
11981154.html
> >> >
> >> > i found the oid of the table:
> >> > SELECT tableoid,1 from pg_toast.pg_toast_125716009 order by chunk_id=
 LIMIT 1
> >> > =C2=A0tableoid =C2=A0| ?column?
> >> > -----------+----------
> >> > =C2=A0125716013 | =C2=A0 =C2=A0 =C2=A0 =C2=A01
> >> >
> >> > (and just to verify)
> >> > SELECT relname from pg_class where oid=3D125716013;
> >> > =C2=A0 =C2=A0 =C2=A0relname
> >> > --------------------
> >> > =C2=A0pg_toast_125716009
> >> >
> >> > Then i did: (as i said i do not need the contents of msgsource - yet=
 the contents of msgno are vital)
> >> > dd if=3D/dev/zero of=3D/usr/local/var/lib/pgsql/data/base/125714957/=
125716013 seek=3D672720 bs=3D8192 count=3D1
> >>
> >> segment have 1.1GB size maximum. You have to catch in what segment the
> >> faulty block is, and reajust the block value from the error report to
> >> the real one in the good segment.
> >>
> >
> > Thanx,
> > Taking a look at /usr/local/src/postgresql-8.3.3/src/backend/storage/sm=
gr/md.c
> > i see the the error comes from function mdnblocks
> >
> > if (nblocks > ((BlockNumber) RELSEG_SIZE))
> > =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0elog(FATAL, "segment too big");
> >
> > That means, that some segment file is bigger than RELSEG_SIZE
> > At least in my system:
> > #define BLCKSZ =C2=A0 8192
> > #define RELSEG_SIZE (0x40000000 / BLCKSZ)
> > So, any segment file cannot be bigger than RELSEG_SIZE blocks (or 2^30 =
bytes =3D 1GB)
> >
> > Currently i dont have any access to the machine but tomorrow i will che=
ck the file sizes.
> >
> > Can anyone shed some light as to some method of identifying all the seg=
ment files of a table?
> > The first one has the same name as the tableoid.
> > How about the subsequent segments?
>=20
> Your execution of dd make your first segment bigger than expected.
> Other segment have the same name with a .1 .2 etc  suffix.
>=20
> You have to shrink your first segment to the correct size.
> check what happens, you should have now the original error.
>=20
> And, I have never used it, but I think it is the purpose of
> zero_damaged_pages to parameter to allow postgresql itself to zero the
> bad black. (reading
> src/backend/storage/buffer/bufmgr.c confirm that.
>=20
> *BUT* take care that it will zero *every* bad page, perhaps not only
> the one trapping an error.
>=20
> In those situation, it is good to make a snapshot of the pgdata
> directory, in case your fingers surf  too fast on the keyboard....
>=20
> If you don't want to activate zero_damage_page,  then go and calculate
> which block in which segment you have to zeroing.
>=20
> side note, it may be usefull to have the relevant information in the
> error message...
>=20


Many Thanks, C=C3=A9dric Villemain and Tom
What i did was first to correct the first segment file with smth like
dd if=3D216293737 of=3D216293737.good seek=3D0 bs=3D8192 count=3D131072
which effectively truncates all but the first 131072 blocks (or 2^30 bytes =
=3D 1GB)

After that was done, and restarting postgresql backend, then i fell back to=
 the situation
with the Invalid page header, as noted before.

I stopped the backend and calculated the exact segment file and offset wher=
e the problem was:
The block with the invalid header was the block with number: 672720=20
Now each segment contains at most 131072 blocks, with all but the last
containing exactly 131072 blocks.
So my problematic segment was the one with number:
672720 /131072  =3D 5
and the block offset inside this segment was:
672720 - (5*131072) =3D 17360

so i tried to zero that patricular block with

dd conv=3Dnotrunc if=3D216293737.5 of=3D216293737.5.GOOD seek=3D17360 bs=3D=
8192 count=3D1

i started postgresql and i threw out a warning about initializing this zero=
 page.
After that, i reported error in header for block 672740,
i repeated the same procedure, and i was able to go further and even reinde=
x the whole database
which went fine.

I tried as the ultimate test (and a useful one at this point) to pg_dump th=
e database.
Unfortunately one table seems to be in error: the error is:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  compressed data is corrupt
pg_dump: The command was: COPY public.mail_entity (msgno, entno, entparentn=
o....

I think the initial issue of this thread is solved, i'll come back with new=
s on the pg_dump issue.

> >
> >> >
> >> > However, after that, unfortunately i get constant postgresql server =
restarts with:
> >> > FATAL: =C2=A0segment too big
> >> > server closed the connection unexpectedly
> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0This probably means the server terminated=
 abnormally
> >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0before or while processing the request.
> >> > The connection to the server was lost. Attempting reset: Succeeded.
> >> >
> >> > Is there anything i can do to savage the situation?
> >> >
> >> > (one of) the hard part here is that i dont have neither physical nor=
 network access to the server
> >> > (only ultra expensive unreliable satellite comms)
> >> >
> >> > Thanks for any hints...
> >> >
> >> > --
> >> > Achilleas Mantzios
> >> >
> >> > --
> >> > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> >> > To make changes to your subscription:
> >> > http://www.postgresql.org/mailpref/pgsql-admin
> >> >
> >>
> >>
> >>
> >
> >
> >
> > --
> > Achilleas Mantzios
> >
>=20
>=20
>=20
> --=20
> C=C3=A9dric Villemain
>=20


=2D-=20
Achilleas Mantzios