pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDump restoration via archive files
3+ messages / 3 participants
[nested] [flat]
* Dump restoration via archive files
@ 2003-12-01 10:17 Kamalraj Singh Madhan <kamalr@networkprograms.com>
2003-12-01 14:16 ` Re: Dump restoration via archive files Jeff <threshar@torgo.978.org>
0 siblings, 1 reply; 3+ messages in thread
From: Kamalraj Singh Madhan @ 2003-12-01 10:17 UTC (permalink / raw)
To: Jason Tishler <jason@tishler.net>; +Cc: pgsql-performance
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.
thanks and regards
Kamalraj Singh
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Dump restoration via archive files
2003-12-01 10:17 Dump restoration via archive files Kamalraj Singh Madhan <kamalr@networkprograms.com>
@ 2003-12-01 14:16 ` Jeff <threshar@torgo.978.org>
2003-12-01 15:23 ` Re: Dump restoration via archive files Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jeff @ 2003-12-01 14:16 UTC (permalink / raw)
To: Kamalraj Singh Madhan <kamalr@networkprograms.com>; +Cc: jason@tishler.net; pgsql-performance
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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Dump restoration via archive files
2003-12-01 10:17 Dump restoration via archive files Kamalraj Singh Madhan <kamalr@networkprograms.com>
2003-12-01 14:16 ` Re: Dump restoration via archive files Jeff <threshar@torgo.978.org>
@ 2003-12-01 15:23 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2003-12-01 15:23 UTC (permalink / raw)
To: Jeff <threshar@torgo.978.org>; +Cc: Kamalraj Singh Madhan <kamalr@networkprograms.com>; jason@tishler.net; pgsql-performance
On Mon, 1 Dec 2003, Jeff wrote:
> On Mon, 1 Dec 2003 15:47:47 +0530
> "Kamalraj Singh Madhan" <kamalr@networkprograms.com> wrote:
>
> 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 :(
You can take a schema dump and a separate data only dump where the latter
specifies --disable-triggers which should disable the checks when the data
is being added.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-12-01 15:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-01 10:17 Dump restoration via archive files Kamalraj Singh Madhan <kamalr@networkprograms.com>
2003-12-01 14:16 ` Jeff <threshar@torgo.978.org>
2003-12-01 15:23 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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