Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42668=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1X9D3q-0005xq-Gn
 for pgsql-admin@arkaria.postgresql.org; Mon, 21 Jul 2014 12:51:22 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42668=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1X9D3q-00049O-0K
 for pgsql-admin@arkaria.postgresql.org; Mon, 21 Jul 2014 12:51:22 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1X9D3o-00049F-IO
 for pgsql-admin@postgresql.org; Mon, 21 Jul 2014 12:51:20 +0000
Received: from mail-yh0-x22e.google.com ([2607:f8b0:4002:c01::22e])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <jayknowsunix@gmail.com>)
 id 1X9D3j-0006tN-SR
 for pgsql-admin@postgresql.org; Mon, 21 Jul 2014 12:51:18 +0000
Received: by mail-yh0-f46.google.com with SMTP id a41so3976044yho.5
 for <pgsql-admin@postgresql.org>; Mon, 21 Jul 2014 05:51:14 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=references:mime-version:in-reply-to:content-type
 :content-transfer-encoding:message-id:cc:from:subject:date:to;
 bh=JfBx38NULrq2+RjpYhTborTfWxZ55t8nqXITTGZHeeo=;
 b=nDKMarZy9yk7DgxqgXCld47DzOpUrWDUjuL9tro+IlK/htJt5zbqCmcFRzCuVrHS1e
 8yAc+LkhfDnxXm4FauQv/vkUgnF69sqMZ9ftH/21JKeBgBaKAJcazpXFVTM0XA974UBO
 HcnfJKI/UoyGknC0xG01SWoOc+QHArrsE6z+epiIn58Ud8UIMFP11v5Voc4MGDCkPS+f
 uOe65NLqONpMODyo5HSOa/ZgzMlL/1VwPE/N9zl4j6je1EG7VsSiZFOGgkb1f9+fM1Gx
 PzaJ9LtXZrL1DLLabNVsOR/jsAdVjF7NClUTcSVRQCrcWefv1VY9vAJKfpgBigcTagM1
 xfnw==
X-Received: by 10.236.36.45 with SMTP id v33mr40131128yha.129.1405947074708;
 Mon, 21 Jul 2014 05:51:14 -0700 (PDT)
Received: from [192.168.254.2] (h77.89.102.166.static.ip.windstream.net.
 [166.102.89.77])
 by mx.google.com with ESMTPSA id d66sm27669136yhd.41.2014.07.21.05.51.13
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 21 Jul 2014 05:51:14 -0700 (PDT)
References: <1361CEF686657C41A139AD8C3145632B2A00F636@E2010-MB1.manord.com>
Mime-Version: 1.0 (1.0)
In-Reply-To: <1361CEF686657C41A139AD8C3145632B2A00F636@E2010-MB1.manord.com>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Message-Id: <5BC3D7B4-2567-4956-9E68-CE29DCD7C528@gmail.com>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
X-Mailer: iPad Mail (11D257)
From: jayknowsunix@gmail.com
Subject: Re: "ERROR: could not access status of transaction" (after upgrding
 from 9.3.2 to 9.3.4?)
Date: Mon, 21 Jul 2014 08:51:15 -0400
To: "Cassiano, Marco" <mcassiano@manord.com>
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

I've seen no other responses, but since I fought an identical error last we=
ek, I'll chime in with what I've learned. FYI, I didn't have any success ot=
her than restoring from a previous backup.

First off, you should determine the fate of the missing pg_clog file. Is it=
 actually gone? If so, do you have file system damage? Maybe an fsck is in =
order. Maybe its permissions got hosed. Maybe you could fix that problem. I=
f you didn't upgrade in place, maybe you could just run the upgrade again. =
Standard procedure for recovery from this is to restart your database with =
the zero_damaged_pages =3D true setting enabled. Then you run a VACUUUM FUL=
L ANALYZE on that table. Be advised that any damage this finds will cause a=
 loss of data. Once the damage has been removed, reindex the table, and res=
tart the database with zero_damaged_pages off. Now, you'll have to determin=
e what data you lost and recover that manually. I've never gotten this to w=
ork, however. So...

You say that you can query the table, but it's doubtful that every record i=
s available, and you could also perform selects against this table to copy =
records out to a new temp table. It will probably take both trial and error=
 and multiple select attempts to get all the uncorrupted data out of this t=
able. Once you extracted everything you can get, drop the bad table and ren=
ame your temporary table to the name of the old bad one. Again, you still h=
ave a loss of data here, but if you recorded what you did in your selects, =
you probably know what disappeared.

--
Jay

Sent from my iPad

> On Jul 21, 2014, at 5:27 AM, "Cassiano, Marco" <mcassiano@manord.com> wro=
te:
>=20
> Hi all,
>=20
>=20
>=20
> every weekend we use to run a batch that makes reindex/cluster of our mai=
n db.
>=20
> This weekend we observed this error in the log :
>=20
>=20
>=20
> 2014-07-19 23:00:41 GMT 172.30.2.200(53959) dba mdn 1265998975 53caf874.6=
826 - ERROR:  could not access status of transaction 1067517164
> 2014-07-19 23:00:41 GMT 172.30.2.200(53959) dba mdn 1265998975 53caf874.6=
826 - DETAIL:  Could not open file "pg_clog/03FA": No such file or director=
y.
> 2014-07-19 23:00:41 GMT 172.30.2.200(53959) dba mdn 1265998975 53caf874.6=
826 - STATEMENT:  cluster pk__cat_taglio on "anamat"."cat_taglio"
>=20
>=20
>=20
> The previous weekend the job worked fine.
>=20
>=20
>=20
>=20
>=20
> The only thing that happened in the middle  (AFAIK) is that on the 17th w=
e apply the minor upgrade of postgresql from 9.3.2 to 9.3.4.
>=20
>=20
>=20
> Can you please help me to understand the cause of this corruption, how mu=
ch is serious and the its correct resolution ?
>=20
>=20
>=20
> By the way the data in the table seems ok : i can dump the table, I can s=
elect the data inside it.
>=20
>=20
>=20
> SInce this table doesn't change frequently, we are considering a dump/del=
ete/restore. Will this fix the problem in yuor opinion ?
>=20
>=20
>=20
>=20
>=20
> Thanks a lot
>=20
>=20
>=20
> Marco Cassiano
>=20
>=20
>=20
>=20
> --=20
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin