Forum OpenACS Q&A: Upgrading the data model as ACS/pg development continues
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
Is this a reasonable approach at this point in time? Any other issues
I should be aware of?
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!