Thread: why restoring a dump file is sooo slow

why restoring a dump file is sooo slow

From
"Reynard Hilman"
Date:
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.

thanks,
reynard


Re: why restoring a dump file is sooo slow

From
Mike Mascari
Date:
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.

This there a reason you are using the 'D' option to dump the database
as INSERTs? A dump which uses COPY (the default) instead will reload
much more quickly.

Mike Mascari
mascarm@mascari.com


Re: why restoring a dump file is sooo slow

From
Ron Johnson
Date:
On Sat, 2003-04-26 at 07:05, Mike Mascari wrote:
> 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.
>
> This there a reason you are using the 'D' option to dump the database
> as INSERTs? A dump which uses COPY (the default) instead will reload
> much more quickly.

Also, are there triggers on the tables?  There's a pg_dump
option to ensure that they don't get activated during restore.

--
+-----------------------------------------------------------+
| Ron Johnson, Jr.     Home: ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson |
|                                                           |
| An ad currently being run by the NEA (the US's biggest    |
| public school TEACHERS UNION) asks a teenager if he can   |
| find sodium and *chloride* in the periodic table of the   |
| elements.                                                 |
| And they wonder why people think public schools suck...   |
+-----------------------------------------------------------+


Re: why restoring a dump file is sooo slow

From
"scott.marlowe"
Date:
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.


Re: why restoring a dump file is sooo slow

From
Reynard Hilman
Date:
> 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.

I'm using 7.3.x. The tables don't have any trigger.
I guess the only problem was the -D option that I used. I tried again
without that, and it only took a couple of minutes. I'm surprised
though, how much difference that makes.
thanks for all the help

- reynard