Re: Easy upgrade on Cpanel *without* downtime - Mailing list pgsql-general

On Tue, Aug 26, 2008 at 12:10 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> On 8/26/08, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>>
>>  Slony replication lets postgresql accomplish this, which is really
>>  quite impressive.  We just upgraded from an 8.1 server to an 8.3
>>  server via slony, and it went smooth as silk.  db downtime was
>>  measured in seconds.
>
> Thanks for this Scott. Sounds promising. But where can I find the
> instructions to install Slony, then install new PG 8.3.3, then start
> it with similar CONF settings and stuff, then setup the master and
> slave (which I am not familiar with), and then switch master and slave
> when everything is working?

Problem here is that while each piece of the puzzle probably has it's
own useful instructions, no one has put them all together into one
document, and the reason for that is choice.  With a choice of
versions to be upgrading from and to, options of whether or not you
need to replicate and entire database or not, versions of slony, and
ways to install each version of pgsql on each of those different OSes,
it's hard to make documents that don't start with "This thing was only
test with pgsql v7.4.7 to v8.0.3 with slony 1.2.09 on RHEL 3.

Suddenly you've got a guide that's 3 years old and most of the info in
it is now not the right thing to do if you're upgrading from 8.1.4 to
8.3.3 with slony 1.2.14 and could cause you problems.  I used to write
docs like that for my last company, and within a year or two they're
useless.

> To others who keep telling us that "PG is complex and if you want it
> to be less so then contribute" -- well, sorry I am not that technical.

Then you should find someone in your neighborhood who's been busy
learning pgsql on an intranet and wishing he could build a production
system on it and hire him.  Or something like that.  Or become a
little bit of him.

Note that you can also contribute by whipping out your checkbook and
hiring one of the hackers who regularly work on pgsql to implement
something.  It's how Slony got started.  Thanks Afilias and folks.

> If the intended target audience of PG is only super-techsavvy folk who
> can write C++ patches for every little functionality they need, then
> perhaps I chose the wrong DB? I doubt it.

No, I don't think that's entirely the case.  If you need a corporate
office db to handle a few thousand users a dozen at a time, with
weekend down time and a small to medium database I'm pretty sure my
mom could keep the machine happy.

But if you're running a 24/7 no down time outside of scheduled
maintenance db with high load then you're not going to find something
that just bolts in and works.  In some ways PostgreSQL is actually
pretty close.  But you're gonna have to at least learn how to plug it
into Nagios or something.

If you then need a 365.25 system with no down days, no scheduled
maintenance, you've reached the point where the air is thin and the
cost of doing in business gets high.  Entire applications were written
in the past to migrate living databases.  Slony is one of the slickest
replication tricks I've ever seen when it comes to the ability to
upgrade in place.  You do it once every 1 to 4 years, so it takes
planning and testing.

Build a test environment, using Xen if you don't have enough physical
machines and need virtual ones, and make sure you understand what's
happening and catch your simple mistakes.  Then backup production and
try it there.  But testing is key.

> It would be really nice if the PG official community can have some
> simple instructions to make a seamless upgrade, if no simpler patches
> exist.

You're as official as me or anybody else.

> At the very least the instructions will help us plentiful folk
> who do NOT use PG in the exalted "enterprise" setting, but to run busy
> websites.

Well, if you can't  be down for a saturday morning from 0000hrs to
0800hrs then you are indeed in the exalted enterprise setting.

> This is how MySQL became big too, by being convenient and
> reliable (until recently anyway), but I see no point in that
> discussion.

Some could argue they're a victim of their own success.  They were
stuck supporting old version for far too long, and spent a lot of
energy trying to make things easy, but many of those things, like
replication, aren't actually guaranteed to work.  Add in the system
catalogs are stored in myisam table and your whole replicated innodb
based system could get corrupted because it was replicating DDL when
the power went out.  Slony is built, like the rest of pgsql, to
survive the power going out.

> Anyhow, it would be really nice to have simple instructions. Searching
> on Google for words like "Slony Postgresql upgrade" or "install slony
> with postgresql 8.3" returns stuff that makes a lot of presumptions!

Well, throw at us what you got and what you got questions about.

> I have a CentOS 4 with Cpanel/WHM running. PG is in the usual place:

I'm running Centos 5.  Using yum / rpm to update packages, admin from
the cmd line.

Here's a major issue with Centos and redhat derivatives: Their
packaging system can't directly support two versions of pgsql on the
same machine.

So, if you're running Centos, you have two choices, have two choices.
Use two db servers, or compile at least one version of pgsql from
source.  When I ran RHEL4 I had two machines, but I still built pgsql
from source because this was right before slony was available as rpm
for my pgsql / rhel version.  Also had to build php from source cause
we Oracle.  So I figured I'd just build them all from source.  Worked
fine, and with --prefix I put them into pg73 pg81 pg82 directories
and compiled them all against each other.

But with my current production setup, I can take the backup
replication server offline, upgrade pgsql, and replicate from the
older version to the newer.  Then switch, take the old master / new
slave offline, upgrade it, replicate, switch, tada!  I'm done.  I
still build slony from source on Centos, but nothing else.

You could do the same thing with ubuntu or debian, but without any
compiling and on one machine, since debian and it's children support >
1 version of pgsql on the same machine and have slony packages for
them too.  If you want pgsql 8.3 AND slony for it you'll need ubuntu
8.04.01.  But I don't trust that OS in production yet.

>  > whereis pgsql
> pgsql: /usr/lib/pgsql /usr/include/pgsql /usr/share/pgsq
>
> Now how can I install Slony so that it install PGSQL and allows me to
> continue working with Apache/PHP for my website? I am reading this --
> http://slony.info/documentation/installation.html -- but while it
> textually mentions the stuff in the writeup, I don't see full
> instructions to install Slony, then new PGSQL, then switching, and so
> on.

It's very distro dependent.  It's not really "our" job how you have to
install on your OS.  It's your OSes job.  With Centos you'll either be
looking for the proper slony rpms, if available, or building from
source.  If you're building slony from source, you'll need the -devel
packages for pgsql.  php won't be affected.