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