Setting up PJSIP Realtimeを参考にPostgreSQLにpjsipの設定を格納する方法のメモ
はじめに
Asterisk13(11ぐらいから？)では、MySQLにPJSIPの設定を格納するのは、ODBC経由になったらしい。
PostgreSQLの場合も同様のようだ。(本当？、PostgreSQLはODBC経由でなくていいのかな？)
Setting up PJSIP Realtime
というドキュメントを見つけたので同じことをPostgreSQLで試した。
環境
- OS: CentOS 6
- Asterisk: 13.9.1
- PostgreSQL: 9.5
設定手順
一部説明を端折っている。後日追記するかもしれない。
PostgreSQLの設定
localhost経由でログインできるようにしておく。
rpm -ivh https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-6-i386/pgdg-centos95-9.5-2.noarch.rpm
yum install postgresql95-server
su postgres -c '/usr/pgsql-9.5/bin/initdb --no-locale -D /var/lib/pgsql/9.5/data -E UTF-8 '
/sbin/service postgresql-9.5 start
/usr/pgsql-9.5/bin/createuser asterisk -P
次のデータベースを作っておく
データベース: asteriskdb
ユーザ: asterisk
alembicの導入
テーブルの作成にalembicというコマンドを使う。CentOS6でもpython-alembicというものがあるがバージョンが古すぎる。pip経由でインストールしたalembicを使う
sudo yum install python-pip python-psycopg2
sudo pip install alembic
Downloading alembic-0.8.6.tar.gz (961kB)
テーブルの作成
Asteriskのソースの中に、コンフィグようの設定ファイルを作るスクリプトが用意されている。
cd ${asterisk_source}/contrib/ast-db-manage
cp config.ini.sample config.ini
vi config.init
config.iniを次のように書き換える。
sqlalchemy.url = postgresql://asterisk:pass@127.0.0.1/asteriskdb
sqlalchemy.url = postgresql://ユーザ名:パスワード@ホスト/データベース
テーブルを作る
alembic -c config.ini upgrade head
こんな感じで実行結果が出力される。
# alembic -c config.ini upgrade head
alembic -c config.ini upgrade head
INFO [alembic.migration] Context impl PostgresqlImpl.
INFO [alembic.migration] Will assume transactional DDL.
INFO [alembic.migration] Running upgrade None -> 4da0c5f79a9c
INFO [alembic.migration] Running upgrade 4da0c5f79a9c -> 43956d550a44
INFO [alembic.migration] Running upgrade 43956d550a44 -> 581a4264e537
INFO [alembic.migration] Running upgrade 581a4264e537 -> 2fc7930b41b3
次のようなテーブルができる。
asteriskdb=> \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------------------+-------+----------
public | alembic_version | table | asterisk
public | extensions | table | asterisk
public | iaxfriends | table | asterisk
public | meetme | table | asterisk
public | musiconhold | table | asterisk
public | ps_aors | table | asterisk
public | ps_auths | table | asterisk
public | ps_contacts | table | asterisk
public | ps_domain_aliases | table | asterisk
public | ps_endpoint_id_ips | table | asterisk
public | ps_endpoints | table | asterisk
public | ps_globals | table | asterisk
public | ps_registrations | table | asterisk
public | ps_subscription_persistence | table | asterisk
public | ps_systems | table | asterisk
public | ps_transports | table | asterisk
public | queue_members | table | asterisk
public | queue_rules | table | asterisk
public | queues | table | asterisk
public | sippeers | table | asterisk
public | voicemail | table | asterisk
(21 rows)
ODBCの設定
PostgreSQL用のODCドライバを導入
sudo yum install postgresql95-odbc.i686
odbc.iniを作成する
[asterisk]
Driver = /usr/pgsql-9.5/lib/psqlodbc.so
Servername = 127.0.0.1
Database = asteriskdb
FileUsage = 1
FileUsage = 1の意味はよくわかっていない
接続確認をする
isql -v asterisk asterisk password
isql -v DSN名 ユーザ名 パスワード
SQL> select * from alembic_version;
+---------------------------------+
| version_num |
+---------------------------------+
| 1c688d9a003c |
+---------------------------------+
SQLRowCount returns 1
1 rows fetched
Asteriskで接続確認
asterisk -vvvc
*CLI> odbc show
ODBC DSN Settings
-----------------
Name: asterisk
DSN: asterisk
Last connection attempt: 1970-01-01 09:00:00
元のドキュメントだと次のようになっていて、PooledやConnectedの出力があるが、Asterisk13.9.1ではこれらのメッセージは表示されない。
# 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
*CLI>
最新のソースでは、Number of active connectionsというエントリが表示されるようだ。
Asteriskの設定
sorcery.conf の下記の部分のコメントを外す。
[res_pjsip]
endpoint=realtime,ps_endpoints
endpoint=config,pjsip.conf,criteria=type=endpoint
auth=realtime,ps_auths
aor=realtime,ps_aors
domain_alias=realtime,ps_domain_aliases
identify=realtime,ps_endpoint_id_ips
endpoint=config,pjsip.conf,criteria=type=endpoint
という設定は、pjsip.confも見る場合に追記するらしい。
res_odbc.confの設定を記述する。
[asterisk]
enabled => yes
dsn => asterisk
username => asterisk
password => password
pre-connect => yes
sanitysql => select count(*) from alembic_version
sanitysqlはなくても良いと思う。
modules.confを次のようにする。noload => chan_sipは必要な場合のみ追記
preload => res_odbc.so
preload => res_config_odbc.so
noload => chan_sip.so
サンプル設定の追加
insert into ps_aors (id, max_contacts) values (101, 1);
insert into ps_aors (id, max_contacts) values (102, 1);
insert into ps_auths (id, auth_type, password, username) values (101, 'userpass', 101, 101);
insert into ps_auths (id, auth_type, password, username) values (102, 'userpass', 102, 102);
insert into ps_endpoints (id, transport, aors, auth, context, disallow, allow, direct_media) values (101, 'transport-udp', '101', '101', 'testing', 'all', 'g722', 'no');
insert into ps_endpoints (id, transport, aors, auth, context, disallow, allow, direct_media) values (102, 'transport-udp', '102', '102', 'testing', 'all', 'g722', 'no');
確認
asterisk -vvvvcで確認をする。
*CLI> pjsip show endpoints
Endpoint: <Endpoint/CID.....................................> <State.....> <Channels.>
I/OAuth: <AuthId/UserName...........................................................>
Aor: <Aor............................................> <MaxContact>
Contact: <Aor/ContactUri..........................> <Hash....> <Status> <RTT(ms)..>
Transport: <TransportId........> <Type> <cos> <tos> <BindAddress..................>
Channel: <ChannelId......................................> <State.....> <Time.....>
Exten: <DialedExten...........> CLCID: <ConnectedLineCID.......>
==========================================================================================
Endpoint: 101 Unavailable 0 of inf
InAuth: 101/101
Aor: 101 1
Endpoint: 102 Unavailable 0 of inf
InAuth: 102/102
Aor: 102 1
データベースで定義したエンドポイントが確認できた。
(参考)古いalembicを利用した場合
alembic -c config.ini upgrade head
INFO [alembic.migration] Context impl PostgresqlImpl.
INFO [alembic.migration] Will assume transactional DDL.
INFO [alembic.migration] Running upgrade None -> 4da0c5f79a9c
INFO [alembic.migration] Running upgrade 4da0c5f79a9c -> 43956d550a44
INFO [alembic.migration] Running upgrade 43956d550a44 -> 581a4264e537
INFO [alembic.migration] Running upgrade 581a4264e537 -> 2fc7930b41b3
Traceback (most recent call last):
File "/usr/bin/alembic", line 9, in <module>
load_entry_point('alembic==0.4.2', 'console_scripts', 'alembic')()
File "/usr/lib/python2.6/site-packages/alembic/config.py", line 255, in main
CommandLine(prog=prog).main(argv=argv)
File "/usr/lib/python2.6/site-packages/alembic/config.py", line 250, in main
self.run_cmd(cfg, options)
File "/usr/lib/python2.6/site-packages/alembic/config.py", line 241, in run_cmd
**dict((k, getattr(options, k)) for k in kwarg)
File "/usr/lib/python2.6/site-packages/alembic/command.py", line 124, in upgrade
script.run_env()
File "/usr/lib/python2.6/site-packages/alembic/script.py", line 191, in run_env
util.load_python_file(self.dir, 'env.py')
File "/usr/lib/python2.6/site-packages/alembic/util.py", line 186, in load_python_file
module = imp.load_source(module_id, path, open(path, 'rb'))
File "config/env.py", line 73, in <module>
run_migrations_online()
File "config/env.py", line 66, in run_migrations_online
context.run_migrations()
File "<string>", line 7, in run_migrations
File "/usr/lib/python2.6/site-packages/alembic/environment.py", line 494, in run_migrations
self.get_context().run_migrations(**kw)
File "/usr/lib/python2.6/contextlib.py", line 34, in __exit__
self.gen.throw(type, value, traceback)
File "/usr/lib/python2.6/site-packages/alembic/operations.py", line 50, in context
yield op
File "/usr/lib/python2.6/site-packages/alembic/environment.py", line 494, in run_migrations
self.get_context().run_migrations(**kw)
File "/usr/lib/python2.6/site-packages/alembic/migration.py", line 211, in run_migrations
change(**kw)
File "config/versions/2fc7930b41b3_add_pjsip_endpoint_options_for_12_1.py", line 131, in upgrade
new_column_name='mwi_from_user', existing_type=sa.String(40))
TypeError: alter_column() got an unexpected keyword argument 'new_column_name'
バージョン0.4.2と古いことが原因の模様
rpm -qi python-alembic
Name : python-alembic Relocations: (not relocatable)
Version : 0.4.2 Vendor: Fedora Project
Release : 3.el6 Build Date: Tue 30 Jul 2013 01:28:23 AM JST
Install Date: Tue 07 Jun 2016 10:03:13 PM JST Build Host: buildvm-03.phx2.fedoraproject.org
Group : Development/Libraries Source RPM: python-alembic-0.4.2-3.el6.src.rpm
Size : 1250852 License: MIT
Signature : RSA/8, Fri 02 Aug 2013 03:11:04 AM JST, Key ID 3b49df2a0608b895
Packager : Fedora Project
URL : http://pypi.python.org/pypi/alembic
Summary : Database migration tool for SQLAlchemy
Description :
Alembic is a new database migrations tool, written by the author of
`SQLAlchemy <http://www.sqlalchemy.org>`_. A migrations tool offers the
following functionality:
* Can emit ALTER statements to a database in order to change the structure
of tables and other constructs.
* Provides a system whereby "migration scripts" may be constructed; each script
indicates a particular series of steps that can "upgrade" a target database to
a new version, and optionally a series of steps that can "downgrade"
similarly, doing the same steps in reverse.
* Allows the scripts to execute in some sequential manner.
Documentation and status of Alembic is at http://readthedocs.org/docs/alembic/