Thread: Somewhat automated method of cleaning table of corrupt records forpg_dump

Hey!

I'm currently in the situation that due to (probably) broken memory in
a server, I have a corrupted PostgreSQL database. Getting at the data
that's in the DB is not time-critical (because backups have restored the
largest part of it), but I'd still like to restore what can be restored
from the existing database to fill in the remaining data. VACUUM FULL
runs successfully (i.e., I've fixed the blocks with broken block
headers, removed rows that have invalid OIDs as recorded by the VACUUM,
etc.), but dumping the DB from the rescue system (which is PostgreSQL
8.3.21) to transfer it to another still fails with "invalid memory alloc
request size 18446744073709551613", i.e., most probably one of the TEXT
colums in the respective tables contains invalid sizings.

The methods for restoring from this condition - which I've not really
found much info on (except some posts on pgsql-hackers) - all require
loads of manual work (and don't seem to actually work in my corruption
case, I can "select * from <table> offset <any> limit 1" without it
failing on me); is there any possibility to automate the row-checking?
I'm not proficient with PL/pgSQL, but would start trying to write a
respective script in case it's not available somewhere. I've found a
corresponding PL/pgSQL method on the web, but that does _not_ work for
my case (it's 9.0+ only).

Thanks in advance if anybody could point me at a corresponding hint!

--
--- Heiko.

On 10/19/2012 10:31 PM, Heiko Wundram wrote:
> Hey!
>
> I'm currently in the situation that due to (probably) broken memory in a
> server, I have a corrupted PostgreSQL database. Getting at the data
> that's in the DB is not time-critical (because backups have restored the
> largest part of it), but I'd still like to restore what can be restored
> from the existing database to fill in the remaining data. VACUUM FULL
> runs successfully (i.e., I've fixed the blocks with broken block
> headers, removed rows that have invalid OIDs as recorded by the VACUUM,
> etc.), but dumping the DB from the rescue system (which is PostgreSQL
> 8.3.21) to transfer it to another still fails with "invalid memory alloc
> request size 18446744073709551613", i.e., most probably one of the TEXT
> colums in the respective tables contains invalid sizings.

Working strictly with a *copy*, does REINDEXing then CLUSTERing the
tables help? VACCUM FULL on 8.3 won't rebuild indexes, so if index
damage is the culprit a reindex may help. Then, if CLUSTER is able to
rewrite the tables in index order you might be able to recover.

--
Craig Ringer

Am 22.10.2012 09:05, schrieb Craig Ringer:
> Working strictly with a *copy*, does REINDEXing then CLUSTERing the
> tables help? VACCUM FULL on 8.3 won't rebuild indexes, so if index
> damage is the culprit a reindex may help. Then, if CLUSTER is able to
> rewrite the tables in index order you might be able to recover.

REINDEXing all indexes works, CLUSTERing all tables on the primary key
(index) after rebuilding them also works, but the dump still displays
the same error as before (i.e., out of memory while dumping a specific
table). I guess that the broken record(s) in that table are either not
moved, or their data portion isn't adapted to the respective index.
Which means that besides walking all table rows (and deleting those by
OID that contain error[s]), there's not much that I can do, I guess.

If there's any other possibility of "out of the box" recovery - except
writing myself a small script to walk all rows - I'd still be grateful
for a hint.

Thanks for the hint at CLUSTER and REINDEX, anyway, which I've never
properly used before - this gave me an incentive to dig in the
documentation.

--
--- Heiko.

On Mon, Oct 22, 2012 at 11:54:47AM +0200, Heiko Wundram wrote:
> If there's any other possibility of "out of the box" recovery -
> except writing myself a small script to walk all rows - I'd still be
> grateful for a hint.

Something that has worked for me in the past is:

$ SELECT ctid FROM table WHERE length(field) < 0;

This gives you a list of ctids (if it works) which you can delete. You
can also look for very large lengths. This works because length()
doesn't actually unpack the string, it just pulls the length.

It doesn't always work, it depends on the kind of corruption. You also
need to start at the leftmost text field and work forwards, because it
blows up while unpacking the tuples.

Otherwise you're back to doing things like:

$ SELECT sum(length(field || '')) FROM table OFFSET x LIMIT y;

And doing a bisect type algorithm to narrow down where it is. The
sum(length()) is so you throw away the output after checking field can
be extracted properly.  Once you get close you start printing the ctids
and take a stab at the ctid of the broken row and delete it.

If your table is large the OFFSET/LIMIT get slow. Unfortunatly Postgres
doesn't understand clauses like 'ctid > (page,tuple)' to start scanning
at a particular spot in the table.

It's not automated, though it might not be hard to do.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

Am 22.10.2012 22:34, schrieb Martijn van Oosterhout:
> Something that has worked for me in the past is:
>
> $ SELECT ctid FROM table WHERE length(field) < 0;

As the structure of the tables (about four were affected) isn't
something that I wanted to actually look at, I set off writing a small
script in Python which fetches ctids from all tables in the DB, then
tries to fetch the corresponding row, and in case that fails, removes
it. That worked beautifully for me (it removed somewhere around
eight/nine rows from the DB), and I can now dump the whole database
correctly. Running the script took around 48 hours for the complete
database.

It's not pretty, but works, and for anybody else finding themselves in
this situation, here's the code:

--------------------------------
# -*- coding: utf-8 -*-

# Imports
# -------

import psycopg2


# Open database
# -------------

conn = psycopg2.connect("dbname=<name of DB> user=postgres")


# Select tables
# -------------

cur = conn.cursor()
cur.execute("SELECT relname FROM pg_class WHERE relkind = 'r' AND
relnamespace = 2200")
TABS = cur.fetchall()
cur.close()


# Walk tables
# -----------

for tab in TABS:
     # Fetching from tab.
     print "Fetching from:", tab[0]

     # Fetch cursor.
     cur = conn.cursor()
     cur.execute("SELECT ctid FROM %s" % (tab[0],))
     stmt = "SELECT * FROM %s WHERE ctid = %%s" % (tab[0],)
     dstmt = "DELETE FROM %s WHERE ctid = %%s" % (tab[0],)
     todelete = []
     while True:
         # Fetch an element.
         ctid = cur.fetchone()
         if ctid is None:
             print "Done with:", tab[0]
             break

         # Fetch complete element.
         curr = conn.cursor()
         try:
             curr.execute(stmt, ctid)
             curr.fetchone()
             curr.close()
         except:
             # Failed to fetch, clean any pending transaction.
             print "Failed to fetch:", ctid[0]
             curr.close()
             cur.close()
             conn.rollback()

             # Create new cursor on connection.
             curr = conn.cursor()
             curr.execute(dstmt, ctid)
             conn.commit()
             curr.close()

             # Reset cursor.
             cur = conn.cursor()
             cur.execute("SELECT ctid FROM %s" % (tab[0],))
--------------------------------

Thanks again for all the hints!

--
--- Heiko.