[Freeswitch-users] Connecting freeswitch to postgresql
Blackhold
blackholdmailer at gmail.com
Thu May 24 18:31:00 UTC 2018
# OK for location of ODBC files
# output freeswitch using psql (I only edited switch.conf.xml file)
<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'" /> (switch.conf.xml file)
I see no errors related on logs (nor freeswitch, nor psql -debug5-),
obviously restarted psql to reload config.
in sofia.conf.xml file I found no option to configure it pointing to
database
no other config file, except switch.conf.xml has configured database
pointing
I tried to configure db.conf.xml file, with switch.conf.xml line but also
do nothing
# output freeswtich using odbc
<param name="core-db-dsn" value="freeswitch:freeswitch_user:XXX"/>
(switch.conf.xml file)
these are the first lines of postgresql log file
2018-05-24 20:23:15 CEST [7518-7] LOG: received fast shutdown request
2018-05-24 20:23:15 CEST [7518-8] LOG: aborting any active transactions
2018-05-24 20:23:15 CEST [7548-2] LOG: autovacuum launcher shutting down
2018-05-24 20:23:15 CEST [7545-1] LOG: shutting down
2018-05-24 20:23:15 CEST [7545-2] LOG: database system is shut down
2018-05-24 20:23:16 CEST [7568-1] LOG: database system was shut down at
2018-05-24 20:23:15 CEST
2018-05-24 20:23:16 CEST [7568-2] LOG: MultiXact member wraparound
protections are now enabled
2018-05-24 20:23:16 CEST [7567-1] LOG: database system is ready to accept
connections
2018-05-24 20:23:16 CEST [7572-1] LOG: autovacuum launcher started
2018-05-24 20:23:16 CEST [7574-1] [unknown]@[unknown] LOG: incomplete
startup packet
2018-05-24 20:23:39 CEST [7596-1] freeswitch_user at freeswitch_db ERROR:
relation "channels" does not exist at character 40
2018-05-24 20:23:39 CEST [7596-2] freeswitch_user at freeswitch_db STATEMENT:
PREPARE "_PLAN0x9f3f90" as delete from channels where
hostname='freeswitch-capa8';EXECUTE "_PLAN0x9f3f90"
2018-05-24 20:23:39 CEST [7596-3] freeswitch_user at freeswitch_db ERROR:
relation "calls" does not exist at character 40
2018-05-24 20:23:39 CEST [7596-4] freeswitch_user at freeswitch_db STATEMENT:
PREPARE "_PLAN0x9f3f90" as delete from calls where
hostname='freeswitch-capa8';EXECUTE "_PLAN0x9f3f90"
2018-05-24 20:23:39 CEST [7596-5] freeswitch_user at freeswitch_db ERROR:
relation "tasks" does not exist at character 40
2018-05-24 20:23:39 CEST [7596-6] freeswitch_user at freeswitch_db STATEMENT:
PREPARE "_PLAN0x9f3f90" as delete from tasks where
hostname='freeswitch-capa8';EXECUTE "_PLAN0x9f3f90"
2018-05-24 20:23:39 CEST [7596-7] freeswitch_user at freeswitch_db ERROR:
relation "aliases" does not exist at character 49
2018-05-24 20:23:39 CEST [7596-8] freeswitch_user at freeswitch_db STATEMENT:
PREPARE "_PLAN0x9f3f90" as select hostname from aliases;EXECUTE
"_PLAN0x9f3f90"
2018-05-24 20:23:39 CEST [7596-9] freeswitch_user at freeswitch_db ERROR:
syntax error at or near "DROP" at character 28
2018-05-24 20:23:39 CEST [7596-10] freeswitch_user at freeswitch_db
STATEMENT: PREPARE "_PLAN0x9f3f90" as DROP TABLE aliases;EXECUTE
"_PLAN0x9f3f90"
2018-05-24 20:23:39 CEST [7596-11] freeswitch_user at freeswitch_db ERROR:
syntax error at or near "CREATE" at character 28
2018-05-24 20:23:39 CEST [7596-12] freeswitch_user at freeswitch_db
STATEMENT: PREPARE "_PLAN0x9f3f90" as CREATE TABLE aliases (
sticky INTEGER,
alias VARCHAR(128),
command VARCHAR(4096),
hostname VARCHAR(256)
);
;EXECUTE "_PLAN0x9f3f90"
and now freeswitch isn't able to run.
If I enable odbc trace in odbcinst.ini
Trace=Yes
TraceFile=/var/log/odbctrace.log
Also there's no output....
In freeswitch log also there's nothing about what is happening :(
- 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:30 GMT+02:00 Michael Jerris <mike at jerris.com>:
> but that table layout is like that because you imported the tables, i was
> asking what is in the recreated sqlite ones. If it fails to connect like
> you later describe, its going to fall back, you should see stuff in the fs
> logs on startup about this. the odbc configs go in the normal place they
> go in the os, not in fs specific dirs. Those errors tell me something is
> up, but not sure what, might need to turn on some debug on pg side to see.
> some of our tests like to see if table schema is right throw errors then
> we act on them, if you restart a second time you should get a more useful
> set of errors.
>
>
> On May 24, 2018, at 1:11 PM, Blackhold <blackholdmailer at gmail.com> wrote:
>
> 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
>
>
> _________________________________________________________________________
> 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
>
>
>
> _________________________________________________________________________
> 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/84ed3b15/attachment-0001.html>
More information about the FreeSWITCH-users
mailing list