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 B8B16D1D851 for ; Sun, 8 Feb 2004 02:35:06 +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 16030-05 for ; Sat, 7 Feb 2004 22:35:08 -0400 (AST) Received: from amsfep14-int.chello.nl (amsfep14-int.chello.nl [213.46.243.22]) by svr1.postgresql.org (Postfix) with ESMTP id 9D6BDD1D5B0 for ; Sat, 7 Feb 2004 22:35:03 -0400 (AST) Received: from palle.girgensohn.se ([213.89.137.38]) by amsfep14-int.chello.nl (InterMail vM.6.00.05.02 201-2115-109-103-20031105) with ESMTP id <20040208023507.WECN18174.amsfep14-int.chello.nl@palle.girgensohn.se>; Sun, 8 Feb 2004 03:35:07 +0100 Received: from localhost (localhost [127.0.0.1]) by palle.girgensohn.se (8.12.9p2/8.12.9) with ESMTP id i182Z5QY061204; Sun, 8 Feb 2004 03:35:06 +0100 (CET) (envelope-from girgen@pingpong.net) Date: Sun, 08 Feb 2004 03:35:05 +0100 From: Palle Girgensohn To: pgsql-admin@postgresql.org Subject: reduce downtime when upgrading 7.3 -> 7.4 Message-ID: <331690000.1076207705@palle.girgensohn.se> X-Mailer: Mulberry/3.1.0 (Linux/x86) MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/114 X-Sequence-Number: 12288 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? The most obvious question is, can we use pg_upgrade from contrib? It seems not to have been updated since 7.3, and is generally documented as untested. What kind of problems can we get, can they be tested for in a testbed in advance? 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? We use FreeBSD-4.9 and want to upgrade from 7.3.4 -> 7.4.1. Thanks, Palle