Re: Updating database structure

Quoting Janning Vygen <vygen(at)gmx(dot)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