PostgreSQL - Slony > FATAL ERROR: duplicate key value violates unique constraint "sl_nodelock-pkey"

Question

Just posting this here and might help the others:

I got this error when we had an outage a while ago, and tried to restart the slon replication service as the replication is NOT really replicating the data from MASTER to SLAVE:

FATAL  localListenThread: "select "_MY_DATABASE_copy".cleanupNodelock(); insert into "_MY_DATABASE_copy".sl_nodelock values (    1, 0, "pg_catalog".pg_backend_pid()); " - ERROR:  duplicate key value violates unique constraint "sl_nodelock-pkey"
DEBUG2 slon_abort() from pid=xxxx
DEBUG1 slon: shutdown requested

Answer 1

Ok so this is how I fix it, but this doesn't guarantee it will work for everyone:

• Check the slon process. "~# ps aux | grep slon".

• Then kill all process running the slon replicatin service that will look something like this.

  ~# ps aux | grep slon root 8321 0.0 0.0 6528 1568 pts/2 S+ 10:35 0:00 slon MY_SCHEMA dbname=MY_DATABASE user=postgres host=169.1.1.1 password=password

Normally it has two process running in the background, and this is normal, so we have to kill those process.

~# kill -9 8321

Then run your slon replicatin service script again.

---