Re: mysqldiff-like utility for PG? - Mailing list pgsql-admin

I think you're missing the point of his request.  Upgrading applications
(or anything binary) is trivial compared to upgrading a working
database, IMHO.  (but maybe you just picked a bad analogy :-)

Some of us are using tools to generate our schemas.  When we finish the
"next revision", the tool produces stuff like:

create table foo (
    id int4,
    data text,
    mod_time timestamp,
    mod_user int4
);

and so on.  While the output of the last revision was:

create table foo (
    id int4,
    data text
);

So you can see that I've added 2 new columns.  He wants a diff-tool that
will take the 2 schemas, note the 2 new columns, maybe new tables,
indices, views, constraints, ...  and produce the "alter" and "create"
commands.

I don't see how you can keep a production DB running in any other way.
If you can, please let us know!  But once a schema is being used in
production, and has a data in it, you can't just drop it and stuff a new
schema in; it needs to be transformed.  And that's what the diff tool
does, helps to transform.

So he's not alone in his problem.  Hope that helps you to understand
what he was asking for.

Hmmm, I wonder if I could write this...  Ignoring constraint changes, it
doesn't sound that hard.  If I do this, I'll post it to the news group.

Kevin


Tim Ellis wrote:
>>"mysqldiff is a Perl script which compares the data structures (i.e.
>>table definitions) of two MySQL databases, and returns the differences
>>as a sequence of MySQL commands suitable for piping into mysql which
>>will transform the structure of the first database to be identical to
>>that of the second (c.f. diff and patch). Database structures can be
>>compared whether they are files containing table definitions or
>>existing databases, local or remote. "
>>
>>It would make our lives 1000x easier if such a tool existed. As it
>>stands now, the process of updating the DB involves manually writing a
>>series of alter statements, which is a huge pain.  I can't be the only
>>one with this problem!
>
...
>
> By the same token, database schemas should be created by some source code
> (ie: ERDs) and then 'compiled' into the form you use. Revisions of the
> ERDs should be stored in revision control (ie: CVS).
>
> Your problem may have a solution (a postgresdiff might exist) but if you
> find it, I urge you not to use it. Instead, keep ERDs for the old version
> and new version, and compile them to the database.
>
> Disallow the use of DDL in your database unless it was generated by the
> tool that stores your ERD. Give developers that need to change the table
> definitions access to your ERD tool. Dia is such a tool that gives you
> these options.
>
> If you cannot do this, your databases will drive you mad.
>