[Freeswitch-users] Connecting freeswitch to postgresql
Blackhold
blackholdmailer at gmail.com
Thu May 24 17:11:37 UTC 2018
Hi,
The tables are:
ostgres at freeswitch-capa8:~/sql$ psql freeswitch_db
freeswitch_db-# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------------------------+-------+----------
public | aliases | table | postgres
public | calls | table | postgres
public | channels | table | postgres
public | complete | table | postgres
public | db_data | table | postgres
public | fifo_bridge | table | postgres
public | fifo_callers | table | postgres
public | fifo_outbound | table | postgres
public | group_data | table | postgres
public | interfaces | table | postgres
public | json_store | table | postgres
public | limit_data | table | postgres
public | nat | table | postgres
public | recovery | table | postgres
public | registrations | table | postgres
public | sip_authentication | table | postgres
public | sip_dialogs | table | postgres
public | sip_presence | table | postgres
public | sip_registrations | table | postgres
public | sip_shared_appearance_dialogs | table | postgres
public | sip_shared_appearance_subscriptions | table | postgres
public | sip_subscriptions | table | postgres
public | tasks | table | postgres
public | voicemail_msgs | table | postgres
public | voicemail_prefs | table | postgres
(25 rows)
I continued testing and found that if I configure switch.conf.xml file like
this:
<param name="core-db-dsn" value="pgsql://hostaddr=127.0.0.1
dbname=freeswitch_db user=freeswitch_user password='****' options='-c
client_min_messages=NOTICE'" />
When I turn on freeswitch, postgresql don't output nothing. Then I tried to
configure freeswitch using odbc I then YES I get something in postgresql
logs
For odbc I have something not clear, it is mandatory to put files in
/usr/local/freeswitch/etc ? or leaving them at default directory (/etc) is
enough?
Here I show the config of odbc files:
root at freeswitch-capa8:/etc# cat odbc.ini
[freeswitch]
; WARNING: The old psql odbc driver psqlodbc.so is now renamed psqlodbcw.so
; in version 08.x. Note that the library can also be installed under an
other
; path than /usr/local/lib/ following your installation.
; Driver = /usr/lib64/odbc/psqlodbcw.so
; Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
Description=Connection to LDAP/POSTGRESQL
Servername=localhost
Port=5432
Protocol=6.4
FetchBufferSize=99
Username=freeswitch_user
Password=XXX
Database=freeswitch_db
ReadOnly=no
Debug=0
CommLog=0
root at freeswitch-capa8:/etc# cat odbcinst.ini
[PostgreSQL]
Description = PostgreSQL driver for Linux & Win32
;Driver = /usr/lib64/odbc/psqlodbcw.so
Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
Threading = 0
MaxLongVarcharSize=65536
And then connect to psql through odbc
root at freeswitch-capa8:/etc# isql -v freeswitch
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
Then in file switch.conf.xml I changed db connection configuration
root at freeswitch-capa8:/usr/local/freeswitch/conf/autoload_configs# cat
switch.conf.xml |grep core-db-dsn
<param name="core-db-dsn" value="pgsql://hostaddr=127.0.0.1
dbname=freeswitch_db user=freeswitch_user password='XXX' options='-c
client_min_messages=NOTICE'" />
<!--<param name="core-db-dsn"
value="freeswitch:freeswitch_user:XXX"/>-->
Last night SwK user at freenode channel told me that freeswitch on turning
on, it creates tables at database (thanks SwK).
I spect that configuration of odbc is working but drops lot of errors (here
you have some lines):
2018-05-24 02:03:25 CEST [3099-338780] freeswitch_user at freeswitch_db
STATEMENT: PREPARE "_PLAN0x140bf90" as CREATE TABLE nat (
sticky INTEGER,
port INTEGER,
proto INTEGER,
hostname VARCHAR(256)
);
;EXECUTE "_PLAN0x140bf90"
2018-05-24 02:03:25 CEST [3099-338781] freeswitch_user at freeswitch_db
WARNING: there is already a transaction in progress
2018-05-24 02:03:25 CEST [3099-338782] freeswitch_user at freeswitch_db
ERROR: relation "registrations" does not exist at character 41
2018-05-24 02:03:25 CEST [3099-338783] freeswitch_user at freeswitch_db
STATEMENT: PREPARE "_PLAN0x140bf90" as delete from registrations where
reg_user='';EXECUTE "_PLAN0x140bf90"
2018-05-24 02:03:25 CEST [3099-338784] freeswitch_user at freeswitch_db
WARNING: there is already a transaction in progress
2018-05-24 02:03:25 CEST [3099-338785] freeswitch_user at freeswitch_db
ERROR: syntax error at or near "DROP" at character 29
2018-05-24 02:03:25 CEST [3099-338786] freeswitch_user at freeswitch_db
STATEMENT: PREPARE "_PLAN0x140bf90" as DROP TABLE registrations;EXECUTE
"_PLAN0x140bf90"
2018-05-24 02:03:25 CEST [3099-338787] freeswitch_user at freeswitch_db
WARNING: there is already a transaction in progress
2018-05-24 02:03:25 CEST [3099-338788] freeswitch_user at freeswitch_db
ERROR: syntax error at or near "CREATE" at character 29
2018-05-24 02:03:25 CEST [3099-338789] freeswitch_user at freeswitch_db
STATEMENT: PREPARE "_PLAN0x140bf90" as CREATE TABLE registrations (
reg_user VARCHAR(256),
realm VARCHAR(256),
token VARCHAR(256),
url TEXT,
expires INTEGER,
network_ip VARCHAR(256),
network_port VARCHAR(256),
network_proto VARCHAR(256),
hostname VARCHAR(256),
metadata VARCHAR(256)
);
;EXECUTE "_PLAN0x140bf90"
2018-05-24 02:03:25 CEST [3099-338790] freeswitch_user at freeswitch_db
WARNING: there is already a transaction in progress
2018-05-24 02:03:25 CEST [3099-338791] freeswitch_user at freeswitch_db LOG:
unexpected EOF on client connection with an open transaction
I tested with tables in psql database and without tables in psql database.
freeswitch at freeswitch-capa8> version
FreeSWITCH Version 1.9.0+git~20180516T211208Z~3c7db639fd~64bit (git 3c7db63
2018-05-16 21:12:08Z 64bit)
As I told in last mail, I'm working with last git version.
Then I have a last question, related to that (db), when it will work, all
mods will save the information to pgsql or a mix some to sqlite and some to
pgsql? I saw in other files the availibility to configure also db, like
/usr/local/freeswitch/conf/sip_profiles/internal.xml
root at freeswitch-capa8:/usr/local/freeswitch/conf# cat
/usr/local/freeswitch/conf/sip_profiles/internal.xml |grep db
<!-- Name of the db to use for this profile -->
<!--<param name="dbname" value="share_presence"/>-->
<!--<param name="odbc-dsn" value="dsn:user:pass"/>-->
<!--<param name="odbc-dsn" value="freeswitch:freeswitch_user:XXX"/>-->
<!-- <param name="odbc-dsn" value="pgsql://hostaddr=127.0.0.1
dbname=freeswitch_db user=freeswitch_user password='XXX' options='-c
client_min_messages=NOTICE' application_name='freeswitch'" /> -->
<!--all inbound reg will stored in the db using this domain -->
<param name="force-register-db-domain" value="$${domain}"/>
The config you see in the mail is the last configuration I defined.
Yesterday I get lost with wich config work or wich one no...
I feel I'm almost at end of this installation! I'm doing a step by step
manual in spanish to how to install last version of freeswitch, this will
be public and if you want I could upload to wiki.
Thanks you much for your help :)
References:
https://freeswitch.org/confluence/display/FREESWITCH/PostgreSQL+in+the+core
https://freeswitch.org/confluence/display/FREESWITCH/ODBC+DSN
https://freeswitch.org/confluence/display/FREESWITCH/FreeSWITCH+Databases
- Blackhold
http://blackhold.nusepas.com
@blackhold_
~> cal lluitar contra el fort per deixar de ser febles, i contra nosaltres
mateixos quan siguem forts (Xirinacs)
<°((( ><
2018-05-24 18:21 GMT+02:00 Michael Jerris <mike at jerris.com>:
> what tables are ending up in the recreated sqlite db? You need to set it
> for the modules that use sqlite too (like for mod_sofia). we have a global
> var for this in default configs.
>
> > On May 23, 2018, at 6:13 PM, Blackhold <blackholdmailer at gmail.com>
> wrote:
> >
> > Hi,
> > I have just installed freeswitch 1.9
> >
> > freeswitch at freeswitch-capa8> version
> > FreeSWITCH Version 1.9.0+git~20180516T211208Z~3c7db639fd~64bit (git
> 3c7db63 2018-05-16 21:12:08Z 64bit)
> >
> > And I'm trying to freeswitch use postgresql instead sqlitedb.
> >
> > I have configured the file switch.conf.xml of /usr/local/freeswitch/conf/autoload_configs
> with uncomenting and configuring this line
> >
> > <param name="core-db-dsn" value="pgsql://hostaddr=127.0.0.1
> dbname=freeswitch_db user=freeswitch_user password='****' options='-c
> client_min_messages=NOTICE'" />
> >
> > then I exported all sqlitedbs to sql and then imported to a database
> that I have created in postgresql
> >
> > I have run again /etc/init.d/freeswitch.sh start
> >
> > But seems it still is taking sqlitedb.
> >
> > I tried to move the sqlitedbs files to other side and turned on again
> freeswitch, but it creates sqlitedbs again.
> >
> > What I'm missing? what I'm doing wrong? what could I check?
>
>
> _________________________________________________________________________
> Professional FreeSWITCH Consulting Services:
> consulting at freeswitch.org
> http://www.freeswitchsolutions.com
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://confluence.freeswitch.org
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20180524/32eb2bb7/attachment-0001.html>
More information about the FreeSWITCH-users
mailing list