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

On Thu, 2002-08-22 at 14:08, Kevin Brannen wrote:
> 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 :-)
>
<snip>
>
> 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.

Assuming you have maintenance windows for when you would be upgrading
your database, there's no reason you have to rule out a full drop/reload
of the database system.

> 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.
>

Either method of upgrading is definitely valid under the right
circumstances, but I would second any notion to keep entire schema
versions in CVS and not just the "upgrades".

> 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
>

I am sure there are open source projects doing something similar to this
now (perhaps even the mentioned mysql utility?) that could give you a
jump start. If your doing this for 7.2.2, watch out for dropping
columns..

Robert Treat

>
> 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.
> >