Re: Why are pg_restore taking that long ?

Hi Josh,

Josh Kupershmidt wrote:
>
> 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).

All our dumps are done like this:
pg_dump -Fc -Z4 >filename.dat

So we have custom format and full-db dumps whenever we do them. Since the
majority of our DB is blobs in compressed file formats we've found that a
compression factor of '4' gives us the best 'size vs time' trade-off.

And we restore them this way:
pg_restore -Ov -j2 -d db_name filename.dat

The per-blob messages are missing whether we use the '-j2' or not and also
regardless of number of '-v's we use.

I'm glad they've reinstated some of the messages. Previously there was
a message per blob between the "restoring BLOBS" and "restored NNN large
objects" messages. It's a pity they've gone because we used them as a
'progress meter' of sorts. They disappeared somewhere around 8.4.8 or
8.4.9 IIRC and, unfortunately, we are currently restricted to the 8.4.x
series. :-(

> It looks like you are correct that the blobs are restored inside a
> single transaction, even without the --single-transaction flag.

*nods* I'm not sure what the motivation there was but I am also sure
that the devs had/have a very good reason for it. :-)

Bosco.