Thursday 9 December 2010

Configure Ejabberd chat server to use PostgreSQL ODBC

In order to make data persistent with in any application you can link it to a PostgreSQL relational database.
As default Ejabberd messaging server uses it's internal Mnesia database.

1. To change the default to use a postgres database we first need to create a new postgresql database on the server (this assumes you have PgSQL installed and configured)


run ./createdb ejabberd

2. Create the tables using the supplied script

./psql ejabberd < /opt/ejabberd-2.1.5/lib/ejabberd-2.1.5/priv/odbc/pg.sql

3. Add a user for the database

./createuser ejabberd

4. Configure ODBC options in ejabberd.cfg:

Scroll down to the section headed Database setup.

edit the following, remove the %% commenting


{odbc_server, {pgsql, "127.0.0.1", "ejabberd", "ejabberd", "yourpassword"}}.
5. Add _odbc to modules you wish to use the odbc database

e.g. mod_offline_odbc instead of mod_offline.

full list in ejabberd user guide

6. Under authentication comment out internal authentication :

%%{auth_method, internal}.

Then un comment {auth_method, odbc}.

7. Register a new user and appear online using PSI client or similar application

8. Add a user to allowed admin access control list in ejabberd.cfg


{acl, admin, {user, "username", "server"}}.

9. Confirm registered user and status in web admin:


http://serverIP:5280/admin/

10. Connect to database using PgAdmin to view tables with data and confirm ejabberd is now using PostgreSQL

4 comments:

  1. Hi, I have a little problem on installing postgresql module for ejabberd, the ./build.sh not return anything without generation of bean files in. /ebin. someone has an idea?

    ReplyDelete
    Replies
    1. Have you installed erlang-tools before compiling build.sh?

      Delete
  2. Hey, This is a nice post and works pretty good for me. But I can't see the table in my postgres database which saves the chat messages of the users. Could you please tell me which table is that and how can I found that?

    ReplyDelete
    Replies
    1. Chat in ejabberd are not saved by default, until you configure it do so. However missed chat are stored in offline table

      Delete