Hey! an another question... In wich table will be stored users? now if I run list_clients or list_users, sorry, I'm new in freeswitch, it picks the configuration files. Wich data I have to delete from config files once db is running? Thanks - 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 19:11 GMT+02:00 Blackhold <blackholdmailer@gmail.com>:
Hi, The tables are:
ostgres@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@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@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@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@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@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@freeswitch_db WARNING: there is already a transaction in progress 2018-05-24 02:03:25 CEST [3099-338782] freeswitch_user@freeswitch_db ERROR: relation "registrations" does not exist at character 41 2018-05-24 02:03:25 CEST [3099-338783] freeswitch_user@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@freeswitch_db WARNING: there is already a transaction in progress 2018-05-24 02:03:25 CEST [3099-338785] freeswitch_user@freeswitch_db ERROR: syntax error at or near "DROP" at character 29 2018-05-24 02:03:25 CEST [3099-338786] freeswitch_user@freeswitch_db STATEMENT: PREPARE "_PLAN0x140bf90" as DROP TABLE registrations;EXECUTE "_PLAN0x140bf90" 2018-05-24 02:03:25 CEST [3099-338787] freeswitch_user@freeswitch_db WARNING: there is already a transaction in progress 2018-05-24 02:03:25 CEST [3099-338788] freeswitch_user@freeswitch_db ERROR: syntax error at or near "CREATE" at character 29 2018-05-24 02:03:25 CEST [3099-338789] freeswitch_user@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@freeswitch_db WARNING: there is already a transaction in progress 2018-05-24 02:03:25 CEST [3099-338791] freeswitch_user@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@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@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@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@gmail.com> wrote:
Hi, I have just installed freeswitch 1.9
freeswitch@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@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@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org