473,881 Members | 1,700 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Best practices for migrating a development database to a releasedatabase

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
Nov 23 '05 #1
4 2634
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

Nov 23 '05 #2
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 <kl*****@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.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBQsLVY5T wig3Ge+YRAksUAJ 0R9T2N0/vyZHRItR4Vd1MdP i6joQCeIvkc
7Qhn1rBiIkU4LYc pP0oTkX4=
=sCqd
-----END PGP SIGNATURE-----

Nov 23 '05 #3
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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #4
>>>>> "CP" == Collin Peters <cp*****@mcrt.c a> 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: kh***@kciLink.c om Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3022
by: Woody | last post by:
I am looking for some examples of how to manage DDL scripts among various versions of a production db and development and testing. I have tried a few things in the past, and it always gets very muddled and cumbersome. I need to be able to build any version of the database from scratch, BUT I also need to maintain an upgrade path from any version to any later version. So it is not enough to just maintain a master build script, but I...
16
3048
by: D Witherspoon | last post by:
I am developing a Windows Forms application in VB.NET that will use .NET remoting to access the data tier classes. A very simple way I have come up with is by creating typed (.xsd) datasets. For example dsParts.xsd and including that in the data tier. I then will create a class that looks like this Public Class CPart Inherits dsParts
11
9295
by: DrUg13 | last post by:
In java, this seems so easy. You need a new object Object test = new Object() gives me exactly what I want. could someone please help me understand the different ways to do the same thing in C++. I find my self sometimes, trying Object app = Object(); Object *app = Object(); Object app = new Object();
217
9295
by: gyromagnetic | last post by:
The following url points to an article written by Damian Conway entitled "Ten Essential Development Practices": http://www.perl.com/pub/a/2005/07/14/bestpractices.html Althought the article has Perl as a focus, I thought that some of the general points made might be of interest to the Python community. It would certainly be interesting to put together an analogous version of this article that centers on Python. Best Regards,
136
9498
by: Matt Kruse | last post by:
http://www.JavascriptToolbox.com/bestpractices/ I started writing this up as a guide for some people who were looking for general tips on how to do things the 'right way' with Javascript. Their code was littered with document.all and eval, for example, and I wanted to create a practical list of best practices that they could easily put to use. The above URL is version 1.0 (draft) that resulted. IMO, it is not a replacement for the FAQ,...
2
1833
by: Amelyan | last post by:
Could anyone recommend a book (or a web site) that defines best practices in ASP.NET application development? E.g. 1) Precede your control id's with type of control btnSubmit, txtName, etc. 2) Group relevant .aspx files into subfolders within your project etc.
8
1302
by: Support | last post by:
Hello: I am deploying a new web site that will have over 100 pages. In the ASP world, I would create templtes - include files, etc... to easily "manage" the site. In asp.net 2.0 I want to create a web control but apparently I have to "create" a web site first. Concern #1: is VS an appropriate interface to manage 100+ web pages site conversely Concern #2: when asp.net 3.0 comes there might be fundamental structural issues the 3.0 does...
1
1663
by: Pablo | last post by:
Hello all, Hope today finds you well. I'm looking to take my knowledge of best practices within the development lifecycle to the next level. Basically I want to follow industry recognised, Microsoft approved practices for every aspect of the lifecycle - so planning and architecture, team development, testing procedures, change control and bug-tracking, version control - etc etc. What software to use, which
2
4562
by: dasomerville | last post by:
We have different settings for our development, stage and production environments. For example, our development environment connection strings point to development database instances, stage connection strings point to stage database instances and so forth. Without having to maintain separate web.config files, what are the various approaches to having environment-specific settings?
1
10812
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9552
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7952
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7108
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5780
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5976
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3223
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.