Wed May 11 14:26:12 PDT 2011
- Previous message: [Slony1-general] Slony seems to have missed an event???
- Next message: [Slony1-general] Slony seems to have missed an event???
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
On 11-05-11 04:29 PM, Richard Yen wrote: > Thanks Steve. I've put the dumps for the master and my two slaves at > http://richyen.com/slony/ As Jan suspected there isn't really anything still left in the logs about events from before the failed insert. When should the row have been deleted from cron_lock? immediately before or sometime before? Did anything else happen around this time? (server restarts, moving masters etc?) > > Thanks again, > --Richard > > > > On Wed, May 11, 2011 at 1:21 PM, Steve Singer <ssinger at ca.afilias.info > <mailto:ssinger at ca.afilias.info>> wrote: > > On 11-05-11 04:18 PM, Richard Yen wrote: > > 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 > > If you can get a dump of sl_event and sl_confirm of both the master and > the slave and send it to one of us we can take a look. > > If you happen to have slon logs of the period it can't hurt to send > those as well. > > > > > > > > > > On Wed, May 11, 2011 at 1:12 PM, Jan Wieck <JanWieck at yahoo.com > <mailto:JanWieck at yahoo.com> > > <mailto:JanWieck at yahoo.com <mailto:JanWieck at yahoo.com>>> 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> > > <http://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> > > <http://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> > > <http://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> > > <http://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 > > Slony1-general at lists.slony.info > <mailto:Slony1-general at lists.slony.info> > > <mailto:Slony1-general at lists.slony.info > <mailto:Slony1-general at lists.slony.info>> > > 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 > > Slony1-general at lists.slony.info > <mailto:Slony1-general at lists.slony.info> > > http://lists.slony.info/mailman/listinfo/slony1-general > > _______________________________________________ > Slony1-general mailing list > Slony1-general at lists.slony.info <mailto:Slony1-general at lists.slony.info> > http://lists.slony.info/mailman/listinfo/slony1-general > >
- Previous message: [Slony1-general] Slony seems to have missed an event???
- Next message: [Slony1-general] Slony seems to have missed an event???
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
More information about the Slony1-general mailing list