Cannot add domain in ENSIM:: Failed to add site to database. DBERR: error ‘ERROR: duplicate key violates unique constraint “domain_key” ‘ in ‘ INSERT INTO siteinfo

You might get this following error while adding new doman in ensim control panel.

Failed to add site to database. DBERR: error ‘ERROR: duplicate key violates unique constraint “domain_key” ‘ in ‘ INSERT INTO siteinfo (site_id, domain, admin_user, email) VALUES (8, ‘prajith.in’ ‘prajith’, ‘xxx@domain.com’)’

To sort this error just follow following simple step:

Login to server via ssh

su postgres

Take a backup of the database…

pg_dump appldb > appldb.dump.out

Now lets edit

psql

postgres=# \l

List of databases
Name | Owner | Encoding
———–+———-+———-
appldb | root | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(4 rows)

this will list all the databases. The Ensim database is “appldb”

postgres=# \c appldb
You are now connected to database “appldb”.
appldb=# \d

This will list all the tables in the database.

You can find the table siteinfo

appldb=# SELECT * FROM siteinfo WHERE domain=’iplinth.co.uk’;
site_id | domain | email | admin_user
———+—————+—————-+————
22 | prajith.in | prajith@prajith.in | prajith
(1 row)

it will list the entry. Now to fix the issue, you need to delete that entry. Find the site_id for the domain. Here it is 22

appldb=# DELETE FROM siteinfo WHERE site_id=22;

Now quit the database

appldb=# \q

bash-3.2$ exit

Now try to add the domain from Control panel.