From: Jeff <threshar@torgo.978.org>
To: Kamalraj Singh Madhan <kamalr@networkprograms.com>
Cc: jason@tishler.net
Cc: pgsql-performance@postgresql.org
Subject: Re: Dump restoration via archive files
Date: Mon, 1 Dec 2003 09:16:34 -0500
Message-ID: <20031201091634.6f19f28a.threshar@torgo.978.org> (raw)
In-Reply-To: <01d601c3b7f4$61897f10$4ecb09c0@KAMALR>
References: <4c0ccf4c27ff.4c27ff4c0ccf@jhmimail.jhmi.edu>
<3FC54A82.2040605@familyhealth.com.au>
<20031127032831.GA4836@gp.word-to-the-wise.com>
<3FC58097.8090803@familyhealth.com.au>
<20031127051230.GA6212@gp.word-to-the-wise.com>
<20031128050417.GA14227@gp.word-to-the-wise.com>
<3FC6DAB8.4080106@familyhealth.com.au>
<20031128203700.GA19831@gp.word-to-the-wise.com>
<3FC9BC76.2030308@familyhealth.com.au>
<01d601c3b7f4$61897f10$4ecb09c0@KAMALR>
On Mon, 1 Dec 2003 15:47:47 +0530
"Kamalraj Singh Madhan" <kamalr@networkprograms.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@jefftrout.com>
http://www.jefftrout.com/http://www.stuarthamm.net/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: threshar@torgo.978.org, kamalr@networkprograms.com, jason@tishler.net
Subject: Re: Dump restoration via archive files
In-Reply-To: <20031201091634.6f19f28a.threshar@torgo.978.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox