Thread: pg_restore seems slow

I'm trying to restore my database from 8.26 into 8.3 (win32) but find
the process to be exceedingly slow.  The database has about 60M records.
I realize there will be differences based on hardware, available memory,
complexity of records but when I first tried a restore with the verbose
option I was able to calculate based on the index incrementing that it
was inserting about 6500 records per minute.
At that rate it would take 153 hours to restore my db.  I then tried
minimizing the verbosity window and would open it only after a minute
and the speed was improved to about 20000 records per minute.  I'm
hoping without the verbose option that the speed increases to at least
200000 records per minute which would be a fairly reasonable 5 hours.
So is there any way besides using verbose to calculate the speed at
which pg_restore is inserting records?  It would be great to have a
'progress' option so that a person could time going out for a sail in
the morning and then return at just the right time.  Guess you know what
I'd rather be doing instead of staring at the command prompt :)

On Feb 9, 2008 10:42 AM, Willem Buitendyk <willem@pcfish.ca> wrote:

By any chance, are you using -d or -D option while doing pg_dump?

Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB      http://www.enterprisedb.com

17° 29' 34.37"N,   78° 30' 59.76"E - Hyderabad
18° 32' 57.25"N,   73° 56' 25.42"E - Pune
37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *

http://gurjeet.frihost.net

Mail sent from my BlackLaptop device

I did use the 'd' switch but I didn't use the 'C' switch so I'm not sure
a database was actually created.  Anyways, after I used the correct
switches all work fast - really fast.  About a 1M records per minute.  I
was able to peek into the server processes to see the current copy
commands in effect.  Would still like to see a progress indicator though :)

Willem

Gurjeet Singh wrote:
> On Feb 9, 2008 10:42 AM, Willem Buitendyk <willem@pcfish.ca
> <mailto:willem@pcfish.ca>> wrote:
>
>     I'm trying to restore my database from 8.26 into 8.3 (win32) but find
>     the process to be exceedingly slow.  The database has about 60M
>     records.
>     I realize there will be differences based on hardware, available
>     memory,
>     complexity of records but when I first tried a restore with the
>     verbose
>     option I was able to calculate based on the index incrementing that it
>     was inserting about 6500 records per minute.
>     At that rate it would take 153 hours to restore my db.  I then tried
>     minimizing the verbosity window and would open it only after a minute
>     and the speed was improved to about 20000 records per minute.  I'm
>     hoping without the verbose option that the speed increases to at least
>     200000 records per minute which would be a fairly reasonable 5 hours.
>     So is there any way besides using verbose to calculate the speed at
>     which pg_restore is inserting records?  It would be great to have a
>     'progress' option so that a person could time going out for a sail in
>     the morning and then return at just the right time.  Guess you
>     know what
>     I'd rather be doing instead of staring at the command prompt :)
>
>     ---------------------------(end of
>     broadcast)---------------------------
>     TIP 3: Have you checked our extensive FAQ?
>
>                   http://www.postgresql.org/docs/faq
>
>
> By any chance, are you using -d or -D option while doing pg_dump?
>
> Best regards,
> --
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB      http://www.enterprisedb.com
>
> 17° 29' 34.37"N,   78° 30' 59.76"E - Hyderabad
> 18° 32' 57.25"N,   73° 56' 25.42"E - Pune
> 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *
>
> http://gurjeet.frihost.net
>
> Mail sent from my BlackLaptop device