From: PFC <lists@peufeu.com>
To: Jesper Krogh <jesper@krogh.cc>
To: pgsql-performance@postgresql.org
Subject: Re: Restore performance?
Date: Mon, 10 Apr 2006 19:20:33 +0200
Message-ID: <op.s7sygjtecigqcu@apollo13> (raw)
In-Reply-To: <e1d31l$2nt$1@sea.gmane.org>
References: <e1d31l$2nt$1@sea.gmane.org>
> I'd run pg_dump | gzip > sqldump.gz on the old system.
If the source and destination databases are on different machines, you
can pipe pg_dump on the source machine to pg_restore on the destination
machine by using netcat.
If you only have 100 Mbps ethernet, compressing the data will be faster.
If you have Gb Ethernet, maybe you don't need to compress, but it doesn't
hurt to test.
use pg_restore instead of psql, and use a recent version of pg_dump which
can generate dumps in the latest format.
If you need fast compression, use gzip -1 or even lzop, which is
incredibly fast.
Turn off fsync during the restore and set maintenance_work_mem to use
most of your available RAM for index creation.
I think that creating foreign key constraints uses large joins ; it might
be good to up work_mem also.
Check the speed of your disks with dd beforehand. You might get a
surprise.
Maybe you can also play with the bgwriter and checkpoint parameters.
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: lists@peufeu.com, jesper@krogh.cc
Subject: Re: Restore performance?
In-Reply-To: <op.s7sygjtecigqcu@apollo13>
* 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