Horton

Thread

  1. Best practices for migrating a development database to a release database

    Collin Peters <cpeters@mcrt.ca> — 2004-09-10T18:55:00Z

    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

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

    Thomas F. O'Connell <tfo@sitening.com> — 2004-09-11T07:29:42Z

    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

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

    Martijn van Oosterhout <kleptog@svana.org> — 2004-09-11T09:18:14Z

    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.

  4. Re: Best practices for migrating a development database to a

    Vivek Khera <khera@kcilink.com> — 2004-09-14T17:46:38Z

    >>>>> "CP" == Collin Peters <cpeters@mcrt.ca> writes:

CP> I have thought of the following situations:
CP> -Simply track all the changes you made to the development database and
CP> make the same changes to the release database
CP> -Back up the release database... overwrite it with the development
CP> database... then copy all your real data back into the release
CP> database (this last step is probably quite difficult)
CP> -Perhaps some combination of the two

You need one more layer: the staging server.

What we do is develop on local workstations, prepare release on a
staging server, then push the staging server info to the production
box, or run the same updating script on production.

Any schema changes are done via scripts within transactions.  The
renames, alters, grants, etc., are all tested on the staging server
with a current copy (pg_dump/restore) from the live server so we know
there won't be any surprizes on the live data (or close to it).  It
also lets us know how long some things might take.

For example, this weekend we need to add a primary key to a 65 million
row table that just logs events.  Until now it really didn't need a PK
since it was never updated and the queries were all aggregates.
However, to run slony replication it needs a PK...  The test procedure
of doing it on the staging server pointed out some flaws in the
conversion script that were not noticed when running on the
development server because the dataset was so small.  These flaws
would have made the DB unusable for something like 5 days (if it ever
completed -- I don't know because I aborted that test) while the
update occurred, and once done would leave the application without
access to the revised table.  Naturally, we found better ways to do it
that have trimmed the expected time down to about 1.5 hours or less.

You really have to take each situation separately.  The easy way of
the PK adding script works fine on tables up to about 60k or 100k
rows, so we used that on some other smaller tables.

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.                Khera Communications, Inc.
Internet: khera@kciLink.com       Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/