手工创建 postgres kamailio 数据库

测试环境如下：

postgres server 16： ip 地址为 192.168.31.100，用户 postgres 的密码为 ******
kamailio v5.7.5： ip 地址为 192.168.31.101

1.1.  创建 kamailio 用户和 kamailio  数据库

ssh 登陆 kamailio (192.168.31.101)，执行下面命令：

psql -U postgres -h 192.168.31.100 -c "CREATE USER kamailio WITH PASSWORD 'kamailio'"
psql -U postgres -h 192.168.31.100 -c "CREATE DATABASE kamailio OWNER kamailio"

1.2.  创建 kamailio 相关的表以及索引等

echo '192.168.31.100:5432:kamailio:kamailio:kamailio' > ~/.pgpass
chmod 0600 ~/.pgpass

psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/standard-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/acc-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/alias_db-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/auth_db-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/avpops-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/carrierroute-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/cpl-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/dialog-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/dialplan-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/dispatcher-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/domain-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/domainpolicy-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/drouting-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/group-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/htable-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/lcr-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/matrix-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/mohqueue-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/msilo-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/mtree-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/pdt-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/permissions-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/pipelimit-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/presence-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/purple-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/registrar-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/rls-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/rtpengine-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/rtpproxy-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/sca-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/secfilter-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/siptrace-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/speeddial-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/topos-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/uac-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/uid_auth_db-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/uid_avp_db-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/uid_domain-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/uid_gflags-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/uid_uri_db-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/uri_db-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/userblocklist-create.sql
psql -U kamailio -h 192.168.31.100 -d kamailio -f /usr/share/kamailio/postgres/usrloc-create.sql

1.3.  其他

如果是源码编译，sql 文件的路径要调整为 /usr/local/share/kamailio/postgres

acc 表一般要扩充字段，一般是：

ALTER TABLE acc ADD COLUMN src_user VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE acc ADD COLUMN src_domain VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE acc ADD COLUMN src_ip varchar(64) NOT NULL default '';
ALTER TABLE acc ADD COLUMN dst_ouser VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE acc ADD COLUMN dst_user VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE acc ADD COLUMN dst_domain VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE missed_calls ADD COLUMN src_user VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE missed_calls ADD COLUMN src_domain VARCHAR(128) NOT NULL DEFAULT '';
ALTER TABLE missed_calls ADD COLUMN src_ip varchar(64) NOT NULL default '';
ALTER TABLE missed_calls ADD COLUMN dst_ouser VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE missed_calls ADD COLUMN dst_user VARCHAR(64) NOT NULL DEFAULT '';
ALTER TABLE missed_calls ADD COLUMN dst_domain VARCHAR(128) NOT NULL DEFAULT '';