pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedCrash with data corruption under Windows
7+ messages / 3 participants
[nested] [flat]
* Crash with data corruption under Windows
@ 2009-02-20 14:32 Nicola Mauri <nicola.mauri@saga.it>
2009-02-20 16:03 ` Re: Crash with data corruption under Windows Kenneth Marshall <ktm@rice.edu>
2009-02-20 16:14 ` Re: Crash with data corruption under Windows Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Nicola Mauri @ 2009-02-20 14:32 UTC (permalink / raw)
To: pgsql-admin
We run into the following issue with Windows 2003 and Postgres 8.2.6
while database was running:
FATAL: "pg_tblspc/16405/37638" is not a valid data directory
DETAIL: File "pg_tblspc/16405/37638/PG_VERSION" is missing.
ERROR: could not open relation 16405/37638/2661: No such file or
directory
ERROR: could not open relation 16405/37638/2659: No such file or
directory
ERROR: could not write block 4 of relation 16405/37638/37656:
Permission denied
CONTEXT: writing block 4 of relation 16405/37638/37656
...
WARNING: could not write block 4 of 16405/37638/37656
DETAIL: Multiple failures --- write error may be permanent.
This happened 4 times in the last few months! Usually after the crash
datafiles appear to be corrupted, but in some other cases they
completely disappear from the filesystem (tablespace directory is empty)
and we have to recreate the entire db from the last dump.
No suspicious activities have been detected on the server (unauthorized
accesses, anti-virus intervention) and information about disappeared
files cannot be found using an undelete utilities. Disk hardware is
healthy and no other part of the filesystem seems to be affected by such
strange deletions (several applications, including an oracle database,
are correctly running on the server).
Since the problem seems involving only directories containing
tablespaces (stored on local partition E:\) we are pointing our
attention to "Reparse Point" and "NTFS Junction" mechanism.
Could be there issues in those features?
Thanks in advance,
Nicola Mauri
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Crash with data corruption under Windows
2009-02-20 14:32 Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
@ 2009-02-20 16:03 ` Kenneth Marshall <ktm@rice.edu>
2009-02-23 08:59 ` Re: Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
1 sibling, 1 reply; 7+ messages in thread
From: Kenneth Marshall @ 2009-02-20 16:03 UTC (permalink / raw)
To: Nicola Mauri <nicola.mauri@saga.it>; +Cc: pgsql-admin
On Fri, Feb 20, 2009 at 03:32:16PM +0100, Nicola Mauri wrote:
>
> We run into the following issue with Windows 2003 and Postgres 8.2.6 while
> database was running:
>
> FATAL: "pg_tblspc/16405/37638" is not a valid data directory
> DETAIL: File "pg_tblspc/16405/37638/PG_VERSION" is missing.
> ERROR: could not open relation 16405/37638/2661: No such file or
> directory
> ERROR: could not open relation 16405/37638/2659: No such file or
> directory
> ERROR: could not write block 4 of relation 16405/37638/37656: Permission
> denied
> CONTEXT: writing block 4 of relation 16405/37638/37656
> ...
> WARNING: could not write block 4 of 16405/37638/37656
> DETAIL: Multiple failures --- write error may be permanent.
>
> This happened 4 times in the last few months! Usually after the crash
> datafiles appear to be corrupted, but in some other cases they completely
> disappear from the filesystem (tablespace directory is empty) and we have
> to recreate the entire db from the last dump.
>
> No suspicious activities have been detected on the server (unauthorized
> accesses, anti-virus intervention) and information about disappeared files
> cannot be found using an undelete utilities. Disk hardware is healthy and
> no other part of the filesystem seems to be affected by such strange
> deletions (several applications, including an oracle database, are
> correctly running on the server).
>
> Since the problem seems involving only directories containing tablespaces
> (stored on local partition E:\) we are pointing our attention to "Reparse
> Point" and "NTFS Junction" mechanism.
>
> Could be there issues in those features?
>
> Thanks in advance,
> Nicola Mauri
>
I did not check the release note, but you do realize that you are
6 releases back from the latest stable 8.2 version. Maybe an upgrade
would help.
Cheers,
Ken
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Crash with data corruption under Windows
2009-02-20 14:32 Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
2009-02-20 16:03 ` Re: Crash with data corruption under Windows Kenneth Marshall <ktm@rice.edu>
@ 2009-02-23 08:59 ` Nicola Mauri <nicola.mauri@saga.it>
0 siblings, 0 replies; 7+ messages in thread
From: Nicola Mauri @ 2009-02-23 08:59 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-admin
> On Fri, Feb 20, 2009 at 03:32:16PM +0100, Nicola Mauri wrote:
>
>> We run into the following issue with Windows 2003 and Postgres 8.2.6 while
>> database was running:
>>
>> FATAL: "pg_tblspc/16405/37638" is not a valid data directory
>> DETAIL: File "pg_tblspc/16405/37638/PG_VERSION" is missing.
>> ERROR: could not open relation 16405/37638/2661: No such file or
>> directory
>> ERROR: could not open relation 16405/37638/2659: No such file or
>> directory
>> ERROR: could not write block 4 of relation 16405/37638/37656: Permission
>> denied
>> CONTEXT: writing block 4 of relation 16405/37638/37656
>> ...
>> WARNING: could not write block 4 of 16405/37638/37656
>> DETAIL: Multiple failures --- write error may be permanent.
>>
>> This happened 4 times in the last few months! Usually after the crash
>> datafiles appear to be corrupted, but in some other cases they completely
>> disappear from the filesystem (tablespace directory is empty) and we have
>> to recreate the entire db from the last dump.
>>
>> No suspicious activities have been detected on the server (unauthorized
>> accesses, anti-virus intervention) and information about disappeared files
>> cannot be found using an undelete utilities. Disk hardware is healthy and
>> no other part of the filesystem seems to be affected by such strange
>> deletions (several applications, including an oracle database, are
>> correctly running on the server).
>>
>> Since the problem seems involving only directories containing tablespaces
>> (stored on local partition E:\) we are pointing our attention to "Reparse
>> Point" and "NTFS Junction" mechanism.
>>
>> Could be there issues in those features?
>>
>> Thanks in advance,
>> Nicola Mauri
>>
>>
> I did not check the release note, but you do realize that you are
> 6 releases back from the latest stable 8.2 version. Maybe an upgrade
> would help.
>
> Cheers,
> Ken
>
Yes, we now upgraded to 8.2.11, but not sure if this will help and
prevent data losses in the future.
So we wonder if someone has some hints.
thanks
Nicola
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Crash with data corruption under Windows
2009-02-20 14:32 Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
@ 2009-02-20 16:14 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-02-23 16:49 ` Re: Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
1 sibling, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2009-02-20 16:14 UTC (permalink / raw)
To: Nicola Mauri <nicola.mauri@saga.it>; +Cc: pgsql-admin
On Fri, Feb 20, 2009 at 7:32 AM, Nicola Mauri <nicola.mauri@saga.it> wrote:
>
> We run into the following issue with Windows 2003 and Postgres 8.2.6 while
> database was running:
>
> FATAL: "pg_tblspc/16405/37638" is not a valid data directory
> DETAIL: File "pg_tblspc/16405/37638/PG_VERSION" is missing.
> ERROR: could not open relation 16405/37638/2661: No such file or directory
> ERROR: could not open relation 16405/37638/2659: No such file or directory
> ERROR: could not write block 4 of relation 16405/37638/37656: Permission
> denied
Usually when I see the permission denied thing there's anti-virus
software hard locking pgsql files in the middle of the day.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Crash with data corruption under Windows
2009-02-20 14:32 Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
2009-02-20 16:14 ` Re: Crash with data corruption under Windows Scott Marlowe <scott.marlowe@gmail.com>
@ 2009-02-23 16:49 ` Nicola Mauri <nicola.mauri@saga.it>
2009-02-23 17:07 ` Re: Crash with data corruption under Windows Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Nicola Mauri @ 2009-02-23 16:49 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
-------- Messaggio Originale --------
Oggetto: Re: [ADMIN] Crash with data corruption under Windows
Da: Scott Marlowe <scott.marlowe@gmail.com>
> Usually when I see the permission denied thing there's anti-virus
> software hard locking pgsql files in the middle of the day.
>
Have you experienced data loss too in that case?
(however, we already excluded pgsql directories from anti-virus scanning).
regards,
Nicola
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Crash with data corruption under Windows
2009-02-20 14:32 Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
2009-02-20 16:14 ` Re: Crash with data corruption under Windows Scott Marlowe <scott.marlowe@gmail.com>
2009-02-23 16:49 ` Re: Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
@ 2009-02-23 17:07 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-02-23 18:18 ` Re: Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
0 siblings, 1 reply; 7+ messages in thread
From: Scott Marlowe @ 2009-02-23 17:07 UTC (permalink / raw)
To: Nicola Mauri <nicola.mauri@saga.it>; +Cc: pgsql-admin
On Mon, Feb 23, 2009 at 9:49 AM, Nicola Mauri <nicola.mauri@saga.it> wrote:
>
> -------- Messaggio Originale --------
> Oggetto: Re: [ADMIN] Crash with data corruption under Windows
> Da: Scott Marlowe <scott.marlowe@gmail.com>
>>
>> Usually when I see the permission denied thing there's anti-virus
>> software hard locking pgsql files in the middle of the day.
>>
>
> Have you experienced data loss too in that case?
> (however, we already excluded pgsql directories from anti-virus scanning).
I've not, but I rarely run postgresql under windows. When we do it's
on a laptop for personal use / demo use. All our servers run Linux.
However, on this list, when I've seen this happen, it has been known
to result in data loss.
Note that once your database is corrupted, no amount of fixing can
make it safe to keep using. you need to get a good reliable dump from
it, reinitdb and reload your data. So not trust a database that's
gotten corrupted and seems mostly fixed. It's a timebomb.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Crash with data corruption under Windows
2009-02-20 14:32 Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
2009-02-20 16:14 ` Re: Crash with data corruption under Windows Scott Marlowe <scott.marlowe@gmail.com>
2009-02-23 16:49 ` Re: Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
2009-02-23 17:07 ` Re: Crash with data corruption under Windows Scott Marlowe <scott.marlowe@gmail.com>
@ 2009-02-23 18:18 ` Nicola Mauri <nicola.mauri@saga.it>
0 siblings, 0 replies; 7+ messages in thread
From: Nicola Mauri @ 2009-02-23 18:18 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
-------- Messaggio Originale --------
Oggetto: Re: [ADMIN] Crash with data corruption under Windows
Da: Scott Marlowe <scott.marlowe@gmail.com>
> On Mon, Feb 23, 2009 at 9:49 AM, Nicola Mauri <nicola.mauri@saga.it> wrote:
>
>> -------- Messaggio Originale --------
>> Oggetto: Re: [ADMIN] Crash with data corruption under Windows
>> Da: Scott Marlowe <scott.marlowe@gmail.com>
>>
>>> Usually when I see the permission denied thing there's anti-virus
>>> software hard locking pgsql files in the middle of the day.
>>>
>>>
>> Have you experienced data loss too in that case?
>> (however, we already excluded pgsql directories from anti-virus scanning).
>>
>
> I've not, but I rarely run postgresql under windows. When we do it's
> on a laptop for personal use / demo use. All our servers run Linux.
> However, on this list, when I've seen this happen, it has been known
> to result in data loss.
>
> Note that once your database is corrupted, no amount of fixing can
> make it safe to keep using. you need to get a good reliable dump from
> it, reinitdb and reload your data. So not trust a database that's
> gotten corrupted and seems mostly fixed. It's a timebomb.
>
>
Hi Scott,
we had to recreate the cluster and reload data from a dump, because we
were unable to read any table (some critical indexes was broken).
However we would like to try to recovery the most recent data from the
corrupted datafiles (as a help for manual insertion). Is there any
method to extract raw data from the pgsql table files?
regards,
Nicola
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2009-02-23 18:18 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-02-20 14:32 Crash with data corruption under Windows Nicola Mauri <nicola.mauri@saga.it>
2009-02-20 16:03 ` Kenneth Marshall <ktm@rice.edu>
2009-02-23 08:59 ` Nicola Mauri <nicola.mauri@saga.it>
2009-02-20 16:14 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-02-23 16:49 ` Nicola Mauri <nicola.mauri@saga.it>
2009-02-23 17:07 ` Scott Marlowe <scott.marlowe@gmail.com>
2009-02-23 18:18 ` Nicola Mauri <nicola.mauri@saga.it>
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