Re: Dump restoration via archive files

On Mon, 1 Dec 2003 15:47:47 +0530
"Kamalraj Singh Madhan" <kamalr(at)networkprograms(dot)com> wrote:

> Hi,
> I'am taking dump of a huge database and do not want the
> restoration of
> that dump to take a lot of time as is the case when you take the dump
> in text files. I want to take the dump as an archive file and get it
> restored in very less time. I'am not able to figure out what is the
> command for taking dump of a database in a archive file. Kindly help
> it's urgent.
>

Fast backups are an area PG needs work in. Currently, PG has no 'archive
file backup'. You do have the following options to get around this:

1. Take big db offline, copy $PGDATA. Has a restore time of how long it
takes to copy $PGDATA (And optionally untar/gzip), bring db back online

2. If you are using an LVM, take a snapshot and copy the data. Like #1,
it also has a "0" restore time.

3. If you are using a pg_dump generated dump, be sure to really jack up
your sort_mem - this will be a HUGE benefit when creating indexes & if
you are using 7.4, adding the foriegn keys. Also turning off fsync
(Don't forget to turn it back on after your restore!) cna give you some
nice speed increases.

4. If you are not using 7.4 and using pg_dump, there isn't much you can
do about adding foreign keys going stupidly slow :(

--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/