Hello, guys!
I wanted to ask you about the proper way to move the FreeSWITCH database to the external server.
I use Postgres.
I changed the config according to this post: https://www.pbxforums.com/threads/experimenting-with-a-full-load-sharing-cluster.867/post-3687
And also here is my configs files:
Everything works, FreeSWITCH writes data to the external PSQL server, database - freeswitch. I see there information about registrations and so on.
But I faced with some errors in the lua-scripts, for example like this one: https://www.pbxforums.com/threads/table-fusionpbx-registrations.5097/
I find that this script doesn't send the queries to my previously configured external database. I found this in such way:
1) Added logging to the script /usr/share/freeswitch/scripts/resources/functions/database/native.lua:
2) Added logging to the script /usr/share/freeswitch/scripts/app/xml_handler/resources/scripts/directory/directory.lua:
3) Made a call from extension 101 to extension 102, domain - formendel, fs_cli:
Due to these logs, query: SELECT hostname FROM registrations WHERE reg_user = '102' AND realm = 'formendel' AND expires > 1616153291; have to be sent to the SQL server, but I don't see this query in the PSQL server query logs, so the query wasn't sent to the external PSQL server.
Please share the information is it possible to move the FreeSWITCH database to the external server and what changes need to be done at the configs.
Thanks in advance!
I wanted to ask you about the proper way to move the FreeSWITCH database to the external server.
I use Postgres.
I changed the config according to this post: https://www.pbxforums.com/threads/experimenting-with-a-full-load-sharing-cluster.867/post-3687
And also here is my configs files:
<?php
/*
FusionPBX
Version: MPL 1.1
The contents of this file are subject to the Mozilla Public License Version
1.1 (the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
Software distributed under the License is distributed on an "AS IS" basis,
WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
for the specific language governing rights and limitations under the
License.
The Original Code is FusionPBX
The Initial Developer of the Original Code is
Mark J Crane <markjcrane@fusionpbx.com>
Portions created by the Initial Developer are Copyright (C) 2008-2016
the Initial Developer. All Rights Reserved.
Contributor(s):
Mark J Crane <markjcrane@fusionpbx.com>
*/
//set the database type
$db_type = 'pgsql'; //sqlite, mysql, pgsql, others with a manually created PDO connection
//sqlite: the db_name and db_path are automatically assigned however the values can be overidden by setting the values here.
//$db_name = 'fusionpbx.db'; //host name/ip address + '.db' is the default database filename
//$db_path = '/var/www/fusionpbx/secure'; //the path is determined by a php variable
//pgsql: database connection information
$db_host = '10.20.30.1';
$db_port = '5432';
$db_name = 'fusionpbx';
$db_username = 'fusionpbx';
$db_password = 'SUPERSECRET';
//show errors
ini_set('display_errors', '1');
//error_reporting (E_ALL); // Report everything
//error_reporting (E_ALL ^ E_NOTICE); // hide notices
error_reporting(E_ALL ^ E_NOTICE ^ E_WARNING ); //hide notices and warnings
?>
/*
FusionPBX
Version: MPL 1.1
The contents of this file are subject to the Mozilla Public License Version
1.1 (the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
Software distributed under the License is distributed on an "AS IS" basis,
WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
for the specific language governing rights and limitations under the
License.
The Original Code is FusionPBX
The Initial Developer of the Original Code is
Mark J Crane <markjcrane@fusionpbx.com>
Portions created by the Initial Developer are Copyright (C) 2008-2016
the Initial Developer. All Rights Reserved.
Contributor(s):
Mark J Crane <markjcrane@fusionpbx.com>
*/
//set the database type
$db_type = 'pgsql'; //sqlite, mysql, pgsql, others with a manually created PDO connection
//sqlite: the db_name and db_path are automatically assigned however the values can be overidden by setting the values here.
//$db_name = 'fusionpbx.db'; //host name/ip address + '.db' is the default database filename
//$db_path = '/var/www/fusionpbx/secure'; //the path is determined by a php variable
//pgsql: database connection information
$db_host = '10.20.30.1';
$db_port = '5432';
$db_name = 'fusionpbx';
$db_username = 'fusionpbx';
$db_password = 'SUPERSECRET';
//show errors
ini_set('display_errors', '1');
//error_reporting (E_ALL); // Report everything
//error_reporting (E_ALL ^ E_NOTICE); // hide notices
error_reporting(E_ALL ^ E_NOTICE ^ E_WARNING ); //hide notices and warnings
?>
--set the variables
conf_dir = [[/etc/freeswitch]];
sounds_dir = [[/usr/share/freeswitch/sounds]];
database_dir = [[/var/lib/freeswitch/db]];
recordings_dir = [[/var/lib/freeswitch/recordings]];
storage_dir = [[/var/lib/freeswitch/storage]];
voicemail_dir = [[/var/lib/freeswitch/storage/voicemail]];
scripts_dir = [[/usr/share/freeswitch/scripts]];
php_dir = [[/usr/bin]];
php_bin = "php";
document_root = [[/var/www/fusionpbx]];
project_path = [[]];
http_protocol = [[http]];
--cache settings
cache = {}
cache.method = [[memcache]];
cache.location = [[/var/cache/fusionpbx]];
cache.settings = true;
--database information
database = {}
database.type = "pgsql";
database.name = "fusionpbx";
database.path = [[]];
database.system = "pgsql://hostaddr=10.20.30.1 port=5432 dbname=fusionpbx user=fusionpbx password=SUPERPASSWORD options=''";
database.switch = "pgsql://hostaddr=10.20.30.1 port=5432 dbname=freeswitch user=fusionpbx password=SUPERPASSWORD options=''";
database.backend = {}
database.backend.base64 = 'luasql'
--set defaults
expire = {}
expire.default = "3600";
expire.directory = "3600";
expire.dialplan = "3600";
expire.languages = "3600";
expire.sofia = "3600";
expire.acl = "3600";
expire.ivr = "3600";
--set xml_handler
xml_handler = {}
xml_handler.fs_path = true;
xml_handler.reg_as_number_alias = false;
xml_handler.number_as_presence_id = true;
--set settings
settings = {}
settings.recordings = {}
settings.voicemail = {}
settings.fax = {}
settings.recordings.storage_type = "base64";
settings.voicemail.storage_type = "base64";
settings.fax.storage_type = "base64";
--set the debug options
debug.params = false;
debug.sql = false;
debug.xml_request = false;
debug.xml_string = false;
debug.cache = false;
--additional info
domain_count = 4;
temp_dir = [[/tmp]];
dial_string = "{sip_invite_domain=${domain_name},leg_timeout=${call_timeout},presence_id=${dialed_user}@${dialed_domain}}${sofia_contact(*/${dialed_user}@${dialed_domain})}";
--include local.lua
require("resources.functions.file_exists");
if (file_exists("/etc/fusionpbx/local.lua")) then
dofile("/etc/fusionpbx/local.lua");
elseif (file_exists("/usr/local/etc/fusionpbx/local.lua")) then
dofile("/usr/local/etc/fusionpbx/local.lua");
elseif (file_exists(scripts_dir.."/resources/local.lua")) then
require("resources.local");
end
conf_dir = [[/etc/freeswitch]];
sounds_dir = [[/usr/share/freeswitch/sounds]];
database_dir = [[/var/lib/freeswitch/db]];
recordings_dir = [[/var/lib/freeswitch/recordings]];
storage_dir = [[/var/lib/freeswitch/storage]];
voicemail_dir = [[/var/lib/freeswitch/storage/voicemail]];
scripts_dir = [[/usr/share/freeswitch/scripts]];
php_dir = [[/usr/bin]];
php_bin = "php";
document_root = [[/var/www/fusionpbx]];
project_path = [[]];
http_protocol = [[http]];
--cache settings
cache = {}
cache.method = [[memcache]];
cache.location = [[/var/cache/fusionpbx]];
cache.settings = true;
--database information
database = {}
database.type = "pgsql";
database.name = "fusionpbx";
database.path = [[]];
database.system = "pgsql://hostaddr=10.20.30.1 port=5432 dbname=fusionpbx user=fusionpbx password=SUPERPASSWORD options=''";
database.switch = "pgsql://hostaddr=10.20.30.1 port=5432 dbname=freeswitch user=fusionpbx password=SUPERPASSWORD options=''";
database.backend = {}
database.backend.base64 = 'luasql'
--set defaults
expire = {}
expire.default = "3600";
expire.directory = "3600";
expire.dialplan = "3600";
expire.languages = "3600";
expire.sofia = "3600";
expire.acl = "3600";
expire.ivr = "3600";
--set xml_handler
xml_handler = {}
xml_handler.fs_path = true;
xml_handler.reg_as_number_alias = false;
xml_handler.number_as_presence_id = true;
--set settings
settings = {}
settings.recordings = {}
settings.voicemail = {}
settings.fax = {}
settings.recordings.storage_type = "base64";
settings.voicemail.storage_type = "base64";
settings.fax.storage_type = "base64";
--set the debug options
debug.params = false;
debug.sql = false;
debug.xml_request = false;
debug.xml_string = false;
debug.cache = false;
--additional info
domain_count = 4;
temp_dir = [[/tmp]];
dial_string = "{sip_invite_domain=${domain_name},leg_timeout=${call_timeout},presence_id=${dialed_user}@${dialed_domain}}${sofia_contact(*/${dialed_user}@${dialed_domain})}";
--include local.lua
require("resources.functions.file_exists");
if (file_exists("/etc/fusionpbx/local.lua")) then
dofile("/etc/fusionpbx/local.lua");
elseif (file_exists("/usr/local/etc/fusionpbx/local.lua")) then
dofile("/usr/local/etc/fusionpbx/local.lua");
elseif (file_exists(scripts_dir.."/resources/local.lua")) then
require("resources.local");
end
Everything works, FreeSWITCH writes data to the external PSQL server, database - freeswitch. I see there information about registrations and so on.
But I faced with some errors in the lua-scripts, for example like this one: https://www.pbxforums.com/threads/table-fusionpbx-registrations.5097/
I find that this script doesn't send the queries to my previously configured external database. I found this in such way:
1) Added logging to the script /usr/share/freeswitch/scripts/resources/functions/database/native.lua:
function FsDatabase.new(name)
local dbh = assert(name)
if type(name) == 'string' then
if name == 'switch' and file_exists(database_dir.."/core.db") then
freeswitch.consoleLog("notice", "-------------- SQLITE DB ----------------------- \n")
dbh = freeswitch.Dbh("sqlite://"..database_dir.."/core.db")
else
freeswitch.consoleLog("notice", "-------------- EXTERNAL DB ----------------------- \n")
dbh = database_handle(name)
end
end
assert(dbh:connected())
local dbh = assert(name)
if type(name) == 'string' then
if name == 'switch' and file_exists(database_dir.."/core.db") then
freeswitch.consoleLog("notice", "-------------- SQLITE DB ----------------------- \n")
dbh = freeswitch.Dbh("sqlite://"..database_dir.."/core.db")
else
freeswitch.consoleLog("notice", "-------------- EXTERNAL DB ----------------------- \n")
dbh = database_handle(name)
end
end
assert(dbh:connected())
2) Added logging to the script /usr/share/freeswitch/scripts/app/xml_handler/resources/scripts/directory/directory.lua:
--connect to the switch database
freeswitch.consoleLog("notice", " ---------- connect to the switch database --------- \n");
dbh_switch = Database.new('switch');
--get register name
local reg_user = dialed_extension
if not DIAL_STRING_BASED_ON_USERID then
reg_user = trim(api:execute("user_data", dialed_extension .. "@" .. domain_name .. " attr id"));
end
--get the destination hostname from the registration
--- local params = {reg_user=reg_user, domain_name=domain_name}
local params = {reg_user=reg_user, domain_name=domain_name, ts = os.time(os.date("!*t"))}
local sql = "SELECT hostname FROM registrations "
.. "WHERE reg_user = :reg_user "
.. "AND realm = :domain_name ";
if (database["type"] == "mysql") then
params.now = os.time();
sql = sql .. "AND expires > :now ";
else
-- sql = sql .. "AND to_timestamp(expires) > NOW()";
sql = sql .. "AND expires > :ts ";
end
if (debug["sql"]) then
freeswitch.consoleLog("notice", "[xml_handler] SQL: " .. sql .. "; params:" .. json.encode(params) .. "\n");
end
dbh_switch:query(sql, params, function(row)
database_hostname = row["hostname"];
end);
--freeswitch.consoleLog("notice", "[xml_handler] sql: " .. sql .. "\n");
--freeswitch.consoleLog("notice", "[xml_handler][directory] database_hostname is " .. database_hostname .. "\n");
--hostname was not found set USE_FS_PATH to false to prevent a database_hostname concatenation error
if (database_hostname == nil) then
freeswitch.consoleLog("notice", "----------------- database_hostname nil ---------------- ");
USE_FS_PATH = false;
end
--close the database connection
dbh_switch:release();
end
freeswitch.consoleLog("notice", " ---------- connect to the switch database --------- \n");
dbh_switch = Database.new('switch');
--get register name
local reg_user = dialed_extension
if not DIAL_STRING_BASED_ON_USERID then
reg_user = trim(api:execute("user_data", dialed_extension .. "@" .. domain_name .. " attr id"));
end
--get the destination hostname from the registration
--- local params = {reg_user=reg_user, domain_name=domain_name}
local params = {reg_user=reg_user, domain_name=domain_name, ts = os.time(os.date("!*t"))}
local sql = "SELECT hostname FROM registrations "
.. "WHERE reg_user = :reg_user "
.. "AND realm = :domain_name ";
if (database["type"] == "mysql") then
params.now = os.time();
sql = sql .. "AND expires > :now ";
else
-- sql = sql .. "AND to_timestamp(expires) > NOW()";
sql = sql .. "AND expires > :ts ";
end
if (debug["sql"]) then
freeswitch.consoleLog("notice", "[xml_handler] SQL: " .. sql .. "; params:" .. json.encode(params) .. "\n");
end
dbh_switch:query(sql, params, function(row)
database_hostname = row["hostname"];
end);
--freeswitch.consoleLog("notice", "[xml_handler] sql: " .. sql .. "\n");
--freeswitch.consoleLog("notice", "[xml_handler][directory] database_hostname is " .. database_hostname .. "\n");
--hostname was not found set USE_FS_PATH to false to prevent a database_hostname concatenation error
if (database_hostname == nil) then
freeswitch.consoleLog("notice", "----------------- database_hostname nil ---------------- ");
USE_FS_PATH = false;
end
--close the database connection
dbh_switch:release();
end
3) Made a call from extension 101 to extension 102, domain - formendel, fs_cli:
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 ---------- connect to the switch database ---------
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 -------------- SQLITE DB -----------------------
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 [xml_handler] Params:
Event-Name: REQUEST_PARAMS
Core-UUID: e8f0e9ab-5fdb-43cc-8016-cd09cce746af
FreeSWITCH-Hostname: fusion149
FreeSWITCH-Switchname: fusion149
FreeSWITCH-IPv4: 89.XXX.XXX.XXX
FreeSWITCH-IPv6: fe80%3A%3A20c%3A29ff%3Afe2d%3A5a08
Event-Date-Local: 2021-03-19%2015%3A28%3A11
Event-Date-GMT: Fri,%2019%20Mar%202021%2013%3A28%3A11%20GMT
Event-Date-Timestamp: 1616160491409744
Event-Calling-File: mod_commands.c
Event-Calling-Function: user_data_function
Event-Calling-Line-Number: 1327
Event-Sequence: 17209
type: attr
key: id
user: 102
domain: formendel
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 [xml_handler] Section: directory
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 [xml_handler] Tag Name: domain
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 [xml_handler] Key Name: name
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 [xml_handler] Key Value: formendel
2021-03-19 15:28:11.429748 [NOTICE] switch_cpp.cpp:1447 [xml_handler][directory][cache] get key: directory:102@formendel pass!
2021-03-19 15:28:11.429748 [NOTICE] switch_cpp.cpp:1447 [xml_handler] directory:102@formendel source: cache
2021-03-19 15:28:11.429748 [NOTICE] switch_cpp.cpp:1447 [xml_handler] XML_STRING:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<document type="freeswitch/xml">
<section name="directory">
<domain name="formendel" alias="true">
<params>
<param name="jsonrpc-allowed-methods" value="verto"/>
<param name="jsonrpc-allowed-event-channels" value="demo,conference,presence"/>
</params>
<groups>
<group name="default">
<users>
<user id="102" type=>
<params>
<param name="password" value="SUPERPASSWORD"/>
<param name="vm-enabled" value="true"/>
<param name="dial-string" value="{sip_invite_domain=${domain_name},leg_timeout=${call_timeout},presence_id=${dialed_user}@${d
ialed_domain}}${sofia_contact(*/${dialed_user}@${dialed_domain})}"/>
<param name="verto-context" value="formendel"/>
<param name="verto-dialplan" value="XML"/>
<param name="jsonrpc-allowed-methods" value="verto"/>
<param name="jsonrpc-allowed-event-channels" value="demo,conference,presence"/>
</params>
<variables>
<variable name="domain_uuid" value="23eedf89-47f1-4e4d-9dc1-c873d79a0a9d"/>
<variable name="domain_name" value="formendel"/>
<variable name="extension_uuid" value="caec4eb1-fea0-45e3-9454-216fd407e3b3"/>
<variable name="call_timeout" value="30"/>
<variable name="caller_id_name" value="102"/>
<variable name="caller_id_number" value="102"/>
<variable name="presence_id" value="102@formendel"/>
<variable name="call_screen_enabled" value="false"/>
<variable name="user_context" value="formendel"/>
<variable name="effective_caller_id_name" value="102_David"/>
<variable name="directory-visible" value="true"/>
<variable name="directory-exten-visible" value="true"/>
<variable name="limit_max" value="5"/>
<variable name="limit_destination" value="error/user_busy"/>
<variable name="force_ping" value="true"/>
<variable name="record_stereo" value="true"/>
<variable name="transfer_fallback_extension" value="operator"/>
<variable name="export_vars" value="domain_name"/>
</variables>
</user>
</users>
</group>
</groups>
</domain>
</section>
</document>
2021-03-19 15:28:11.429748 [NOTICE] switch_cpp.cpp:1447 [xml_handler] SQL: SELECT hostname FROM registrations WHERE reg_user = :reg_user AND realm = :domain_name AND expires > :ts ; params:
{"domain_name":"formendel","reg_user":"102","ts":1616153291}
2021-03-19 15:28:11.429748 [NOTICE] switch_cpp.cpp:1447 ----------------- database_hostname nil ----------------
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 -------------- SQLITE DB -----------------------
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 [xml_handler] Params:
Event-Name: REQUEST_PARAMS
Core-UUID: e8f0e9ab-5fdb-43cc-8016-cd09cce746af
FreeSWITCH-Hostname: fusion149
FreeSWITCH-Switchname: fusion149
FreeSWITCH-IPv4: 89.XXX.XXX.XXX
FreeSWITCH-IPv6: fe80%3A%3A20c%3A29ff%3Afe2d%3A5a08
Event-Date-Local: 2021-03-19%2015%3A28%3A11
Event-Date-GMT: Fri,%2019%20Mar%202021%2013%3A28%3A11%20GMT
Event-Date-Timestamp: 1616160491409744
Event-Calling-File: mod_commands.c
Event-Calling-Function: user_data_function
Event-Calling-Line-Number: 1327
Event-Sequence: 17209
type: attr
key: id
user: 102
domain: formendel
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 [xml_handler] Section: directory
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 [xml_handler] Tag Name: domain
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 [xml_handler] Key Name: name
2021-03-19 15:28:11.409744 [NOTICE] switch_cpp.cpp:1447 [xml_handler] Key Value: formendel
2021-03-19 15:28:11.429748 [NOTICE] switch_cpp.cpp:1447 [xml_handler][directory][cache] get key: directory:102@formendel pass!
2021-03-19 15:28:11.429748 [NOTICE] switch_cpp.cpp:1447 [xml_handler] directory:102@formendel source: cache
2021-03-19 15:28:11.429748 [NOTICE] switch_cpp.cpp:1447 [xml_handler] XML_STRING:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<document type="freeswitch/xml">
<section name="directory">
<domain name="formendel" alias="true">
<params>
<param name="jsonrpc-allowed-methods" value="verto"/>
<param name="jsonrpc-allowed-event-channels" value="demo,conference,presence"/>
</params>
<groups>
<group name="default">
<users>
<user id="102" type=>
<params>
<param name="password" value="SUPERPASSWORD"/>
<param name="vm-enabled" value="true"/>
<param name="dial-string" value="{sip_invite_domain=${domain_name},leg_timeout=${call_timeout},presence_id=${dialed_user}@${d
ialed_domain}}${sofia_contact(*/${dialed_user}@${dialed_domain})}"/>
<param name="verto-context" value="formendel"/>
<param name="verto-dialplan" value="XML"/>
<param name="jsonrpc-allowed-methods" value="verto"/>
<param name="jsonrpc-allowed-event-channels" value="demo,conference,presence"/>
</params>
<variables>
<variable name="domain_uuid" value="23eedf89-47f1-4e4d-9dc1-c873d79a0a9d"/>
<variable name="domain_name" value="formendel"/>
<variable name="extension_uuid" value="caec4eb1-fea0-45e3-9454-216fd407e3b3"/>
<variable name="call_timeout" value="30"/>
<variable name="caller_id_name" value="102"/>
<variable name="caller_id_number" value="102"/>
<variable name="presence_id" value="102@formendel"/>
<variable name="call_screen_enabled" value="false"/>
<variable name="user_context" value="formendel"/>
<variable name="effective_caller_id_name" value="102_David"/>
<variable name="directory-visible" value="true"/>
<variable name="directory-exten-visible" value="true"/>
<variable name="limit_max" value="5"/>
<variable name="limit_destination" value="error/user_busy"/>
<variable name="force_ping" value="true"/>
<variable name="record_stereo" value="true"/>
<variable name="transfer_fallback_extension" value="operator"/>
<variable name="export_vars" value="domain_name"/>
</variables>
</user>
</users>
</group>
</groups>
</domain>
</section>
</document>
2021-03-19 15:28:11.429748 [NOTICE] switch_cpp.cpp:1447 [xml_handler] SQL: SELECT hostname FROM registrations WHERE reg_user = :reg_user AND realm = :domain_name AND expires > :ts ; params:
{"domain_name":"formendel","reg_user":"102","ts":1616153291}
2021-03-19 15:28:11.429748 [NOTICE] switch_cpp.cpp:1447 ----------------- database_hostname nil ----------------
Due to these logs, query: SELECT hostname FROM registrations WHERE reg_user = '102' AND realm = 'formendel' AND expires > 1616153291; have to be sent to the SQL server, but I don't see this query in the PSQL server query logs, so the query wasn't sent to the external PSQL server.
Please share the information is it possible to move the FreeSWITCH database to the external server and what changes need to be done at the configs.
Thanks in advance!