pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedI: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?)
3+ messages / 3 participants
[nested] [flat]
* I: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?)
@ 2014-07-28 12:27 Cassiano, Marco <mcassiano@manord.com>
2014-07-28 13:38 ` Re: I: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?) John Scalia <jayknowsunix@gmail.com>
2014-07-29 00:12 ` Re: I: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?) Cliff Pratt <enkiduonthenet@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Cassiano, Marco @ 2014-07-28 12:27 UTC (permalink / raw)
To: 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 somewhere in 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 a 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 transaction 1067517164
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.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 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: I: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?)
2014-07-28 12:27 I: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?) Cassiano, Marco <mcassiano@manord.com>
@ 2014-07-28 13:38 ` John Scalia <jayknowsunix@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: John Scalia @ 2014-07-28 13:38 UTC (permalink / raw)
To: Cassiano, Marco <mcassiano@manord.com>; 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 that my 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 blocks trashed 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 somewhere in 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 a 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 transaction 1067517164
> 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.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 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
>
>
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: I: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?)
2014-07-28 12:27 I: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?) Cassiano, Marco <mcassiano@manord.com>
@ 2014-07-29 00:12 ` Cliff Pratt <enkiduonthenet@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Cliff Pratt @ 2014-07-29 00:12 UTC (permalink / raw)
To: Cassiano, Marco <mcassiano@manord.com>; +Cc: pgsql-admin
If there is nothing in the *postgres* logs, could it be something outside
of postgres that is removing the files? Some rogue "clean up script" maybe.
Cheers,
Cliff
On Tue, Jul 29, 2014 at 12:27 AM, Cassiano, Marco <mcassiano@manord.com>
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 somewhere in 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 a 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 transaction 1067517164
> 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.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
> 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
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2014-07-29 00:12 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-07-28 12:27 I: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?) Cassiano, Marco <mcassiano@manord.com>
2014-07-28 13:38 ` John Scalia <jayknowsunix@gmail.com>
2014-07-29 00:12 ` Re: I: "ERROR: could not access status of transaction" (after upgrding from 9.3.2 to 9.3.4?) Cliff Pratt <enkiduonthenet@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox