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
On 11/06/2010 11:24, Ulas Albayrak wrote:
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.
Slony-I (http://www.slony.info) can handle different major releases,
allowing you to switch from one server to another in seconds.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
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
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAkwSOk8ACgkQvJuQZxSWSsgt6QCfYgx6mBibJjNNY88iPBOJNmSL
+FAAoLEVuYUw/VJWg3tRC25VH+ZrNsiH
=yhFJ
-----END PGP SIGNATURE-----
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
Import Notes
Resolved by subject fallback
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 longer software 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 definitely grown a lot in the last years, but historically the Windows version used to have a relatively small user base and was 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 Postgres was 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 speaking higher 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 multiple processes.
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