Re: mysqldiff-like utility for PG?

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