pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Advice on migration without down-time
2+ messages / 2 participants
[nested] [flat]

* Advice on migration without down-time
@ 2007-04-05 11:59  Gabriele Bartolini <g.bartolini@comune.prato.it>
  0 siblings, 1 reply; 2+ messages in thread

From: Gabriele Bartolini @ 2007-04-05 11:59 UTC (permalink / raw)
  To: pgsql-admin

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).
 
   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.
 
   Thank you for your attention.
 
Cheers,
Gabriele
--
Gabriele Bartolini - Istruttore Informatico - Comune di Prato
Sistema Informativo - Servizi di E-Government e Open-Source
g.bartolini@comune.prato.it - www.comune.prato.it
<http://www.comune.prato.it/;  - www.htminer.it <http://www.htminer.it/;

 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Advice on migration without down-time
@ 2007-04-12 01:20  Robert Treat <xzilla@users.sourceforge.net>
  parent: Gabriele Bartolini <g.bartolini@comune.prato.it>
  0 siblings, 0 replies; 2+ messages in thread

From: Robert Treat @ 2007-04-12 01:20 UTC (permalink / raw)
  To: pgsql-admin; +Cc: Gabriele Bartolini <g.bartolini@comune.prato.it>

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2007-04-12 01:20 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-05 11:59 Advice on migration without down-time Gabriele Bartolini <g.bartolini@comune.prato.it>
2007-04-12 01:20 ` Robert Treat <xzilla@users.sourceforge.net>

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