Hi;

I have a client who is running PostgreSQL 8.0.8. We are migrating to a new server. The new Fedora box has PostgreSQL 8.4.7 - are there any blatant dissimilar areas that I should be concerned about? If I do a pg_dumpall of the 8.0.8 db, and attempt to import it into the 8.4.7 version, anything I should be worried about?

The database is driving a Tomcat container application. The connectors/JDBC should be quite fundamental I would think. I am thinking that if the tables 'look' the same as far as the application is concerned, it will work.

However, I have found this not to be true in all cases.

Anyone do anything similar?

Thanks for your help in advance.

Glen

Dump/Restore is the way to go. Once you are at 8.4 you can use pg_upgrade for future upgrades which is a bit faster than a dump/restore.

What's the reason for not going straight to 9.1?

Note that there were large differences in how strict PostgreSQL is regarding automatic casting between numbers and strings from 8.2 to 8.3 so if you used sloppy SQL statements (e.g.: where number_column = '1') you might get some syntax errors.

There other behaviour changes that might break your statements (add_missing_from, standard_conforming_strings come to my mind)

Make sure you read the release notes for all versions after 8.0 so that you get a picture on what has changed.

PostgreSQL: Documentation: Manuals: Release 8.1
PostgreSQL: Documentation: Manuals: Release 8.2
PostgreSQL: Documentation: Manuals: Release 8.3
PostgreSQL: Documentation: Manuals: Release 8.4
PostgreSQL: Documentation: Manuals: Release 9.0
PostgreSQL: Documentation: Manuals: Release 9.1

You also want to upgrade the JDBC driver used in your application.

You had asked me:

"What's the reason for not going straight to 9.1? "

Well, the version of Fedora that the client was using, the distro/package manager contained this version of PostgreSQL within. I always try to install what is included with the distribution first, unless I specifically need a different version. Less chance of error/dependency issues that way. No other reason than that.

I did not write the SQL - I inherited this. So, I am stuck with it. It seems like well-written SQL, but I guess when I try to launch the application, I will know for certain.

Thanks for your insight. I have not used PostrgeSQL in a number of years - I have mostly been working with MySQL and I am finding that PostgreSQL is popping up more and more now. Adapt or die I guess.


Glen

That's something I will never understand with Linux. Why it's forcing me to install some out-dated software instead of the version I want.
But as I'm not using it on a regular basis I probably miss an important point about what the advantage of that is.

It's a double edged sword. With Linux the main focus is on reliability and stability. It's a developer mentality that they test against before they lock packages and label them as stable in their package manager. During the time the Fedora developers were testing, 8.x was the latest version but now the OP customer decided to use a dated version of Fedora and of course the package manager for that release is frozen on that version. It also has a lot to do with dependencies. The package manager also manages dependencies for software so if we were to use it and install 9.1, it would for example upgrade 'openssl' which in turn would break Apache and many other packages. The O.P. doesn't have to use Yum and can simply just install PostgreSQL 9.1 but then it's his duty to make sure he stays up to date with this or it will remain static.