Re: mysqldiff-like utility for PG?

From: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
To: Kevin Brannen <kevinb(at)nurseamerica(dot)net>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: mysqldiff-like utility for PG?
Date: 2002-08-22 19:41:59
Message-ID: 1030045319.27266.35.camel@camel
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.
> >

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Brannen 2002-08-22 20:16:31 Re: mysqldiff-like utility for PG?
Previous Message Kevin Brannen 2002-08-22 18:08:39 Re: mysqldiff-like utility for PG?