Configure Ejabberd chat server to use PostgreSQL

In order to make data persistent with in any application, we can link it to a PostgreSQL relational database.

  1. To change the default to use a postgres database we first need to create a new postgresql database on the server
1
2
3

huazhang@zhoutekiMacBook-Air:~/ejabberd-15.09/bin$ psql
psql (9.4.4)
huazhang=# run ./createdb ejabberd
  1. Create the tables using the supplied script
1

huazhang-# ./psql ejabberd < ~/ejabberd-15.09/lib/ejabberd-15.09/priv/sql/pg.sql
  1. Add a user for the database
1
2
3
4

~/ejabberd-15.09/bin$ createuser -P -s -e admin  
Enter password for new role: root123  
Enter it again: root123  
CREATE ROLE testadmin PASSWORD 'md55ebf4663bc3108d036c91bc4bbcfd599' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
  1. Configure ODBC options in ejabberd.cfg:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

Scroll down to the section headed Database setup.  
edit the following, remove the %% commenting  
##  
## PostgreSQL server:  
##  
odbc_type: pgsql  
odbc_server: "localhost"  
odbc_database: "ejabberd"  
odbc_username: "admin"  
odbc_password: "root123"
  1. Add _odbc to modules you wish to use the odbc database, and store messages into archive table
1
2
3
4
5
6
7
8
9

e.g. mod_offline_odbc instead of mod_offline.  
full list in ejabberd user guide   
## Modules enabled in all ejabberd virtual hosts.  
##    
modules:    
  #By default mod_mam does not store messages. If you like to store messages, simply add this mod_mam   option:  
  mod_mam:  
      default: always     
      db_type: odbc
  1. Under authentication comment out internal authentication:
1
2

##  auth_method:  internal  
Then un comment auth_method: odbc

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

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

1

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

  1. Confirm registered user and status in web admin:
    http://serverIP:5280/admin/

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

Reference

Getting started with ejabberd
[Initial setup and install of PostgreSQL Database] (http://stackoverflow.com/questions/9753710/setup-ejabberd-with-postgresql)