On 10/16/07, Stanislav Raskin <sr@xxxxxxxxxxxxx> wrote: > > Hello everybody, > > I am currently running two PostgreSQL servers on two different machines. One > of them I use for development and the other one as the "real" production > server for my applications. > > While developing new versions of these applications, I of course have to > modify the database schema, including changing the definitions of views and > sql functions, creating new indexes and removing others, changing schemas > and data types in some tables, redistributing privileges and so on. Until > now, when I decided to update the applications on the production machine to > a newer version, I used to apply these changes manually, which is quite > error-prone and annoying. > > I figure there must be a better way to do so. Is there some kind of > software, which compares two database schemas (preferably two sql dumps), > and generates a script for applying differences to one of them? > > What would be the best practice for applying such updates without > endangering the data and its integrity? The very simple way of doing it is to use scripts for all your updates. I.e. update-0001.sql contains: begin; alter table abc add moreinfo text; drop table def cascade; update table xyz... insert into schematracker (id,versioninfo) values (1,'Update number 1, added yada and blah blah'); commit; then if there's an error on any of update the whole thing rolls back. Otherwise the whole thing gets applied. You can write a set of plpgsql functions to make it semi-automated and handle dependencies even (i.e. update 5 requres updates 2 and 3, etc...) But to begin with, get OUT of the habit of applying changes by hand. do it in scripts which you check into your CVS (or other versioning server) and apply in a particular order. That way a simple select * from schematracker order by id desc limit 1 can show you what the latest patch is that's been applied. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
