Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group