Jabber server + PostgreSQL == good

13 Oct 2005

Recently I needed to get Jabber 1.4.4 to store rosters and users and presence and so forth to a PostgreSQL database.

  • I modified the SQL scripts to create tables in PostgreSQL; script is here. Note that there are some extra sequences and id columns in there so that ActiveRecord can its object-relational mapping magic
  • I'm not too familiar with PostgreSQL date formats, so I just put storetime, delivertime, and lastmodified columns in TEXT format

I modified the sample jabber.xml so now the queries use PostgreSQL CASE and COALESCE statements vs the MySQL IF statement; sample jabber.xml is here. Just scroll down to the xdb element to see the new queries.

For what it's worth, this setup works fine with the Ruby client library for Jabber. Not surprising, but, cool to know anyway. We might end up adding some load testing cases to the Jabber4R project.