Обсуждение: PG_DUMP error : unexpected chunk number
Hello,
I think I got a big problem now, I'm not able to do pg_dump on one of my production database. When I do pg_dump it give me this error:
pg_dump: Error message from server: ERROR: unexpected chunk number 18390760 (expected 4) for toast value 92784 in pg_toast_88487
I believe this message mean that my database is corrupted.
I do file base + continues archive backup every day, there is no problem for that, until now I need to upgrade from 9.0.4 to 9.1.1, so I did a pg_dump and found that the database is corrupted.
Hope someone can help me, thanks
Regards
Louis
On 10/28/2011 06:24 PM, mailtolouis2020-postgres@yahoo.com wrote: > Hello, > > I think I got a big problem now, I'm not able to do pg_dump on one of my > production database. When I do pg_dump it give me this error: > pg_dump: Error message from server: ERROR: unexpected chunk number > 18390760 (expected 4) for toast value 92784 in pg_toast_88487 > > I believe this message mean that my database is corrupted. Yup, pretty much. Check your hard drives. It's not impossible that there's a PostgreSQL bug that's caused the issue, but it's more likely going to be a hard drive, RAID array, or system memory/cpu/heat issue. For recovery: First, stop postgresql and take a file-level copy of your whole database. Keep that copy somewhere safe, in case your repair efforts make the issue worse. In this case, I'd probably try zeroing damaged pages as my first recovery effort. That's a bit of a big hammer, but might let you get a dump out. It WILL DESTROY DATA, so I'd recommend doing it by copying your backup to another directory and running a temporary postgresql instance with zero_damaged_pages enabled on it, then trying to dump from the temporary postmaster you've started. That way you don't have to mess with your original running database. See: http://www.postgresql.org/docs/current/static/runtime-config-developer.html It might help to look up which "real" table the pg_toast_88487 TOAST table is associated with, and see how important it is. Use pg_catalog for that; see the documentation. -- Craig Ringer
Hi,
Thanks for the info.
I've sorted out my problem by recreating the table and re-insert back the data exclude the corrupted row into the newly create table. And went back to my old backup to get back the data before it corrupt. I was lucky only 1 row affected.
Regards
Louis
From: Craig Ringer <ringerc@ringerc.id.au>
To: "mailtolouis2020-postgres@yahoo.com" <mailtolouis2020-postgres@yahoo.com>
Cc: Postgres <pgsql-general@postgresql.org>
Sent: Saturday, October 29, 2011 5:05 PM
Subject: Re: [GENERAL] PG_DUMP error : unexpected chunk number
On 10/28/2011 06:24 PM, mailtolouis2020-postgres@yahoo.com wrote:
> Hello,
>
> I think I got a big problem now, I'm not able to do pg_dump on one of my
> production database. When I do pg_dump it give me this error:
> pg_dump: Error message from server: ERROR: unexpected chunk number
> 18390760 (expected 4) for toast value 92784 in pg_toast_88487
>
> I believe this message mean that my database is corrupted.
Yup, pretty much. Check your hard drives. It's not impossible that there's a PostgreSQL bug that's caused the issue, but it's more likely going to be a hard drive, RAID array, or system memory/cpu/heat issue.
For recovery: First, stop postgresql and take a file-level copy of your whole database. Keep that copy somewhere safe, in case your repair efforts make the issue worse.
In this case, I'd probably try zeroing damaged pages as my first recovery effort. That's a bit of a big hammer, but might let you get a dump out. It WILL DESTROY DATA, so I'd recommend doing it by copying your backup to another directory and running a temporary postgresql instance with zero_damaged_pages enabled on it, then trying to dump from the temporary postmaster you've started. That way you don't have to mess with your original running database.
See: http://www.postgresql.org/docs/current/static/runtime-config-developer.html
It might help to look up which "real" table the pg_toast_88487 TOAST table is associated with, and see how important it is. Use pg_catalog for that; see the documentation.
--
Craig Ringer
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general