X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 09046D1D489 for ; Tue, 10 Feb 2004 14:33:33 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 94014-10 for ; Tue, 10 Feb 2004 10:33:25 -0400 (AST) Received: from bramble.mmrd.com (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id 01102D1D17D for ; Tue, 10 Feb 2004 10:33:21 -0400 (AST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by bramble.mmrd.com (8.12.8/8.12.8) with ESMTP id i1ADxVcM032576; Tue, 10 Feb 2004 08:59:31 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id i1AEXLl15368; Tue, 10 Feb 2004 09:33:21 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id XT88FBBW; Tue, 10 Feb 2004 09:33:19 -0500 Subject: Re: reduce downtime when upgrading 7.3 -> 7.4 From: Robert Treat To: Palle Girgensohn Cc: pgsql-admin@postgresql.org In-Reply-To: <331690000.1076207705@palle.girgensohn.se> References: <331690000.1076207705@palle.girgensohn.se> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 10 Feb 2004 09:33:20 -0500 Message-Id: <1076423601.17917.8.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/130 X-Sequence-Number: 12304 On Sat, 2004-02-07 at 21:35, Palle Girgensohn wrote: > Hi, > > We use postgresql for rather large databases. For a typical installation, a > pg_restore takes a couple of hours, at least (the dumpfiles are usually 2-4 > gigabytes or so, including BLOBs). The machines are expected to be up 24/7, > so this dump/restore procedure makes upgrading unpopular. Is there any > (safe) way to speed this process up? > > If pg_upgrade is not a good idea, how can I speed up pg_restore? Best way > to set things like fsync etc in postgresql.conf? Will it make a big > difference? > yes, setting fsync off should make a significant difference. I usually recommend it cause if there is a machine failure during restore I will want to start the process again anyway. Other items you should probably change are cranking up sort_mem significantly (if your restore is the only process running, let it take up most of the ram on the box) and you can increase check_point segments as well. HTH Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL