Slony-I: duplicate key value violates unique constraint "sl_nodelock-pkey", part 1

Slony-I をアップグレードしたら、エラーが発生するようになった。

2012-02-22 12:15:13.37963 FATAL  localListenThread: "select "_slony1".cleanupNodelock(); insert into "_slony1".sl_nodelock values (    1, 0, "pg_catalog".pg_backend_pid()); " - ERROR:  duplicate key value violates unique constraint "sl_nodelock-pkey"

slon 再起動でも復旧しない。公式ドキュメントによると、

• Slony-I FAQ: I was starting a slon, and got the following "FATAL" messages in its logs. What's up???

This error message is typically a sign that you have started up a second slon process for a given node. The slon asks the obvious question: "Do you already have a slon running against this node?"

Supposing you experience some sort of network outage, the connection between slon and database may fail, and the slon may figure this out long before the PostgreSQL instance it was connected to does. The result is that there will be some number of idle connections left on the database server, which won't be closed out until TCP/IP timeouts complete, which seems to normally take about two hours. For that two hour period, the slon will try to connect, over and over, and will get the above fatal message, over and over.

とのことだが、今回の場合には二重起動もネットワークエラーもあり得ない。サーバーを再起動しても直らないし。エラーになる前にやったことは、

1. slave: stop slon
2. slave: upgrade Slony-I 2.0.6 -> 2.0.7
3. slave: reboot (start slon)
4. master: stop slon
5. master: upgrade Slony-I 2.0.6 -> 2.0.7
6. master: reboot (start slon)

で、master 側がエラーになる。今まで 2 回起こったので、再現性はあるような気がする。いつか調べるハメになるかも知れないが、まずは復旧させないと。

# psql -U postgres testdb
testdb=# select * from _slony1.sl_nodelock;
 nl_nodeid | nl_conncnt | nl_backendpid
-----------+------------+---------------
         2 |        184 |          2608
         2 |        185 |          2593
         2 |        186 |         11678
         1 |          0 |          2583
         2 |        183 |          2606
(5 rows)

testdb=# delete from _slony1.sl_nodelock where nl_nodeid = 1 and nl_conncnt = 0;
DELETE 1
testdb=# \q

sl_nodelock から duplicate しているレコードを削除して、slon を再起動すれば復旧する。

---

2012-06-27 追記

いつか調べるハメになるかも知れないが、まずは復旧させないと。

調べた。

• piyolian: Slony-I: duplicate key value violates unique constraint "sl_nodelock-pkey", part 2