Subject: Re: Missing clogs and pg_upgrade. What to do without any clog backup?
From: Bernhard Schrader <bernhard.schrader@innogames.de>
To: pgsql-admin@postgresql.org
In-Reply-To: <1308226904.2334.15.camel@schrader-laptop>
References: <1308226904.2334.15.camel@schrader-laptop>
Content-Type: text/plain; charset="UTF-8"
Organization: Innogames GmbH
Date: Thu, 16 Jun 2011 16:18:35 +0200
Message-ID: <1308233915.2334.47.camel@schrader-laptop>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

Am Donnerstag, den 16.06.2011, 14:21 +0200 schrieb Bernhard Schrader:
> Hi out there,
> 
> i migrated some month ago our databases from 8.4 to 9.0 and now getting
> errors like:
> 
> 2011-06-12 06:25:33 CEST ERROR:  could not access status of transaction
> 59768467
> 2011-06-12 06:25:33 CEST DETAIL:  Could not open file "pg_clog/0038": No
> such file or directory.
> 2011-06-12 06:25:33 CEST CONTEXT:  automatic vacuum of table
> "database.pg_toast.pg_toast_19029"
> 
> Well, I googled so far and also saw that there are other known issues
> and also a fix script. But in all cases a backup of the clogs would have
> been necessary. I don't have these files. Is there any way to fix my
> servers? 
> 
> I have migrated around 200 servers, until now 40 servers have the clog
> issues. i will run the script on all other machines. But for the 40
> servers i need a solution. 
> 
> Would a normal dump & restore fix the issues? This would be the worst
> way i could imagine but if there is no way around i will have to do it. 
> 
> 
> thanks in advance
> 
> regards
> Bernhard Schrader
> 
> 
Hi again,

i just tried the pg_upgrade_fix.sql on databases where the errors didnt
appear yet, this is what i get partially:

VACUUM FREEZE pg_toast.pg_toast_182129;
psql:pg_upgrade_tmp.sql:43: ERROR:  could not access status of
transaction 252555499
DETAIL:  Could not open file "pg_clog/00F0": No such file or directory.

Well, right now i dont know how to handle this. Is it now clean or is it
worse than before?

Just to get it in my mind.

The clog is just a commit log, and the clogs which are missing are just
some transactions that where used some months ago, right? So, even if
the clogs are missing there should be no data loss or am i totally
wrong? Well a recovery without them could be difficult but if its not
necessary at all why can't i just start from zero with the clogs? 


regards
Bernhard