On Wed 29 Dec 2004, Christopher Browne wrote:
> Paul Slootman wrote:
> 
> >We have here a database with (amongst others) a table named 'ssouser'.
> >That table used to be named 'user', but it was impossible to setup slony
> >with that name (or at least very difficult), so the application was
> >modified and the table renamed.
> >
> >However, I've now discovered that the sequence used to dole out numbers
> >for that table's primary key 'id' wasn't renamed; that is still called
> >user_id_seq.

> >I now find out that the sequence for that table is called user_id_seq on
> >the master, and ssouser_id_seq on the subscriber.
> >
> >It looks like this means that that sequence can't be replicated at the
> >moment. What would be the best course of action? Preferably without
> >downtime for the master :-)  Note I'm not very experienced with
> >postgresql...
> > 
> >
> This doesn't sound too hard...
> 
> 1.  Create the sequence with its new name on all of the nodes (e.g. - 
> ssouser_id_seq).
> 
> 2.  Add the new sequence to a new replication set, and start replicating it.
> 
> 3.  On the provider node, set ssouser_id_seq to some value comfortably 
> larger than the present value of user_id_seq
> 
> 4.  Very soon after that, do the command, on all nodes that need it:
> 
>  alter table ssouser alter column id set default 
> nextval('public.ssouser_id_seq');
> 
> That needs to be soon enough that, on the origin, user_id_seq hasn't 
> gotten bigger than ssouser_id_seq  (which is why I wrote "comfortably 
> larger").

Ah, OK, this sounds like a reasonable way to do it.
I hadn't realized that it was that simple to start using a new sequence
on an existing column. I'll try it soon when the database is relatively
idle.


Thanks!
Paul Slootman