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