Easy upgrade on Cpanel *without* downtime

Hi,

Phoenix Kiula wrote:

Hi.

I have googled and googled for good, simple instructions to upgrade
from 8.2.3 to 8.3.3 (latest stable at this time?)

I am on a Cpanel interface. Use Apache and PHP for most of my websites.

This seems to be the most often quoted resource on forums etc:

http://kb.linuxnetworkcare.com/node/21

But this suggests backing up, then UNinstalling (downtime and loss of
settings!), then REinstalling postgresql.

Is there no sensible way of simply upgrading the database engine
without affecting either the uptime or the data itself?

I really really do not wish to backup gigabytes worth of data and then
reupload it back into the DB. This represents a downtime of *at least*
an hour or so, which our busy website doesn't have.

Any thoughts or pointers?

The usual way to do it and to avoid downtime is to install
the new version along the old one, backup/restore as usual
(e.g. pg_dump | pg_restore to avoid wasting space 2 times)
then run slony to permanently update the last bits
while you are checking settings and performance of the new
version. If everything is fine, use a calm moment to switch
your configuration to use the new database in production.

Regards
Tino

Normally we don't use the packages from the distributions. They're
mostly outdated and installation ov two or more versions of Postgres
isn't that easy normally in this case. We've a directory structure which
allows
us to install and startup two or more versions in parallel. I haven't
tried this
but if you have two instances running (one 8.2 and the thoer 8.3)
you can try to install Slony migrating all the data through replication.
When all data is synchronized a shot downtime is needed to switch
between the two versions. Maybe this is a way to go...

Robert

Phoenix Kiula wrote:

On Mon, Aug 25, 2008 at 11:34:00AM +0200, Tino Wildenhain wrote:

(e.g. pg_dump | pg_restore to avoid wasting space 2 times)
then run slony to permanently update the last bits

If you're going to run slony, then the pg_dump|pg_restore step is
completely wasted. Slony will restore all the data again. You do
need to run pg_dump -s, of course.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

Andrew Sullivan wrote:

On Mon, Aug 25, 2008 at 11:34:00AM +0200, Tino Wildenhain wrote:

(e.g. pg_dump | pg_restore to avoid wasting space 2 times)
then run slony to permanently update the last bits

If you're going to run slony, then the pg_dump|pg_restore step is
completely wasted. Slony will restore all the data again. You do
need to run pg_dump -s, of course.

good point :-)

Regards
Tino

If you're going to run slony, then the pg_dump|pg_restore step is
completely wasted. Slony will restore all the data again. You do
need to run pg_dump -s, of course.

Thanks to everyone who replied. We have no experience with this
"Slony". Any simple instructions on installing it and getting it
working?

Why is PG so complex! It's a such a lovely database. If only at least
the installation and upgrade could be made more humane..

On Tue, 26 Aug 2008 09:25:09 +0800
"Phoenix Kiula" <phoenix.kiula@gmail.com> wrote:

"Slony". Any simple instructions on installing it and getting it
working?

Why is PG so complex! It's a such a lovely database. If only at least
the installation and upgrade could be made more humane..

Well the installation is about as easy as it gets. I grant you we are
in the stone age when it comes to upgrading. We are however welcoming
patches should you feel the need to create and or sponsor :)

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

On Mon, Aug 25, 2008 at 7:25 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

If you're going to run slony, then the pg_dump|pg_restore step is
completely wasted. Slony will restore all the data again. You do
need to run pg_dump -s, of course.

Thanks to everyone who replied. We have no experience with this
"Slony". Any simple instructions on installing it and getting it
working?

Why is PG so complex! It's a such a lovely database. If only at least
the installation and upgrade could be made more humane..

Any database that has real ACID guarantees is going to be complex.
Some more than others. I think that upgrading postgresql in place is
a LOT of work and not the highest priority, and slony lets you upgrade
LIVE. Note that you cannot upgrade an Oracle installation from 9g to
10i live. You take it offline from the app, migrate your data, and
start up the new database.

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.

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?

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

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

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!

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

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.

So many thanks for any help anyone can provide! Or point me to some
resource that exists but is hiding from Google.

On 8/26/08, 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?

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

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

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!

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

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.

So many thanks for any help anyone can provide! Or point me to some
resource that exists but is hiding from Google.

See, this is where I get confused. I want to upgrade from 8.2.3 to
8.3.3. The recommendation is to try Slony. So I download Slony and try
to configure it. The configure command gives me this:

----
checking for correct version of PostgreSQL... "error"
configure: error: Your version of PostgreSQL (8.2) is lower
than the required 8.3. Slony-I needs functionality included in
a newer version.
----

Well, if Slony needs a newer version, then how can it be used to upgrade?!

Phoenix Kiula wrote:

On 8/26/08, 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?

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

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

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!

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

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.

So many thanks for any help anyone can provide! Or point me to some
resource that exists but is hiding from Google.

See, this is where I get confused. I want to upgrade from 8.2.3 to
8.3.3. The recommendation is to try Slony. So I download Slony and try
to configure it. The configure command gives me this:

----
checking for correct version of PostgreSQL... "error"
configure: error: Your version of PostgreSQL (8.2) is lower
than the required 8.3. Slony-I needs functionality included in
a newer version.
----

Well, if Slony needs a newer version, then how can it be used to upgrade?!

It looks like you have downloaded Slony 2.0.0RC1, which has in it's
release notes "It drops all support for databases prior to Postgres
version 8.3. ". It's also just a release candidate, not an actual release.

You need to download Slony 1.2.14, which supports both 8.2 and 8.3.
You'll find it under Quick downloads.

//Magnus

You need to download Slony 1.2.14, which supports both 8.2 and 8.3.
You'll find it under Quick downloads.

Ok done. Slony is installed. Now what? How should I install a new
database which is 8.3.3?

I was also told that the postgresql.conf settings across 8.2 and 8.3
are different so I cannot (or should not) use the same old
postgresql.conf for the new database install. Is this true? Where can
I find the instructions? I read this lengthy document --
http://www.postgresql.org/docs/8.3/static/release-8-3.html -- but it
is not clear which variables I need to change.

Thanks.

On 2008-08-26 13:39, Phoenix Kiula wrote:

Ok done. Slony is installed. Now what?

http://www.slony.info/documentation/versionupgrade.html
I think nobody would guide you step by step. Either read documentation
and do it yourself or hire an expert:
http://www.postgresql.org/support/professional_support

I was also told that the postgresql.conf settings across 8.2 and 8.3
are different so I cannot (or should not) use the same old
postgresql.conf for the new database install.

Find out what changes were made to your old postgresql.conf (compare it
to the default) and make the same changes to new posgresql.conf.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh

On 8/26/08, Tomasz Ostrowski <tometzky@batory.org.pl> wrote:

I think nobody would guide you step by step. Either read documentation
and do it yourself or hire an expert:

Thanks. I suppose that spirit is quite evident in the documentation.
Why make it easy or easily understandable when you can win fanatical
fans by requiring them to invest months of their time!

Cheers.

On Tue, Aug 26, 2008 at 5:02 AM, Tomasz Ostrowski
<tometzky@batory.org.pl> wrote:

http://www.slony.info/documentation/versionupgrade.html
I think nobody would guide you step by step.

Well they may, then is nothing wrong with asking especially when
"breaking new ground." Since my databases are currently pretty small,
a dump and reload is not a problem. However, I am following this
tread with keen interest for future reference. Perhaps others are
also?

Either read documentation
and do it yourself or hire an expert:
http://www.postgresql.org/support/professional_support

Reading the documentation is always good advice. Ofcourse, I think
that DBA/SA wants and should be able to perform all tasks associated
with maintaining a database. However, I very much agree that buying a
support aggreement for "mission critical" applications is an important
safety net to have.

Find out what changes were made to your old postgresql.conf (compare it
to the default) and make the same changes to new posgresql.conf.

I very much agree. There wasn't that much that changed between 8.2
and 8.3. Just look for the uncommented postgresql.conf setting in 8.2
and then compare with 8.3.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Mon, Aug 25, 2008 at 08:36:34PM -0600, Scott Marlowe wrote:

Slony replication lets postgresql accomplish this, which is really
quite impressive.

Pleased as I am to hear accounts of Slony being used successfully to
solve the upgrade problem -- it was one of our design goals in the
early discussions at Afilias -- I have to confess that if you find
Postgres administration arcane, Slony administration is going to seem
very like interpreting runes.

I have heard that Londiste (in the skytools package) can also do this,
and it is intended to be easier to administer. I have no personal
experience trying it.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Tue, Aug 26, 2008 at 5:31 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

I suppose that spirit is quite evident in the documentation.

All kidding aside, the problem that you are having IS recognized as a
weakness with PostgreSQL. This is why some are already working on
solving the problem of in place upgrades. Some time in the future
perhaps > 8.4 this will be a mute point. In the meantime, there are
other workarounds (which of course can be complex) to mitigate this
problem.

Why make it easy or easily understandable when you can win fanatical
fans by requiring them to invest months of their time!

I sense your frustration. But it is important to remember that the
PostgreSQL project is largely supported by volunteers. I am sure that
you agree that no one intentionally designs any give task to be more
complicated than necessary. As a side note there are many useful "how
to" articles on the web. Many of these HOWTOs were created by users
(like yourself) that have faced a difficult problem with no apparent
solution. However, after struggling with the problem and then finding
a solution, they document it for all to benefit from. Perhaps some
good with come from overcoming this challenge.

So in the mean time, the best advice is not to rush time upgrade but
take your time to insure that you do it right the first time.
Some useful article that you should consider are:
http://www.depesz.com/index.php/2008/05/05/error-operator-does-not-exist-integer-text-how-to-fix-it/
http://people.planetpostgresql.org/greg/index.php?/archives/136-Upgrading-to-8.3-MediaWiki-lessons-learned.html

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Tue, Aug 26, 2008 at 5:39 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

You need to download Slony 1.2.14, which supports both 8.2 and 8.3.
You'll find it under Quick downloads.

Ok done. Slony is installed. Now what? How should I install a new
database which is 8.3.3?

Which OS are you running? How was 8.2 installed?

I was also told that the postgresql.conf settings across 8.2 and 8.3
are different so I cannot (or should not) use the same old
postgresql.conf for the new database install. Is this true? Where can
I find the instructions? I read this lengthy document --
http://www.postgresql.org/docs/8.3/static/release-8-3.html -- but it
is not clear which variables I need to change.

Well, look at the entries that aren't commented out in your 8.2 and
make the same changes in your 8.3 postgresql.conf

I just want to point out that the reason there aren't a lot of step by
step guides on this is that it's a complex subject. There are many
different OSes and several ways you could have installed postgresql,
and step by step instructions for RHEL4 may not be the same as for
Ubuntu 8.04 etc...

On Tue, Aug 26, 2008 at 6:31 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:

On 8/26/08, Tomasz Ostrowski <tometzky@batory.org.pl> wrote:

I think nobody would guide you step by step. Either read documentation
and do it yourself or hire an expert:

Thanks. I suppose that spirit is quite evident in the documentation.
Why make it easy or easily understandable when you can win fanatical
fans by requiring them to invest months of their time!

Christ, remind to do you no favors. I'm sure if I had written a step
by step guide and one part of it didn't work you'd be right back here
threatening to sue me or something.

Look, it's a comlex subject, and you need to have a pretty good clue
what you're doing so if something goes wrong you're not making a big
mistake and losing all your data. And you can stop with the
histrionics. It took me less than one weekend to install and test
slony for migration / backup at work.

Remember, for every problem, there is a simple, elegant, easy
solution. Which is wrong.

phoenix.kiula@gmail.com ("Phoenix Kiula") writes:

See, this is where I get confused. I want to upgrade from 8.2.3 to
8.3.3. The recommendation is to try Slony. So I download Slony and try
to configure it. The configure command gives me this:

----
checking for correct version of PostgreSQL... "error"
configure: error: Your version of PostgreSQL (8.2) is lower
than the required 8.3. Slony-I needs functionality included in
a newer version.
----

Well, if Slony needs a newer version, then how can it be used to upgrade?!

I expect that what you downloaded was a pre-release candidate for
version 2.0, which does indeed "eschew" older versions (for some good
reasons - you can't support *all* versions of *everything*,
*forever*).

With version 1.2.14, the latest *official* release, versions of
PostgreSQL as far back as 7.4 are certainly supported.
--
output = reverse("moc.enworbbc" "@" "enworbbc")
http://cbbrowne.com/info/lsf.html
Dickson's Gardening Rule: When weeding, the best way to make sure you
are removing a weed and not a valuable plant is to pull on it. If it
comes out of the ground easily, it is a valuable plant.