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
