Обсуждение: why restoring a dump file is sooo slow
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
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