Hi,
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 want this time to be reduced as much as possible since 8-9 hrs for restore
is definitely not desirable and I might have to do it 3-4 times a day.
I have already tuned postgres to set maintenance_work_mem = 1000000 (I guess
other settings do not effect db backup and restore).
One thing I tried was to take a backup of the complete PGDATA directory and
untar it to a seperate location. The problem comes in case of tablespaces
which will now point to the same location as the place from where the dump
was taken. I tried modifying tablespace of the retsored db to set it to some
other location but it seems to change it for the original db as well. So,
this won't work.
Note: I need to take into account tablespaces that can be distributed across
different partitions. I cannot modify tablespaces in the database from which
a dump is generated to set them to default tablespace, copy PGDATA dir and
work from there on. (as a requirement orig. db should not be modified in any
way)
Is there some other way to get fast db backups and restore?
Major bottleneck here is CPU usage where CPU usage gets to 100% for one of
the processors. Is there a way to distribute dump and restore across all the
processors.
I also looked at Incremental backups but could not see anything on how to
restore them to some other location. Every where backup is combined with
recovery and not restore.
Regards,
Vinita Bansal
_________________________________________________________________
Special offer for NRIs!
http://creative.mediaturf.net/creatives/citibankrca/rca_msntagofline.htm Get
a zero balance account for next 20 years. From Citibank