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:
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?
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 *
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:
Show quoted text
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 }.comEnterpriseDB 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 *Mail sent from my BlackLaptop device