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.
>
>This is the definition of the table (uninteresting columns edited out):
>
>ssoserver=# \d ssouser;
>                                     Table "public.ssouser"
>    Column     |            Type       |                        Modifiers                        
>---------------+-----------------------+-----------------------------------------------------
> id            | integer               | not null default nextval('public.user_id_seq'::text)
> username      | character varying(40) | not null
> password      | character varying(40) | not null
>Indexes:
>    "user_pkey" primary key, btree (id)
>    "user_username_key" unique, btree (username)
>
>The problem is that when setting up a subscriber, I did a dump of the
>schema, imported that on the subscriber, and then setup slony. I first
>stupidly forgot about the sequences, so I wanted to add those now.
>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").