Hanging with pg_restore and large objects

"Reuven M. Lerner" <reuven@xxxxxxxxxxxx> · Tue, 07 Dec 2010 15:51:24 +0200



    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?

      
    Thanks in advance for any help you can offer,

    
    Reuven

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