Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 :)

share|improve this question
up vote 0 down vote accepted

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

in particular the Migration sections that list the known incompatibilities.

share|improve this answer
Welcome to the ranks of the Stack Athletes. – Dennis Williamson Feb 17 '11 at 19:05

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

share|improve this answer
WHERE dob LIKE '%-09-%' should be rewriten to WHERE EXTRACT(month FROM dob) = 9. Now you can also use an index on the month, to speed things up: CREATE INDEX idx_dob_month ON t (EXTRACT(month FROM dob)); – Frank Heikens Feb 16 '11 at 10:22

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.

share|improve this answer

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.