From: Robert Treat <xzilla@users.sourceforge.net>
To: pgsql-admin@postgresql.org
Cc: Gabriele Bartolini <g.bartolini@comune.prato.it>
Subject: Re: Advice on migration without down-time
Date: Wed, 11 Apr 2007 21:20:42 -0400
Message-ID: <200704112120.42987.xzilla@users.sourceforge.net> (raw)
In-Reply-To: <910CF843580B3C40A25CD0D04B3908E20B7733@exchange4.comune.prato.local>
References: <910CF843580B3C40A25CD0D04B3908E20B7733@exchange4.comune.prato.local>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: xzilla@users.sourceforge.net, g.bartolini@comune.prato.it
Subject: Re: Advice on migration without down-time
In-Reply-To: <200704112120.42987.xzilla@users.sourceforge.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox