Search Postgresql Archives

Re: Why are pg_restore taking that long ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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.

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Programming PHP]     [Kernel Newbies]     [PHP Classes]     [Find Someone Nice]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

Add to Google Powered by Linux