Thread: 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

On 14/06/2010 14:09, Ulas Albayrak wrote:

> 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?

AIUI, the Slony-I 2.x branch requires PG 8.3+, but the 1.x branch ill
work perfectly fine on 8.2 and lower.

This is because 1.x messes with the system catalogues and strange ways,
whereas changes in PG 8.3 made this unnecessary.

HTH,

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

OK,

I see. Does this mean I need to install Slony-I 1.x on both systems or
is different versions of Slony-I, say a 2.x and a 1.x, compatible? The
reason I'm asking is because the new server db will be part of a
permanent postgres replication system in the future and installing a
newer verision of Slony-I would spare me some future work.

/Regards

On Mon, Jun 14, 2010 at 3:15 PM, Raymond O'Donnell <rod@iol.ie> wrote:
> On 14/06/2010 14:09, Ulas Albayrak wrote:
>
>> 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?
>
> AIUI, the Slony-I 2.x branch requires PG 8.3+, but the 1.x branch ill
> work perfectly fine on 8.2 and lower.
>
> This is because 1.x messes with the system catalogues and strange ways,
> whereas changes in PG 8.3 made this unnecessary.
>
> HTH,
>
> Ray.
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie
>



--
Ulas Albayrak
ulas.albayrak@gmail.com

On 14/06/2010 14:33, Ulas Albayrak wrote:
> OK,
>
> I see. Does this mean I need to install Slony-I 1.x on both systems or
> is different versions of Slony-I, say a 2.x and a 1.x, compatible? The
> reason I'm asking is because the new server db will be part of a
> permanent postgres replication system in the future and installing a
> newer verision of Slony-I would spare me some future work.

Yes, I'm afraid the versions of Slony have to be identical across the
replicated systems.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


...
> 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?

The daemon itself cannot run on a Windows system, but it's perfectly fine
for any of the databases it is replicating to be Windows. So in this case
you'd just install Bucardo on the BSD machines, tell it about the Windows
box, setup the tables, kick it off in onetimecopy mode, then do the final
transfer once the initial copying is done.

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201006141048
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkwWQUAACgkQvJuQZxSWSsjMiQCcDo9QJDdeZKziEBFBeR0yyb9M
ATkAoJO1dJkkeADnDcPLTtUkcGUF8tRF
=EF+J
-----END PGP SIGNATURE-----

On Mon, Jun 14, 2010 at 7:33 AM, Ulas Albayrak <ulas.albayrak@gmail.com> wrote:
> OK,
>
> I see. Does this mean I need to install Slony-I 1.x on both systems or
> is different versions of Slony-I, say a 2.x and a 1.x, compatible? The
> reason I'm asking is because the new server db will be part of a
> permanent postgres replication system in the future and installing a
> newer verision of Slony-I would spare me some future work.

Slony I 2.x is still not quite ready for production anyway (but it's
getting close) so you're better off with Slony I 1.2.x anyway.  If the
older version of pgsql needs an older slony, then you'd have to go
back to Slony I 1.0.x or 1.1.x or whatever to migrate, then you could
upgrade to Slony I 1.2.latest.

We do all our migrations / upgrades with slony I 1.2.x and it works
like a champ, with total downtime measured in seconds or minutes for
the switchover.

On Mon, Jun 14, 2010 at 7:09 AM, Ulas Albayrak <ulas.albayrak@gmail.com> wrote:
> 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.

Is there are good reason to go to Windows instead of a new BSD system?
 Windows is a known mediocre performer for postgres.

BTW the slony versions need to match down to the minor rev number.

On 14 Jun 2010, at 22:22, Scott Marlowe wrote:
> Is there are good reason to go to Windows instead of a new BSD system?
> Windows is a known mediocre performer for postgres.


I was wondering that too. I assume the good reasons wear ties.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c16a94f286219093520888!

Unfortunately, the switch to Windows is out of my hands. If it were up
to me I'd stick with BSD. When you say postgres on Windows is known
for its "mediocre performance", do you mean it's slower or buggy? Or
both?

/Ulas

On Mon, Jun 14, 2010 at 10:22 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Jun 14, 2010 at 7:09 AM, Ulas Albayrak <ulas.albayrak@gmail.com> wrote:
>> 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.
>
> Is there are good reason to go to Windows instead of a new BSD system?
>  Windows is a known mediocre performer for postgres.
>
> BTW the slony versions need to match down to the minor rev number.
>



--
Ulas Albayrak
ulas.albayrak@gmail.com

Le 15/06/2010 09:18, Ulas Albayrak a écrit :
> Unfortunately, the switch to Windows is out of my hands. If it were up
> to me I'd stick with BSD. When you say postgres on Windows is known
> for its "mediocre performance", do you mean it's slower or buggy? Or
> both?
>

Slower. If it were buggy, it would be fixed.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

On 15 Jun 2010, at 9:21, Guillaume Lelarge wrote:

> Le 15/06/2010 09:18, Ulas Albayrak a écrit :
>> Unfortunately, the switch to Windows is out of my hands. If it were up
>> to me I'd stick with BSD. When you say postgres on Windows is known
>> for its "mediocre performance", do you mean it's slower or buggy? Or
>> both?
>>
>
> Slower. If it were buggy, it would be fixed.


Yes, bugs do get fixed, but bugs that haven't been fixed yet are still bugs.

Why I'm saying this is that Postgres on Windows is relatively new compared to other operating systems. In general, the
longersoftware exists and the more users it has on a certain operating system, the more bugs get fixed. 

Now I don't know the usage numbers on the different operating systems, the number of implementations on Windows has
definitelygrown a lot in the last years, but historically the Windows version used to have a relatively small user base
andwas therefore more likely to contain bugs. 

That aside, I think Windows is the only supported OS that has API's that differ a lot from the usual API's that
Postgreswas developed for. Therefore the Windows version has its own set of potential problems. 

I'm not saying that Postgres on Windows is buggy, I'm just saying that the chances you run into one are relatively
speakinghigher on Windows than on other operating systems. If you check the archives you won't see a lot of bug reports
though,no matter what OS people are using. 

And yes, it's slower on Windows. IIRC that's because Windows isn't very good at multi-processing and Postgres runs as
multipleprocesses. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4c17598b286211489720513!

Ulas Albayrak wrote:
> Unfortunately, the switch to Windows is out of my hands. If it were up
> to me I'd stick with BSD. When you say postgres on Windows is known
> for its "mediocre performance", do you mean it's slower or buggy? Or
> both?
>

Three examples that have varying proportions of slow and buggy in them:

-Without risky registry hacking, Windows systems won't allow more than
about 125 connections to the server at a time if you're using the
standard service infrastructure to manage the server.  See the last
entry at
http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows
for details.  If you need more clients than that connecting to the
database, you'll need to either tweak the registry, run it outside of
the services model, or put a connection pooler between the clients and
the database.

-UNIX systems normally allow giving the database up to several gigabytes
of RAM for its direct utilization.  Windows installs have to be kept in
the 128MB - 512MB range because they get unexpectedly slower when the
database has more memory than that.

-Anti-virus software installed on Windows servers has to be very
carefully screened for compatibility with the database, with really
random sorts of problems popping up when you have a bad combination.
Any time you let your AV software get updated, you're potentially
exposed to the database becoming unreliable afterwards.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us