why restoring a dump file is sooo slow

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

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...   |
+-----------------------------------------------------------+

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.

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