Forum OpenACS Q&A: Upgrading the data model as ACS/pg development continues

I am familiar with the tried and true method "dropdb, createdb, reload
datamodel", but now that the CVS version is working pretty well (great
work everyone!) I would like to be able to actually keep my data with
the upgrades, rather than just play around and test.  I assume one can
use pg_dump with inserts to restore data to the new db, provided that
the data model doesn't change too much. I am guessing that one could
pay attention to the diffs for .sql files and edit inserts accordingly
if needed.

Is this a reasonable approach at this point in time?  Any other issues
I should be aware of?

This issue has been raised before, but I won't ding you 'cause we don't have a full-text search engine available anyway (hey, we suck when you think about it!  oh well, I have some ideas for summer...)

We need to move into a mode where our porting efforts for upgrades include doing upgrade SQL files wherever possible (isn't as possible for PG as it is for Oracle, but I think we can satisfy the immediate needs to a great extent).

There's a more global problem we don't have control over, which is the  Postgres paradigm that built-in types and functions are defined in Postgres header files used to compile the engine.  When these change (and I have the dubious honor of being the source of one such change, when I added the capability to index columns of type boolean to Postgres), you have to init a new database and reload it from a previous pg_dump.

Long-term, this is one of the more insidious strikes against Postgres.  There are ways to fix it, obviously, for many cases (define more of the functionality in SQL rather than in C header files, perfectly possible in today's PG but not how it is done).

Postgres is a horrid offender in this regard, but not the only one.  My understanding is that even Oracle has forced such things on users in times past...

Still doesn't make it right, though!

Upgrading ACS installations has always been quite hard. After this version 3.2 (and even possibly for 3.1 to 3.2 transition), we're going to include upgrade SQL files that Don mentioned. This means work, so extra SQL hackers would be great!