Skip site navigation (1) Skip section navigation (2)

Re: Advice on migration without down-time

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Cc: "Gabriele Bartolini" <g(dot)bartolini(at)comune(dot)prato(dot)it>
Subject: Re: Advice on migration without down-time
Date: 2007-04-12 01:20:42
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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

In response to

pgsql-admin by date

Next:From: JoeDate: 2007-04-12 04:07:07
Subject: Re: [SQL] Urgent help in bit_string data type
Previous:From: Robert TreatDate: 2007-04-12 01:12:02
Subject: Re: Restoring single database from an export dump of a cluster

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group