Re: Updating database structure

On 23.03.2006, at 9:50 Uhr, subscribe@angelosystems.com wrote:

> I currently use phpPgAdmin to make changes to the database, so it  
> would
> be very handy if Postgres could add a change made to a lable  
> somewhere,
> after which I gather all the rows with changes and put them in a SQL
> query.

I mostly use the command line tools or a graphical tool to make my  
own sql calls for every schema change I make on the development  
database.

Then all these changes come to a "script" in my application (it's not  
actually a script but similar). The database has a version tag in one  
table. The application has a bunch of these "scripts" to convert from  
one version to another.  It knows the sequence in how they have to be  
applied by looking at the version numbers.

So, let's say, the production database is on version 1.1 and you have  
made several test versions connecting to a test server, the test/dev  
db is on version 1.4 and now I want to put my new application online.  
I simply shut down all running instances of my app, replace the  
application binaries, start the application again (one single  
instance for now), the app checks the DB version, sees that changes  
have to be made and applies all scripts necessary for converting the  
db from version 1.1 to 1.4 automatically. When this is done, I start  
the other instances and everything is fine.

For the kids: Don't do this at home without a fresh backup and  
without a lot of testing of this process!

cug



-- 
PharmaLine, Essen, GERMANY
Software and Database Development