One concern in implementing Slony as PostgreSQL Replication
System is that table sl_event easily growing to a
huge number of record. So, I keep monitor its size on weekly basis.
Its size ever reaches more than a million record.
After doing some troubleshoot, I have found that it is caused by
some inactive replication node which does not confirm the changes
message conveyed by the event. By re-activating slony daemon on the
nodes, the problem goes away.
How to find out which nodes inactive? This is my query (assuimng
the slony cluster name is clstr):
SELECT t.con_received, t.ts FROM (
SELECT con_received, MAX(con_timestamp) ts
FROM
_clstr.sl_confirm GROUP BY
con_received
) t ORDER BY t.ts;
Following is example of the query outcome:
con_received | ts
————–+—————————-
24 | 2011-07-11 08:49:18.004292
26 | 2011-07-18 05:59:33.483955
30 | 2011-07-18 10:31:55.235895
31 | 2011-07-18 12:26:35.889352
22 | 2011-07-18 12:27:02.932156
21 | 2011-07-18 12:27:21.21836
15 | 2011-07-18 12:27:23.298656
18 | 2011-07-18 12:27:23.701799
17 | 2011-07-18 12:27:27.052192
28 | 2011-07-18 13:43:19.826342
11 | 2011-07-18 13:58:25.891667
1 | 2011-07-18 13:58:37.566528
29 | 2011-07-18 13:58:47.321221
16 | 2011-07-18 14:01:08.206147
13 | 2011-07-18 14:01:10.92797
19 | 2011-07-18 14:02:02.476146
14 | 2011-07-18 14:02:02.51104
20 | 2011-07-18 14:02:05.63391
27 | 2011-07-18 14:14:55.750242
It tells us that node 24 has been inactive for 7 days relative to
other nodes.
Permalink