pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feed7.3.4 Table corruption
3+ messages / 2 participants
[nested] [flat]
* 7.3.4 Table corruption
@ 2004-08-25 01:41 Andrew Farmer <mail@andrewfarmer.com>
2004-08-26 01:56 ` Re: 7.3.4 Table corruption Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Andrew Farmer @ 2004-08-25 01:41 UTC (permalink / raw)
To: pgsql-admin
I'm running a 7.3.4 on a RedHat 7.2 i686 box. I take daily backups of
my application's database, and last week it failed on one of the
tables. After some checking I found that the rest of the data was ok,
just a large number of tuples on one table were corrupt. I'm pretty sure
it's table corruption, as a REINDEX does not solve the problem, and I
can't COPY data from the table.
I have backups I can use, but I only want to restore this one table,
which would not normally be a big problem, as I can just pull the
relevant commands out of the dump file. But there are a lot of
connections through rules and references from the broken table to other
tables, so I can't simply truncate this table and reload it, I would
have to reload a large number of other tables as well. Something I
don't really want to do, as I could potentially lose more data.
My question is, if I load the good dump into a clean database, and then
find the underlying file that represents the broken table and copy it
over the top of the broken table, am I likely to face any big problems?
Thanks
Andrew
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 7.3.4 Table corruption
2004-08-25 01:41 7.3.4 Table corruption Andrew Farmer <mail@andrewfarmer.com>
@ 2004-08-26 01:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-27 04:03 ` Re: 7.3.4 Table corruption Andrew Farmer <mail@andrewfarmer.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2004-08-26 01:56 UTC (permalink / raw)
To: Andrew Farmer <mail@andrewfarmer.com>; +Cc: pgsql-admin
Andrew Farmer <mail@andrewfarmer.com> writes:
> My question is, if I load the good dump into a clean database, and then
> find the underlying file that represents the broken table and copy it
> over the top of the broken table, am I likely to face any big problems?
This strikes me as a real good way to shoot yourself in the foot ;-).
Better take a backup and be prepared to restore from it. And I'd
suggest experimenting in a scratch installation before you try it for
real.
Having said that, I think it would work, if your "clean database" is
another DB in the same cluster (you could *not* copy from data prepared
under a different postmaster). And you'll need to copy all the indexes
on that table, and its toast table and toast table index if it has one.
And shut down the postmaster while you do the copying.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 7.3.4 Table corruption
2004-08-25 01:41 7.3.4 Table corruption Andrew Farmer <mail@andrewfarmer.com>
2004-08-26 01:56 ` Re: 7.3.4 Table corruption Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-27 04:03 ` Andrew Farmer <mail@andrewfarmer.com>
0 siblings, 0 replies; 3+ messages in thread
From: Andrew Farmer @ 2004-08-27 04:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks for the advice. I'll relax the constraints on the table, and
reload that table from the dump, might take a while to fix any problems,
but it should be safer.
Regards, Andrew
Tom Lane wrote:
>Andrew Farmer <mail@andrewfarmer.com> writes:
>
>
>>My question is, if I load the good dump into a clean database, and then
>>find the underlying file that represents the broken table and copy it
>>over the top of the broken table, am I likely to face any big problems?
>>
>>
>
>This strikes me as a real good way to shoot yourself in the foot ;-).
>Better take a backup and be prepared to restore from it. And I'd
>suggest experimenting in a scratch installation before you try it for
>real.
>
>Having said that, I think it would work, if your "clean database" is
>another DB in the same cluster (you could *not* copy from data prepared
>under a different postmaster). And you'll need to copy all the indexes
>on that table, and its toast table and toast table index if it has one.
>And shut down the postmaster while you do the copying.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-08-27 04:03 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-25 01:41 7.3.4 Table corruption Andrew Farmer <mail@andrewfarmer.com>
2004-08-26 01:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-27 04:03 ` Andrew Farmer <mail@andrewfarmer.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