SOLVED fusionpbx.registrations table

[kt351b]

Hello guys!
I have two separate instances with FusionPBX with the same version - 4.5.19 at Debian 10, PostgreSQL.
I have some strange things with the databases.
At one database-server I have tables - fusionpbx.registrations and freeswitch.registrations.
At another DB instance, I have only freeswitch.registrations table, and no table fusionpbx.registrations.
I am trying to find in the install scripts how does FusionPBX install the fusionpbx database, from dump, or so on. I made grep -io -r 'CREATE TABLE', but didn't find it in install scripts.

1) In my first instance where I have two registrations tables, I see changes at both tables (usionpbx.registrations and freeswitch.registrations. ). I need to move some functionality to another instance, but there is no fusionpbx.registrations table. I created it, but it doesn't filling with the data. Who writes to the fusionpbx.registrations table? I thought there is some trigger or so on, but didn't find it in the DB-dump file. Cause I greped (grep registrations /var/lib/postgresql/11/main/pg_log/postgresql-2021-02-16_171201.log | grep -v "select\|SELECT") in the pgsql debug log and found only this:
fusionpbx@freeswitch LOG: statement: delete from registrations where expires > 0 and expires <= 1613488963 and hostname='fusion161'
So, who deletes the data from the fusionpbx.registrations?

2) Where can I find the "vanilla" fusionpbx database dump to compare my DB with this dump?

Thanks in advance!

 

[DigitalDaz]

I have no idea what you have done but a default installation has NO tables related to registrations, none of the freeswitch running info is kept in postgres it is all kept in an sqlite database in a folder /var/lib/freeswitch/db.

 

[kt351b]

I have no idea what you have done but a default installation has NO tables related to registrations, none of the freeswitch running info is kept in postgres it is all kept in an sqlite database in a folder /var/lib/freeswitch/db.

Thanks for the reply. Seems I have a unique install
So, what I have done:
0) My schematic is: Kamailio server - FusionPBX1 server - FusionPBX2 server - RTPEngine server - DB (PSQL for all) - NGINX server for HTTP\HTTPS FusionPBX web-page balancer.
FusionPBX-cluster made by this article - https://www.pbxforums.com/threads/experimenting-with-a-full-load-sharing-cluster.867/ -
1) Installed via install-script,
with database settings = 127.0.0.1, then make dump from the DB, moved this dump to the remote DB and there run the install script again ( I don't remember why I have done that, I remember that I got some errors, that's why I installed everything again but with the credential to use remote DB)
/usr/src/fusionpbx-install.sh/debian/resources/config.sh:

Spoiler: /usr/src/fusionpbx-install.sh/debian/resources/config.sh:

# FusionPBX Settings
domain_name=ip_address # hostname, ip_address or a custom value
system_username=admin # default username admin
system_password=random # random or a custom value
system_branch=master # master, stable

# FreeSWITCH Settings
switch_branch=stable # master, stable
switch_source=false # true or false
switch_package=true # true or false
switch_version=1.10.3 # only for source
switch_tls=true # true or false

# Database Settings
database_password=mysuperpassword # random or a custom value (safe characters A-Z, a-z, 0-9)
database_repo=2ndquadrant # PostgreSQL official, system, 2ndquadrant
database_version=latest # requires repo official
database_host=10.20.30.1 # hostname or IP address
database_port=5432 # port number
database_backup=false # true or false

# General Settings
php_version=7.1 # PHP version 5.6 or 7.0, 7.1, 7.2
letsencrypt_folder=true # true or false

/etc/fusionpbx/config.php

Spoiler: /etc/fusionpbx/config.php

<?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

Mozilla Public Licence

About the Licence

www.mozilla.org

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 connec
tion

//sqlite: the db_name and db_path are automatically assigned however the values can be overid
den by setting the values here.
//$db_name = 'fusionpbx.db'; //host name/ip address + '.db' is the default database f
ilename
//$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 = 'mysuperpassword';

//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
?>

/etc/fusionpbx/config.lua

Spoiler: /etc/fusionpbx/config.lua

--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 = false;

--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=mysuperpassword options=''";
database.switch = "pgsql://hostaddr=10.20.30.1 port=5432 dbname=freeswitch user=fusionpbx password=mysuperpassword 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 = false;
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
~

 

[kt351b]

I either don't understand how it happened, but it is a very useful bug/feature. I even wanted to use it in my script )) But I will use table freeswitch.registrations instead, cause it works at two of my databases.

 

[kt351b]

I have no idea what you have done but a default installation has NO tables related to registrations, none of the freeswitch running info is kept in postgres it is all kept in an sqlite database in a folder /var/lib/freeswitch/db.

I found the reason of such behavior. In this article https://www.pbxforums.com/threads/experimenting-with-a-full-load-sharing-cluster.867/, there is such variable: dsn:


Telling FS to use PGSQL as its core DB

Spoiler: Telling FS to use PGSQL as its core DB

Edit /etc/freeswitch/autoload_configs/switch.conf.xml on both nodes.

Uncomment the "core-db-dsn" param which has the value $${dsn}.

Uncomment the "core-dbtype" param and set the value to "pgsql".

In fusionpbx, go to the Variables and add a new variable:

• name: dsn
• value: the same DSN string used in the config.lua file with dbname=freeswitch
• enabled: true

Go to the SIP Profiles section and edit each SIP profile. Enable the "odbc-dsn" parameter which already points to $${dsn}. (Note - I don't know strictly why this is necessary to specify odbc settings for the SIP profiles if we have postgres enabled for the core)

At this point to get all the configs reloaded I restarted memcached and freeswitch on both nodes. Using psql you should be able to check the freeswitch database and see that it populated the schema and if you select from the freeswitch.interfaces table you can see entries for both nodes.

And at one datacenter I put into this variable dbname=freeswitch, at another one - dbname=fusionpbx. That's why at one datacenter I had a table freeswitch.registrations, and at another - fusionpbx.registrations.

Sorry about bothering you, and thanks!