sowjanya v <sowjuec at gmail.com> writes:
> I have two databases db1 and db2. db2 is replica of db1. Now i have added a
> column in a table in db1. Since schema changes do not propogate by itself to
> db2, need to execute execute script. But the input for that is a sql file with
> all  difference in schema. Hence please let me know how replicate the schema
> changes. If i do the diff of two schemas, it only shows the lines where the
> difference is present and not the sql command to be executed on db2 to make it
> same as db1. Please do reply immediately.

It sounds to me as though you're trying to use an SCM's "diff" command
to find the difference between schemas.

At simplest level, this would be like having two files:

% cat schema1.sql
create table t1 (
  id serial primary key,
  name text not null unique,
  created_on timestamptz default now()
);
% cat schema2.sql
create table t1 (
  id serial primary key,
  name text not null unique,
  created_on timestamptz NOT NULL default now(),
  updated_on timestamptz NOT NULL default now()
);
% diff schema1.sql schema2.sql
4c4,5
<   created_on timestamptz default now()
---
>   created_on timestamptz NOT NULL default now(),
>   updated_on timestamptz NOT NULL default now()

If you're using CVS, Subversion, or such, the commands might be a bit
different, but the output would be pretty similar.

What you'd presumably need, in order to set up EXECUTE SCRIPT to make
this change, is a SQL script consisting of something like the following:

  -- Upgrade from schema1.sql to schema2.sql
  alter table t1 alter column created_on set not null;
  alter table t1 add column updated_on timestamptz;
  update t1 set updated_on = '2010-05-10'; -- somewhat arbitrary value
  alter table t1 alter column updated_on set default now();
  alter table t1 alter column updated_on set not null;

There isn't a way to get a text-file-oriented SCM to generate that set
of 5 SQL statements.

There exist some tools that *try* to create "differencing scripts."
Some of our folks at Afilias have had some success with DBSolo
<http://www.dbsolo.com/>, though that success was fairly limited.

DBSolo might be able to figure out the 4 ALTER TABLE statements, but
it's not aware that the data requires alteration (e.g. - the UPDATE
statement), and that's not something that seems reasonable to expect a
tool to mechanically figure out for you.

In effect, you need to generate the update script.

And, not inicidentally, if you've already altered the first database,
db1, then replication is more than likely already quite badly broken.
Cleaning up after the problem is liable to be a lot more burdensome in
understanding all the details.
-- 
output = ("cbbrowne" "@" "ca.afilias.info")
Christopher Browne
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"