Updating a production database schema from dev server

On 10/16/07, Stanislav Raskin <sr@brainswell.de> 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.

Stanislav Raskin wrote:

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?

What Scott said, unfortunately there's no substitute for discipline here.

It's not actually possible to fully automate this (what would it do if
you merged the data in two columns for example). There are some tools to
help you though - try searching for "diff" on www.pgfoundry.org or
"pgdiff" in google.

--
Richard Huxton
Archonet Ltd

Stanislav Raskin wrote:

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?

What Scott said, unfortunately there's no substitute for discipline here.

It's not actually possible to fully automate this (what would it do if you
merged the data in two columns for example). There are some tools to help you
though - try searching for "diff" on www.pgfoundry.org or "pgdiff" in google.

Inspired by the tool "rancid", used to monitor router configs, I'm
configuring a cronjob that pg_dump's the schema from our development
database server to a file in CVS. The schema file will be tagged
with the rest of our software and configuration files between our
releases, so we'll be able to diff schemas between releases and double
check our release procedure.

This too is not a substitute for real discipline, but it will help.

-Michael

Thank you very much for the advice.
I'll start getting rid of the manual labour habit right away by writing the
changes for the current update into a nice and tidy script :)

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Scott Marlowe
Gesendet: Dienstag, 16. Oktober 2007 19:51
An: Stanislav Raskin
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] Updating a production database schema from dev server

On 10/16/07, Stanislav Raskin <sr@brainswell.de> 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

Hi,

try using liquibase. http://www.liquibase.org/ . It works very well.

kind regards,
Janning

Am Dienstag, 16. Oktober 2007 18:38 schrieb Stanislav Raskin:

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?

Thank you very much in advance.

SR

--
kind regards,
janning