Skip to end of metadata
Go to start of metadata


This tutorial describes the configuration of Asterisk's PJSIP channel driver with the "realtime" database storage backend.  The realtime interface allows storing much of the configuration of PJSIP, such as endpoints, auths, aors and more, in a database, as opposed to the normal flat-file storage of pjsip.conf.

Installing Dependencies

For the purposes of this tutorial, we will assume a base Ubuntu x86_64 server installation, with the OpenSSH server and LAMP server options, and that Asterisk will use its ODBC connector to reach a back-end MySQL database.

Beyond the normal packages needed to install Asterisk 12 on such a server (build-essential, libncurses5-dev, uuid-dev, libjansson-dev, libxml2-dev, libsqlite3-dev) as well as the Installation of pjproject, you will need to install the following packages:

  • unixodbc and unixodbc-dev
    • ODBC and the development packages for building against ODBC
  • libmyodbc
    • The ODBC to MySQL interface package
  • python-dev and python-pip
    • The Python development package and the pip package to allow installation of Alembic
  • python-mysqldb
    • The Python interface to MySQL, which will be used by Alembic to generate the database tables

So, from the CLI, perform:

# apt-get install unixodbc unixodbc-dev libmyodbc python-dev python-pip python-mysqldb

Once these packages are installed, check your Asterisk installation's make menuconfig tool to make sure that the res_config_odbc and res_odbc resource modules, as well as the res_pjsip_xxx modules are selected for installation.  If they are, then go through the normal Asterisk installation process: ./configure; make; make install

And, if this is your first installation of Asterisk, be sure to install the sample files: make samples

Creating the MySQL Database

Use the mysqladmin tool to create the database that we'll use to store the configuration.  From the Linux CLI, perform:

# mysqladmin -u root -p create asterisk

This will prompt you for your MySQL database password and then create a database named asterisk that we'll use to store our PJSIP configuration.

Installing and Using Alembic

Alembic is a full database migration tool, with support for upgrading the schemas of existing databases, versioning of schemas, creation of new tables and databases, and a whole lot more.  A good guide on using Alembic with Asterisk can be found on the Managing Realtime Databases with Alembic wiki page.  A shorter discussion of the steps necessary to prep your database will follow.

First, install Alembic:

# pip install alembic

Then, move to the Asterisk source directory containing the Alembic scripts:

# cd contrib/ast-db-manage/

Next, edit the config.ini.sample file and change the sqlalchemy.url option, e.g.

sqlalchemy.url = mysql://root:[email protected]/asterisk

such that the URL matches the username and password required to access your database.

Then rename the config.ini.sample file to config.ini

# cp config.ini.sample config.ini

Finally, use Alembic to setup the database tables:

# alembic -c config.ini upgrade head

You'll see something similar to:

# alembic -c config.ini upgrade head
INFO  [alembic.migration] Context impl MySQLImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
INFO  [alembic.migration] Running upgrade None -> 4da0c5f79a9c, Create tables
INFO  [alembic.migration] Running upgrade 4da0c5f79a9c -> 43956d550a44, Add tables for pjsip

You can then connect to MySQL to see that the tables were created:

# mysql -u root -p -D asterisk

mysql> show tables;
| Tables_in_asterisk |
| alembic_version    |
| iaxfriends         |
| meetme             |
| musiconhold        |
| ps_aors            |
| ps_auths           |
| ps_contacts        |
| ps_domain_aliases  |
| ps_endpoint_id_ips |
| ps_endpoints       |
| sippeers           |
| voicemail          |
12 rows in set (0.00 sec)
mysql> quit

Configuring ODBC

Now that we have our MySQL database created and populated, we'll need to setup ODBC and Asterisk's ODBC resource to access the database.  First, we'll tell ODBC how to connect to MySQL.  To do this, we'll edit the /etc/odbcinst.ini configuration file.  Your file should look something like:


Next, we'll tell ODBC which MySQL database to use.  To do this, we'll edit the /etc/odbc.ini configuration file and create a database handle called asterisk.  Your file should look something like:


Take care to use your database access UserName and Password, and not necessarily what's defined in this example.

Now, we need to configure Asterisk's ODBC resource, res_odbc, to connect to the ODBC asterisk database handle that we just created.  res_odbc is configured using the /etc/asterisk/res_odbc.conf configuration file.  There, you'll want:


Again, take care to use the proper username and password.

Now, you can start Asterisk and you can check its connection to your "asterisk" MySQL database using the "asterisk" res_odbc connector to ODBC.  You can do this by executing "odbc show" from the Asterisk CLI.  If everything went well, you'll see:

# asterisk -vvvvc
*CLI> odbc show
ODBC DSN Settings
 Name: asterisk
 DSN:    asterisk
 Last connection attempt: 1969-12-31 18:00:00
 Pooled: No
 Connected: Yes

Connecting PJSIP Sorcery to the Realtime Database

The PJSIP stack uses a new data abstraction layer in Asterisk called sorcery. Sorcery lets a user build a hierarchical layer of data sources for Asterisk to use when it retrieves, updates, creates, or destroys data that it interacts with. This tutorial focuses on getting PJSIP's configuration stored in a realtime back-end; the rest of the details of sorcery are beyond the scope of this page.

PJSIP bases its configuration on types of objects.  For more information about these types of objects, please refer to the Configuring res_pjsip wiki page.  In this case, we have a total of five objects we need to configure in Sorcery:

  • endpoint
  • auth
  • aor
  • domain
  • identify

We'll also configure the contact object, though we don't need it for this example.

Sorcery is configured using the /etc/asterisk/sorcery.conf configuration file.  So, we need to add the following lines to the file:


The items use the following nomenclature:

{object_type} = {sorcery_wizard_name},{wizard_arguments}

In our case, the sorcery_wizard_name is realtime, and the wizard_arguments are the name of the database connector ("asterisk") to associate with our object types. Note that the "identify" object is separated from the rest of the configuration objects. This is because this object type is provided by an optional module ( and not the main PJSIP module ( 

Optionally configuring sorcery for realtime and non-realtime data sources

If you want to configure both realtime and static configuration file lookups for PJSIP then you need to add additional lines to the sorcery config.

For example if you want to read endpoints from both realtime and static configuration:


You can swap the order to control which data source is read first.

Realtime Configuration

Since we've associated the PJSIP objects with database connector types, we now need to tell Asterisk to use a database backend with the object types, and not just the flat pjsip.conf file.  To do this, we modify the /etc/asterisk/extconfig.conf configuration file to provide these connections.

Open extconfig.conf (/etc/asterisk/extconfig.conf) and add the following lines to the 'settings' configuration section


Other tables allowed but not demonstrated in this tutorial: ps_systems, ps_globals, ps_transports, and ps_registrations.


At this point, Asterisk is nearly ready to use the tables created by alembic with PJSIP to configure endpoints, authorization, AORs, domain aliases, and endpoint identifiers.

A warning for adventurous types:


Sorcery.conf allows you to try to configure other PJSIP objects such as transport using realtime and it currently won't stop you from doing so. However, some of these object types should not be used with realtime and this can lead to errant behavior.

Asterisk Startup Configuration

Now, we need to configure Asterisk to load its ODBC driver at an early stage of startup, so that it's available when any other modules might need to take advantage of it.  Also, we're going to prevent the old chan_sip channel driver from loading, since we're only worried about PJSIP.

To do this, edit the /etc/asterisk/modules.conf configuration file.  In the [modules] section, add the following lines:


Asterisk PJSIP configuration

Next, we need to configure a transport in /etc/asterisk/pjsip.conf.  PJSIP transport object types are not stored in realtime as unexpected results can occur.  So, edit it and add the following lines:


Here, we created a transport called transport-udp that we'll reference in the next section.

Endpoint Population

Now, we need to create our endpoints inside of the database.  For this example, we'll create two peers, 101 and 102, that register using the totally insecure passwords "101" and "102" respectively.  Here, we'll be populating data directly into the database using the MySQL interactive tool.

# mysql -u root -p -D asterisk;
mysql> insert into ps_aors (id, max_contacts) values (101, 1);
mysql> insert into ps_aors (id, max_contacts) values (102, 1);
mysql> insert into ps_auths (id, auth_type, password, username) values (101, 'userpass', 101, 101);
mysql> insert into ps_auths (id, auth_type, password, username) values (102, 'userpass', 102, 102);
mysql> insert into ps_endpoints (id, transport, aors, auth, context, disallow, allow, direct_media) values (101, 'transport-udp', '101', '101', 'testing', 'all', 'g722', 'no');
mysql> insert into ps_endpoints (id, transport, aors, auth, context, disallow, allow, direct_media) values (102, 'transport-udp', '102', '102', 'testing', 'all', 'g722', 'no');
mysql> quit;

In this example, we first created an aor for each peer, one called 101 and the other 102.

Next, we created an auth for each peer with a userpass of 101 and 102, respectively.

Then, we created two endpoints, 101 and 102, each referencing the appropriate auth and aor, we selected the G.722 codec and we forced media to route inside of Asterisk (not the default behavior of Asterisk).

Now, you can start Asterisk and you can check to see if it's finding your PJSIP endpoints in the database.  You can do this by executing "pjsip show endpoints" from the Asterisk CLI.  If everything went well, you'll see:

# asterisk -vvvvc
*CLI> pjsip show endpoints

A Little Dialplan

Now that we have our PJSIP endpoints stored in our MySQL database, let's add a little dialplan so that they can call each other.  To do this, edit Asterisk's /etc/asterisk/extensions.conf file and add the following lines to the end:


Or to dial multiple AOR contacts at the same time, use the PJSIP_DIAL_CONTACTS function:


Reserved Characters

Realtime uses the semicolon ( ; ) as a delimiter for multiple entries.  It must be replaced with "^3B" to prevent the data from being interpreted as multiple entries.

"^3B" is the corresponding byte value of the semicolon character in ASCII, represented as a pair of hexadecimal digits, preceded by a caret ( ^ ) acting as the escape character.


For example, this outbound_proxy parameter

should be stored in the database as


Now, start Asterisk back up, or reload it using core reload from the Asterisk CLI, register your two SIP phones using the 101/101 and 102/102 credentials, and make a call.

  • No labels


  1. The example block code of the file /etc/asterisk/extconfig.conf is missing the following line:

    If this is not added, res_pjsip cannot load because it is defined in the sorcery.conf file example.

  2. To enable outbound registrations, which are probably useful to everyone add the following to sorcery.conf

    And to extconfig.conf


    Maybe it's worth mentioning that sorcery.conf.sample that comes with Asterisk puts the line ;identify=realtime,ps_endpoint_id_ips under section res_pjsip, but it should be under [res_pjsip_endpoint_identifier_ip] as Jonathan Rose wrote in this article.

  3. detail field table 



    1. have a look in the alembic scripts for this.

      Wouldn't make sense to post them here as they change over time, which is why alembic is there to allow migrations of the database when updating the Asterisk version.

  4. As mentioned on the the asterisk-users mailing list I managed to get this running on Debian 9 ("Stretch") as well. The 3 main differences are that Debian switched from MySQL to MariaDB as well as that Debian by default uses utf8mb4 as character set in MariaDB instead of latin1 resulting in errors if unchanged and that alembic should be installed from apt rather than via pip.

    So here is a step-by-step what's different when setting things up in Debian 9 in relation to the chapters above:

    Install dependencies

    The unixodbc-dev, python-dev and python-pip packages mentioned above are not needed (The latter because we install alembic via apt).

    Changing the default settings in Debian's MariaDB configuration

    By default MariaDB uses 'latin1' encoding whereas Debian and Ubuntu change that to use utf8mb4 (4-bytes per character). There is a length limit
    of 767 bytes for an entry in a column which will collide with strings that hold 255 characters in utf8mb4 (255 * 4 = 1020 > 767). So we need to
    change that back to default by uncommenting the following lines in the following files:


    Creating the MySQL database

    MariaDB in Debian 9 by default isn't setup to allow any user database access as root database user. Also a password isn't set for this user.
    Instead any user with sudo rights can access mariadb without a password as user root.
    Since the alembic scripts require root login to work with password we need to change that. This can be done by running the following commands:

    You will be prompted a few times. Select the suggested defaults and enter password for the root database user. Afterwards run

    Instead of the command in the wiki run the following (add sudo but leave out -p)

    The following steps can be done as mentioned above in section "Installing and using alembic" except that we do not need to install alembic but just use it.

    4.) Configuring ODBC connector

    Since we are using MariaDB instead of MySQL there are quite a few differences.

    First of all MariaDB in Debian is missing the libmyodbc ODBC connector and unfortunately there is no packaged equivalent for MariaDB even though it exists. This means that we have to install that manually.

    Download, extract and copy MariaDB connector

    Configure ODBC connector

    We need to set up ODBC and Asterisk ODBC by making changes to the following files.

    First tell ODBC to connect to MariaDB (this step uses the connector that we manually installed in the previous step in the "Driver" line. The binary in the "Setup" line is part of unixodbc):


    Verify that this step is working by running

    The output of the above command should be "[mariadb]".

    Then we need to tell ODBC which MariaDB database to use



    Now ODBC should be all set up and working. We can verify this using isql and perform the rest of the steps as mentioned above.

  5. Hello Everyone,

    Is possible edit wiki and add notes about correct syntax for realtime outbound_proxy syntax 



    outbound_proxy sip:proxy_ip:proxy_port^3Btransport=udp^3Blr

    1. Reserved Characters section has been added. Your suggestion is appreciated.

  6. Hello Scott,

    Thank you for update.

  7. These are the changes I made to set up PJSIP Realtime with PostgreSQL:

    1) If you want to use PostgreSQL instead of MariaDB, you need to install psycopg dependency:
    for Python < 3:
    apt-get install python-psycopg2
    for Python >= 3:
    apt-get install python3-psycopg2

    2) Create a postgres user to use in the Realtime Asterisk Architecture (I named it asterisk):

    3) Create asterisk database (any name you want) with the user asterisk as the owner (use the user you just created before):
    CREATEDB asterisk -O asterisk -U asterisk

    4) Change the url for alembic in the file config.ini located in the asterisk source directory contrib/ast-db-manage/ :
    sqlalchemy.url = postgresql://asterisk:[email protected]/asterisk

    5) I am working with native Postgres (no ODBC). You just need to edit the file /etc/asterisk/res_pgsql.conf to configure the connection parameters.

    6) The only change in /etc/asterisk/extconfig.conf is putting the word pgsql instead of odbc:

    ps_endpoints => pgsql,asterisk
    ps_auths => pgsql,asterisk
    ps_aors => pgsql,asterisk
    ps_domain_aliases => pgsql,asterisk
    ps_endpoint_id_ips => pgsql,asterisk
    ps_contacts => pgsql,asterisk

    And that's it. Everything remains the same.