Quoting Janning Vygen <vygen@xxxxxx>:
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
