Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 85ED76330BF
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 21 Feb 2009 03:14:56 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 43278-05 for
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	Sat, 21 Feb 2009 03:14:53 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sd-2317.dedibox.fr (sd-2317.dedibox.fr [88.191.17.192])
	by mail.postgresql.org (Postfix) with ESMTP id 7870D632DB0
	for <pgsql-admin@postgresql.org>; Sat, 21 Feb 2009 03:14:53 -0400 (AST)
Received: from [192.168.10.64]
	(ASte-Genev-Bois-153-1-28-131.w83-112.abo.wanadoo.fr [83.112.74.131])
	by sd-2317.dedibox.fr (Postfix) with ESMTPA id 0B3A42CD3;
	Sat, 21 Feb 2009 08:14:50 +0100 (CET)
Message-ID: <499FA9E7.1040109@lelarge.info>
Date: Sat, 21 Feb 2009 08:14:47 +0100
From: Guillaume Lelarge <guillaume@lelarge.info>
User-Agent: Thunderbird 2.0.0.19 (X11/20090105)
MIME-Version: 1.0
To: Tino Schwarze <postgresql@tisc.de>
CC: pgsql-admin@postgresql.org
Subject: Re: Tuning postgres for fast restore?
References: <20090221004219.GA10127@easy2.in-chemnitz.de>
In-Reply-To: <20090221004219.GA10127@easy2.in-chemnitz.de>
X-Enigmail-Version: 0.95.7
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200902/162
X-Sequence-Number: 31383

Tino Schwarze a écrit :
> [...]
> I'm going to pg_restore a database dump of about 220 GiB (uncompressed,
> but most data is BLOBs). The machine has 8 GiB of memory and 8 cores.
> Is there any advice to speed up restoring, postgresql.conf-wise?
> 
> I already have a script which does the data loading and index creation
> in parallel. I'm looking for advice regarding shared_mem, work_mem and
> maintenance_mem - shall I raise them?
> 

You should definitely raise shared_buffers and maintenance_work_mem.

> I currently have:
> shared_buffers = 240MB
> work_mem = 4096               # no units here, d'oh!
> maintenance_work_mem = 150000

When there's no unit, work_mem and maintenance_work_mem are in KB. You
didn't tell us your PostgreSQL release, but shared_buffers can be set at
least to 1GB and maintenance_work_mem to 512MB. You can also raise
checkpoint_segments.

Regards.


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com