Re: mysqldiff-like utility for PG?

Robert Treat wrote:
> 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.

Fair enough--some people have circumstances which allow this, I don't.
:-/   My maintanance windows are very small, and one day I will hit the
multi-million row size, creating a physical impossibility to drop/reload
in the time I have.  And I suspect this is not an uncommon problem. :-)

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

You bet!  I always store the entire new schema.  And I also create a
"transition" file that contains all the sql (creates and alters) to go
from one version to the next.  A tool to help me create this transition
file would be a great thing! :-)

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

Good advice, I'll look around before I do anything.  Yeh, the lack of
"alter table T drop column ..." is a real pain.  If I write this tool,
I'll probably assume that command exists, which means the sql won't be
fully valid until PG7.3. :-(

Kevin