Обсуждение: Replication for migration
Hi list! I've to do a migration of postgresql from version 8.2 to 8.3 in one server. The problem I see here is, to avoid data loss (people still using the web site that uses postgres), I'll need to stop postgres 8.2, make a pg_dumpall, and then restore that information in 8.3. Taking some considerations, like database size, server, etc. I estimate that it will be at least 30 minutes of downtime, and that's pretty much time. I've thinking in the possibility of doing some kind of master/slave with both versions (I must change the default port in one of them, as everything must happen on a single machine), and find some useful links like: http://wiki.postgresql.org/wiki/Replication%2C_Clustering% 2C_and_Connection_Pooling http://momjian.us/main/writings/pgsql/replication.pdf I'd love to read some experiences or advices, since I haven't done it before. Also if you have some link o document that you think I must read, it'll be welcomed. Thanks to all. Martín
On Thu, May 14, 2009 at 10:57:26AM -0300, Martin Spinassi wrote: > Hi list! > > I've to do a migration of postgresql from version 8.2 to 8.3 in one > server. The problem I see here is, to avoid data loss (people still > using the web site that uses postgres), I'll need to stop postgres 8.2, > make a pg_dumpall, and then restore that information in 8.3. > Taking some considerations, like database size, server, etc. I estimate > that it will be at least 30 minutes of downtime, and that's pretty much > time. > > I've thinking in the possibility of doing some kind of master/slave with > both versions (I must change the default port in one of them, as > everything must happen on a single machine), and find some useful links > like: > > http://wiki.postgresql.org/wiki/Replication%2C_Clustering% > 2C_and_Connection_Pooling > http://momjian.us/main/writings/pgsql/replication.pdf > > > I'd love to read some experiences or advices, since I haven't done it > before. Also if you have some link o document that you think I must > read, it'll be welcomed. > > > Thanks to all. > > > Mart??n > I used Slony replication to upgrade a database from 8.2 -> 8.3 and it worked quite well. You will need an outage to change the master to the 8.3 database and re-point your apps, but if scripted, that is minimal. Cheers, Ken
On Thu, 2009-05-14 at 08:59 -0500, Kenneth Marshall wrote: > On Thu, May 14, 2009 at 10:57:26AM -0300, Martin Spinassi wrote: > > Hi list! > > [snip] > > > > I'd love to read some experiences or advices, since I haven't done it > > before. Also if you have some link o document that you think I must > > read, it'll be welcomed. > > > > > > Thanks to all. > > > > > > Mart??n > > > > I used Slony replication to upgrade a database from 8.2 -> 8.3 and > it worked quite well. You will need an outage to change the master > to the 8.3 database and re-point your apps, but if scripted, that > is minimal. > > Cheers, > Ken Thanks for the advice Ken. I've seen that slony documentation is huge! I'll give it a shot in testing environment. Cheers Martin
Hi, Martin Spinassi <martins.listz@gmail.com> writes: > On Thu, 2009-05-14 at 08:59 -0500, Kenneth Marshall wrote: >> I used Slony replication to upgrade a database from 8.2 -> 8.3 and >> it worked quite well. You will need an outage to change the master >> to the 8.3 database and re-point your apps, but if scripted, that >> is minimal. > > I've seen that slony documentation is huge! I'll give it a shot in > testing environment. The skytools project's documentation is an easy read ;) http://wiki.postgresql.org/wiki/Londiste_Tutorial Londiste in its current stable version (2.1.9) doesn't support failover, but as long as you're migrating away from 8.2 I suppose you don't need to be able to switch back. The procedure would then be: - setup londiste and replicate data - enter site maintenance mode (no more writes on the database) - wait for the londiste logs to show there's nothing to replicate (while waiting, maybe setup the site to point to 8.3) - open up the site again, on 8.3 - shut down the londiste daemons and the 8.2 pg server The waiting for emptying queues shouldn't be very long, expect 3s to 30s, but be sure to test it before switching for real, of course. Regards, -- dim
On Thu, May 14, 2009 at 7:59 AM, Kenneth Marshall <ktm@rice.edu> wrote: > On Thu, May 14, 2009 at 10:57:26AM -0300, Martin Spinassi wrote: >> Hi list! >> >> I've to do a migration of postgresql from version 8.2 to 8.3 in one >> server. The problem I see here is, to avoid data loss (people still >> using the web site that uses postgres), I'll need to stop postgres 8.2, >> make a pg_dumpall, and then restore that information in 8.3. >> Taking some considerations, like database size, server, etc. I estimate >> that it will be at least 30 minutes of downtime, and that's pretty much >> time. >> >> I've thinking in the possibility of doing some kind of master/slave with >> both versions (I must change the default port in one of them, as >> everything must happen on a single machine), and find some useful links >> like: >> >> http://wiki.postgresql.org/wiki/Replication%2C_Clustering% >> 2C_and_Connection_Pooling >> http://momjian.us/main/writings/pgsql/replication.pdf >> >> >> I'd love to read some experiences or advices, since I haven't done it >> before. Also if you have some link o document that you think I must >> read, it'll be welcomed. >> >> >> Thanks to all. >> >> >> Mart??n >> > > I used Slony replication to upgrade a database from 8.2 -> 8.3 and > it worked quite well. You will need an outage to change the master > to the 8.3 database and re-point your apps, but if scripted, that > is minimal. Where I work we use slony as our standard replication platform, and we used it to migrate from 8.1 to 8.3. Went smooth as silk. We had about 30 seconds of downtime on a Saturday night, our slowest time.