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(dot)albayrak(at)srt(dot)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(dot)ru(at)gmail(dot)com / Skype: gray-hemp / ICQ: 29353802