Thread: Considering an upgrade...

Hi,
I did a bunch of updates from databases that where on 7.4 and there were
no issues at all.

You could always setup a test server on win32,linux or whatever and
simply restore one of your databases and see how it goes.

Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com


Gary Horton wrote:

> I'm needing to convince my boss that we should upgrade from 7.3.4 to
> 8.x Postgresql. I've already enumerated the upsides, but now we need
> to consider the risks...I wonder if anyone can mention/summarize any
> issues, problems, gotchas, etc. that have happened in anyone's upgrade
> from 7.x to 8.x ... this would be greatly appreciated. For that
> matter, if there are any benefits people have seen in addition to
> what's mentioned on postgres website, this would be good info too.
>
> We are on a Solaris system (including customers with sol 8, 9, and
> 10), running 7.3.4.  Thanks in advance ... and if you would, please cc
> me at my email address since I'm not subscribed to this alias.
>
> - Gary Horton

On Tue, 2005-10-11 at 10:50, Gary Horton wrote:
> I'm needing to convince my boss that we should upgrade from 7.3.4 to
> 8.x Postgresql. I've already enumerated the upsides, but now we need
> to consider the risks...I wonder if anyone can mention/summarize any
> issues, problems, gotchas, etc. that have happened in anyone's upgrade
> from 7.x to 8.x ... this would be greatly appreciated. For that
> matter, if there are any benefits people have seen in addition to
> what's mentioned on postgres website, this would be good info too.
>
> We are on a Solaris system (including customers with sol 8, 9, and
> 10), running 7.3.4.  Thanks in advance ... and if you would, please cc
> me at my email address since I'm not subscribed to this alias.

If you'd like to see if all your data works in 8.0 AND that all your
updates / inserts / deletes work there too, you can use the trick of
making a slony replicant running 8.0 under your 7.3 server and seeing if
everything runs smoothly on the 8.0 server before trying it under the
real app.  This will let you make sure it works in production before
committing to an update.

Gary Horton wrote:
> I'm needing to convince my boss that we should upgrade from 7.3.4 to 8.x
> Postgresql. I've already enumerated the upsides, but now we need to
> consider the risks...I wonder if anyone can mention/summarize any
> issues, problems, gotchas, etc. that have happened in anyone's upgrade
> from 7.x to 8.x ... this would be greatly appreciated. For that matter,
> if there are any benefits people have seen in addition to what's
> mentioned on postgres website, this would be good info too.
We (the company I work for) has upgraded a rather large e-commerce
appplication
from pg 7.4 to 8.0. It took a few hours to fix some sql that wouldn't
work in 8.0,
but it was mostly buggy code that happened to work in 7.4 "by chance" (e.g,
triggers declared as "BEFORE" instead of "AFTER", and therefore reported
a foreignkey
violation in 8.0, but worked in 7.4, probably due to slight differences
in trigger execution order).

8.0 brought a big performance win, especially when running selects while
another process
is importing lots of data, and we didn't have a single crash with 8.0 so
far (We had a few data-file
corruptions with the first 7.4 releases).

On the whole, the update as quite easy ride, but I'd still recommend you
to test all aspects
of your software after the migration, since 8.0 isn't bug-for-bug
compatible with 7.4

> We are on a Solaris system (including customers with sol 8, 9, and 10),
> running 7.3.4.  Thanks in advance ... and if you would, please cc me at
> my email address since I'm not subscribed to this alias.
Well, we are running on linux (32 and 64 bit x86), so I can't really
comment on
how things are on solaris.

greetings, Florian Pflug

On Oct 11, 2005, at 3:07 PM, Florian G. Pflug wrote:

> On the whole, the update as quite easy ride, but I'd still
> recommend you to test all aspects
> of your software after the migration, since 8.0 isn't bug-for-bug
> compatible with 7.4
>

I'll second this.  One thing we found out too, was we also upgrade
DBD::Pg which introduced some incompatibilities with respect to
placeholders.  They are now always treated as strings unless
otherwise specified, and that doesn't always work with Pg 8.0.

Our specific case was a query that did something like this:

   CURRENT_DATE - ?

if the parameter  is substituted as '3' it fails, but just plain 3
works.  So watch for things like this too.

On Tuesday 11 October 2005 14:59, Scott Marlowe wrote:
> On Tue, 2005-10-11 at 10:50, Gary Horton wrote:
> > I'm needing to convince my boss that we should upgrade from 7.3.4 to
> > 8.x Postgresql. I've already enumerated the upsides, but now we need
> > to consider the risks...I wonder if anyone can mention/summarize any
> > issues, problems, gotchas, etc. that have happened in anyone's upgrade
> > from 7.x to 8.x ... this would be greatly appreciated. For that
> > matter, if there are any benefits people have seen in addition to
> > what's mentioned on postgres website, this would be good info too.
> >
> > We are on a Solaris system (including customers with sol 8, 9, and
> > 10), running 7.3.4.  Thanks in advance ... and if you would, please cc
> > me at my email address since I'm not subscribed to this alias.
>
> If you'd like to see if all your data works in 8.0 AND that all your
> updates / inserts / deletes work there too, you can use the trick of
> making a slony replicant running 8.0 under your 7.3 server and seeing if
> everything runs smoothly on the 8.0 server before trying it under the
> real app.  This will let you make sure it works in production before
> committing to an update.
>

IIRC Slony wont run on anything < 7.3.6, so you would have to do an in place
upgrade to 7.3.x as appropriate, and then you could set up slony to propogate
to the 8.x system.

Outside of using the slony method the main issues to be aware of are having
enough space to pg_dump out your 7.3 systems and then loading them up into
8.0. I always recommened using the 8.x pg_dump.  The advantage to this is
that you can test this entire process a few time to make sure there are no
schema issues or application issues on the new db.  Don't forget to time the
process so you can plan your outage accordingly.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

On Wed, Oct 12, 2005 at 06:15:36PM -0400, Robert Treat wrote:
>
> IIRC Slony wont run on anything < 7.3.6, so you would have to do an in place
> upgrade to 7.3.x as appropriate, and then you could set up slony to propogate
> to the 8.x system.

You should do that anyway, because on older releases, you really
don't want to lag the last dot-release -- there are usually some big
bugs that have been shaken out.

One thing that is really nice about using the Slony trick, by the
way, is that when you switchover to your upgraded system, you make
your old database into a replica too.  That means that you have the
ability to roll back to the old release if, once you get the new
system into production use, you happen to find something you'd
overlooked.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
When my information changes, I alter my conclusions.  What do you do sir?
        --attr. John Maynard Keynes