Обсуждение: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?)

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.6826 - ERROR:  could not access status of
transaction1067517164 
2014-07-19 23:00:41 GMT 172.30.2.200(53959) dba mdn 1265998975 53caf874.6826 - 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.6826 - 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.2to 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 select the data inside it.



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





Thanks a lot



Marco Cassiano

I've seen no other responses, but since I fought an identical error last week, I'll chime in with what I've learned.
FYI,I didn't have any success other 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
systemdamage? Maybe an fsck is in order. Maybe its permissions got hosed. Maybe you could fix that problem. If you
didn'tupgrade in place, maybe you could just run the upgrade again. Standard procedure for recovery from this is to
restartyour database with the zero_damaged_pages = true setting enabled. Then you run a VACUUUM FULL 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 restart the database with zero_damaged_pages off. Now, you'll have to determine what data you lost and
recoverthat manually. I've never gotten this to work, however. So... 

You say that you can query the table, but it's doubtful that every record is available, and you could also perform
selectsagainst this table to copy records out to a new temp table. It will probably take both trial and error and
multipleselect attempts to get all the uncorrupted data out of this table. Once you extracted everything you can get,
dropthe bad table and rename your temporary table to the name of the old bad one. Again, you still have 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> wrote:
>
> 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.6826 - ERROR:  could not access status of
transaction1067517164 
> 2014-07-19 23:00:41 GMT 172.30.2.200(53959) dba mdn 1265998975 53caf874.6826 - 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.6826 - 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.2to 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 select the data inside it.
>
>
>
> SInce this table doesn't change frequently, we are considering a dump/delete/restore. Will this fix the problem in
yuoropinion ? 
>
>
>
>
>
> Thanks a lot
>
>
>
> Marco Cassiano
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Hi all,

first of all, thank you Jay (jayknowsunix) for your answer.

With further investigation, we found other two cases of missing pg_clog files.

I ran a fsck against the filsystem and  everything is OK (whew!!)

So the question is still : why are they missing ?

I noticed that in my pg_clog directory the oldest file is 040D (date 3/9/2014).
The missing files were 03FA, 03CF and 03E3.
It seems that something deleted all files prior to 3/9/2014 (Sunday).
I searched through postgresql logs but didn't find anything that could explain the reason why these files are missing.

Another option to explain the problem could be that it's correct that those files are gone but the problem is that
somewherein the database there's something still referring to them that wasn't cleared... Could it be possible ? 

Anyway, in the three cases we found, the dump/delete/reload table system worked. Luckily the three tables allowed such
aprocedure (small tables, not a lot of referential integrity, not frequently accessed) 

I will appreciate any idea, experience, suggestion you will share

Thanks

Marco



-----Messaggio originale-----
Da: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] Per conto di Cassiano, Marco
Inviato: lunedì 21 luglio 2014 11:27
A: pgsql-admin@postgresql.org
Oggetto: [ADMIN] "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?)

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.6826 - ERROR:  could not access status of
transaction1067517164 
2014-07-19 23:00:41 GMT 172.30.2.200(53959) dba mdn 1265998975 53caf874.6826 - 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.6826 - 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.2to 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 select the data inside it.



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





Thanks a lot



Marco Cassiano




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

Thanks for the kind words, and I'm glad you were able to get those tables restored to full functionality. I'm afraid
thatmy experience level is insufficient to offer any further  
explanations as to why this happened. I know when this bit me, it was due to a hardware failure that both had some disk
blockstrashed as well as two different servers got started  
up and both tried accessing the database, on an NFS partition, simultaneously.

On 7/28/2014 8:27 AM, Cassiano, Marco wrote:
> Hi all,
>
> first of all, thank you Jay (jayknowsunix) for your answer.
>
> With further investigation, we found other two cases of missing pg_clog files.
>
> I ran a fsck against the filsystem and  everything is OK (whew!!)
>
> So the question is still : why are they missing ?
>
> I noticed that in my pg_clog directory the oldest file is 040D (date 3/9/2014).
> The missing files were 03FA, 03CF and 03E3.
> It seems that something deleted all files prior to 3/9/2014 (Sunday).
> I searched through postgresql logs but didn't find anything that could explain the reason why these files are
missing.
>
> Another option to explain the problem could be that it's correct that those files are gone but the problem is that
somewherein the database there's something still referring to them that wasn't cleared... Could it be possible ? 
>
> Anyway, in the three cases we found, the dump/delete/reload table system worked. Luckily the three tables allowed
sucha procedure (small tables, not a lot of referential integrity, not frequently accessed) 
>
> I will appreciate any idea, experience, suggestion you will share
>
> Thanks
>
> Marco
>
>
>
> -----Messaggio originale-----
> Da: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] Per conto di Cassiano, Marco
> Inviato: lunedì 21 luglio 2014 11:27
> A: pgsql-admin@postgresql.org
> Oggetto: [ADMIN] "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?)
>
> 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.6826 - ERROR:  could not access status of
transaction1067517164 
> 2014-07-19 23:00:41 GMT 172.30.2.200(53959) dba mdn 1265998975 53caf874.6826 - 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.6826 - 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.2to 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 select the data inside it.
>
>
>
> SInce this table doesn't change frequently, we are considering a dump/delete/restore. Will this fix the problem in
yuoropinion ? 
>
>
>
>
>
> Thanks a lot
>
>
>
> Marco Cassiano
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>