I'd truncate it on both ends then run a delete without where on the master and see if that fixed it.
On Tue, Jun 1, 2010 at 11:56 AM, Jason Culverhouse <[email protected]> wrote: > Steve, > I tracked it down to a TRUNCATE TABLE on company_zip_updated. > This table is just a transient record keeping table, items get inserted, > processed and deleted. > > The question is, would a drop set on the set that contains the table repair > the problem? > i.e, will this remove the statements that update that table from the log > since the SET is removed? > OR > should I consider the EXECUTE SCRIPT ONLY ON to remove the offending rows in > that table on the replicas? > > I goal is to avoid replicating the whole database again. > Jason > > On Jun 1, 2010, at 9:48 AM, Steve Singer wrote: > >> Jason Culverhouse wrote: >>> I have a problem where my replication is hung, I don't really know where >>> to start.... This table is in set "38", most everything is in set "1", it >>> looks like set 38 isn't "caught up" in the ordering. >>> Any Idea's on how to repair this? Is this problem because the set's are >>> not merged? Can I merge the sets? Version is slony1-1.2.15 on postgres 8.3 >> >> Your problem appears to be that a row being inserted into your replica is >> already on your replica so it is failing. This is bad and I am very curious >> to know how your cluster got into this state. >> >> Have you mean making any schema changes to this cluster. Was EXECUTE SCRIPT >> used or was it not used? >> >> Have you tried making any data changes through EXECUTE SCRIPT (ie would >> someone have run a EXECUTE SCRIPT (..ONLY ON ..) the replica that inserst >> data into your company_zip_updated table). >> >> Options for fixing it include >> >> -Dropping this node from replication and rebuilding the replica. Depending >> on the size of your data this might be the simplest. >> >> -Deleting the offending row of company_zip_updated from the replica using >> EXECUTE SCRIPT ONLY ON and letting replication progress. I will warn you >> that this is treating the visible symptom only and does not address the >> cause. This could make things worse for you (it is hard to say without >> knowing what the actual problem is) >> >> >> >> >> >> >>> 2010-06-01 09:11:53 PDT DEBUG2 syncThread: new sl_action_seq 1 - SYNC 574847 >>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_50: forward confirm >>> 60,574847 received by 50 >>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_40: SYNC 33381140 >>> processing >>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_40: syncing set 38 with 4 >>> table(s) from provider 40 >>> 2010-06-01 09:11:53 PDT DEBUG2 ssy_action_list length: 0 >>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_40: syncing set 1 with >>> 139 table(s) from provider 40 >>> 2010-06-01 09:11:53 PDT DEBUG2 ssy_action_list length: 0 >>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_40: current local >>> log_status is 0 >>> 2010-06-01 09:11:53 PDT DEBUG2 remoteWorkerThread_40_40: current remote >>> log_status = 1 >>> 2010-06-01 09:11:53 PDT DEBUG2 remoteHelperThread_40_40: 0.008 seconds >>> delay for first row >>> 2010-06-01 09:11:53 PDT ERROR remoteWorkerThread_40: "update only >>> "public"."review" set zipcode_id='80203' where id='1026409'; >>> update only "public"."review" set zipcode_id='80203' where id='1026401'; >>> update only "public"."review" set zipcode_id='80203' where id='1008795'; >>> update only "public"."review" set zipcode_id='80203' where id='1008048'; >>> update only "public"."review" set zipcode_id='80203' where id='1007445'; >>> insert into "public"."company_zip_updated" (id,company_id,zipcode_id) >>> values ('1205','30149282','80122'); >>> update only "public"."merchant" set zipcode_id='80203' where id='905955'; >>> update only "public"."advertisement" set zipcode_id='80203' where >>> id='1467390'; >>> update only "public"."advertisement" set zipcode_id='80203' where >>> id='1375973'; >>> update only "public"."advertisement" set zipcode_id='80203' where >>> id='1389545'; >>> " ERROR: duplicate key value violates unique constraint >>> "company_zip_updated_company_id_key" >>> - qualification was: where log_origin = 40 and ( ( >>> log_tableid in (158,159,160,161) >>> and (log_xid < '622094999') >>> and (log_xid >= '622094987') >>> ) or ( >>> log_tableid in >>> (124,121,117,118,6,22,23,25,26,31,32,43,44,45,46,55,56,57,59,78,115,79,106,114,116,3,4,5,9,11,12,13,14,15,1,7,2,8,16,17,27,28,29,33,34,36,39,40,41,42,47,48,49,50,51,52,53,54,61,63,64,65,69,71,72,74,75,76,77,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,107,108,109,110,111,112,113,119,120,123,122,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157) >>> and (log_xid < '622094999') >>> and (log_xid >= '622094987') >>> ) ) >>> 2010-06-01 09:11:53 PDT DEBUG2 remoteHelperThread_40_40: 0.016 seconds >>> until close cursor >>> 2010-06-01 09:11:53 PDT DEBUG2 remoteHelperThread_40_40: inserts=1 >>> updates=79 deletes=0 >>> 2010-06-01 09:11:53 PDT ERROR remoteWorkerThread_40: SYNC aborted >>> Here is the table definition >>> Table "public.company_zip_updated" >>> Column | Type | Modifiers >>> >>> ------------+---------+------------------------------------------------------------------ >>> id | integer | not null default >>> nextval('company_zip_updated_id_seq'::regclass) >>> company_id | integer | not null >>> zipcode_id | integer | not null >>> Indexes: >>> "company_zip_updated_pkey" PRIMARY KEY, btree (id) >>> "company_zip_updated_company_id_key" UNIQUE, btree (company_id) >>> Foreign-key constraints: >>> "company_zip_updated_company_id_fkey" FOREIGN KEY (company_id) >>> REFERENCES company(id) ON DELETE CASCADE >>> "company_zip_updated_zipcode_id_fkey" FOREIGN KEY (zipcode_id) >>> REFERENCES zipcode(zip) ON DELETE CASCADE >>> Triggers: >>> _mc_cluster_logtrigger_161 AFTER INSERT OR DELETE OR UPDATE ON >>> company_zip_updated FOR EACH ROW EXECUTE PROCEDURE >>> _mc_cluster.logtrigger('_mc_cluster', '161', 'kvv') >>> _______________________________________________ >>> Slony1-general mailing list >>> [email protected] >>> http://lists.slony.info/mailman/listinfo/slony1-general >> >> >> -- >> Steve Singer >> Afilias Canada >> Data Services Developer >> 416-673-1142 > > _______________________________________________ > Slony1-general mailing list > [email protected] > http://lists.slony.info/mailman/listinfo/slony1-general > -- When fascism comes to America, it will be intolerance sold as diversity. _______________________________________________ Slony1-general mailing list [email protected] http://lists.slony.info/mailman/listinfo/slony1-general
