Thanks Steve. I've put the dumps for the master and my two slaves at <a href="http://richyen.com/slony/">http://richyen.com/slony/</a><div><br></div><div>Thanks again,</div><div>--Richard</div><div><br></div><div><br>
<br><div class="gmail_quote">On Wed, May 11, 2011 at 1:21 PM, Steve Singer <span dir="ltr"><<a href="mailto:ssinger@ca.afilias.info">ssinger@ca.afilias.info</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div class="im">On 11-05-11 04:18 PM, Richard Yen wrote:<br>
> Thanks Jan,<br>
><br>
> I'll go ahead and do as recommended. I was actually hoping that maybe<br>
> we could scour the sl_confirm and/or sl_event tables for clues, but if<br>
> you think it's not possible to find out what happened, I guess I'll just<br>
> delete the row and move on.<br>
><br>
> Thanks again,<br>
> --Richard<br>
<br>
</div>If you can get a dump of sl_event and sl_confirm of both the master and<br>
the slave and send it to one of us we can take a look.<br>
<br>
If you happen to have slon logs of the period it can't hurt to send<br>
those as well.<br>
<div class="im"><br>
<br>
><br>
><br>
><br>
> On Wed, May 11, 2011 at 1:12 PM, Jan Wieck <<a href="mailto:JanWieck@yahoo.com">JanWieck@yahoo.com</a><br>
</div><div><div></div><div class="h5">> <mailto:<a href="mailto:JanWieck@yahoo.com">JanWieck@yahoo.com</a>>> wrote:<br>
><br>
> On 5/11/2011 2:56 PM, Richard Yen wrote:<br>
><br>
> Hello,<br>
><br>
> running slon version 2.0.6 here...<br>
><br>
><br>
> This is the first time I've heard about an operation missing. I<br>
> remember in some old version of PostgreSQL (8.1 I think) that we<br>
> experienced duplicate sl_log rows due to index corruption. I was<br>
> actually able to get a result set with a duplicate ctid. But never<br>
> was a row missing so far.<br>
><br>
> To fix your replica(s), you should be able to manually DELETE the<br>
> offending row using psql and doing<br>
><br>
> set session_replication_role to "replica";<br>
> delete from cron_lock where ...<br>
><br>
> You need to be a superuser to do so. After that SET statement, the<br>
> psql prompt will bypass the deny_access triggers and the DELETE<br>
> statement will behave exactly as if it was coming from slon.<br>
><br>
> I assume that by now, a log switch has probably destroyed all traces<br>
> that could be used to debug the problem further.<br>
><br>
><br>
> Jan<br>
><br>
><br>
><br>
> Would anyone be able to help me track down why slony missed a<br>
> DELETE?<br>
> It seems that my replication is broken as the subscribers are<br>
> trying<br>
> to process an INSERT, but a primary key is being violated. The<br>
> origin<br>
> machine does not have the offending tuple, which leads me to believe<br>
> that a DELETE was processed, but wasn't propagated to the<br>
> subscribers<br>
><br>
> From my origin machine:<br>
> my_db=# select * from cron_lock;<br>
> id | lock_until_time<br>
> -------------------+----------------------------<br>
> anonymous_marking | 2011-05-11 11:40:02.456091<br>
> (1 row)<br>
><br>
> From my subscriber machines:<br>
> my_db=# select * from cron_lock ;<br>
> id | lock_until_time<br>
> -------------------+----------------------------<br>
> anonymous_marking | 2011-05-11 10:40:02.123721<br>
> (1 row)<br>
><br>
> From the origin's sl_log_* tables:<br>
> my_db=# select * from _sac_uk.sl_log_2 where log_tableid =190;<br>
> log_origin | log_txid | log_tableid | log_actionseq |<br>
> log_cmdtype |<br>
> log_cmddata<br>
> ------------+------------+-------------+---------------+-------------+-----------------------------------------------------------------------------------------------------<br>
> 1 | 1369072247 | 190 | 239698918 | I<br>
> |<br>
> ("id","lock_until_time") values ('anonymous_marking','2011-05-11<br>
> 11:40:02.456091')<br>
> 1 | 1369182578 | 190 | 239728797 | I<br>
> |<br>
> ("id","lock_until_time") values<br>
> ('<a href="http://process_past_due_pm_assignments.pl" target="_blank">process_past_due_pm_assignments.pl</a><br>
> <<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>><br>
> <<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>>','2011-05-11<br>
> 11:00:23.944101')<br>
><br>
> 1 | 1369182587 | 190 | 239728806 | D<br>
> |<br>
> "id"='<a href="http://process_past_due_pm_assignments.pl" target="_blank">process_past_due_pm_assignments.pl</a><br>
> <<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>><br>
> <<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>>'<br>
><br>
> 1 | 1369182626 | 190 | 239728830 | I<br>
> |<br>
> ("id","lock_until_time") values<br>
> ('<a href="http://process_past_due_pm_assignments.pl" target="_blank">process_past_due_pm_assignments.pl</a><br>
> <<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>><br>
> <<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>>','2011-05-11<br>
><br>
> 11:00:24.525818') 1 | 1369182671 | 190 |<br>
> 239728833<br>
> | D | "id"='<a href="http://process_past_due_pm_assignments.pl" target="_blank">process_past_due_pm_assignments.pl</a><br>
> <<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>><br>
> <<a href="http://process_past_due_pm_assignments.pl" target="_blank">http://process_past_due_pm_assignments.pl</a>>'<br>
><br>
> (5 rows)<br>
><br>
> my_db=# select * from _sac_uk.sl_log_1 where log_tableid =190;<br>
> log_origin | log_txid | log_tableid | log_actionseq |<br>
> log_cmdtype |<br>
> log_cmddata<br>
> ------------+------------+-------------+---------------+-------------+------------------------------------------------------------------------------------<br>
> 1 | 1369393174 | 190 | 239789790 | D<br>
> |<br>
> "id"='anonymous_marking'<br>
> 1 | 1369403276 | 190 | 239793047 | I<br>
> |<br>
> ("id","lock_until_time") values ('anonymous_marking','2011-05-11<br>
> 12:40:02.970433')(2 rows)<br>
><br>
> On the subscriber logs:May 11 11:54:40 uk-sdb2 postgres[30851]:<br>
> [926-1]<br>
> 2011-05-11 11:54:40.755 PDT [user=slony,db=my_db 10.1.0.149(47318)<br>
> PID:30851 XID:1509911291]ERROR: duplicate key value violates unique<br>
> constraint "cron_lock_pkey"<br>
> May 11 11:54:40 uk-sdb2 postgres[30851]: [926-2] 2011-05-11<br>
> 11:54:40.755<br>
> PDT [user=slony,db=my_db 10.1.0.149(47318) PID:30851<br>
> XID:1509911291]STATEMENT: update only "public"."m_user" set<br>
> "last_login"='2011-05-11 10:40:02.429308' where "id"='2459339';<br>
><br>
> Any help would be much appreciated.<br>
> --Richard<br>
><br>
><br>
><br>
> _______________________________________________<br>
> Slony1-general mailing list<br>
> <a href="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</a><br>
</div></div>> <mailto:<a href="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</a>><br>
<div><div></div><div class="h5">> <a href="http://lists.slony.info/mailman/listinfo/slony1-general" target="_blank">http://lists.slony.info/mailman/listinfo/slony1-general</a><br>
><br>
><br>
><br>
> --<br>
> Anyone who trades liberty for security deserves neither<br>
> liberty nor security. -- Benjamin Franklin<br>
><br>
><br>
><br>
><br>
> _______________________________________________<br>
> Slony1-general mailing list<br>
> <a href="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</a><br>
> <a href="http://lists.slony.info/mailman/listinfo/slony1-general" target="_blank">http://lists.slony.info/mailman/listinfo/slony1-general</a><br>
<br>
_______________________________________________<br>
Slony1-general mailing list<br>
<a href="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.info</a><br>
<a href="http://lists.slony.info/mailman/listinfo/slony1-general" target="_blank">http://lists.slony.info/mailman/listinfo/slony1-general</a><br>
</div></div></blockquote></div><br></div>