Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B96DE6335CB for ; Thu, 22 Apr 2010 14:02:11 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49497-01-8 for ; Thu, 22 Apr 2010 17:02:00 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ew0-f220.google.com (mail-ew0-f220.google.com [209.85.219.220]) by mail.postgresql.org (Postfix) with ESMTP id 0A18A63EF6A for ; Thu, 22 Apr 2010 13:02:21 -0300 (ADT) Received: by ewy20 with SMTP id 20so2845802ewy.21 for ; Thu, 22 Apr 2010 09:02:20 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:received:message-id:subject:to:cc:content-type :content-transfer-encoding; bh=/E+A7Qu+1PJAvfPpbx2AwuD4uiQDEdhH+sF33W07A7E=; b=aEgYB0cObVKrpulUjz2bW/RaT1+SMkSsajn0z05VSdM7TfjrMgGFLhm5NXE6wJFtnW dLmgoHqsWnAhkCVB33cHroj0H5AOvR5TafC3dLJKT/rcGaUNP8ZxE5yGS0uPY/bi6cej +WjYjDfOQvKMpzAju5wxYXbI7coI9/b9/Bgxw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; b=hAOl3abwKRbp2j7ovO58mKQIRUQoR53elc0sO4WgXJ8pZiI088QdYlj2yAjqs5bZfL o3UaQmM8+NhXlmUNGVk+x2sQtZhvsnIsgijzXb9sYTBGBUHpSROGZwu87HpfCnne9eKZ hEWS5/TEO2KugXwdzpRYt3tOmfSKMwvAaqQDE= MIME-Version: 1.0 Received: by 10.239.158.134 with HTTP; Thu, 22 Apr 2010 09:02:00 -0700 (PDT) In-Reply-To: <201004221818.51840.achill@matrix.gatewaynet.com> References: <201004221516.08729.achill@matrix.gatewaynet.com> <201004221818.51840.achill@matrix.gatewaynet.com> From: =?ISO-8859-1?Q?C=E9dric_Villemain?= Date: Thu, 22 Apr 2010 18:02:00 +0200 Received: by 10.239.193.141 with SMTP id j13mr929460hbi.188.1271952140137; Thu, 22 Apr 2010 09:02:20 -0700 (PDT) Message-ID: Subject: Re: pgsql 8.3.3 "Invalid page header" leads to "FATAL: segment too big" To: Achilleas Mantzios Cc: "pgsql-admin@postgresql.org" Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201004/307 X-Sequence-Number: 34401 2010/4/22 Achilleas Mantzios : > =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 : >> > Hello, >> > i have this serious problem in one of our remote vessels. (comm is don= e by minicom to the remote satelite modem) >> > I think that this server was under some sort of constant resets or har= dware failures. >> > Initially,i had this problem: >> > ERROR: =C2=A0invalid page header in block 672720 of relation "pg_toast= _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 >> > -----------+-------------------+--------------------------------------= --------------------- >> > =C2=A0msgno =C2=A0 =C2=A0 | mail_msgno_domain | not null default nextv= al('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 contents = of which is not of vital importance. >> > I tried, REINDEXING, with no success, and after that, i tried resettin= g the said block on disk as per this >> > suggestion by Tom here: http://old.nabble.com/invalid-page-header-td11= 981154.html >> > >> > i found the oid of the table: >> > SELECT tableoid,1 from pg_toast.pg_toast_125716009 order by chunk_id L= IMIT 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 t= he contents of msgno are vital) >> > dd if=3D/dev/zero of=3D/usr/local/var/lib/pgsql/data/base/125714957/12= 5716013 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/smgr= /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 by= tes =3D 1GB) > > Currently i dont have any access to the machine but tomorrow i will check= the file sizes. > > Can anyone shed some light as to some method of identifying all the segme= nt files of a table? > The first one has the same name as the tableoid. > How about the subsequent segments? Your execution of dd make your first segment bigger than expected. Other segment have the same name with a .1 .2 etc suffix. You have to shrink your first segment to the correct size. check what happens, you should have now the original error. 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. *BUT* take care that it will zero *every* bad page, perhaps not only the one trapping an error. In those situation, it is good to make a snapshot of the pgdata directory, in case your fingers surf too fast on the keyboard.... If you don't want to activate zero_damage_page, then go and calculate which block in which segment you have to zeroing. side note, it may be usefull to have the relevant information in the error message... > >> > >> > However, after that, unfortunately i get constant postgresql server re= starts with: >> > FATAL: =C2=A0segment too big >> > server closed the connection unexpectedly >> > =C2=A0 =C2=A0 =C2=A0 =C2=A0This probably means the server terminated a= bnormally >> > =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 n= etwork 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 C=C3=A9dric Villemain