>>>>> "RC" == Rodrigo Carvalhaes <grupos@no-spam> writes:
RC> Hi!
RC> I am using PostgreSQL with a proprietary ERP software in Brazil. The
RC> database have around 1.600 tables (each one with +/- 50 columns).
RC> My problem now is the time that takes to restore a dump. My customer
RC> database have arount 500mb (on the disk, not the dump file) and I am
RC> making the dump with pg_dump -Fc, my dumped file have 30mb. To make
RC> the dump, it's taking +/- 1,5 hours BUT to restore (using pg_restore )
RC> it it takes 4 - 5 hours!!!
I regularly dump a db that is compressed at over 2Gb. Last time I did
a restore on the production box it took about 3 hours. Restoring it
into a development box with a SATA RAID0 config takes like 7 hours or
so.
The biggest improvement in speed to restore time I have discovered is
to increase the checkpoint segments. I bump mine to about 50. And
moving the pg_xlog to a separate physical disk helps a lot there, too.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@no-spam Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Josh Berkus <josh@no-spam> writes:
> Not as much, but it's still a good idea to serialize the load. With too few
> segments, you get a pattern like:
> Fill up segments
> Write to database
> Recycle segments
> Fill up segments
> Write to database
> Recycle segments
> etc.
Actually I think the problem is specifically that you get checkpoints
too often if either checkpoint_timeout or checkpoint_segments is too
small. A checkpoint is expensive both directly (the I/O it causes)
and indirectly (because the first update of a particular data page
after a checkpoint causes the whole page to be logged in WAL). So
keeping them spread well apart is a Good Thing, as long as you
understand that a wider checkpoint spacing implies a longer time to
recover if you do suffer a crash.
I think 8.0's bgwriter will considerably reduce the direct cost of
a checkpoint (since not so many pages will be dirty when the checkpoint
happens) but it won't do a thing for the indirect cost.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@no-spam so that your
message can get through to the mailing list cleanly
Hi,
Sorry, I didn't catch the original message, so I'm not sure if the original
poster mentioned the postgres version that he's using.
I just thought that I'd contribute this observation.
I have a DB that takes several hours to restore under 7,1 but completes in
around 10 minutes on 7.4. The main reason for this is that by default the
7.4 restore delays creation of PKs and indexes until after the data load,
whereas 7.1 doesn't.
I noticed that 7.1 has a re-arrange option that reportedly delays the pks
and indexes, so presumably this would have alleviated the problem.
I also noticed that a dumpfile created under 7.1 took hours to restore using
7.4 to load it as the order remained in the default of 7.1.
I don'tknow when the default behaviour changed, but I get the feeling it may
have been with 7.4.
HTH
Iain
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@no-spam so that your
message can get through to the mailing list cleanly