pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddatabase corruption
9+ messages / 6 participants
[nested] [flat]
* database corruption
@ 2005-04-15 20:55 Ian Westmacott <ianw@intellivid.com>
2005-04-16 01:29 ` Re: database corruption Chris Travers <chris@travelamericas.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ian Westmacott @ 2005-04-15 20:55 UTC (permalink / raw)
To: pgsql-admin
For several weeks now we have been experiencing fairly
severe database corruption upon clean reboot. It is very
repeatable, and the corruption is of the following forms:
ERROR: could not access status of transaction foo
DETAIL: could not open file "bar": No such file or directory
ERROR: invalid page header in block foo of relation "bar"
ERROR: uninitialized page in block foo of relation "bar"
At first, we believed this was related to XFS, and have
been pursuing investigations along those lines. However,
we have now experienced the exact same problem with JFS.
Here are some details:
- Postgres 7.4.2
- 2.6.6 kernel.org kernel
- dedicated database partition
- repeatable with XFS and JFS (have not seen on ext3)
- repeatable with and without Linux software RAID 0
- repeatable with IDE and SATA
- repeatable with and without fsync, and with fdatasync
- repeatable on multiple systems
I have two questions:
- any known reason why this might be occurring? (we must
have something wrong, for this high rate of severe
error).
- if I don't care about losing data, and am not interested
in trying to recover anything, how can I arrange for
Postgres to proceed normally? I know about
zero_damaged_pages, but this doesn't help with missing
transaction files and such. Is there any way to get
Postgres to chuck anything bad and proceed?
Thanks,
--Ian
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: database corruption
2005-04-15 20:55 database corruption Ian Westmacott <ianw@intellivid.com>
@ 2005-04-16 01:29 ` Chris Travers <chris@travelamericas.com>
2005-04-16 03:39 ` Re: database corruption Ian Westmacott <ianw@intellivid.com>
0 siblings, 1 reply; 9+ messages in thread
From: Chris Travers @ 2005-04-16 01:29 UTC (permalink / raw)
To: Ian Westmacott <ianw@intellivid.com>; pgsql-admin
Hi Ian;
I think it is important to figure out why this is happening. I would
not want to run any production databases on systems that were failing
like this.
I am trying to figure out what are the likely causes of the errors...
1) Any other computers suffer random application crashes, power downs,
etc. in your building?
2) I take it there are no Raid controllers involved?
3) RAM is non-ECC?
4) Are the systems on UPS's?
If I could make a wild (and probably wrong) guess, I would wonder if
something external to the system (like electrical supply) was
introducing glitches into memory, causing bad data to be written. I am
only mentioning it because I have implicated electrical supply in other
cases where rare computer failurres weer affecting many systems...
Ian Westmacott wrote:
>For several weeks now we have been experiencing fairly
>severe database corruption upon clean reboot. It is very
>repeatable, and the corruption is of the following forms:
>
>ERROR: could not access status of transaction foo
>DETAIL: could not open file "bar": No such file or directory
>
>ERROR: invalid page header in block foo of relation "bar"
>
>ERROR: uninitialized page in block foo of relation "bar"
>
>
>At first, we believed this was related to XFS, and have
>been pursuing investigations along those lines. However,
>we have now experienced the exact same problem with JFS.
>
>Here are some details:
>
>- Postgres 7.4.2
>- 2.6.6 kernel.org kernel
>- dedicated database partition
>- repeatable with XFS and JFS (have not seen on ext3)
>- repeatable with and without Linux software RAID 0
>- repeatable with IDE and SATA
>- repeatable with and without fsync, and with fdatasync
>- repeatable on multiple systems
>
>
>I have two questions:
>
>- any known reason why this might be occurring? (we must
> have something wrong, for this high rate of severe
> error).
>
>- if I don't care about losing data, and am not interested
> in trying to recover anything, how can I arrange for
> Postgres to proceed normally? I know about
> zero_damaged_pages, but this doesn't help with missing
> transaction files and such. Is there any way to get
> Postgres to chuck anything bad and proceed?
>
>Thanks,
>
> --Ian
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: database corruption
2005-04-15 20:55 database corruption Ian Westmacott <ianw@intellivid.com>
2005-04-16 01:29 ` Re: database corruption Chris Travers <chris@travelamericas.com>
@ 2005-04-16 03:39 ` Ian Westmacott <ianw@intellivid.com>
2005-04-16 16:37 ` Re: database corruption Amadeus Zilfinski <amadeus0@cox.net>
0 siblings, 1 reply; 9+ messages in thread
From: Ian Westmacott @ 2005-04-16 03:39 UTC (permalink / raw)
To: Chris Travers <chris@travelamericas.com>; Ian Westmacott <ianw@intellivid.com>; pgsql-admin
Hi Chris,
> I think it is important to figure out why this is happening. I would
> not want to run any production databases on systems that were failing
> like this.
You and me both :) (in our application though, it is not a total
disaster to lose the last 5 minutes of transactions, it is a disaster
if the database is unusable when it comes up)
> 1) Any other computers suffer random application crashes, power downs,
> etc. in your building?
No, but more importantly, we have seen this failure happen in different
buildings (in different cities), on same spec but different hardware
(at least three motherboards and power supplies, six disks). That's why
it really feels like a bug or configuration error.
> 2) I take it there are no Raid controllers involved?
No. But we get this error with and without software RAID, FWIW.
> 3) RAM is non-ECC?
I'll have to double-check, but I think it is.
> 4) Are the systems on UPS's?
Yes.
> If I could make a wild (and probably wrong) guess, I would wonder if
> something external to the system (like electrical supply) was
> introducing glitches into memory, causing bad data to be written. I am
> only mentioning it because I have implicated electrical supply in other
> cases where rare computer failurres weer affecting many systems...
I would tend to agree, but this occurs on multiple systems in
multiple locations (but, oddly enough, we are having trouble reproducing
it in our lab). And we have run memtest.
However, it is true that all the systems on which
this has been seen have the same spec power supply/UPS. I would think
though, that this could cause error at any time -- all
of these failures occur after reboot (that is, no corruption, reboot,
immediate corruption). I have stopped/started Postgres
while the application is running, without corruption. (smells like
a dirty buffer not being written to disk, which is why we focused on
the filesystem).
Here are some further details:
- 865PE/G Neo2-P (MS-6728) ATX motherboard (and similar for IDE)
- 2x 512MB/400MHz DIMM RAM
- Intel Pentium 4/3.2GHz/1MB/800MHz CPU (hyperthreading enabled)
- 2x WD 250GB/7200RPM/8MB/SATA-150 on ICH5 SATA ports (also tested
similar IDE drives), writethrough
- XFS and JFS (not seen on ext3, but not fully tested)
- either software RAID 0 on both drives, or one drive alone without RAID
- SuSE 9.1
- 2.6.6 kernel
- Postgres 7.4.2
- 300 TPS against DB containing 5-50GB data, no more than a dozen
concurrent connections.
- fsync (or not) and fdatasync
- Postgres may be taken down (via init script) with connections open to
it (in fact the application may aggressively try to re-establish the
connection as it goes down).
- we have put syncs, sleeps and large dd to the disk in the shutdown
scripts, none of which work.
At this point, I'm really looking for fresh ideas. Thanks,
--Ian
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: database corruption
2005-04-15 20:55 database corruption Ian Westmacott <ianw@intellivid.com>
2005-04-16 01:29 ` Re: database corruption Chris Travers <chris@travelamericas.com>
2005-04-16 03:39 ` Re: database corruption Ian Westmacott <ianw@intellivid.com>
@ 2005-04-16 16:37 ` Amadeus Zilfinski <amadeus0@cox.net>
2005-04-18 01:12 ` Re: database corruption Ian Westmacott <ianw@intellivid.com>
0 siblings, 1 reply; 9+ messages in thread
From: Amadeus Zilfinski @ 2005-04-16 16:37 UTC (permalink / raw)
To: Ian Westmacott <ianw@intellivid.com>; +Cc: Chris Travers <chris@travelamericas.com>; pgsql-admin
,snipped.
> - SuSE 9.1
> - 2.6.6 kernel
> - Postgres 7.4.2
> - 300 TPS against DB containing 5-50GB data, no more than a dozen
> concurrent connections.
> - fsync (or not) and fdatasync
I remember a problem that was fixed in the 2.6.9 kernel concerning XFS
corruption (shutdowns I think were the worst). Also introduced some JFS
stuff, but I don't run JFS so I didn't really pay much attention to
that.
Can you try a later (the latest?) vanilla kernel?
AmadeusZ.-
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: database corruption
2005-04-15 20:55 database corruption Ian Westmacott <ianw@intellivid.com>
2005-04-16 01:29 ` Re: database corruption Chris Travers <chris@travelamericas.com>
2005-04-16 03:39 ` Re: database corruption Ian Westmacott <ianw@intellivid.com>
2005-04-16 16:37 ` Re: database corruption Amadeus Zilfinski <amadeus0@cox.net>
@ 2005-04-18 01:12 ` Ian Westmacott <ianw@intellivid.com>
0 siblings, 0 replies; 9+ messages in thread
From: Ian Westmacott @ 2005-04-18 01:12 UTC (permalink / raw)
To: Amadeus Zilfinski <amadeus0@cox.net>; +Cc: pgsql-admin
Thanks for the tip. Later kernel versions have unrelated problems for
us, but we'll take a look at the filesystem mods and see if we can
backpatch them.
--Ian
> I remember a problem that was fixed in the 2.6.9 kernel concerning XFS
> corruption (shutdowns I think were the worst). Also introduced some JFS
> stuff, but I don't run JFS so I didn't really pay much attention to
> that.
>
> Can you try a later (the latest?) vanilla kernel?
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: database corruption
@ 2005-04-18 01:20 Ian Westmacott <ianw@intellivid.com>
0 siblings, 0 replies; 9+ messages in thread
From: Ian Westmacott @ 2005-04-18 01:20 UTC (permalink / raw)
To: Brennan Stewart <brennan@ideahamster.org>; +Cc: pgsql-admin
> In your previous emails, you stated that these errors were seen on
> multiple systems. Multiple systems, configured identically, with
> diverse motherboards/hardware or always identical hardware except for
> the sata/IDE drives?
>
> I ask, because I noted that you are using the Neo 2 ATX motherboard that
> has the "dynamic overclocking" feature. This can cause grave
> instability in systems when it set to an aggressive setting. This
> motherboard is not compatible with low quality ram either. This
> motherboard also is prone to heat problems, and requires extra cooling.
> If you have multiple systems failing, using this motherboard, then that
> particular model could be the culprit.
We have seen this using at least one other motherboard besides the Neo 2
ATX. And (fortunately or unfortunately) we don't use the dynamic
overclocking feature. Do you know how the RAM incompatibility is
manifest?
Thanks,
--Ian
^ permalink raw reply [nested|flat] 9+ messages in thread
* Database Corruption
@ 2005-11-06 08:46 fabio guidi <f.guidi@libero.it>
0 siblings, 0 replies; 9+ messages in thread
From: fabio guidi @ 2005-11-06 08:46 UTC (permalink / raw)
To: pgsql-admin
Hello to all, it's my first mail in this group ...
I have a problem with a database 8.0.4 (installed on a Windows XP),
yesterday during startup my windows-xp generate an error message and
postgresql service don't start more.
I try to start service manually but nothing, appare a message that say
that service could not start.
So i done a phisical copy of the directory containing the postgresql
(binary and database) i uninstall postgres binary and reinstall all
using the same directory without creating first database cluster ... and
again nothing ... so i deinstall again all ... delete all files
including databases and install all again and now all ok ...
So now the question ... it's possibile to recover my database starting
from a filesystem phisical copy ?
Thanks to all in advance
Fabio
^ permalink raw reply [nested|flat] 9+ messages in thread
* Database corruption
@ 2010-06-24 18:28 David Haube <dch@frolix.muddywaters.org>
0 siblings, 0 replies; 9+ messages in thread
From: David Haube @ 2010-06-24 18:28 UTC (permalink / raw)
To: pgsql-admin
Postgres version 8.4.4
Hardware:
12 cpu intel
sda 15K 600GB raid 1
sdb ssd 173GB raid 1 (most tables and indexes on sdb)
adaptec controller
24G of memory
shared buffers 12GB
Machine age 3 months
Normal load 2-4
200-300 Transactions per second
We had a database failure last night after one of the tables had a
corrupted block. After we noticed the corruption all available memory
was used up plus swap. The database died (or rather killed by the
kernel) with an out of memory error. We switched to the warm standby
which doesn't have any corruption.
On the postmortem I found 4 tables with corruption. Only thing that
links these tables was there was autovacuum (to prevent wraparound)
either running or had run on those tables. All tables are in the Gig
range or multi Gig range. The vacuum of some of the tables had been
going on for days.
The errors from the log file were in the form of :
ERROR: invalid page header in block 290125 of relation
pg_tblspc/16385/18674/205612
After an attempted vaccum we had this error:
2010-06-24 17:31:09 UTC [31766]: [36-1]WARNING: PD_ALL_VISIBLE flag was
incorrectly set in relation "org_crawl_page_scrape_result" page 128902
The first error was logged at 10:15pm
At 1 am a pg_dump was run from cron and failed after 20 minutes while
try ing to allocate an immenense amount of memory while attempting to
dump one of the corrupted tables.
At 2:00 am All memory was used up and cpu was maxed and a load average
of 56.
We transferred to the standby and rebooted the machine. At this time
the database is sitting there though we'll need to remake the database
and turn it into the warm standby.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Database corruption
@ 2011-11-14 09:54 Manoj K P <manoj@comodo.com>
0 siblings, 0 replies; 9+ messages in thread
From: Manoj K P @ 2011-11-14 09:54 UTC (permalink / raw)
To: pgsql-admin
Due to Hard disk space issue on the WAL partition, database was down , When I
am going to start postgres again ,got following error message
This DB is grater than 1 TB restore will take long time , any other way
other than pg_resetxlog ?
Nov 14 04:32:41 dbname postgres[4384]: [2-1] 2011-11-14 04:32:41.423 EST
4384 LOG: invalid record length at 500C/5EFFF2B8
Nov 14 04:32:41 dbname postgres[4384]: [3-1] 2011-11-14 04:32:41.423 EST
4384 LOG: invalid primary checkpoint record
Nov 14 04:32:41 dbname postgres[4384]: [4-1] 2011-11-14 04:32:41.423 EST
4384 LOG: invalid resource manager ID in secondary checkpoint record
Nov 14 04:32:41 dbname postgres[4384]: [5-1] 2011-11-14 04:32:41.423 EST
4384 PANIC: could not locate a valid checkpoint record
Nov 14 04:32:41 dbname postgres[4383]: [1-1] 2011-11-14 04:32:41.424 EST
4383 LOG: startup process (PID 4384) was terminated by signal 6: Aborted
Nov 14 04:32:41 dbname postgres[4383]: [2-1] 2011-11-14 04:32:41.424 EST
4383 LOG: aborting startup due to startup process failure
pg_controldata : Result is
pg_control version number: 843
Catalog version number: 200904091
Database system identifier: 5404099570172901375
Database cluster state: in production
pg_control last modified: Mon 14 Nov 2011 01:02:51 AM EST
Latest checkpoint location: 500C/5EFFF2B8
Prior checkpoint location: 500C/5EFFF270
Latest checkpoint's REDO location: 500C/5EFFF2B8
Latest checkpoint's TimeLineID: 10000
Latest checkpoint's NextXID: 1/2784291602
Latest checkpoint's NextOID: 63034795
Latest checkpoint's NextMultiXactId: 1658683101
Latest checkpoint's NextMultiOffset: 3632122117
Time of latest checkpoint: Mon 14 Nov 2011 01:02:51 AM EST
Minimum recovery ending location: 0/0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Database-corruption-tp4990279p4990279.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2011-11-14 09:54 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-15 20:55 database corruption Ian Westmacott <ianw@intellivid.com>
2005-04-16 01:29 ` Chris Travers <chris@travelamericas.com>
2005-04-16 03:39 ` Ian Westmacott <ianw@intellivid.com>
2005-04-16 16:37 ` Amadeus Zilfinski <amadeus0@cox.net>
2005-04-18 01:12 ` Ian Westmacott <ianw@intellivid.com>
2005-04-18 01:20 Re: database corruption Ian Westmacott <ianw@intellivid.com>
2005-11-06 08:46 Database Corruption fabio guidi <f.guidi@libero.it>
2010-06-24 18:28 Database corruption David Haube <dch@frolix.muddywaters.org>
2011-11-14 09:54 Database corruption Manoj K P <manoj@comodo.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