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 A4AA4D1CACB for ; Sun, 8 Feb 2004 04:45: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 42769-04 for ; Sun, 8 Feb 2004 00:45:31 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 2C72AD1C995 for ; Sun, 8 Feb 2004 00:45:31 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i184jO1Y034661 for ; Sun, 8 Feb 2004 04:45:24 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i184aT5w033696 for pgsql-admin@postgresql.org; Sun, 8 Feb 2004 04:36:29 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.admin Subject: Re: reduce downtime when upgrading 7.3 -> 7.4 Date: Sat, 07 Feb 2004 23:30:59 -0500 Organization: cbbrowne Computing Inc Lines: 75 Message-ID: References: <331690000.1076207705@palle.girgensohn.se> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:Kp/xlsk/lrYeRHuyQ45Nzg+l9VI= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/176 X-Sequence-Number: 12350 Martha Stewart called it a Good Thing when girgen@pingpong.net (Palle Girgensohn) wrote: > 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. A "faster" method would be to use one of the replication systems, such as ERserv. You have your existing database, running 7.3.4, and set up another DB instance (perhaps on the same box) running 7.4.1. You replicate the 7.3 DB over to the 7.4 one. It may take a substantial period of time to get them in near sync, but once you get them close, you can disconnect the application that is injecting updates to the 7.3 DB, and it should take mere minutes to get those updates into the 7.4 system. You then shut down the 7.3 system, shift the 7.4 one to the ports your application expects to use, and voila! You did it all with a mere 10 minute outage. You may need a few minutes to add back integrity constraints that the replication system required you to drop (because it may not order inserts in the exact same order that they went into place in the origin system). All that being said, ERserv may not cope perfectly with BLOBs, so you may need to do something special about that. But the above approach, while it has aspects that are fragile, can certainly cut down "down time" REALLY substantially. The other major approach that would provide something equivalent is the "Holy Grail" of PITR, which some people are thinking of working on now. That would be a more universal scheme which would be logically quite similar: -> You start a pg_dump to send to the 7.5 system, and start archiving WAL files. -> You load the pg_dump into the 7.5 system. -> You then move over the WAL files, and replay them into the 7.5 system. (Big magic occurs here!) -> You shut down the 7.3 system, copy the last WAL files over, and and load them into 7.5. And voila! You have a 7.5 database that started with the contents of the pg_dump, and then had all of the subsequent transactions applied to it, bringing it up to date. The Big Magic part is of the need to load 7.3 WAL data into a 7.5 system. If anything about data format changes, something fancy has to happen read it in properly. I wrote 7.5 rather than 7.4 because this is certainly not something that will be ready for a 7.4 release. If you need something sooner, then you'll need to look into the existing replication solutions. -- output = ("cbbrowne" "@" "ntlug.org") http://cbbrowne.com/info/rdbms.html If con is the opposite of pro, is Congress the opposite of progress?