pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / 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