ERROR: could not access status of transaction

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

On Thu, Mar 24, 2011 at 6:09 PM, Stevie <stevieg(at)web(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>

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

On Thu, Mar 24, 2011 at 1:43 PM, raghu ram <raghuchennuru(at)gmail(dot)com> wrote:
>
>
> On Thu, Mar 24, 2011 at 6:09 PM, Stevie <stevieg(at)web(dot)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).
>

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.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services