Re: Best practices for migrating a development database to a release database

On Sat, Sep 11, 2004 at 02:29:42AM -0500, Thomas F. O'Connell wrote:
> One thing I used to do (and I won't necessarily claim it as a best 
> practice) was to maintain my entire data model (tables, functions, 
> indexes, sequences) as SQL (plus postgres extensions) CREATE statements 
> in text files that were version controlled (via CVS). I had an entire 
> set of utilities that could modify the existing database as necessary 
> to treat the SQL files as authoritative. For anything new, the create 
> statements sufficed, but for modifications, some objects had to be 
> regenerated. When it was time to release, we would export the textual 
> SQL schema to the production server, make the necessary updates using 
> my utilities, and then restart services.

One thing I was thinking about at my job which I would really have
liked is some kind of version control linked with the database. Say for
example I'd be able to 'checkout' a database function, edit it and
check it in again. This would require some kind of backing store and I
was wondering whether that would be in the database too.

I always found it annoying when I had function definitions in seperate
files which could be checked into CVS, but there was no guarentee that
those files had any relationship with what was in the database.

Maybe I should sketch something out that could be merged with psql or
something... I don't suppose anything like this exists anywhere
already?
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.