Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 1316F9FB48B for ; Wed, 11 Apr 2007 22:21:18 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 55854-09 for ; Wed, 11 Apr 2007 22:21:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from vms046pub.verizon.net (vms046pub.verizon.net [206.46.252.46]) by postgresql.org (Postfix) with ESMTP id 1EBBD9FB2C1 for ; Wed, 11 Apr 2007 22:21:15 -0300 (ADT) Received: from new-host.home ([71.166.59.121]) by vms046.mailsrvcs.net (Sun Java System Messaging Server 6.2-6.01 (built Apr 3 2006)) with ESMTPA id <0JGD006Y92F6TOW2@vms046.mailsrvcs.net> for pgsql-admin@postgresql.org; Wed, 11 Apr 2007 20:21:06 -0500 (CDT) Date: Wed, 11 Apr 2007 21:20:42 -0400 From: Robert Treat Subject: Re: Advice on migration without down-time In-reply-to: <910CF843580B3C40A25CD0D04B3908E20B7733@exchange4.comune.prato.local> To: pgsql-admin@postgresql.org Cc: "Gabriele Bartolini" Message-id: <200704112120.42987.xzilla@users.sourceforge.net> MIME-version: 1.0 Content-type: text/plain; charset=iso-8859-15 Content-transfer-encoding: 7bit Content-disposition: inline References: <910CF843580B3C40A25CD0D04B3908E20B7733@exchange4.comune.prato.local> User-Agent: KMail/1.9.1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/110 X-Sequence-Number: 25174 On Thursday 05 April 2007 07:59, Gabriele Bartolini wrote: > Hi guys, > > due to the wrong initialisation of PostgreSQL data dir (which > generates 'invalid multibyte character for locale'), I need to migrate > approximately a 90GB database system on PostgreSQL 8.2.3. There is no > replication system set up, yet. > > The rawest solution is to: > - stop the server from updates, > - perform a backup with pg_dumpall (the daily bzipped dump all file is > approximately 4GB) > - isolate the server (no incoming TCP connection allowed) > - stop the server > - rename the data dir and the tablespace directories > - reinit the data dir with the correct locale > - create the empty tablespace directories with proper rights for the > postgres user > - restore the whole backup > - restart the server > > Of course, this solution is time consuming, given the quantity of > data to be restored (90GB). > Yes... one thing to note though... you seem to imply you can set the server into a read only mode (one step is to stop the server from updates and another to disallow tcp/ip). If you can do this, then it is very viable that you can do the whole dump/restore while the site is running and then just have an outage while you do a shutdown / cutover to the new server. > I was wondering if you have ideas to propose. I imagine using > replication (Slony-I?) could be a viable option, assuming that the > different locale for the data dir of the two database servers does not > influence the results. Using a slave DBMS could limit the down-time. > Using slony is another option, though if your unfamiliar it certainly comes with more complexity than the first solution. -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL