Move FreeSWITCH DB from SQLite into PostgreSql

Adrian Fretwell

Well-Known Member
Aug 13, 2017
1,339
354
83
Over the next few days, we will be introducing a change that will move the FreeSWITCH database from SQLite into PostgreSql.

The key reason for doing this is to allow the FreeSWITCH database to be read directly from within the DjangoPBX application. The benefits of doing this will be better and easier reporting in applications like mod_callcenter but also it will allow all the tables to be viewed in the Admin interface and thus make diagnostics easier.

Once we have done this you will see an additional database dictionary in your settings.py file. This databse definition will be for the freeswitch database, it will contain the github insecure password so on an existing installation you will need to edit the settings.py file and change 'postgres-insecure-abcdef9876543210' to be the same as the password you have for the 'default' database shown just above in the settings file:

Code:
    'freeswitch': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'freeswitch',
        'USER': 'freeswitch',
        'PASSWORD': 'postgres-insecure-abcdef9876543210',
        'HOST': '127.0.0.1',
        'PORT': '5432'
    }
 

Adrian Fretwell

Well-Known Member
Aug 13, 2017
1,339
354
83
Hey @Adrian Fretwell

Let us know when the switch over is actioned and completed.

I would like to test this PBX

Cheers. mcs3ss2
Hi mcs3ss2,
Yes I will let you know. It will be a select-able option in the install script. We need to add some additional primary key fields to the FreeSWITCH schema for the add on viewing application to work correctly, but this is nearly done now.
 
  • Like
Reactions: mcs3ss2

ttt

New Member
Jan 18, 2024
2
0
1
58
Could I offer a suggestion here - switch from Postgress to MySQL. Most of our larger installations have other apps running on their PBX's and they need MySQL. Pretty much all of these companies resent running a 2nd major database service on the PBX. That's also a barrier for moving our FreePBX clients over to FreeSWITCH. A totally MySQL solution would be a big step forward.

I don't think there is a real reason for using PostgreSQL other than convience (eg. using GUID field type, whereas in MySQL you would use a char array type). The SQLite tables were actually a positive (anything to keep tables out of PostgreSQL).
 
Last edited:

ardyhash

Member
Jan 7, 2021
39
6
8
44
Could I offer a suggestion here - switch from Postgress to MySQL. Most of our larger installations have other apps running on their PBX's and they need MySQL. Pretty much all of these companies resent running a 2nd major database service on the PBX. That's also a barrier for moving our FreePBX clients over to FreeSWITCH. A totally MySQL solution would be a big step forward.

I don't think there is a real reason for using PostgreSQL other than convience (eg. using GUID field type, whereas in MySQL you would use a char array type). The SQLite tables were actually a positive (anything to keep tables out of PostgreSQL).

Thats cute, but if there's one thing I've learned over the years is that the customer is never right. That's not to say those requresting MySQL are right either, Postgresql is the better choice. The people maintaining those larger installations really should get back to the basics and try to follow best practices, unfortunately its easier to get a job in tech than Chick-Fil-A or Chipotle, which is why they serve excellent food and people are stuck with subpar technology solutions.
 

ttt

New Member
Jan 18, 2024
2
0
1
58
I've found it risky to assume the customer is stupid and I know better. We deal with large clients (hospitals, US military, PSAP call centers, etc) and they have some very capable IT departments. I have no doubt that you are highly skilled so if you find the customer's point of view 'cute' then it's based on your career experience, but we tend to take a different approach.
 

johnny

New Member
Apr 10, 2020
10
1
1
39
It may help to understand some key differences here and a clear reason why PostgreSQL was chosen:

PostgreSQL and MariaDB are both highly capable open-source relational database management systems. Each has its strengths, making them better suited to different use cases and environments. When considering the use case of a PBX (Private Branch Exchange) system, which is a private telephone network used within a company or organization, certain features and capabilities of PostgreSQL might make it more appealing compared to MariaDB. Here are ten reasons why PostgreSQL could be considered better than MariaDB for a PBX system:
  1. Advanced Data Types: PostgreSQL supports a wider range of data types including geometric primitives, network addresses, and JSONB. This is particularly useful for PBX systems that need to store complex data structures or require efficient querying within those data structures.
  2. Superior Transactional Integrity: PostgreSQL adheres strictly to ACID (Atomicity, Consistency, Isolation, Durability) principles, providing robust transactional integrity and multi-version concurrency control. This ensures data consistency and reliability in PBX systems where transaction integrity is crucial.
  3. Extensible Framework: PostgreSQL is highly extensible. Users can define their own data types, custom functions, and even code from different programming languages. This flexibility can be beneficial for integrating custom functionalities within a PBX system.
  4. Full Text Search: PostgreSQL includes powerful full-text search capabilities that are easy to use, supporting complex search queries natively. This can be particularly useful for PBX systems in searching through call records, notes, or other textual data.
  5. Superior Support for Geospatial Data: With its PostGIS extension, PostgreSQL offers advanced support for geographic objects, allowing for location-based queries and operations. This could be useful for PBX systems needing to manage data related to geographic locations, such as in dispatch systems or location-based routing.
  6. Concurrency and Performance: PostgreSQL's MVCC (Multi-Version Concurrency Control) model offers high levels of concurrency, with improved performance in environments where read operations are more common than writes, which is often the case in PBX systems.
  7. Robust Security Features: PostgreSQL offers strong built-in security features, including robust access controls, SSL for data encryption, and column-level encryption. This is essential for PBX systems that handle sensitive information.
  8. Comprehensive Indexing: PostgreSQL supports a variety of indexing techniques, including B-tree, Hash, GiST, SP-GiST, GIN, and BRIN. This allows for faster query processing and can significantly enhance the performance of PBX systems with large datasets.
  9. Large Community and Ecosystem: PostgreSQL has a large and active community, providing an extensive ecosystem of tools, extensions, and libraries. This can be beneficial for PBX systems requiring integration with other tools or needing custom extensions for specific functionalities.
  10. Foreign Data Wrappers: PostgreSQL supports foreign data wrappers (FDWs), which allow it to connect and query other databases or data sources directly. This interoperability can be useful for PBX systems that need to integrate with other systems or databases.
While PostgreSQL offers many advantages, the best choice between PostgreSQL and MariaDB depends on the specific requirements of the PBX system, including factors like existing infrastructure, performance needs, and the skill set of the development team. MariaDB also has its strengths, such as a focus on scalability and speed, making it a worthy contender depending on the scenario.
 
  • Like
Reactions: Neyamt

ardyhash

Member
Jan 7, 2021
39
6
8
44
Thanks Johnny for the detailed explanation, and sorry ttt for the tone. Just because something can be done doesn't always mean that it should, we could be living in a much better world if not for the fact that anyone who can do anything does so without considerations beyond their immediate wants or needs.

Experienced, people with knowledge beyond your customers' wants have invested good time in making the best decisions, then here you come making a 'suggestion' based purely on your requirements. The project is generously shared as open source, and frankly due to good decisions made during design I imagine its quite trivial to change the backend datasource, but to then write the documentation and do the testing and to have to support a configuration only the guy who isn't a contributor or customer is running suddenly makes a cool fun project feel like work work instead of fun work... but that doesn't excuse my poor tone, I should have done as Johnny did and shed light.
 
Last edited:

NerdUno

New Member
Sep 4, 2018
17
9
3
77
If I'm not mistaken, both Facebook and Meta use MySQL as their database engine of choice which is to say it scales pretty well. :cool:
 

Adrian Fretwell

Well-Known Member
Aug 13, 2017
1,339
354
83
If I'm not mistaken, both Facebook and Meta use MySQL as their database engine of choice which is to say it scales pretty well. :cool:
Currently only PostgreSQL is integrated into the core of FreeSWITCH - hence it is native. Other DBs can be used via ODBC but that would not be my choice.
 
  • Like
Reactions: chris021

ardyhash

Member
Jan 7, 2021
39
6
8
44
I would have figured the meta crowd to be pioneers of NoSQL or graphQL, though not surprised, wouldn't even be surprised to learn they’ve spun up a few storage engines of their own.