any tips for upgrade postgres 8.2 to 8.4?

Question

i have to migrate a 80GB database from pg8.2 to pg8.4. that database has a lot of stock procedures, and i need tips to migrate it sucesfully ( and painless ;) ) any tips are welcome :)

Answer 1

Aside from the valuable specific advice given in other answers, the canonical answer to this sort of question is to read the release notes. For example, in an upgrade from 8.2 to 8.4 you should read the release notes

• of 8.3: http://www.postgresql.org/docs/8.3/static/release-8-3.html
• of 8.4: http://www.postgresql.org/docs/8.4/static/release-8-4.html

in particular the Migration sections that list the known incompatibilities.

Answer 2

Testing is ABSOLUTELY REQUIRED. Since you skipped it, as of 8.3:

Non-character data types are no longer automatically cast to TEXT (Peter, Tom)

It took us quite a bit of work to go through and find all the places we had trusted the automatic casting (for instance, we had WHERE dob LIKE '%-09-%' to send birthday emails, which trusted not only that dob was automatically typecast, but also that Postgres would pick the yyyy-mm-dd format for its output).

Answer 3

The best possible advice here is to follow the instructions for upgrading provided in the Postgres manual (also see this section) - This will involve a database dump and restore in your case.

You should probably set up the new database on a development machine and test the restore (& DB functionality) before doing this on production hardware.
You should DEFINITELY back up the PGDATA directory from your old installation when you make the switch (or give the new installation a new path for its data directory) so that you can go back to the old system in the event of an unforeseen catastrophic failure in the migration process.

Answer 4

Testing testing and more testing. Get it installed on a new machine/server if possible before the move, and do a test run, check what runs- and more importantly, what doesn't. Draw up a test plan of everything which needs to work.