Re: why restoring a dump file is sooo slow

On Sat, 26 Apr 2003, Reynard Hilman wrote:

> Hi,
>
> I'm trying to restore a dump file that I created using:
> pg_dump -CD dbname > dump.sql
>
> the dump file size is about 87 Mb.
>
> so I did this to restore:
> psql -f dump.sql -U user template1
>
> It takes almost an hour and a half to restore the database.
>
> I'm just wondering if that is normal for such a small database (87Mb), or
> is something wrong with how I did the restore or with my db configuration?
> btw, it's on dual pIII server running redhat 7.3. with about 2G of memory.

You don't mention what flavor of Postgresql this is.  The latest version
would probably help a bit.  Also, things like FK constraints can cause
slow load times.  But 87 megs in an hour and a half is REALLY slow.  for
comparison, I can dump our 1 gig (post dump) database from one Dual PIII
to another dual PIII in 10 minutes.  i.e.:

'pg_dump -h otherbox dbname| psql dbname'

But that's running 7.3.x.  It took about 30 to 40 minutes on 7.2 but my
memory of that time period is fading fast.