pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedData Corruption?
10+ messages / 10 participants
[nested] [flat]
* Data Corruption?
@ 2001-02-02 22:22 Robin's Postgres List Archive <pglist@mail.robin.net>
0 siblings, 1 reply; 10+ messages in thread
From: Robin's Postgres List Archive @ 2001-02-02 22:22 UTC (permalink / raw)
To: pgsql-admin
Hello,
After about a year and a half of use, I ran into a peculiar problem
with my PostgreSQL driven app and I would like to understand why.
I have a web app that uses PostgreSQL 6.5.2 on the backend. The app logic
is Java beans and JSP. The OS is RedHat 6.2 i386. Anyways, there are two
identical databases that are simultaneously accessed by the application
for any type of data transaction, for redundancy purposes.
Last month, I started getting various inexplicable error messages both
with the web app and with psql. The most incriminating were these two:
ERROR: carcustomer: Table does not exist
~and~
FATAL 1: Memory exhausted in AllocSetAlloc()
In psql, for example, simple queries would hang for awhile and then
produce the memory error message.
After doing some troubleshooting, I dropped the table that seemed to be at
least in part the culprit, the "car" table, and then replaced it with a
dump of the same table from the secondary mirror database. At that point,
the error messages ceased and everything has worked great for the last 3
weeks.
Now I need to understand why this happened so I can address it properly.
Does anybody know what may have caused this?
Thank you,
-Robin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Data Corruption?
@ 2001-02-03 21:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robin's Postgres List Archive <pglist@mail.robin.net>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2001-02-03 21:23 UTC (permalink / raw)
To: Robin's Postgres List Archive <pglist@mail.robin.net>; +Cc: pgsql-admin
"Robin's Postgres List Archive" <pglist@mail.robin.net> writes:
> Last month, I started getting various inexplicable error messages both
> with the web app and with psql.
I'd recommend an update to 7.0.3. It's unlikely you'll find anyone
who's seriously interested in pursuing bugs in 6.5.2 ...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* data corruption
@ 2005-09-28 19:12 Hemapriya <priyam_1121@yahoo.com>
0 siblings, 1 reply; 10+ messages in thread
From: Hemapriya @ 2005-09-28 19:12 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have a query that runs fine for one set of data and
not for another.
In the following query, all the columns used in a
where
clause are indexed. If i use this for the
origindbs('02','05','06','07') except '04', it works
fine.
when i look for the data from origindb "04" it just
run for 2 hours.
"select count(*) from qpassextrequest a,request b
where a.requestFK=b.uid and a.origindb='04' and
a.origindb=b.origindb and b.enteredon >='2005-09-28'
and b.enteredon < '2005-09-29'"
What could be the reason.. Can that be a index or data
corruption? If so, rebuiding the index will solve the
problem?
Any hint is appreciated.
Thanks
Priya
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: data corruption
@ 2005-09-30 06:06 Gourish Singbal <gourish@gmail.com>
parent: Hemapriya <priyam_1121@yahoo.com>
0 siblings, 0 replies; 10+ messages in thread
From: Gourish Singbal @ 2005-09-30 06:06 UTC (permalink / raw)
To: Hemapriya <priyam_1121@yahoo.com>; +Cc: pgsql-admin
priya,
I would suggest do a vacuum full analyze verbose <tablename> on both the
tables.
and than explain analyze <query> command to check if proper indexes are been
used.
regards
gourish singbal
On 9/29/05, Hemapriya <priyam_1121@yahoo.com> wrote:
>
> Hi,
>
> I have a query that runs fine for one set of data and
> not for another.
>
> In the following query, all the columns used in a
> where
> clause are indexed. If i use this for the
> origindbs('02','05','06','07') except '04', it works
> fine.
> when i look for the data from origindb "04" it just
> run for 2 hours.
>
> "select count(*) from qpassextrequest a,request b
> where a.requestFK=b.uid and a.origindb='04' and
> a.origindb=b.origindb and b.enteredon >='2005-09-28'
> and b.enteredon < '2005-09-29'"
>
> What could be the reason.. Can that be a index or data
> corruption? If so, rebuiding the index will solve the
> problem?
>
> Any hint is appreciated.
>
> Thanks
> Priya
>
>
>
>
>
> __________________________________
> Yahoo! Mail - PC Magazine Editors' Choice 2005
> http://mail.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
Best,
Gourish Singbal
^ permalink raw reply [nested|flat] 10+ messages in thread
* Data corruption
@ 2021-07-21 16:14 Konrad Garus <konrad.garus@gmail.com>
0 siblings, 3 replies; 10+ messages in thread
From: Konrad Garus @ 2021-07-21 16:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello,
we have a database that apparently got corrupted in a crash a few months
ago, long enough that there is no "good" backup.
There are at least two tables occasionally emitting errors like this:
ERROR: invalid page header in block 39684 of relation base/12926/32397
while performing SQL query: SQL DML: insert into foo (a, b, c) values (?,
?, ?) | PARAMETERS: foo, bar, baz: org.postgresql.util.PSQLException:
ERROR: invalid page header in block 39684 of relation base/12926/32397
The files in question (e.g. 32397) correspond to the table itself, not an
index. The larger of these tables has two files (32397 & 32397.1), almost
2G total.
To make things worse, vacuum ends with:
ERROR: could not access status of transaction 2828785257
DETAIL: Could not open file "pg_clog/0A89": No such file or directory.
That file is nowhere to be found.
When I do this:
set zero_damaged_pages = on;
select count(*) from foo
It shows 3 errors like this:
WARNING: invalid page header in block 78550 of relation base/12926/31642;
zeroing out page
WARNING: invalid page header in block 78551 of relation base/12926/31642;
zeroing out page
WARNING: invalid page header in block 78552 of relation base/12926/31642;
zeroing out page
One idea I found is to generate a zero file for the missing clog, set
zero_damaged_pages=on, then vacuum or recreate the table. Looks like it
might work, but I'm trying to understand the implications.
1. When I generate a zero clog file like this, what impact will it have on
everything? Can it break anything else in the database, introduce some kind
of inconsistency with other (correctly committed) table data?
2. How can I gauge how much actual data (e.g. how many rows) will be lost
by doing this (zero clog file + vacuum/recreate table)?
3. Is there some place I can look to reliably tell how many rows the table
should have, so I can compare to how many I can read after setting
zero_damaged_pages?
4. Is there any way at all to recover/repair the corrupted data at this
point?
--
Konrad Garus
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Data corruption
@ 2021-07-21 16:32 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
parent: Konrad Garus <konrad.garus@gmail.com>
2 siblings, 1 reply; 10+ messages in thread
From: Vijaykumar Jain @ 2021-07-21 16:32 UTC (permalink / raw)
To: Konrad Garus <konrad.garus@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, 21 Jul 2021 at 21:44, Konrad Garus <konrad.garus@gmail.com> wrote:
> Hello,
>
> we have a database that apparently got corrupted in a crash a few months
> ago, long enough that there is no "good" backup.
>
> There are at least two tables occasionally emitting errors like this:
>
> ERROR: invalid page header in block 39684 of relation base/12926/32397
> while performing SQL query: SQL DML: insert into foo (a, b, c) values (?,
> ?, ?) | PARAMETERS: foo, bar, baz: org.postgresql.util.PSQLException:
> ERROR: invalid page header in block 39684 of relation base/12926/32397
>
> The files in question (e.g. 32397) correspond to the table itself, not an
> index. The larger of these tables has two files (32397 & 32397.1), almost
> 2G total.
>
> To make things worse, vacuum ends with:
>
> ERROR: could not access status of transaction 2828785257
> DETAIL: Could not open file "pg_clog/0A89": No such file or directory.
>
I have not tried this (not sure to simulate corruption on a laptop) , but
,maybe the below extension and demo, help to see if how much of it has got
corrupted ?
Physical recovery with pg_filedump (alexey-n-chernyshov.github.io)
<https://alexey-n-chernyshov.github.io/blog/physical-recovery-with-pg_filedump.html;
Also, does pg_dump on those relations complete? or does it throw errors and
abort?
--
Thanks,
Vijay
Mumbai, India
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Data corruption
@ 2021-07-21 17:03 soumitra bhandary <soumitra.bhandary@hotmail.com>
parent: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: soumitra bhandary @ 2021-07-21 17:03 UTC (permalink / raw)
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; +Cc: Konrad Garus <konrad.garus@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
This is very nasty error scenario .
You can try by faking the clog blocks . Anyways your data is corrupted and if you don’t have any back up so it’s lost .
Thanks
Soumitra
Sent from my iPhone
On 21-Jul-2021, at 10:02 PM, Vijaykumar Jain <vijaykumarjain.github@gmail.com> wrote:
[https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif][https://ssl.gstatic.com/ui/v1/icons/mail/images/cleardot.gif]On Wed, 21 Jul 2021 at 21:44, Konrad Garus <konrad.garus@gmail.com<mailto:konrad.garus@gmail.com>> wrote:
Hello,
we have a database that apparently got corrupted in a crash a few months ago, long enough that there is no "good" backup.
There are at least two tables occasionally emitting errors like this:
ERROR: invalid page header in block 39684 of relation base/12926/32397 while performing SQL query: SQL DML: insert into foo (a, b, c) values (?, ?, ?) | PARAMETERS: foo, bar, baz: org.postgresql.util.PSQLException: ERROR: invalid page header in block 39684 of relation base/12926/32397
The files in question (e.g. 32397) correspond to the table itself, not an index. The larger of these tables has two files (32397 & 32397.1), almost 2G total.
To make things worse, vacuum ends with:
ERROR: could not access status of transaction 2828785257
DETAIL: Could not open file "pg_clog/0A89": No such file or directory.
I have not tried this (not sure to simulate corruption on a laptop) , but ,maybe the below extension and demo, help to see if how much of it has got corrupted ?
Physical recovery with pg_filedump (alexey-n-chernyshov.github.io)<https://alexey-n-chernyshov.github.io/blog/physical-recovery-with-pg_filedump.html;
Also, does pg_dump on those relations complete? or does it throw errors and abort?
--
Thanks,
Vijay
Mumbai, India
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Data corruption
@ 2021-07-21 17:09 Scott Ribe <scott_ribe@elevated-dev.com>
parent: soumitra bhandary <soumitra.bhandary@hotmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Scott Ribe @ 2021-07-21 17:09 UTC (permalink / raw)
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Do you have any clue as to how this happened?
If you have a hardware problem, disk, controller or cable that is corrupting data written, then any efforts you make at recovery on the same hardware could just result in more & more corruption.
You don't mention, I think, whether you've copied the data to different storage (and preferably different server), but if you haven't already done so, STOP RIGHT NOW and do nothing else with this db until you've done that.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Data corruption
@ 2021-07-21 18:33 Ron <ronljohnsonjr@gmail.com>
parent: Konrad Garus <konrad.garus@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Ron @ 2021-07-21 18:33 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
I've zeroes out Pg 8.4 pc_clog files using dd (instructions are out there on
the web) without any visible negative effect.
Naturally, YMMV.
On 7/21/21 11:14 AM, Konrad Garus wrote:
> Hello,
>
> we have a database that apparently got corrupted in a crash a few months
> ago, long enough that there is no "good" backup.
>
> There are at least two tables occasionally emitting errors like this:
>
> ERROR: invalid page header in block 39684 of relation base/12926/32397
> while performing SQL query: SQL DML: insert into foo (a, b, c) values (?,
> ?, ?) | PARAMETERS: foo, bar, baz: org.postgresql.util.PSQLException:
> ERROR: invalid page header in block 39684 of relation base/12926/32397
>
> The files in question (e.g. 32397) correspond to the table itself, not an
> index. The larger of these tables has two files (32397 & 32397.1), almost
> 2G total.
>
> To make things worse, vacuum ends with:
>
> ERROR: could not access status of transaction 2828785257
> DETAIL: Could not open file "pg_clog/0A89": No such file or directory.
>
> That file is nowhere to be found.
>
> When I do this:
>
> set zero_damaged_pages = on;
> select count(*) from foo
>
> It shows 3 errors like this:
>
> WARNING: invalid page header in block 78550 of relation base/12926/31642;
> zeroing out page
> WARNING: invalid page header in block 78551 of relation base/12926/31642;
> zeroing out page
> WARNING: invalid page header in block 78552 of relation base/12926/31642;
> zeroing out page
>
> One idea I found is to generate a zero file for the missing clog, set
> zero_damaged_pages=on, then vacuum or recreate the table. Looks like it
> might work, but I'm trying to understand the implications.
>
> 1. When I generate a zero clog file like this, what impact will it have on
> everything? Can it break anything else in the database, introduce some
> kind of inconsistency with other (correctly committed) table data?
>
> 2. How can I gauge how much actual data (e.g. how many rows) will be lost
> by doing this (zero clog file + vacuum/recreate table)?
>
> 3. Is there some place I can look to reliably tell how many rows the table
> should have, so I can compare to how many I can read after setting
> zero_damaged_pages?
>
> 4. Is there any way at all to recover/repair the corrupted data at this point?
>
> --
> Konrad Garus
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Data corruption
@ 2021-07-21 20:02 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Konrad Garus <konrad.garus@gmail.com>
2 siblings, 0 replies; 10+ messages in thread
From: Laurenz Albe @ 2021-07-21 20:02 UTC (permalink / raw)
To: Konrad Garus <konrad.garus@gmail.com>; pgsql-admin@lists.postgresql.org
On Wed, 2021-07-21 at 18:14 +0200, Konrad Garus wrote:
> we have a database that apparently got corrupted in a crash a few months ago, long enough that there is no "good" backup.
>
> There are at least two tables occasionally emitting errors like this:
>
> ERROR: invalid page header in block 39684 of relation base/12926/32397 while performing SQL query: SQL DML: insert into foo (a, b, c) values (?, ?, ?)
> | PARAMETERS: foo, bar, baz: org.postgresql.util.PSQLException: ERROR: invalid page header in block 39684 of relation base/12926/32397
>
> The files in question (e.g. 32397) correspond to the table itself, not an index.
> The larger of these tables has two files (32397 & 32397.1), almost 2G total.
>
> To make things worse, vacuum ends with:
>
> ERROR: could not access status of transaction 2828785257
> DETAIL: Could not open file "pg_clog/0A89": No such file or directory.
>
> That file is nowhere to be found.
>
> When I do this:
>
> set zero_damaged_pages = on;
> select count(*) from foo
>
> It shows 3 errors like this:
>
> WARNING: invalid page header in block 78550 of relation base/12926/31642; zeroing out page
> WARNING: invalid page header in block 78551 of relation base/12926/31642; zeroing out page
> WARNING: invalid page header in block 78552 of relation base/12926/31642; zeroing out page
>
> One idea I found is to generate a zero file for the missing clog, set zero_damaged_pages=on,
> then vacuum or recreate the table. Looks like it might work, but I'm trying to understand the implications.
>
> 1. When I generate a zero clog file like this, what impact will it have on everything?
> Can it break anything else in the database, introduce some kind of inconsistency with other (correctly committed) table data?
Certainly. I would create a fake clog file with only 0x55 in it, that corresponds to
all transactions being committed, so you will "see" more of the damaged data.
But of yourse there is no guarantee that the data are consistent. For example,
foreign keys could be violated. You will have to manually fix these data
before you can create the constraints.
> 2. How can I gauge how much actual data (e.g. how many rows) will be lost by doing this (zero clog file + vacuum/recreate table)?
You'd have to look at the binary data of the affected blocks to get an idea.
The "pageinspect" contrib can extract a block from a data file.
> 3. Is there some place I can look to reliably tell how many rows the table should have,
> so I can compare to how many I can read after setting zero_damaged_pages?
No.
> 4. Is there any way at all to recover/repair the corrupted data at this point?
With the techiques you describe, you can salvage some data. Carefully reading the damaged
blocks, you may glean some more.
But typically, at that point you are happy if you lose only some data. You may have to
delete some more to make the data consistent. Then cut your losses and load the dump
into a new cluster on good hardware.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2021-07-21 20:02 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-02 22:22 Data Corruption? Robin's Postgres List Archive <pglist@mail.robin.net>
2001-02-03 21:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-28 19:12 data corruption Hemapriya <priyam_1121@yahoo.com>
2005-09-30 06:06 ` Re: data corruption Gourish Singbal <gourish@gmail.com>
2021-07-21 16:14 Data corruption Konrad Garus <konrad.garus@gmail.com>
2021-07-21 16:32 ` Re: Data corruption Vijaykumar Jain <vijaykumarjain.github@gmail.com>
2021-07-21 17:03 ` Re: Data corruption soumitra bhandary <soumitra.bhandary@hotmail.com>
2021-07-21 17:09 ` Re: Data corruption Scott Ribe <scott_ribe@elevated-dev.com>
2021-07-21 18:33 ` Re: Data corruption Ron <ronljohnsonjr@gmail.com>
2021-07-21 20:02 ` Re: Data corruption Laurenz Albe <laurenz.albe@cybertec.at>
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