Обсуждение: Updating database structure

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

Luuk Jansen <subscribe@angelosystems.com> 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

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

Quoting Janning Vygen <vygen@gmx.de>:

> 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

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

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 //

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
justuploaded project at SourceForge.Net on topic of PostgreSQL database schema upgrades because I needed to find out
differencesbetween current and new schemas. The project is called apgdiff (Another PostgreSQL Diff Tool) and can be
foundat 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,
>

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