Re: Backup and Restore mechanism in Postgres

At 10:00 AM 9/20/2005 -0400, Vivek Khera wrote:


>On Sep 14, 2005, at 9:45 AM, vinita bansal wrote:
>
>>I have a 4 proc. AMD Opteron machine with 32 GB RAM and ~400GB HDD
>>and a 40GB database. I need to take backup of this database and
>>restore it some other location (say some test environment). I am
>>currently using pg_dump and pg_restore utilities to get this done
>>which takes 4-5 hrs for a dump and 8-9 hrs for restore
>>respectively. I am using custom format for taking dumps.
>
>i'll bet you've saturated your disk I/O bandwidth, since for me
>dumping a db a bit larger than that takes roughly 1 hour, and restore
>about 4.

I don't think disk I/O is saturated, unless the database is very fragmented
on disk.

Most modern drives can manage at least 40MB/sec sequential throughput. Even
random seeks of 64KB or 128KB blocks should get you about 6-12MB/sec. So 4
hours is quite slow. And 8-9 hours for a restore of 40GB probably won't be
very pleasant if you have a boss or customer breathing down your neck...

Any reason why Postgresql would only get 2.8MB/sec for dumps or slower?

Regards,
Link.