<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>
<!--
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:Arial;
color:windowtext;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
/* List Definitions */
@list l0
{mso-list-id:892934630;
mso-list-type:hybrid;
mso-list-template-ids:1835954610 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}
@list l0:level1
{mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Hello list,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I’ve been using Slony in production for months and
things have worked out pretty well so far. But this past week I ran into
problems when we switched from Postgres 8.2 to Postgres 8.3 (still using Slony version
1.2.13). I don’t think the error results from the version upgrade, but I
could be wrong.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>I set up the master database (query-1) and then proceeded to
set up replication to two slaves (query-2 and query-4). I kicked off the slon
processes for all three simultaneously. Replication to query-4 got started
right away and succeeded – it took 2 hours for the initial bulk copy of
data, and soon after that it was caught up with incremental changes. However,
replication to query-2 died right away. The slon process for query-2 quit
immediately and I saw the following message in the log:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>2008-08-15 13:57:01 PDT FATAL localListenThread:
"select "_stingray_cluster".cleanupNodelock(); insert into
"_stingray_cluster".sl_nodelock values ( 2, 0, "pg_catalog".pg_backend_pid());
" - ERROR: duplicate key value violates unique constraint "sl_nodelock-pkey"<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Now, I searched the interweb for solutions to this problem
and I found several suggestions, but none of them have helped so far…<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<ol style='margin-top:0in' start=1 type=1>
<li class=MsoNormal style='mso-list:l0 level1 lfo1'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>People suggest that this
happens when you try to start multiple slon processes for the same node at
the same time. I’m quite certain that this is not the case. I’ve
checked and double-checked and quadruple-checked. For what it’s
worth, remember that I’ve successfully run Slony replication to
multiple slaves for months.<o:p></o:p></span></font></li>
</ol>
<p class=MsoNormal style='margin-left:.25in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.25in;text-indent:.25in'><font size=2
face=Arial><span style='font-size:10.0pt;font-family:Arial'>Here are the ones
that are running:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='text-indent:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>25537 pts/3 S 0:00 slon -d 2
stingray_cluster host=query-1.colo.redfin.com dbname=my_db user=my_user
password=my_password<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>25541 pts/3 Sl 0:00 \_ slon
-d 2 stingray_cluster host=query-1.colo.redfin.com dbname= my_db user= my_user
password= my_password<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>25544 pts/3 S 0:00 slon -d 2
stingray_cluster host=query-4.colo.redfin.com dbname= my_db user= my_user password=
my_password<o:p></o:p></span></font></p>
<p class=MsoNormal style='text-indent:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>25545 pts/3 Sl 0:00 \_ slon
-d 2 stingray_cluster host=query-4.colo.redfin.com dbname= my_db user= my_user
password= my_password<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'> And the one that starts and then immediately
dies has query-2 as the host.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<ol style='margin-top:0in' start=2 type=1>
<li class=MsoNormal style='mso-list:l0 level1 lfo1'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Some people suggest that this
happens when a slon process dies and leaves a stale connection, a
connection so deep in the network stack that slony and postgres are not
aware. For this people suggest either waiting about two hours for some
timeout to kick in, or else having the admin kill -2 the zombie
process/connection. Apparently this happens more often if you try to run
slony across a wan.<o:p></o:p></span></font></li>
</ol>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Well, our slon processes all run on
the master node and the other nodes are right there in the same LAN. (we did
once try to run Slony across a WAN but that never worked)<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>This duplicate key issue first
happened more than 12 hours ago, and it continues to happen. Each time I try
to start a slon process for query-2, it dies right away with the same error.<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>So, I don’t think there’s
a zombie connection, but I also don’t know exactly how to check for one.
I can say that all the proc’s in the pg_stat_activity have a xact_start
that is less than an hour old.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<ol style='margin-top:0in' start=3 type=1>
<li class=MsoNormal style='mso-list:l0 level1 lfo1'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>One person mentioned having to
go into the sl_nodelock table and manually whack the bad row. I’m a
little concerned about doing this kind of surgery because I don’t
know the sl_nodelock table and therefore I don’t know what would be
the consequence of whacking a row (especially if I accidentally whack the
wrong row).<o:p></o:p></span></font></li>
</ol>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>Here’s what I see in that
table:<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>stingray_prod=# select *
from _stingray_cluster.sl_nodelock;<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> nl_nodeid | nl_conncnt |
nl_backendpid<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>-----------+------------+---------------<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> 2 | 11
| 14774<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> 2 | 15
| 20569<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> 1 | 0
| 25551<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> 4 | 16
| 25568<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'> 4 | 17
| 25570<o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face="Courier New"><span
style='font-size:10.0pt;font-family:"Courier New"'>(5 rows)</span></font><font
size=2 face=Arial><span style='font-size:10.0pt;font-family:Arial'><o:p></o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal style='margin-left:.5in'><font size=2 face=Arial><span
style='font-size:10.0pt;font-family:Arial'>What do these columns mean exactly?
Is there documentation on this table somewhere? Which one is likely to be the
offending row? Is it crazy to whack the row manually? Would I need to also
whack corresponding rows in other tables?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Any help with this would be much appreciated.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>On a related note – is there a psql command (like \d)
that I can use to list all the objects in my _<i><span style='font-style:italic'>stingray</span></i>_cluster
schema?<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>Thanks,<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'>--S<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 face=Arial><span style='font-size:10.0pt;
font-family:Arial'><o:p> </o:p></span></font></p>
</div>
</body>
</html>