Mysql To Postgresql

[Srinivasa Rao]

Hi Everyone,

Need Help,

Following are my server details

1. Asterisk 1.4.42
2. vicidial 2.2.1
3. REDFONE BRIDGE
4. REDHAT 5.6
5. CISCO IP Phone

Installation Method : Vicidialnow
$version = '2.2.1-259';
$build = '100510-2014';
Asterisk version: 1.4.42-vici

I am using the vicidial application from past two years, it is very nice. Is it possible to change the database of vicidial from mysql to postgresql ? One of client is asking me to do this.

Any one suggest this or not ?

Thanks & regards
Srinivas

[mcargile]

It would be a total mess.

Vicidial is heavily dependent on the MyISAM database engine in MySQL. Just switching it to InnoDB causes massive issues. Changing it to Postgresql would most likely cause just as many issues if not more. Things like Vicidial is designed around MyISAM's table level locking. Depending on how Postgresql handles locking you could have serious issues.

Also MySQL and Postgresql handle things like NULL data very differently which would cause other issue. Many queries that MySQL has no issue running Postgresql will error out. This would probably result in you having to change a number of the INSERT and UPDATE queries to ensure they get inserted properly.

The big thing to remember is that Vicidial uses MySQL for inter process communication as well as data storage. As such it needs a database that is designed around realtime data handling. I do not believe Postgresql is really up for this. Just Googling, 'Postgresql realtime' brings up mostly stuff about MySQL.

[GaD]

Well..., on Postgres's defense I believe it does a MUCH better job on almost all aspects (except clustering which is 'dirty') than MySQL. Unfortunately I'm not a DB programmer though so maybe I do not have the foundation to backup my statements. I do service the servers though (not creating programs for 'em), and from my standpoint I would LOVE if asterisk could work well with psql, unfortunately it doesn't...

Going back to the matters of the question... What are the chances of ODBC with Vici? I know that we could sacrifice response time (because odbc would have to do a lot of 'translations' per second), but how bad would it get? It **could** mean that you **may** be able to get Vici working in Postresql. maybe not very efficient as it would with MySQL, but... if it is worth the 'risk', you could go ahead and benchmark it. Again..., I am no programmer and I'm not really sure if I could have Vici call odbc for all of it's 'needs'... What are the chances?

[mcargile]

The biggest thing to remember about Vicidial is that it uses the MySQL database as a Real Time database. It is not just simply a data store for records and logs. All inter process communication is done through the database. Events have to be processed within a specific time frame and in a specific order. Thus it is imperative that the database behave a very specific way. Changing variables within the database causes the whole process to break down. Switching the system to be able to use any database connection (ODBC) would mean that there would be absolutely no way to control these variables thus it will never be done. Switching to PostGres may be possible, but it would be a MASSIVE development project and would require considerable amount of time playing with database settings to get everything perfect. That is assuming the features required are even available in PostGres.