Postgres as core database

[nhwebforge]

Is anyone aware of a definitve guide (that works) of how to make FreeSwitch use Postgres as it's database rather than SQLITE? So that the whole FusionPBX system uses Postgres and SQLITE is not used at all.

I can't seem to find a single one that results in a working system or actually stops some part of FS from using SQLITE. All the guides and references I've seen either break my system or result in SQLITE .db files still being created.

Many, many thanks in advance

Nick

 

[DigitalDaz]

Nick, I have done this many times in the past but its so long ago that I can't remember. Why are you trying to do this? Just get sqlite into RAM and everything will be fine.

 

[nhwebforge]

Nick, I have done this many times in the past but its so long ago that I can't remember. Why are you trying to do this? Just get sqlite into RAM and everything will be fine.

I should have said in my post For some reason the entire machine locks up... no web interface, no SSH, nothing. When I look back through the logs, the last thing in there before the necessary reboot is about 6 or 7 lines of SQLITE being busy. I know this is by design, but "something" is causing this lock up and the only way I can think of to troubleshoot it is to try and remove SQLITE. I will however try moving it to RAM as you suggested and I'll report back.

Thanks for the advice.

 

[DigitalDaz]

No of what you have described above would have anything at all to do with sqlite. What you are descibing is more like your IP got blacklisted somehow.

The lines showing sqlite busy are perfectly normal.

100% this has nothing to do with sqlite.

 

[pbxgeek]

There is document about this in member docs But @DigitalDaz is correct if it’s the entire machine and not just freeswitch it’s something else that’s locking you out. Fail2ban foe example is designed to block rapid http requests. It will block both http and ssh access. The best way to determine that is to login from a different IP address.

 

[hfoster]

There's a script provided with the installer called dsn.sh that does it all for you, you'll need to provide it with the postgres password found in /etc/fusionpbx/config.conf

Unless you have a lot of domains, SQLITE is perfectly fine as long as it's on a RAM disk. It's perfectly fine on an SSD in most scenarios too. The problem manifested for me as a cascading rejection of registrations where thousands of extensions were all fighting to add themselves to the registration table.

 

[Adrian Fretwell]

SQLite in RAM disk will serve you very well. We generally use SQLite in RAM, despite having options to put the Freeswitch core in to postgreSQL and manage it in a GUI/API interface.
If you are interested in the Freeswitch Database Schema, you will find the result of our last attempt to decipher it from the Freeswitch source code, plus the addition of UUID primary keys where applicable here:
https://codeberg.org/DjangoPBX/djangopbx-applications/src/branch/main/freeswitchdb/models.py
And a shell script to create said schema here:
https://codeberg.org/DjangoPBX/DjangoPBX/src/branch/main/switch/resources/templates/sql/switch.sql

 

[whut]

Don't chase sqlite squirrels and rabbits. When you have been locked out of gui and ssh start with all firewalls and vpns.

 

[nhwebforge]

Some interesting comments here! Thanks everyone! I should clarify the "lock up". It wasn;t a particular IP that was locked out. Even local direct access to the machine was unresponsive (i.e. plug a monitor, keyboard and mouse into the server). The whole machine just locked up and only a hard reboot would fix it. I can confidently say it was not just a simple IP ban.

I agree that SQLite shouldn't be the culprit and, to all intents and purposes, probably isn't the culprit, however it's interesting to note that when I did manage to get most of FS moved over to Postgres (something was stubbornly still creating a .db file) the lockups ceased.

I have still not found time to test moving SQLite into RAM, but I will do ASAP.

Thanks again

 

[Adrian Fretwell]

Linux boxes, don't often lock up / hang like that. It could be an indication of something more serious like a hardware fault.