bricklen <bricklen@xxxxxxxxx> writes: > We have run into some corruption in one of our production tables. We know > the cause (a compute node was moved), but now we need to fix the data. We > have backups, but at this point they are nearly a day old, so recovering > from them is a last-resort and will incur significant downtime. > We are running 9.3.9 > Following the steps at > http://blog.endpoint.com/2010/06/tracking-down-database-corruption-with.html > I get the following output for ctid, id, other_id, tstamp: > (690651,42) | 318698967 | 347978007 | 2015-10-20 01:55:41.757+00 > (690651,43) | 318698968 | 347978008 | 2015-10-20 01:55:41.663+00 > (690651,44) | 318698969 | 347978009 | 2015-10-20 01:55:42.005+00 > ERROR: invalid page in block 1226710 of relation base/16750/27244 > It appears 690652 is what would be dd'd if that's the route we take. Is > that accurate? I'm confused by the block mentioned in the error message not having anything to do with the TID sequence. I wonder whether it refers to an index not the table proper. What query were you using to get this output, exactly? Have you confirmed which relation has relfilenode 27244? > Because the message indicates the corruption is in the table's page, not > the page header, according to the docs zero_damaged_pages probably won't > work. I should think that zero_damaged_pages would work fine, if the problem is indeed in the base table. But if it's in an index, a REINDEX would be a better answer. Another thing to keep in mind here, if you've got replication slaves, is that I'm not sure whether the effects of zero_damaged_pages would propagate to slaves. Have you investigated the possibility that some slave has an uncorrupt copy that you could dd into place in the master? > Is this the correct command if option #2 is chosen? Can it be executed > against a running cluster? > dd if=/dev/zero of=database/16750/27244 bs=8192 seek=690652 count=1 > conv=notrunc Uh, no, you're not accounting for the fact that such an offset wouldn't be in the first segment file of the relation. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
