On 3/6/2013 7:08 AM, Jonathan Soong wrote:
> Hi guys
>
> Replication has died on my master-slave.
>
> What i would really like to do is stop replication and then get it to clear out the replicated tables in the Slave and start again (i.e truncate the replicated tables ONLY ).
>
> The problem I have is that only SOME tables are replicated between the Master and the Slave.
>
> - i do NOT want the replication to destroy the un-replicated tables on the Slave.
>
> I have seen all the instructions on how to start replication but most of them have the step of drop the Slave db and re-import the schema from the Master. I can't do this as I don't want to clobber my un-replicated tables.
>
> Is it possible to do what I want?

Certainly.

>
> My initial thoughts were to do an slonik "unsubscribe set" and then do a slonik "subscribe set"
>
> The problem I have is that the slon daemon on the Slave is erroring (see below), and from what I understand the "unsubscribe set" event will just be put in the event queue. I.e. The Slave will never be able to unsubscribe because it is erroring on an earlier event.
>
> I was thinking there might be a way to delete all events in the queue so it could just continue ... is this possible? I realise that after that the data is not in sync, but im planning on blowing away the replicated tables and starting again anyway...
>
> Although there might be a way to fix replication, i kind of want to know how to just do a "clean replication" from this point, as I see it as an important task.
>
> Thanks for any thoughts/opinions.
>
> Cheers
>
> Jon
>
> ----------------------------------------------------------------
>
>
>
> Error in Slave log is:
> 2013-03-06 19:56:41 HKTINFO   remoteWorkerThread_1: syncing set 1 with 47 table(s) from provider 1
> 2013-03-06 19:56:42 HKTERROR  remoteWorkerThread_1: "select "_replication".sequenceSetValue(10,1,'5002597503','291048'); " PGRES_FATAL_ERROR ERROR:  Slony-I: sequenceSetValue(): sequence 10 not found

You did not tell us which version of Slony you are running. That is 
always good to include in a problem report. In this case it is 
especially relevant since the above is probably the result from doing 
some DDL on the replica (slave) directly, without sending it through 
slonik's EXECUTE SCRIPT functionality. My bet is that someone dropped 
and recreated the sequence in question. You can find out what sequence 
that is by doing

     SELECT * FROM "_replication".sl_sequence where seq_id = 10;

If you are still running Slony 1.2 then it is important to know what 
other DDL may have been executed on the replica since doing that in 1.2 
can lead to a permanently corrupted system catalog. There is no danger 
of that in 2.0 or 2.1.

Operations harmful on replicated tables on a replica under 1.2 include 
things like create/drop index, add/drop constraint, create/drop trigger 
as well as dropping columns.

If you conclude that your system catalog must still be consistent 
because you are running 2.0 or higher or none of the above has happened, 
then you can try to fix the problem first by running the slonik command 
REPAIR CONFIG against the replica node.

     http://slony.info/documentation/2.1/stmtrepairconfig.html

If that does not fix the problem, then you can uninstall Slony using the 
UNINSTALL NODE command for all nodes.

     http://slony.info/documentation/1.2/stmtuninstallnode.html

This does not touch any data on any of the nodes. It only restores 
things so that both systems at that point are stand alone databases.

After that you simply rebuild your replication cluster like it was done 
originally. Creating nodes, paths, sets, tables and subscribing them.

You do not need to truncate anything on the replica. Slony will take 
care of that.

If you have to go through that it would be a good opportunity to upgrade 
to Slony 2.1 while at it.


Regards,
Jan

-- 
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin