Updating database structure

От:

Luuk Jansen <subscribe@angelosystems.com>

Дата:

22 марта 2006 г. в 15:39:02


I have a problem with finding a way to update a database structure.
This might be a very simple problem, just cannot find the info.

I am looking at updating the structure of my database. I put an
application on my production server some time ago and further developed
the application till a point I have to merge the changes.

How can I update the structure on the production server to reflect the
database on my test machine in an easy way with preservation of the data
on the production server. There are no major changes in the fields types
etc., mainly additions/deletions of fields in the tables.

Thanks,

Luuk

Re: Updating database structure

От:

Janning Vygen <vygen@gmx.de>

Дата:

22 марта 2006 г. в 16:29:19

Am Mittwoch, 22. März 2006 20:40 schrieb Luuk Jansen:
> I have a problem with finding a way to update a database structure.
> This might be a very simple problem, just cannot find the info.
>
> I am looking at updating the structure of my database. I put an
> application on my production server some time ago and further developed
> the application till a point I have to merge the changes.
>
> How can I update the structure on the production server to reflect the
> database on my test machine in an easy way with preservation of the data
> on the production server. There are no major changes in the fields types
> etc., mainly additions/deletions of fields in the tables.

usually you write some update SQL scripts which can be deployed to the 
production database.

you have a base schema in

sql/schema.sql

and further changes are placed inside

sql/update_0001.sql

with content like
BEGIN;
ALTER TABLE ...
COMMIT;

now i use a script called update.pl and a version table inside my database to 
register which update scripts are already deployed. The script checks which 
changes have to be deployed and installs them to the production database.   

if you dont have those update SQL scripts it can become quite difficult. You 
have to compare original schema and current schema. To get a schema look at 
pg_dump option '-s'. Compare those schemas and write your update SQL scripts.

kind regards
janning

Re: Updating database structure

От:

subscribe@angelosystems.com

Дата:

23 марта 2006 г. в 04:50:36

Quoting Janning Vygen :

> Am Mittwoch, 22. März 2006 20:40 schrieb Luuk Jansen:
>> I have a problem with finding a way to update a database structure.
>> This might be a very simple problem, just cannot find the info.
>>
>> I am looking at updating the structure of my database. I put an
>> application on my production server some time ago and further developed
>> the application till a point I have to merge the changes.
>>
>> How can I update the structure on the production server to reflect the
>> database on my test machine in an easy way with preservation of the data
>> on the production server. There are no major changes in the fields types
>> etc., mainly additions/deletions of fields in the tables.
>
> usually you write some update SQL scripts which can be deployed to the
> production database.
>
> you have a base schema in
>
> sql/schema.sql
>
> and further changes are placed inside
>
> sql/update_0001.sql
>
> with content like
> BEGIN;
> ALTER TABLE ...
> COMMIT;
>
> now i use a script called update.pl and a version table inside my database to
> register which update scripts are already deployed. The script checks which
> changes have to be deployed and installs them to the production database.
>
> if you dont have those update SQL scripts it can become quite difficult. You
> have to compare original schema and current schema. To get a schema look at
> pg_dump option '-s'. Compare those schemas and write your update SQL scripts.
>
> kind regards
> janning
>
>


Thanks Janning,

I have something like that in mind, but is there an easy way to create
that script? Is there an easy way to let postgres keep track of the
changes?

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.

Can a rule be made for that or so, or do you track it manually when you say:

> and further changes are placed inside
>
> sql/update_0001.sql
>
> with content like
> BEGIN;
> ALTER TABLE ...
> COMMIT;

Thanks,

Luuk

Re: Updating database structure

От:

Alban Hertroys <alban@magproductions.nl>

Дата:

23 марта 2006 г. в 07:01:37

Guido Neitzer wrote:
> 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.

Looks like it could be useful to make the database 'log' the DDL 
statements and having the ability to export those to a script starting 
from a certain version.

It would probably still need user intervention, as sometimes experiments 
and mistakes require fixing things - which would result in bogus or 
non-optimal DDL statements in the log.

There's also sometimes the need to update data between statements, for 
example when adding a new NOT NULL column to a table. Not sure what to 
do in that case. Automating that would require some server side 
'intelligence'...

Regards,

-- 
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //

Re: Updating database structure

От:

Miroslav Šulc <miroslav.sulc@startnet.cz>

Дата:

23 марта 2006 г. в 12:34:29

Hello,

I accidentaly came across this post. I didn't follow it so I don't know whether my posting is to the topic or not. I've just uploaded project at SourceForge.Net on topic of PostgreSQL database schema upgrades because I needed to find out differences between current and new schemas. The project is called apgdiff (Another PostgreSQL Diff Tool) and can be found at apgdiff.sourceforge.net. It is still in beta but might be useful.

Sorry if this post is not to the topic.

Miroslav Šulc



Alban Hertroys napsal(a):
> Guido Neitzer wrote:
>> 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.
>
> Looks like it could be useful to make the database 'log' the DDL
> statements and having the ability to export those to a script starting
> from a certain version.
>
> It would probably still need user intervention, as sometimes
> experiments and mistakes require fixing things - which would result in
> bogus or non-optimal DDL statements in the log.
>
> There's also sometimes the need to update data between statements, for
> example when adding a new NOT NULL column to a table. Not sure what to
> do in that case. Automating that would require some server side
> 'intelligence'...
>
> Regards,
>

Re: Updating database structure

От:

Guido Neitzer <guido.neitzer@pharmaline.de>

Дата:

23 марта 2006 г. в 05:49:29

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

Re: Updating database structure

От:

Jim Nasby <jnasby@pervasive.com>

Дата:

23 марта 2006 г. в 14:07:00

On Mar 23, 2006, at 9:50 AM, 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.

My suggestion: don't do that.

What I do is keep the files used to create a database from scratch  
under version control (such as subversion). Then, depending on how  
active you development is, you can either commit scripts to make  
schema changes every time they happen, or you can do a diff between  
two releases of your application (you'll want to tag each release)  
and see what's changed.

By changing things on-the-fly, you end up with no history of what's  
changed, when it changed, and why it was changed (commit logs). You  
may not thing having such information is important if you're the only  
one working on something, but trust me, having that info available  
has saved my bacon many times.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

Re: Updating database structure

От:

Johan Vromans <jvromans@squirrel.nl>

Дата:

22 марта 2006 г. в 16:08:03

Luuk Jansen  writes:

> How can I update the structure on the production server to reflect the
> database on my test machine in an easy way with preservation of the data
> on the production server. There are no major changes in the fields types
> etc., mainly additions/deletions of fields in the tables.

ALTER TABLE ?

-- Johan