On 3/17/07, David Fetter <david at fetter.org> wrote:
> On Fri, Mar 16, 2007 at 05:11:15PM -0700, Andrew Hammond wrote:
> > On 3/16/07, David Fetter <david at fetter.org> wrote:
> > >Folks,
> > >
> > >When managing database changes on a single database, I frequently do
> > >things like:
> > >
> > >BEGIN;
> > >CREATE TABLE ...;
> > either slonik execute script or via psql against all members in the cluster
> >
> > slonik create new set
> > slonik add table,
> > slonik subscribe set as necessary
>
> Is there a wrapper for the "add a table to a replication set"
> operation?  It seems this would be a common use case.
>
> > >COPY ... ; -- Populate the new table
> >
> > Against the origin, as you'd expect.
>
> OK
>
> > >ALTER TABLE ... ADD COLUMN ... USING ... ; -- From previous statements
> > >ALTER TABLE ...;
> >
> > slonik execute script,
>
> Right.
>
> > >DROP TABLE ....;
> >
> > slonik drop set,
>
> Really?!?  I thought it would be a SET DROP TABLE.

Sure, if you want to. But then you still should probably deal with the
temporary set you created to do this. If you're planning to just dump
it anyway, why bother removing the table first?

> > slonik execute script or via psql on all nodes
> >
> > >COMMIT;
> > >
> > >This way, only whole schema changes are visible.
> >
> > I am not aware of any way to achieve this in a single transaction.
>
> This strikes me as a major lack.  I discussed this with Jan yesterday,
> and he told me that it would be possible to send the script through an
> EXECUTE SCRIPT, but that it would be a Good Idea(TM) to schedule some
> down time for write operations.  I didn't ask about the effects going
> forward on the set, though.

Yes, in retrospect, you certainly could create your working table,
copy into it, do your alters then drop the working table all in a
single execute script. That'd be a very clever approach, and you
wouldn't have to bother making a new set or replicating the working
table. Neat.

> > >How do people approach this with Slony?
> >
> > I recently submitted an RFC to both this list and pgsql-general for a
> > tool we're building here that's intended to address automation of this
> > kind of multi-phased upgrade.
> >
> > http://archives.postgresql.org/pgsql-general/2007-03/msg00272.php
> >
> > There has been further thinking about this tool locally so that
> > posting is a bit out of date. If you're interested. I need to clean up
> > the wiki page that hosts the design anyway. I'll put it back on the
> > list if you're intereested. We're already  into coding at this point.
> > I intend to release the tool once it's got the basic functionality
> > implemented.
>
> Interesting :)
>
> Could you publish the updates, and what you have so far?  This looks
> like a good candidate for inclusion the Slony-I tool kit. :)

I've been working on polishing up the wiki page and will post it again
shortly. I'll CC you when I do.

Andrew