Hanging with pg_restore and large objects - Mailing list pgsql-general

Hi, everyone.  I'm working on a project that is using 8.3.0; among other things, I'm helping them to move to 9.0.  The project is running on Windows XP.

Someone from this project asked me earlier today why a particular database restore was taking a long time.  How long?  Well, it has been running for 1.5 days (yes, that's 36 hours).  The restore is running under Windows XP, and the backup was done using pg_dump into the custom/binary format.  The dumpfile was about 140 MB in size. 

Using the Windows process monitor, we saw that pg_restore was using about 50 percent of the CPU, doing an enormous (about 60 billion, by this point) reads from the disk, but zero writes.  The dumpfile does contain a number of large (binary) objects, as well as a number of regular tables with integer and textual content.  The restore was run with the -a (data only) flag, on an empty database schema.

We tried to replicate this problem on another, similarly equipped machine, adding the -c (clean before restoring), -e (exit upon error), and -v (verbose) flags.  We saw that the restore hung (for about 30 minutes, as of this writing) while loading one of the large objects from the restore.

We tried to use pg_restore on the dumpfile, but found that it hung when restoring the same large object.  It's not even close to the first large object, and I don't believe that it's the last one, either.

My guess is pg_dump in 8.3 is somehow causing a problem in the dumpfile on or around that large object.

So:

• Is this a known problem on PostgreSQL 8.3, Windows, or the combination?
• Is there an easy way to identify problems, corruption, and the like in our pg_dump file?
• Should we be using a different type of dumpfile, such as text, to get around this problem for now?
• Is there any obvious way to diagnose or work around this problem?
• I don't believe that there's a way to tell either pg_dump or pg_restore to ignore objects with particular OIDs.  Am I right?
  

-- 
Reuven M. Lerner -- Web development, consulting, and training
Mobile: +972-54-496-8405 * US phone: 847-230-9795
Skype/AIM: reuvenlerner