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.

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

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

Cheers,
David.
-- 
David Fetter <david at fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate