Kannel with Postgresql DLR storage

Kannel with Postgresql DLR storage.md

Kannel with Postgresql DLR storage

Create the dlr table in PostgreSQL

• Must add WITH OIDS to the CREATE statment since kannel expects old PostgreSQL behaviour.
• Optionally add extra column created_at to keep track of old DLR. DLR entries are automatically removed by kannel once the final dlr is received from the SMSC. In cases where this message is not received, the DLR will not be removed. A cron job can be setup to remove old DLR by comparing the current time to created_at.

CREATE TABLE dlr (
    smsc        VARCHAR(48),
    ts          VARCHAR(48),
    destination VARCHAR(48),
    source      VARCHAR(48),
    service     VARCHAR(48),
    url         VARCHAR(255),
    mask        INTEGER,
    status      INTEGER,
    boxc        VARCHAR(48),
    created_at timestamp  DEFAULT now()
) WITH OIDS;

Update kannel.conf

Set dlr-storage to pgsql in the core group

group = core
dlr-storage = pgsql

Add the pgsql-connection group with correct matching db details.

group = pgsql-connection
id = mydlr
host = localhost
username = foo
password = bar
database = db_name
max-connections = 2

Add the dlr-db group with correct matching db details.

group = dlr-db
id = mydlr
table = dlr
field-smsc = smsc
field-timestamp = ts
field-destination = destination
field-source = source
field-service = service
field-url = url
field-mask = mask
field-status = status
field-boxc-id = boxc

NOTES

The above assumes the kannel binary was compiled with postgresql support enabled.