Re: Moving a live production database to different server and postgres release

The database is < 10GB and currently on a postgres version 8.2.15 on a
BSD system and moving to postgres version 8.4.4 on a windows 2008
server. The adding of data is continuous but in small quantities,
totaling at about 20MB a day.

After your post I had a look at Slony-I, which, according to it web
page, requires postgres 8.3 or later, so I guess that won't work. That
leaves Bucardo: will it work on a Windows system?

/Regards


> > 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 dont know how that would be done practically. And I
> > dont even know if thats the best way to go, as I said, its only an
> > idea.
>
> What you need is a replication system. Take a look at Slony or Bucardo.
> Basically, you copy over everything except for the data to the new
> database, switch the replication system on, let it catch up, then
> stop apps from hitting the server, wait for the new one to catch up,
> and point your apps to the new one.
>
> Important factors that you left out are exactly how big your database is,
> what version you are on, what version you are moving to, and how busy your
> system is. Also keep in mind that both Bucardo and Slony are trigger based
> on primary keys or unique indexes, so tables without such constraints
> cannot be replicated: you'll need to either add a unique constraint to
> the tables, or copy them separately (e.g. pg_dump -t tablename or
> Bucardo's fullcopy mode).
>
> If you weren't also moving your OS and server, pg_migrator (aka pg_upgrade)
> might work for you as well: it does an inplace, one-time upgrade but only
> supports a limited number of versions at the moment.
>
> - --
> Greg Sabino Mullane greg@turnstep.com
> End Point Corporation http://www.endpoint.com/
> PGP Key: 0x14964AC8 201006110927
> http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8


--
Ulas Albayrak
ulas.albayrak@gmail.com