Have you tried test_slony_state.pl or the dbi variant? That tool will spot quite a number of common problems. It sounds like you already restarted both your slons. Is the slon on your origin running? I'm assuming that you haven't done anything like, say modifying millions of rows in a single statement very recently (or that you'd mention it if you had). And that you've done basics like VACUUM ANALYZE on the origin and subscriber.
Andrew On 6/12/07, James Tucker <[EMAIL PROTECTED]> wrote: > Apologies all, sent accidently before I added details. > > I'm running RHEL3 ES with Postgresql 8.0.7 and Slony 1.2-2.1. I have 1 > tuple and 1 page in pg_listener on both nodes in question. sl_log_1 is > continiously growing on the origin however not changing at all on the > subsriber (sitting at 680 records). > > The "idle in transaction" has various locks on the subscriber table. > However I'd imagine this is normal > > relation | database | transaction | pid | mode | granted > -----------+----------+-------------+-------+-----------------+--------- > 1259 | 23773257 | | 17530 | AccessShareLock | t > | | 538907354 | 17530 | ExclusiveLock | t > 113903009 | 23773257 | | 17530 | AccessShareLock | t > 16672 | 23773257 | | 17530 | AccessShareLock | t > 113903128 | 23773257 | | 17530 | AccessShareLock | t > 113902994 | 23773257 | | 17530 | AccessShareLock | t > > These are details for various tables on origin node. > relname | relpages | reltuples > ------------+----------+----------- > sl_confirm | 2 | 62 > sl_log_1 | 1551 | 30435 > sl_log_2 | 0 | 0 > sl_seqlog | 25 | 3533 > (4 rows) > > and subscriber > relname | relpages | reltuples > ------------+----------+----------- > sl_confirm | 7 | 2 > sl_log_1 | 47 | 681 > sl_log_2 | 0 | 0 > sl_seqlog | 8 | 1048 > (4 rows) > > > This is my sl_listen table on the origin. > li_origin | li_provider | li_receiver > -----------+-------------+------------- > 2 | 2 | 1 > 1 | 1 | 2 > > > > > > ---------- Forwarded message ---------- > From: James Tucker <[EMAIL PROTECTED]> > Date: Jun 12, 2007 8:24 PM > Subject: idle in transaction on subscriber > To: [email protected] > > Hi Folks, > > I've recently configured slony to replicate our production databases. One > of the databases has ceased replicating and I can see events queueing in > sl_status. On the subscriber node I have one connection "idle in > transaction" to postgresql running as the slony user. I see no errors, > warnings or even notices in the logs, just debug level sync events which > appear to be going through ok. > > I've tried restarting the slon processes however the idle in transaction > connection just keeps reappearing, I am unable to restart the postgresql > service on either node because of availibity issues. Any hints on where to > go from here would be very welcome. > > Cheers > James > > _______________________________________________ > Slony1-general mailing list > [email protected] > http://gborg.postgresql.org/mailman/listinfo/slony1-general > > _______________________________________________ Slony1-general mailing list [email protected] http://gborg.postgresql.org/mailman/listinfo/slony1-general
