Re: Best practices for migrating a development database - Mailing list pgsql-general

Beside version controlled schema files we have a guy who writes
migration scripts based on the old schema and the new (development)
schema (frozen e.g. by branching in CVS).
Usually there are 3 steps involved:
 - a pre-migration script, which prepares the data base for the new
schema, by adding the new structures needed for the data migration;
 - a data migration script, which moves around data between the old and
the new structures;
 - a finalization script, which removes the old structures not needed
anymore;

I think there's no way to make any of these steps automatically computed
as a diff between the old and new schemas...
We usually do it anyway so that after step 1 was executed, both the old
version of the application and the new version can work at the same
time, and the new version will only use the data migrated by step 2, but
I suppose our application is not very typical (we have lots of distinct
customers which live in the same data base but have distinct data).
This also means we try to do minimal changes to the data base and we try
to only have additions, no modifications, this makes migration easier.

HTH,
Csaba.


On Sat, 2004-09-11 at 09:29, 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.
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend