Thread: mysqldiff-like utility for PG?

Is there any utility like mysqldiff
(http://adamspiers.org/computing/mysqldiff/) for PG?

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

Any and all help is appreciated.  Thanks.

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

This tool would be somewhat like saying: "We have a copy of Windows95b1
and Windows95b2, and we want something that takes all the EXEs and DLLs
and converts them from one to the other. Right now we use a hex editor and
hand-change each hex value from the old to the new."

Someone might ask you: "Why? Just go to CVS, check out the source for
Windows95b2, compile it, and there you have it. Why are you diff'ing two
binary sets of data?"

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.

--
Tim Ellis
Senior Database Architect
Gamet, Inc.

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

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

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

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

There are definitely instances where this wouldn't work (or at least
wouldn't be practical), though FWIW I run at least 1 million + entry
system and I can do a full drop/reload in 15 minutes with plenty of time
to spare.

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

Given 7.3 is right around the corner, this might be too big of a deal,
but if so the way we handle it in phpPgAdmin works pretty well. You can
use something like

assume mytable has fields 1,2,3.....n and I need to drop 2 and 3

BEGIN;
CREATE TABLE mytable_2112 AS SELECT "field1","fieldn" FROM "mytable";
DROP TABLE mytable;
CREATE TABLE "mytable" ("field1" int4 NOT NULL,"fieldn" int4 NOT NULL);
INSERT INTO "mytable" SELECT * FROM mytable_2112;
DROP TABLE mytable_2112;
COMMIT;

Robert Treat