Обсуждение: Moving a live production database to different server and postgres release
Hi,
I’m in the process of moving our production database to a different physical server, running a different OS and a newer release of postgreSQL. My problem is that I’m not really sure how to go about it.
My initial idea was to use WAL archiving to reproduce the db on the new server and then get it up to date with the logs from the time of base backup creation to the time the new server can get up. That was until I found out WAL archiving doesn’t work between major postgreSQL releases.
I can’t make a simple pg_dump – pg_restore and then redirect traffic when the new server is up either, because during that time new data will have been inserted in the original db.
My best idea so far is to do a pg_dump and somehow archive all the DML in the original db from that point in time for later insertion in the new db, but I don’t know how that would be done practically. And I don’t even know if that’s the best way to go, as I said, it’s only an idea.
If anyone can give me some ideas on this, I’d be much obliged.
Best Regards /Ulas
Re: Moving a live production database to different server and postgres release
Hi, Use one of the existent replication systems http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling#Replication p.s. I would highlight Slony, Londiste and Bucardo. On 11 June 2010 14:11, Ulas Albayrak <ulas.albayrak@srt.se> wrote: > Hi, > > > > I’m in the process of moving our production database to a different physical > server, running a different OS and a newer release of postgreSQL. My problem > is that I’m not really sure how to go about it. > > > > My initial idea was to use WAL archiving to reproduce the db on the new > server and then get it up to date with the logs from the time of base backup > creation to the time the new server can get up. That was until I found out > WAL archiving doesn’t work between major postgreSQL releases. > > > > I can’t make a simple pg_dump – pg_restore and then redirect traffic when > the new server is up either, because during that time new data will have > been inserted in the original db. > > > > My best idea so far is to do a pg_dump and somehow archive all the DML in > the original db from that point in time for later insertion in the new db, > but I don’t know how that would be done practically. And I don’t even know > if that’s the best way to go, as I said, it’s only an idea. > > > > If anyone can give me some ideas on this, I’d be much obliged. > > > > Best Regards /Ulas -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp / ICQ: 29353802