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.