Re: Why are pg_restore taking that long ?

On Fri, May 18, 2012 at 3:11 PM, Bosco Rama <postgres(at)boscorama(dot)com> wrote:

> One thing you may want to look at (if this is PG 8.4.x) is the number of
> large objects in pg_largeobjects.  If your apps don't use large objects
> this is not relevant.  If they do, then it may be.  I've noticed that
> pg_restore no longer reports the restoration of individual LO items.  It
> used to but no longer.  That means that even with multiple '-v' options
> specified it appears that the restore has hung even though it is in fact
> adding large objects all the while.  I've also noticed that I can no longer
> see the objects as they are added to the db.  I wonder if their restoration
> got moved to a single transaction?
>
> Anyway, if you are seeing no activity at the end of the restore for quite
> a while you may want to see if large objects are the reason.

Using a recent pg_restore (tested with 9.1.2, verbose mode on) and a
custom-format dump containing large objects, you should see messages
like this:

pg_restore: creating TABLE image
pg_restore: executing BLOB 126810
pg_restore: executing BLOB 133157
...
pg_restore: restoring data for table "image"
pg_restore: restoring BLOBS
pg_restore: restored 1111 large objects
...
pg_restore: setting owner and privileges for BLOB 126810
pg_restore: setting owner and privileges for BLOB 133157
...

If you're not seeing those messages, maybe your dump didn't actually
include large objects (e.g. because you didn't dump the whole database
and left off --blobs). It looks like you are correct that the blobs
are restored inside a single transaction, even without the
--single-transaction flag.

Josh