Move freeswitch from sqlite to postgresql

[ChrisLab]

Hi All,

Does anybody have a guide on moving freeswitch from sqlite to postgresql?

I've tried everything, but don't seem to get this working 100%.

At the moment I've got set in:

/etc/freeswitch/autoload_configs/switch.conf.xml​

<param name="core-db-dsn" value="pgsql://hostaddr=127.0.0.1 dbname=freeswitch user=fusionpbx password='fusionpbx'" />

.When I connect to postgres I can see the freeswitch database, and I can see data is being populated in ex. sip_registrations.

When I restart Freeswitch, the folder /var/lib/freeswitch/db is being populated again.

When I do a simple function like *8, fs_cli gives error:

2024-01-14 16:27:49.142903 97.33% [ERR] switch_core_db.c:246 SQL ERR [unable to open database file]
2024-01-14 16:27:49.142903 97.33% [CRIT] switch_core_sqldb.c:645 Failure to connect to CORE_DB /var/lib/freeswitch/db/core.db!
2024-01-14 16:27:49.142903 97.33% [ERR] freeswitch_lua.cpp:374 Connection failed. DBH NOT Connected.
2024-01-14 16:27:49.142903 97.33% [ERR] mod_lua.cpp:202 ...eeswitch/scripts/resources/functions/database/native.lua:35: assertion failed!

So it seems like sqlite is still in play here somewhere.

Am I missing something somewhere?

 

[Adrian Fretwell]

Just a guess, but maybe FreeSWITCH is starting before PostgreSQL is ready. In my own PBX installations I modify the /lib/systemd/system/freeswitch.service file and add:

Requires=network.target local-fs.target postgresql.service
After=network.target network-online.target local-fs.target postgresql.service memcached.service nginx.service uwsgi.service

You won't need uwsgi.service for FusionPBX.

 

[ChrisLab]

Hi Adrian,

Thanks for your input.
I changed the freeswitch.service file as above, but getting same results.

Where do we need to specify, to tell freeswitch to use postgres? is it only in /etc/freeswitch/autoload_configs/switch.conf.xml, like this:
<param name="core-db-dsn" value="pgsql://hostaddr=127.0.0.1 dbname=freeswitch user=fusionpbx password='fusionpbx'" />

 

[Adrian Fretwell]

Hi Chris, this is something I am quite interested in. I have always stuck with SQLite in the past because it seems to work quite well for the sort of loads we put on it. But now I've written my own GUI wrap around FreeSWITCH, I am starting to think about the advantages of having the FreeSWITCH tables within PostgreSql so I can query them easily from within my application, thus reduce the amount of access to the event socket.

There are several applications that use the FreeSWITCH DB; mod_sofia, mod_limit, mod_voicemail, mod_lcr, mod_nibblebill, and I believe these all need setting in their config.xml to tell them to use PostgreSql.

There is a dsn.sh section in the FusionPBX install script that may be worth looking at:
https://github.com/fusionpbx/fusionpbx-install.sh/blob/master/debian/resources/switch/dsn.sh

Cheers,
Adrian.

 

[ChrisLab]

Hi Adrian. Thank you so much for your help!
I was going around it the wrong way.

I think everything is now sorted, still busy checking.

I found only one database that I think sqlite is still handling under /var/lib/freeswitch/db/callcenter.db.

The only reason I wanted to use postgresql, was because i read somewhere that it performs better in big systems. I didn't know when sqlite would potentially become a problem, and I didn't want to wait around to find out when, so I thought, if I could get the db sorted before hand, that might save me some stress in the future.

Please do share any additional knowledge around this, if there's anything else you can think of.

Regards

 

[Adrian Fretwell]

No problem. I have just moved FreeSWITCH over to PostgreSql on my DjangoPBX dev box. I too found that callcenter.db was still being recreated, this is because the mod_callcenter configuration is being delivered dynamically, in my case via mod_xml_curl, and I guess for FusionPBX, via the .lua XML handler.

I think FusionPBX tests for the existence of the global variable dsn_callcenter.
Have a look at /usr/share/freeswitch/scripts/app/xml_handler/resources/scripts/configuration/callcenter.conf.lua

I'm now in the process of reverse engineering the freeswitch schema to create a Django (https://www.djangoproject.com/) model for all the tables.

 

[ChrisLab]

Thank you for the additional feedback!

Your final sentence scares me

Sorry, last question.
would this be normal behavior now?

2024-01-17 18:41:13.799236 98.33% [WARNING] switch_core_db.c:92 SQLite is BUSY, sane=299 [update sip_subscriptions set version=version+1 where ((expires > 0 and expires <= 1705509673)) and profile_name='internal-ipv6' and hostname='fusionpbx']

 

[Adrian Fretwell]

There have been a few posts on this forum in the past regarding the SQLite is BUSY warning and it was decided that this was normal freeswitch behavior. However, I would not expect to see that message if we are using PostgreSql.
I don't know the answer because I don't have enough experience of running freeswitch with the core DB in Postgresql. I will look out for that warning on my dev box.
I'll post back here if I find any more information.