A few months ago, I upgraded postgres from 7.4 to 8.2. There were a few gotchas, but we don't keep a whole lot of data so even the biggest problems were, on the whole, minor.
- The cidr data type became more strict, and a few tables in our network database would not restore until this was fixed.
- One of the primary keys broke and couldn't get created. This was more the fault of poor admins (before my time) and internal fragmentation than postgres 7.4. I had to fix the underlying table before it would restore.
- There were a few permissions issues (new acls + an inconsistent previous policy = fun).
- The system databases went from SQL_ASCII encoding to UTF8 encoding. I had to explicitly create the database during the restore, or else the database would have the wrong encoding.
I doubt that you will run into these exact problems, but my point is that there are inevitably some gotchas. If it's not prohibitively time-consuming, I'd recommend a full dump/restore of your database(s) from 8.0 to 8.2 so you can catch many of these gotchas before going live. Also, you might want to create test versions of your apps and try them against the 8.2 server.
On the whole, I've found postgres to be very good at maintaining backwards compatibility of interfaces and sql, so I estimate that most queries and db apps should "just work" with 8.2.
Peter
On 5/10/07, Steve Holdoway <steve.holdoway@firetrust.com> wrote: Are there any gotchas? I've got the opportunity to move to another database server for this application and yould like to take the opportunity to upgrade at the same time.
tia,
Steve
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate