Re: Updating database structure

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