On Tue, Aug 26, 2008 at 12:10 AM, Phoenix Kiula <phoenix.kiula@xxxxxxxxx> wrote: > On 8/26/08, Scott Marlowe <scott.marlowe@xxxxxxxxx> 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.
