Re: Why are pg_restore taking that long ?

Hi,

Poul Møller Hansen wrote:
> I have just restored a database about 347GB in size
>
> postgres=# select * from pg_database_size('dbname');
>   pg_database_size
> ------------------
>       346782483256
>
> using this command:
> pg_restore -d dbname -O -Fc dbname.backup
>
> It started at 13/5 21:28 and at 14/5 18:47 all user tables were imported
> and indexes were build.
> It's on a single SATA drive, so I think that's OK.
>
> But the restore wasn't finished until 15/5 08:47

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.

Just a thought.

Bosco.