From stevieg@web.de Thu Mar  24 12:39:45 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 2FA1F1337BC2
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 24 Mar 2011 09:39:45 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 84132-07 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 24 Mar 2011 12:39:27 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from fmmailgate02.web.de (fmmailgate02.web.de [217.72.192.227])
	by mail.postgresql.org (Postfix) with ESMTP id 618971337BBB
	for <pgsql-admin@postgresql.org>; Thu, 24 Mar 2011 09:39:26 -0300 (ADT)
Received: from smtp04.web.de  ( [172.20.0.225])
	by fmmailgate02.web.de (Postfix) with ESMTP id 2523B19B0F911
	for <pgsql-admin@postgresql.org>; Thu, 24 Mar 2011 13:39:25 +0100 (CET)
Received: from [91.66.178.84] (helo=[192.168.179.13])
	by smtp04.web.de with asmtp (TLSv1:AES256-SHA:256) (WEB.DE 4.110 #2)
	id 1Q2joy-00053T-00
	for pgsql-admin@postgresql.org; Thu, 24 Mar 2011 13:39:25 +0100
Subject: ERROR: could not access status of transaction
From: Stevie <stevieg@web.de>
To: pgsql-admin@postgresql.org
Content-Type: multipart/mixed; boundary="=-KO2lm2regcquOctxegCo"
Date: Thu, 24 Mar 2011 13:39:22 +0100
Message-ID: <1300970362.2349.27.camel@stevie>
Mime-Version: 1.0
X-Mailer: Evolution 2.30.3 
X-Sender: stevieg@web.de
X-Provags-ID: V01U2FsdGVkX1+q0/ldZwGKZ1WfcN31vF+mPPsKXKSryhbCTziB
	Kx6qpwJzQs1O5DpcQayB39c8c7Is8H3nkajeq5KMi59eNkHTXA BCmpKpCU0=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.898 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001,
	RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01,
	T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201103/232
X-Sequence-Number: 37104


--=-KO2lm2regcquOctxegCo
Content-Type: multipart/alternative; boundary="=-y8A7L/Rkd9QSMmR0Mp+c"


--=-y8A7L/Rkd9QSMmR0Mp+c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

Hello pgsql-admins,

we have a problem with our Postgresql 9.0.3 backup database (Ubuntu
10.4).
The backup and restore is done as described here:
http://www.postgresql.org/docs/9.0/static/continuous-archiving.html
If you want to know the exact steps of backup and restore,please ask.

We restore the database on the backup host and it's starting. I've
append the mainlog.

If we dump or reindex the database we get "could not access status of
transaction NNNNNN" errors.
PG tries to access old clog files: "pg_clog/00CD". But in the pg_clog
directory the files start at "00E6" and end at "0198".
I've append the pg_controldata output. This clog files doesn't exist on
the original database neither.

I could create empty clog files, but it seems that our database is in an
inconsistent state?
Any suggestions are welcome.

Regards
Steffen

--=-y8A7L/Rkd9QSMmR0Mp+c
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.30.3">
</HEAD>
<BODY>
Hello pgsql-admins,<BR>
<BR>
we have a problem with our Postgresql 9.0.3 backup database (Ubuntu 10.4).<BR>
The backup and restore is done as described here: <A HREF="http://www.postgresql.org/docs/9.0/static/continuous-archiving.html">http://www.postgresql.org/docs/9.0/static/continuous-archiving.html</A><BR>
If you want to know the exact steps of backup and restore,please ask.<BR>
<BR>
We restore the database on the backup host and it's starting. I've append the mainlog.<BR>
<BR>
If we dump or reindex the database we get &quot;could not access status of transaction NNNNNN&quot; errors.<BR>
PG tries to access old clog files: &quot;pg_clog/00CD&quot;. But in the pg_clog directory the files start at &quot;00E6&quot; and end at &quot;0198&quot;.<BR>
I've append the pg_controldata output. This clog files doesn't exist on the original database neither.<BR>
<BR>
I could create empty clog files, but it seems that our database is in an inconsistent state?<BR>
Any suggestions are welcome.<BR>
<BR>
Regards<BR>
Steffen
</BODY>
</HTML>

--=-y8A7L/Rkd9QSMmR0Mp+c--

--=-KO2lm2regcquOctxegCo
Content-Disposition: attachment; filename="pg_controldata.txt"
Content-Type: text/plain; name="pg_controldata.txt"; charset="UTF-8"
Content-Transfer-Encoding: 7bit

pg_control version number:            903
Catalog version number:               201008051
Database system identifier:           5531973395283826175
Database cluster state:               in production
pg_control last modified:             Thu 24 Mar 2011 12:22:58 PM UTC
Latest checkpoint location:           3FD/3528A5C0
Prior checkpoint location:            3FD/352144C0
Latest checkpoint's REDO location:    3FD/3524ED30
Latest checkpoint's TimeLineID:       2
Latest checkpoint's NextXID:          0/428237218
Latest checkpoint's NextOID:          204294451
Latest checkpoint's NextMultiXactId:  406490
Latest checkpoint's NextMultiOffset:  901370
Latest checkpoint's oldestXID:        242122419
Latest checkpoint's oldestXID's DB:   16409
Latest checkpoint's oldestActiveXID:  0
Time of latest checkpoint:            Thu 24 Mar 2011 12:22:23 PM UTC
Minimum recovery ending location:     0/0
Backup start location:                0/0
Current wal_level setting:            minimal
Current max_connections setting:      100
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value


--=-KO2lm2regcquOctxegCo
Content-Disposition: attachment; filename="postgresql-9.0-main.log"
Content-Type: text/x-log; name="postgresql-9.0-main.log"; charset="UTF-8"
Content-Transfer-Encoding: 7bit

2011-03-24 11:36:56 UTC LOG:  database system was interrupted; last known up at 2011-03-24 05:35:14 UTC
2011-03-24 11:36:56 UTC LOG:  incomplete startup packet
2011-03-24 11:36:56 UTC LOG:  starting archive recovery
2011-03-24 11:36:56 UTC LOG:  restored log file "00000001000003FC000000DC" from archive
2011-03-24 11:36:56 UTC LOG:  redo starts at 3FC/DC000020
2011-03-24 11:36:56 UTC FATAL:  the database system is starting up
2011-03-24 11:36:56 UTC LOG:  restored log file "00000001000003FC000000DD" from archive
2011-03-24 11:36:57 UTC LOG:  restored log file "00000001000003FC000000DE" from archive
2011-03-24 11:36:57 UTC FATAL:  the database system is starting up
2011-03-24 11:36:57 UTC LOG:  restored log file "00000001000003FC000000DF" from archive
2011-03-24 11:36:57 UTC LOG:  restored log file "00000001000003FC000000E0" from archive
2011-03-24 11:36:57 UTC LOG:  restored log file "00000001000003FC000000E1" from archive
2011-03-24 11:36:57 UTC FATAL:  the database system is starting up
2011-03-24 11:36:57 UTC LOG:  restored log file "00000001000003FC000000E2" from archive
2011-03-24 11:36:58 UTC LOG:  restored log file "00000001000003FC000000E3" from archive
2011-03-24 11:36:58 UTC LOG:  restored log file "00000001000003FC000000E4" from archive
2011-03-24 11:36:58 UTC FATAL:  the database system is starting up
2011-03-24 11:36:58 UTC LOG:  restored log file "00000001000003FC000000E5" from archive
2011-03-24 11:36:58 UTC FATAL:  the database system is starting up
2011-03-24 11:36:58 UTC LOG:  restored log file "00000001000003FC000000E6" from archive
2011-03-24 11:36:59 UTC LOG:  restored log file "00000001000003FC000000E7" from archive
2011-03-24 11:36:59 UTC LOG:  restored log file "00000001000003FC000000E8" from archive
2011-03-24 11:36:59 UTC FATAL:  the database system is starting up
2011-03-24 11:36:59 UTC LOG:  restored log file "00000001000003FC000000E9" from archive
2011-03-24 11:36:59 UTC LOG:  restored log file "00000001000003FC000000EA" from archive
2011-03-24 11:36:59 UTC LOG:  restored log file "00000001000003FC000000EB" from archive
2011-03-24 11:36:59 UTC FATAL:  the database system is starting up
2011-03-24 11:37:00 UTC LOG:  restored log file "00000001000003FC000000EC" from archive
2011-03-24 11:37:00 UTC LOG:  restored log file "00000001000003FC000000ED" from archive
2011-03-24 11:37:00 UTC FATAL:  the database system is starting up
2011-03-24 11:37:00 UTC LOG:  restored log file "00000001000003FC000000EE" from archive
2011-03-24 11:37:00 UTC LOG:  restored log file "00000001000003FC000000EF" from archive
2011-03-24 11:37:00 UTC LOG:  restored log file "00000001000003FC000000F0" from archive
2011-03-24 11:37:00 UTC FATAL:  the database system is starting up
2011-03-24 11:37:01 UTC LOG:  restored log file "00000001000003FC000000F1" from archive
2011-03-24 11:37:01 UTC LOG:  restored log file "00000001000003FC000000F2" from archive
2011-03-24 11:37:01 UTC FATAL:  the database system is starting up
2011-03-24 11:37:01 UTC LOG:  restored log file "00000001000003FC000000F3" from archive
2011-03-24 11:37:01 UTC LOG:  restored log file "00000001000003FC000000F4" from archive
2011-03-24 11:37:01 UTC FATAL:  the database system is starting up
2011-03-24 11:37:01 UTC LOG:  incomplete startup packet
2011-03-24 11:37:02 UTC LOG:  restored log file "00000001000003FC000000F5" from archive
2011-03-24 11:37:02 UTC LOG:  restored log file "00000001000003FC000000F6" from archive
2011-03-24 11:37:02 UTC LOG:  restored log file "00000001000003FC000000F7" from archive
2011-03-24 11:37:03 UTC LOG:  restored log file "00000001000003FC000000F8" from archive
2011-03-24 11:37:03 UTC LOG:  restored log file "00000001000003FC000000F9" from archive
2011-03-24 11:37:03 UTC LOG:  restored log file "00000001000003FC000000FA" from archive
2011-03-24 11:37:04 UTC LOG:  restored log file "00000001000003FC000000FB" from archive
2011-03-24 11:37:04 UTC LOG:  restored log file "00000001000003FC000000FC" from archive
2011-03-24 11:37:04 UTC LOG:  restored log file "00000001000003FC000000FD" from archive
2011-03-24 11:37:05 UTC LOG:  restored log file "00000001000003FC000000FE" from archive
2011-03-24 11:37:05 UTC LOG:  restored log file "00000001000003FD00000000" from archive
2011-03-24 11:37:05 UTC LOG:  restored log file "00000001000003FD00000001" from archive
2011-03-24 11:37:05 UTC LOG:  restored log file "00000001000003FD00000002" from archive
2011-03-24 11:37:06 UTC LOG:  restored log file "00000001000003FD00000003" from archive
2011-03-24 11:37:06 UTC LOG:  restored log file "00000001000003FD00000004" from archive
2011-03-24 11:37:06 UTC LOG:  restored log file "00000001000003FD00000005" from archive
2011-03-24 11:37:07 UTC LOG:  restored log file "00000001000003FD00000006" from archive
2011-03-24 11:37:07 UTC LOG:  restored log file "00000001000003FD00000007" from archive
2011-03-24 11:37:08 UTC LOG:  restored log file "00000001000003FD00000008" from archive
2011-03-24 11:37:08 UTC LOG:  restored log file "00000001000003FD00000009" from archive
2011-03-24 11:37:08 UTC LOG:  restored log file "00000001000003FD0000000A" from archive
2011-03-24 11:37:09 UTC LOG:  restored log file "00000001000003FD0000000B" from archive
2011-03-24 11:37:09 UTC LOG:  restored log file "00000001000003FD0000000C" from archive
2011-03-24 11:37:09 UTC LOG:  restored log file "00000001000003FD0000000D" from archive
2011-03-24 11:37:10 UTC LOG:  restored log file "00000001000003FD0000000E" from archive
2011-03-24 11:37:10 UTC LOG:  restored log file "00000001000003FD0000000F" from archive
2011-03-24 11:37:10 UTC LOG:  restored log file "00000001000003FD00000010" from archive
2011-03-24 11:37:10 UTC LOG:  restored log file "00000001000003FD00000011" from archive
2011-03-24 11:37:11 UTC LOG:  restored log file "00000001000003FD00000012" from archive
2011-03-24 11:37:11 UTC LOG:  restored log file "00000001000003FD00000013" from archive
2011-03-24 11:37:11 UTC LOG:  restored log file "00000001000003FD00000014" from archive
2011-03-24 11:37:12 UTC LOG:  restored log file "00000001000003FD00000015" from archive
2011-03-24 11:37:12 UTC LOG:  restored log file "00000001000003FD00000016" from archive
2011-03-24 11:37:13 UTC LOG:  restored log file "00000001000003FD00000017" from archive
2011-03-24 11:37:13 UTC LOG:  restored log file "00000001000003FD00000018" from archive
2011-03-24 11:37:13 UTC LOG:  restored log file "00000001000003FD00000019" from archive
2011-03-24 11:37:14 UTC LOG:  restored log file "00000001000003FD0000001A" from archive
2011-03-24 11:37:14 UTC LOG:  restored log file "00000001000003FD0000001B" from archive
2011-03-24 11:37:14 UTC LOG:  restored log file "00000001000003FD0000001C" from archive
2011-03-24 11:37:15 UTC LOG:  restored log file "00000001000003FD0000001D" from archive
2011-03-24 11:37:15 UTC LOG:  restored log file "00000001000003FD0000001E" from archive
2011-03-24 11:37:15 UTC LOG:  restored log file "00000001000003FD0000001F" from archive
2011-03-24 11:37:16 UTC LOG:  restored log file "00000001000003FD00000020" from archive
2011-03-24 11:37:16 UTC LOG:  restored log file "00000001000003FD00000021" from archive
2011-03-24 11:37:16 UTC LOG:  restored log file "00000001000003FD00000022" from archive
2011-03-24 11:37:17 UTC LOG:  restored log file "00000001000003FD00000023" from archive
2011-03-24 11:37:17 UTC LOG:  restored log file "00000001000003FD00000024" from archive
2011-03-24 11:37:17 UTC LOG:  restored log file "00000001000003FD00000025" from archive
2011-03-24 11:37:18 UTC LOG:  restored log file "00000001000003FD00000026" from archive
2011-03-24 11:37:18 UTC LOG:  restored log file "00000001000003FD00000027" from archive
2011-03-24 11:37:18 UTC LOG:  restored log file "00000001000003FD00000028" from archive
2011-03-24 11:37:19 UTC LOG:  restored log file "00000001000003FD00000029" from archive
2011-03-24 11:37:19 UTC LOG:  restored log file "00000001000003FD0000002A" from archive
2011-03-24 11:37:20 UTC LOG:  restored log file "00000001000003FD0000002B" from archive
2011-03-24 11:37:20 UTC LOG:  restored log file "00000001000003FD0000002C" from archive
2011-03-24 11:37:20 UTC LOG:  restored log file "00000001000003FD0000002D" from archive
2011-03-24 11:37:21 UTC LOG:  restored log file "00000001000003FD0000002E" from archive
2011-03-24 11:37:21 UTC LOG:  restored log file "00000001000003FD0000002F" from archive
2011-03-24 11:37:21 UTC LOG:  restored log file "00000001000003FD00000030" from archive
2011-03-24 11:37:21 UTC LOG:  restored log file "00000001000003FD00000031" from archive
2011-03-24 11:37:22 UTC LOG:  restored log file "00000001000003FD00000032" from archive
2011-03-24 11:37:22 UTC LOG:  restored log file "00000001000003FD00000033" from archive
2011-03-24 11:37:22 UTC LOG:  consistent recovery state reached at 3FD/34000000
2011-03-24 11:37:22 UTC LOG:  restored log file "00000001000003FD00000034" from archive
cp: cannot stat `/tmp/replication/00000001000003FD00000035': No such file or directory
2011-03-24 11:37:23 UTC LOG:  could not open file "pg_xlog/00000001000003FD00000035" (log file 1021, segment 53): No such file or directory
2011-03-24 11:37:23 UTC LOG:  redo done at 3FD/34017098
2011-03-24 11:37:23 UTC LOG:  last completed transaction was at log time 2011-03-24 05:41:50.355309+00
2011-03-24 11:37:23 UTC LOG:  restored log file "00000001000003FD00000034" from archive
cp: cannot stat `/tmp/replication/00000002.history': No such file or directory
2011-03-24 11:37:23 UTC LOG:  selected new timeline ID: 2
cp: cannot stat `/tmp/replication/00000001.history': No such file or directory
2011-03-24 11:37:23 UTC LOG:  archive recovery complete
2011-03-24 11:39:23 UTC LOG:  autovacuum launcher started
2011-03-24 11:39:23 UTC LOG:  database system is ready to accept connections
2011-03-24 11:47:58 UTC ERROR:  could not access status of transaction 215826066
2011-03-24 11:47:58 UTC DETAIL:  Could not open file "pg_clog/00CD": No such file or directory.
2011-03-24 11:47:58 UTC STATEMENT:  COPY domain.publications (publication_id, publication_title, publication_date, publication_type, science_field_keywords, publication_authors, publication_source_key, publication_file, publication_issn, last_modified, publication_file_upload_date, publication_uid) TO stdout;
2011-03-24 12:02:09 UTC ERROR:  could not access status of transaction 207617642
2011-03-24 12:02:09 UTC DETAIL:  Could not open file "pg_clog/00C5": No such file or directory.
2011-03-24 12:22:36 UTC STATEMENT:  REINDEX DATABASE rg_www;

--=-KO2lm2regcquOctxegCo--




From raghuchennuru@gmail.com Thu Mar  24 13:43:34 2011
Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id AA81113364DE
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 24 Mar 2011 10:43:34 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 13976-04 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Thu, 24 Mar 2011 13:43:17 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-qw0-f46.google.com (mail-qw0-f46.google.com
	[209.85.216.46])
	by mail.postgresql.org (Postfix) with ESMTP id D05761337BCF
	for <pgsql-admin@postgresql.org>; Thu, 24 Mar 2011 10:43:16 -0300 (ADT)
Received: by qwk3 with SMTP id 3so6639156qwk.19
	for <pgsql-admin@postgresql.org>; Thu, 24 Mar 2011 06:43:17 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:in-reply-to:references:date
	:message-id:subject:from:to:cc:content-type;
	bh=tjMrKLoRlhkPLcnz5BxjE7Uz3o54pxsQcQNrwvvbqDQ=;
	b=n1MrkzGlXKaBisjRPCriugdxqhNCE+o/t0iYWevIxpSzAWbdxWYJm3uAF3nV58/iNy
	vqmtQCOKZKtzcQBqVWewqVyziAwddVQexAMVnN9zpgy+P3lRgj8jK1UBC7agPV9/A08Z
	kARh6SQXQi2/PgCKEBzTc5TQPB7sVA+5nuWAg=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=GqAqyKRWSJdicMuDlHkNSbrQahlLCsBixVvezagEyviDivAvy1/nJ64qwypbjTE/IV
	pQUXgZR0Mr1YcaYh5wJQHpmQMUNDQvMeZQw0uPjJhWEcHQ9YoTyU/96+7tCapNlug6S0
	0ka3+EFQkv62YaTPYvP6AcoAI6fkIVjtNRtj0=
MIME-Version: 1.0
Received: by 10.224.211.65 with SMTP id gn1mr6969617qab.355.1300974195449;
	Thu, 24 Mar 2011 06:43:15 -0700 (PDT)
Received: by 10.224.37.81 with HTTP; Thu, 24 Mar 2011 06:43:15 -0700 (PDT)
In-Reply-To: <1300970362.2349.27.camel@stevie>
References: <1300970362.2349.27.camel@stevie>
Date: Thu, 24 Mar 2011 19:13:15 +0530
Message-ID: <AANLkTims48njGKkvkXHiTG6=19hQ31VFmFRPPLg+5w03@mail.gmail.com>
Subject: Re: ERROR: could not access status of transaction
From: raghu ram <raghuchennuru@gmail.com>
To: Stevie <stevieg@web.de>
Cc: pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary=20cf300fad73c81da4049f3aa85d
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001,
	RCVD_IN_DNSWL_LOW=-0.7
X-Spam-Level: 
X-Archive-Number: 201103/235
X-Sequence-Number: 37107

--20cf300fad73c81da4049f3aa85d
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Mar 24, 2011 at 6:09 PM, Stevie <stevieg@web.de> wrote:

>  Hello pgsql-admins,
>
> we have a problem with our Postgresql 9.0.3 backup database (Ubuntu 10.4).
> The backup and restore is done as described here:
> http://www.postgresql.org/docs/9.0/static/continuous-archiving.html
> If you want to know the exact steps of backup and restore,please ask.
>
> We restore the database on the backup host and it's starting. I've append
> the mainlog.
>
> If we dump or reindex the database we get "could not access status of
> transaction NNNNNN" errors.
> PG tries to access old clog files: "pg_clog/00CD". But in the pg_clog
> directory the files start at "00E6" and end at "0198".
> I've append the pg_controldata output. This clog files doesn't exist on the
> original database neither.
>
> I could create empty clog files, but it seems that our database is in an
> inconsistent state?
> Any suggestions are welcome.
>
>
There is a commit log corruption in your database. You need to make the
files with right size (256K of zeroes).A suitable "dd" from /dev/zero will
accomplish this on modernUnixen (ie, anything that has /dev/zero). Filling
of those files with zeroes nothing but  " all those old transactions are
aborted".



The safest way to resolve the issue by using the below command:-


dd if=/dev/zero of=Transaction_missing_file_of pg_clog bs=256K count=1 (To
make the uncommitted record as they haven't been committed).


--Raghu Ram




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

--20cf300fad73c81da4049f3aa85d
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<br><br><div class=3D"gmail_quote">On Thu, Mar 24, 2011 at 6:09 PM, Stevie =
<span dir=3D"ltr">&lt;<a href=3D"mailto:stevieg@web.de">stevieg@web.de</a>&=
gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 0 =
0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">



 =20
 =20

<div>
Hello pgsql-admins,<br>
<br>
we have a problem with our Postgresql 9.0.3 backup database (Ubuntu 10.4).<=
br>
The backup and restore is done as described here: <a href=3D"http://www.pos=
tgresql.org/docs/9.0/static/continuous-archiving.html" target=3D"_blank">ht=
tp://www.postgresql.org/docs/9.0/static/continuous-archiving.html</a><br>
If you want to know the exact steps of backup and restore,please ask.<br>
<br>
We restore the database on the backup host and it&#39;s starting. I&#39;ve =
append the mainlog.<br>
<br>
If we dump or reindex the database we get &quot;could not access status of =
transaction NNNNNN&quot; errors.<br>
PG tries to access old clog files: &quot;pg_clog/00CD&quot;. But in the pg_=
clog directory the files start at &quot;00E6&quot; and end at &quot;0198&qu=
ot;.<br>
I&#39;ve append the pg_controldata output. This clog files doesn&#39;t exis=
t on the original database neither.<br>
<br>
I could create empty clog files, but it seems that our database is in an in=
consistent state?<br>
Any suggestions are welcome.<br>
<br></div></blockquote><div><br></div><div><p style=3D"margin: 0.0px 0.0px =
0.0px 0.0px; font: 13.0px Arial">There is a commit log corruption in your d=
atabase. You need to make the files with right size (256K of zeroes).A suit=
able &quot;dd&quot; from /dev/zero will accomplish this on modernUnixen (ie=
, anything that has /dev/zero). Filling of those files with zeroes nothing =
but =A0&quot; all those old transactions are aborted&quot;.</p>

<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; min-height=
: 15.0px"><br></p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; min-height=
: 15.0px"><br></p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial">The safest=
 way to resolve the issue by using the below command:-</p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial; min-height=
: 15.0px"><br></p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial">dd if=3D/d=
ev/zero of=3DTransaction_missing_file_of pg_clog bs=3D256K count=3D1 (To ma=
ke the uncommitted record as they haven&#39;t been committed).</p><p style=
=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial">
<br></p><p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 13.0px Arial">--=
Raghu Ram</p></div><div><br></div><div><br></div><div>=A0</div><blockquote =
class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid=
;padding-left:1ex;">
<div>
Regards<br>
Steffen
</div>

<br><br>
--<br>
Sent via pgsql-admin mailing list (<a href=3D"mailto:pgsql-admin@postgresql=
.org">pgsql-admin@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-admin" target=3D"_blank=
">http://www.postgresql.org/mailpref/pgsql-admin</a><br>
<br></blockquote></div><br>

--20cf300fad73c81da4049f3aa85d--



From stevieg@web.de Fri Mar  25 08:50:17 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 53BEA1337B4E
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 25 Mar 2011 05:50:17 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 55263-01 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 25 Mar 2011 08:50:07 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from fmmailgate02.web.de (fmmailgate02.web.de [217.72.192.227])
	by mail.postgresql.org (Postfix) with ESMTP id 7A5C91337B43
	for <pgsql-admin@postgresql.org>; Fri, 25 Mar 2011 05:50:06 -0300 (ADT)
Received: from smtp05.web.de  ( [172.20.4.166])
	by fmmailgate02.web.de (Postfix) with ESMTP id 5CC4919B1A01B
	for <pgsql-admin@postgresql.org>; Fri, 25 Mar 2011 09:50:05 +0100 (CET)
Received: from [91.66.178.84] (helo=[192.168.179.13])
	by smtp05.web.de with asmtp (TLSv1:AES256-SHA:256) (WEB.DE 4.110 #2)
	id 1Q32ib-0004yM-00
	for pgsql-admin@postgresql.org; Fri, 25 Mar 2011 09:50:05 +0100
Subject: Re: ERROR: could not access status of transaction
From: Stevie <stevieg@web.de>
To: pgsql-admin <pgsql-admin@postgresql.org>
In-Reply-To: <AANLkTims48njGKkvkXHiTG6=19hQ31VFmFRPPLg+5w03@mail.gmail.com>
References: <1300970362.2349.27.camel@stevie>
	<AANLkTims48njGKkvkXHiTG6=19hQ31VFmFRPPLg+5w03@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Date: Fri, 25 Mar 2011 09:50:04 +0100
Message-ID: <1301043004.2357.8.camel@stevie>
Mime-Version: 1.0
X-Mailer: Evolution 2.30.3 
Content-Transfer-Encoding: 7bit
X-Sender: stevieg@web.de
X-Provags-ID: V01U2FsdGVkX18YbeiV5M3svLhB0nIx76kH81GcwyIrXy87/iOf
	NazGldSRNOMn7N5A2h4oMBsfRi37iyNH8zn4O6rtG1wqvrygAG DMPFgiWZ4=
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201103/239
X-Sequence-Number: 37111

Hello

> There is a commit log corruption in your database. 

Yes, and it seems this is produce by a pg_upgrade bug from pg8 to pg9 in
October 2010. 
> You need to make the files with right size (256K of zeroes).A suitable
> "dd" from /dev/zero will accomplish this on modernUnixen (ie, anything
> that has /dev/zero). Filling of those files with zeroes nothing but  "
> all those old transactions are aborted".

I was able to restore these files from backup. The dump is finished
without errors.

This corruption is just on our master replication, which was upgraded. 
Is it a good idea to restore the clog files there too?

Regards
Steffen





From simon@2ndQuadrant.com Fri Mar  25 09:43:01 2011
Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id CB6161337BE4
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 25 Mar 2011 06:43:01 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 65155-10 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Fri, 25 Mar 2011 09:42:52 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from outmail148104.authsmtp.com (outmail148104.authsmtp.com
	[62.13.148.104])
	by mail.postgresql.org (Postfix) with ESMTP id 8FE851337BE1
	for <pgsql-admin@postgresql.org>; Fri, 25 Mar 2011 06:42:50 -0300 (ADT)
Received: from mail-c187.authsmtp.com (mail-c187.authsmtp.com [62.13.128.33])
	by punt9.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id p2P9god2004187
	for <pgsql-admin@postgresql.org>; Fri, 25 Mar 2011 09:42:50 GMT
Received: from mail-qy0-f174.google.com (mail-qy0-f174.google.com
	[209.85.216.174]) (authenticated bits=0)
	by mail.authsmtp.com (8.14.2/8.14.2) with ESMTP id p2P9gkco071709
	(version=TLSv1/SSLv3 cipher=RC4-SHA bits=128 verify=FAIL)
	for <pgsql-admin@postgresql.org>; Fri, 25 Mar 2011 09:42:47 GMT
Received: by qyk7 with SMTP id 7so5901644qyk.19
	for <pgsql-admin@postgresql.org>; Fri, 25 Mar 2011 02:42:46 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.229.40.134 with SMTP id k6mr395036qce.242.1301046166282; Fri,
	25 Mar 2011 02:42:46 -0700 (PDT)
Received: by 10.229.96.9 with HTTP; Fri, 25 Mar 2011 02:42:46 -0700 (PDT)
In-Reply-To: <AANLkTims48njGKkvkXHiTG6=19hQ31VFmFRPPLg+5w03@mail.gmail.com>
References: <1300970362.2349.27.camel@stevie>
	<AANLkTims48njGKkvkXHiTG6=19hQ31VFmFRPPLg+5w03@mail.gmail.com>
Date: Fri, 25 Mar 2011 09:42:46 +0000
Message-ID: <AANLkTikjzQe9SxGqHZ22z_znzr1wSn7+A=rGc2R4i2Zg@mail.gmail.com>
Subject: Re: ERROR: could not access status of transaction
From: Simon Riggs <simon@2ndQuadrant.com>
To: raghu ram <raghuchennuru@gmail.com>
Cc: Stevie <stevieg@web.de>, pgsql-admin <pgsql-admin@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Server-Quench: 3e2a9edf-56c4-11e0-ab46-001185d377ca
X-AuthReport-Spam: If SPAM / abuse - report it at:
	http://www.authsmtp.com/abuse
X-AuthRoute: OCdxZQATClZOTQEd DAteCiN5VAwpPBRK HVkIKg5MOFUSTAAU
	O0pfL1x7KFoHWV5D ViVKB0oLEgo/W2Zq FlsTcgddY0teXwxg
	VEpDRlUcFgVhHxQE GBwfVAdzcAFZfXZ3 ekcsHnI7VRMhJhl9
	RU9TFWRIYG5oai4c BUIJflYAJh5PeExB d1F+AXMQMDcDYGdg
	R1Zqb209ZmoAIy1D eAxfZWE7bWMqOAt0 aio4Og99VUYCWiE/
	ZxohNhYVF00cKV56 KkcoRU8DexgcQhFE FkdWCSRFJl4HLwAA
X-Authentic-SMTP: 61633235383639.1000:706
X-AuthFastPath: 0 (Was 255)
X-AuthVirus-Status: No virus detected - but ensure you scan with your own
	anti-virus system.
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level: 
X-Archive-Number: 201103/240
X-Sequence-Number: 37112

On Thu, Mar 24, 2011 at 1:43 PM, raghu ram <raghuchennuru@gmail.com> wrote:
>
>
> On Thu, Mar 24, 2011 at 6:09 PM, Stevie <stevieg@web.de> wrote:
>>
>> Hello pgsql-admins,
>>
>> we have a problem with our Postgresql 9.0.3 backup database (Ubuntu 10.4=
).
>> The backup and restore is done as described here:
>> http://www.postgresql.org/docs/9.0/static/continuous-archiving.html
>> If you want to know the exact steps of backup and restore,please ask.
>>
>> We restore the database on the backup host and it's starting. I've appen=
d
>> the mainlog.
>>
>> If we dump or reindex the database we get "could not access status of
>> transaction NNNNNN" errors.
>> PG tries to access old clog files: "pg_clog/00CD". But in the pg_clog
>> directory the files start at "00E6" and end at "0198".
>> I've append the pg_controldata output. This clog files doesn't exist on
>> the original database neither.
>>
>> I could create empty clog files, but it seems that our database is in an
>> inconsistent state?
>> Any suggestions are welcome.
>>
>
> There is a commit log corruption in your database. You need to make the
> files with right size (256K of zeroes).A suitable "dd" from /dev/zero wil=
l
> accomplish this on modernUnixen (ie, anything that has /dev/zero). Fillin=
g
> of those files with zeroes nothing but =A0" all those old transactions ar=
e
> aborted".
>
> The safest way to resolve the issue by using the below command:-
>
> dd if=3D/dev/zero of=3DTransaction_missing_file_of pg_clog bs=3D256K coun=
t=3D1 (To
> make the uncommitted record as they haven't been committed).
>

Just for the record, playing with pg_clog files like this is an
insanely unsafe thing to do. Notably it will make all aborted
transactions in that range become committed, leading to all sorts of
fun.

This is a desperate measures only action and not one that looks
immediately related to the problem.

--=20
=A0Simon Riggs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http:/=
/www.2ndQuadrant.com/
=A0PostgreSQL Development, 24x7 Support, Training & Services



From mcassiano@manord.com Mon Jul  21 09:27:14 2014
Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-admin-owner+M42667=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1X99sI-0004sR-AW
 for pgsql-admin@arkaria.postgresql.org; Mon, 21 Jul 2014 09:27:14 +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+M42667=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
 id 1X99sH-0003eq-Gw
 for pgsql-admin@arkaria.postgresql.org; Mon, 21 Jul 2014 09:27:13 +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 <mcassiano@manord.com>)
 id 1X99sF-0003eg-CO
 for pgsql-admin@postgresql.org; Mon, 21 Jul 2014 09:27:11 +0000
Received: from mailsender.mmfg.it ([213.92.29.145] helo=cg.mmfgmail.com)
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <mcassiano@manord.com>) id 1X99sA-0003Az-Gp
 for pgsql-admin@postgresql.org; Mon, 21 Jul 2014 09:27:08 +0000
Received: from [172.30.2.233] (HELO E2010-CAS2.manord.com)
 by mail-02.cg.mmfgmail.com (CommuniGate Pro SMTP 5.3.9)
 with ESMTPS id 58806070 for pgsql-admin@postgresql.org;
 Mon, 21 Jul 2014 11:27:01 +0200
Received: from E2010-MB1.manord.com ([fe80::8090:680f:4ef0:29c8]) by
 E2010-CAS2.manord.com ([::1]) with mapi id 14.03.0195.001; Mon, 21 Jul 2014
 11:27:01 +0200
From: "Cassiano, Marco" <mcassiano@manord.com>
To: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: "ERROR:  could not access status of transaction" (after upgrding
 from 9.3.2 to 9.3.4?)
Thread-Topic: "ERROR:  could not access status of transaction" (after
 upgrding from 9.3.2 to 9.3.4?)
Thread-Index: Ac+kxByolmk/0jK0SpS+4NBqyGbE4A==
Date: Mon, 21 Jul 2014 09:27:00 +0000
Message-ID: <1361CEF686657C41A139AD8C3145632B2A00F636@E2010-MB1.manord.com>
Accept-Language: it-IT, en-US
Content-Language: it-IT
X-MS-Has-Attach: 
X-MS-TNEF-Correlator: 
x-originating-ip: [192.168.186.11]
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-Pg-Spam-Score: -0.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

Hi all,



every weekend we use to run a batch that makes reindex/cluster of our main =
db.

This weekend we observed this error in the log :



2014-07-19 23:00:41 GMT 172.30.2.200(53959) dba mdn 1265998975 53caf874.682=
6 - ERROR:  could not access status of transaction 1067517164
2014-07-19 23:00:41 GMT 172.30.2.200(53959) dba mdn 1265998975 53caf874.682=
6 - DETAIL:  Could not open file "pg_clog/03FA": No such file or directory.
2014-07-19 23:00:41 GMT 172.30.2.200(53959) dba mdn 1265998975 53caf874.682=
6 - STATEMENT:  cluster pk__cat_taglio on "anamat"."cat_taglio"



The previous weekend the job worked fine.





The only thing that happened in the middle  (AFAIK) is that on the 17th we =
apply the minor upgrade of postgresql from 9.3.2 to 9.3.4.



Can you please help me to understand the cause of this corruption, how much=
 is serious and the its correct resolution ?



By the way the data in the table seems ok : i can dump the table, I can sel=
ect the data inside it.



SInce this table doesn't change frequently, we are considering a dump/delet=
e/restore. Will this fix the problem in yuor opinion ?





Thanks a lot



Marco Cassiano




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



From jayknowsunix@gmail.com Mon Jul  21 12:51:22 2014
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