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

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.

Since I'm deploying postgres in new environments now, and I left these
utilities behind at another job (where they're still in use), I've been
thinking more about the concept of schema version control. But I'm
similarly interested in any concepts of best practices in this area.

-tfo

On Sep 10, 2004, at 1:55 PM, Collin Peters wrote:

> I have searched the Internet... but haven't found much relating to
> this.
>
> I am wondering on what the best practices are for migrating a
> developmemnt database to a release database.  Here is the simplest
> example of my situation (real world would be more complex).
>
> Say you have two versions of your application.  A release version and
> a development version.  After a month of developing you are ready to
> release a new version.  There have been many changes to the
> development database that are not in the release database.  However,
> the release database contains all your real information (customers,
> etc...).  What is the best practice for migrating the development
> database to the release database?
>
> I have thought of the following situations:
> -Simply track all the changes you made to the development database and
> make the same changes to the release database
> -Back up the release database... overwrite it with the development
> database... then copy all your real data back into the release
> database (this last step is probably quite difficult)
> -Perhaps some combination of the two
>
> Does anybody have any recommendations?
>
> Regards,
> Collin Peters