Re: Updating database structure

On 23.03.2006, at 9:50 Uhr, subscribe(at)angelosystems(dot)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