Hi,
We manage a number of high-volume databases that require 24/7 uptime (pretty
much) and deal with this problem a lot.
The solution we employ is that once a database is in production the only
way to alter the database is using a change script that deals with any data
migration issues as well as altering the schema.
The practice is for a developer to make changes as they see fit in dev
using a simple change script. Once the changes are complete (and the
application coding work has been done) the schema change is passed to a
member of the dba team to code up properly.
It is this change script (which usually includes a section that can reverse
the change) that is then run against the qa database. The application is
then
tested at this stage before a production release is performed.
Depending on the nature of the change the applications and schema changes
are
all then release together at a defined time.
This is generally a fairly easy process to manage. One of the useful things
we do is to include an insert at teh top of each schema change into a
standard schema table. This way we can easily see which schema changes
have been run against each instance of each db.
All of schema change scripts are managed in source control.
J.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Csaba Nagy
Sent: 13 September 2004 09:13
To: Thomas F.O'Connell
Cc: Collin Peters; Postgres general mailing list
Subject: Re: [GENERAL] Best practices for migrating a development
database
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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
________________________________________________________________________
This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
________________________________________________________________________
This e-mail has been scanned for all viruses by Star. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
