Configuring Spacewalk's jabberd to use a PostgreSQL backend
This post details how to switch the database backed used by jabberd on a Spacewalk
server to use a PostgreSQL Database so that osa-dispatcher becomes stable.
By default, jabberd when installed for use by Spacewalk uses a Berkeley DB to
store session data. This is notoriously flaky which results in osad clients
dropping their connection and often requires an administrator to regularly stop
the jabberd service, remove the corrupt database and restart it.
The solution is to reconfigure jabberd to use a better database to store its
session information.
The following process will switch jabberd to use PostgreSQL instead. It assumes
that you already have PostgreSQL up and running as the database backend
for Spacewalk itself either locally on the same server as jabberd or remotely
on another server.
Create the PostgreSQL user and database
Run the following commands to create a new PostgreSQL user and a new PostgreSQL
database with the required jabberd schema in place and assign ownership of
the database to the newly created user:
|
|
You will be prompted twice for a password for the new user.
Once you have provided a password, continue to creating the database:
|
|
This make take a few moments to complete. You can verify the database was created
correctly by running psql -l and checking for the following output:
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-----------+----------+-------------+-------------+-----------------------
jabberd2 | jabberd2 | UTF8 | en_AU.UTF-8 | en_AU.UTF-8 |
Edit the PostgreSQL configuration to allow connections
By default, PostgreSQL does not allow connections to new databases. You need to
modify the pg_hba.conf file to allow connections for the newly created user to
access the newly created database.
Open /var/lib/pgsql/data/pg_hba.conf and add the following line:
local jabberd2 jabberd2 md5
This will allow you to connect to the jabberd2 database as the jabberd2 user
from the local machine using the password you provided when you created the user.
If you have a standalone PostgreSQL and need to provide remote connectivity for
your jabberd services, you need to add an additional line.
host jabberd2 jabberd2 192.168.1.100/32 md5
This will permit the jabberd2 user to connect to the jabberd2 database from
192.168.1.100. Change the IP address to match your jabberd server.
If you’re using IPv6, you may need to provide another line for the IPv6 address
for your jabberd server:
host jabberd2 jabberd2 2001:db8:a0b:12f0::1/128 md5
Once you’ve updated pg_hba.conf, restart PostgreSQL to allow access:
Using service on OL6:
|
|
Using systemctl on OL7:
|
|
Import the base schema into the PostgreSQL database
This can either be run locally if jabberd and PostgreSQL are on the same
server or remotely from the Spacewalk/jabberd server. You need to run this
command on the server that has the db-setup.pgsql file.
Local PostgreSQL instance
|
|
Remote PostgreSQL instance
|
|
Replace postgresql.fqdn with the fully-qualified domain name of your
PostgreSQL server.
You will be prompted for the jabberd2 user’s password. Once you provide the
password, you should see the following output as the schema is created:
CREATE SEQUENCE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "authreg_pkey" for table "authreg"
CREATE TABLE
CREATE INDEX
CREATE INDEX
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "active_pkey" for table "active"
CREATE TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "logout_pkey" for table "logout"
CREATE TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roster-items_pkey" for table "roster-items"
CREATE TABLE
CREATE INDEX
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "roster-groups_pkey" for table "roster-groups"
CREATE TABLE
CREATE INDEX
CREATE INDEX
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "vcard_pkey" for table "vcard"
CREATE TABLE
CREATE TABLE
CREATE INDEX
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "private_pkey" for table "private"
CREATE TABLE
CREATE INDEX
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "motd-message_pkey" for table "motd-message"
CREATE TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "motd-times_pkey" for table "motd-times"
CREATE TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "privacy-default_pkey" for table "privacy-default"
CREATE TABLE
CREATE TABLE
CREATE INDEX
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "vacation-settings_pkey" for table "vacation-settings"
CREATE TABLE
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "status_pkey" for table "status"
CREATE TABLE
Stop jabberd and osa-dispatcher
Use service on OL6:
|
|
Or systemctl on OL7:
|
|
Edit the jabberd configuration
Local PostgreSQL instance
This configuration is used when PostgreSQL and jabberd are on
the same server.
Edit /etc/jabberd/sm.xml with the following changes using the password you
provided when creating the jabberd2 user for PostgreSQL:
|
|
Edit /etc/jabberd/c2s.xml and make the following changes:
|
|
If your Spacewalk Server has SElinux enabled in ENFORCING mode, you will need
to allow the jabberd processes to connect to PostgreSQL via the socket.
To do so, run the following command:
# setsebool -P daemons_enable_cluster_mode 1
Remote PostgreSQL instance
This configuration is used when PostgreSQL is on another server.
Edit /etc/jabberd/sm.xml with the following changes using the password you
provided when creating the jabberd2 user for PostgreSQL. Remember to change
postgresql.fqdn to the real fully-qualified domain name of your PostgreSQL
server.
If you want to remove the overhead of hostname resolution, you can replace the
host parameter with hostaddr and provide the IP address instead.
|
|
Edit /etc/jabberd/c2s.xml and make the following changes:
|
|
Start jabberd and osa-dispatcher again
Using service on OL6:
|
|
Or using systemctl on OL7:
|
|
Ensure that PostgreSQL is being used
Check /var/log/messages to make sure that jabberd uses the pgsql storage module.
|
|
Reset and restart osad
If you have already registered client servers and installed osad on those
servers, it will most likely be necessary to remove the osad-auth.conf file
and restart osad before they will work after the database has been switched.
Using service on OL6:
|
|
Using systemctl on OL7:
|
|
This will trigger the osad client on each server to re-authenticate to jabberd.