[Freeswitch-users] Connecting freeswitch to postgresql
Madovsky
infos at madovsky.org
Thu May 24 19:58:42 UTC 2018
First of all, pgsql:// does not use odbc, instead use odbc:// to use
odbc (btw your odbc.ini file shows Protocol=6.4 (!?) are you using
postgresql 6.x !??!)
what version of postgresql are you using? compiled yourself or from
system package?
check if there are not multiple versions of libpq.xx in your system.
check pg_hba.conf if localhost is on trust mode for all local users.
if you compiled yourself so be sure the right libpq.xx have been chosen.
On 5/24/2018 12:33 PM, Blackhold wrote:
> nope, it is not able to create stuff
>
> now I also have enabled that in switch.conf.xml
>
> <param name="auto-create-schemas" value="true"/>
> <param name="auto-clear-sql" value="true"/>
>
> tested with odbc and directly with psql and behaviour is the same.
>
> for psql the procedure I have followed is
>
> root at freeswitch-capa8:~# su - postgres
> root at freeswitch-capa8:~#postgres at freeswitch-capa8:~$ createuser
> freeswitch_user
> root at freeswitch-capa8:~#createdb -O freeswitch_user freeswitch_db
> root at freeswitch-capa8:~#psql freeswitch_db
> freeswitch_db=# ALTER USER freeswitch_user WITH PASSWORD 'XXX';
> freeswitch_db=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO
> freeswitch_user;
> freeswitch_db=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public
> TO freeswitch_user;
>
> the most beatiful was with odbc + tables created... in the last
> mail... freeswitch turned on, in other ways freeswitch doesn't turns on.
>
> logs doesn't talk about permissions....
>
>
>
>
> - 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 21:20 GMT+02:00 Michael Jerris <mike at jerris.com
> <mailto:mike at jerris.com>>:
>
> you shouldn't need to pre-import the tables, if the permissions
> are right, it should create the tables and indexes for you.
>
>
>> On May 24, 2018, at 3:15 PM, Blackhold <blackholdmailer at gmail.com
>> <mailto:blackholdmailer at gmail.com>> wrote:
>>
>> I continue with tests
>>
>> I think I know wich problem I had with errors
>>
>> I have missed to grant privileges in psql:
>>
>> GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO
>> freeswitch_user;
>> GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO
>> freeswitch_user;
>>
>> # with tables imported from sqlite it shows (freeswitch runs)
>>
>> root at freeswitch-capa8:/var/log/postgresql# tail -f
>> postgresql-9.4-main.log
>>
>> 2018-05-24 21:03:34 CEST [8623-1] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-2] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index recovery1 on
>> recovery(technology);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-3] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-4] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index recovery2 on
>> recovery(profile_name);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-5] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-6] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index recovery3 on
>> recovery(uuid);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-7] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-8] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index recovery3 on
>> recovery(runtime_uuid);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-9] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-10] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index alias1 on
>> aliases (alias);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-11] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-12] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index tasks1 on
>> tasks (hostname,task_id);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-13] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-14] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete1 on
>> complete (a1,hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-15] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-16] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete2 on
>> complete (a2,hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-17] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-18] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete3 on
>> complete (a3,hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-19] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-20] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete4 on
>> complete (a4,hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-21] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-22] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete5 on
>> complete (a5,hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-23] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-24] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete6 on
>> complete (a6,hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-25] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-26] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete7 on
>> complete (a7,hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-27] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-28] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete8 on
>> complete (a8,hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-29] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-30] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete9 on
>> complete (a9,hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-31] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-32] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete10
>> on complete (a10,hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-33] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-34] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index complete11
>> on complete (a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,hostname);EXECUTE
>> "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-35] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-36] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index
>> nat_map_port_proto on nat (port,proto,hostname);EXECUTE
>> "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-37] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-38] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index channels1 on
>> channels(hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-39] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-40] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index calls1 on
>> calls(hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-41] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-42] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index chidx1 on
>> channels (hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-43] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-44] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index uuindex on
>> channels (uuid, hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-45] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-46] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index uuindex2 on
>> channels (call_uuid);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-47] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-48] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index callsidx1 on
>> calls (hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-49] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-50] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index eruuindex on
>> calls (caller_uuid, hostname);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-51] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-52] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index eeuuindex on
>> calls (callee_uuid);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-53] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-54] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index eeuuindex2
>> on calls (call_uuid);EXECUTE "_PLAN0x123ff90"
>> 2018-05-24 21:03:34 CEST [8623-55] freeswitch_user at freeswitch_db
>> ERROR: syntax error at or near "create" at character 29
>> 2018-05-24 21:03:34 CEST [8623-56] freeswitch_user at freeswitch_db
>> STATEMENT: PREPARE "_PLAN0x123ff90" as create index regindex1 on
>> registrations (reg_user,realm,hostname);EXECUTE "_PLAN0x123ff90"
>>
>> # with a clean database
>>
>> start dropping last errors, and don't create any table in db.
>> Freeswitch doesn't turns on.
>>
>> each time I stop freeswitch when it don't runs, it keeps a
>> process running that I have to kill manually
>>
>> root at freeswitch-capa8:/var/log/postgresql# ps aux |grep free
>> root 8706 39.6 0.7 141296 15244 ? S<l 21:08 1:13
>> /usr/local/freeswitch/bin/freeswitch -nc
>>
>>
>> # with granted permisions if I turn configuration to not use
>> odbc, using psql directly
>>
>> freeswitch turns on, there's no errors in psql log file and no
>> table was created on db.
>>
>>
>>
>> - Blackhold
>> http://blackhold.nusepas.com <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 20:31 GMT+02:00 Blackhold <blackholdmailer at gmail.com
>> <mailto:blackholdmailer at gmail.com>>:
>>
>> # 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 <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
>> <mailto: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
>>> <mailto: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,
>>> portINTEGER,
>>> protoINTEGER,
>>> 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/PostgreSQL+in+the+core>
>>> https://freeswitch.org/confluence/display/FREESWITCH/ODBC+DSN
>>> <https://freeswitch.org/confluence/display/FREESWITCH/ODBC+DSN>
>>> https://freeswitch.org/confluence/display/FREESWITCH/FreeSWITCH+Databases
>>> <https://freeswitch.org/confluence/display/FREESWITCH/FreeSWITCH+Databases>
>>>
>>>
>>>
>>> - Blackhold
>>> http://blackhold.nusepas.com <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 <mailto: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
>>> <mailto: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
>>> <mailto:consulting at freeswitch.org>
>>> http://www.freeswitchsolutions.com
>>> <http://www.freeswitchsolutions.com/>
>>>
>>> Official FreeSWITCH Sites
>>> http://www.freeswitch.org <http://www.freeswitch.org/>
>>> http://confluence.freeswitch.org
>>> <http://confluence.freeswitch.org/>
>>> http://www.cluecon.com <http://www.cluecon.com/>
>>>
>>> FreeSWITCH-users mailing list
>>> FreeSWITCH-users at lists.freeswitch.org
>>> <mailto:FreeSWITCH-users at lists.freeswitch.org>
>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>> <http://lists.freeswitch.org/mailman/listinfo/freeswitch-users>
>>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>>> <http://lists.freeswitch.org/mailman/options/freeswitch-users>
>>> http://www.freeswitch.org <http://www.freeswitch.org/>
>>>
>>>
>>> _________________________________________________________________________
>>> Professional FreeSWITCH Consulting Services:
>>> consulting at freeswitch.org <mailto:consulting at freeswitch.org>
>>> http://www.freeswitchsolutions.com
>>> <http://www.freeswitchsolutions.com/>
>>>
>>> Official FreeSWITCH Sites
>>> http://www.freeswitch.org <http://www.freeswitch.org/>
>>> http://confluence.freeswitch.org
>>> <http://confluence.freeswitch.org/>
>>> http://www.cluecon.com <http://www.cluecon.com/>
>>>
>>> FreeSWITCH-users mailing list
>>> FreeSWITCH-users at lists.freeswitch.org
>>> <mailto:FreeSWITCH-users at lists.freeswitch.org>
>>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>>> <http://lists.freeswitch.org/mailman/listinfo/freeswitch-users>
>>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>>> <http://lists.freeswitch.org/mailman/options/freeswitch-users>
>>> http://www.freeswitch.org <http://www.freeswitch.org/>
>>
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org <mailto:consulting at freeswitch.org>
>> http://www.freeswitchsolutions.com
>> <http://www.freeswitchsolutions.com/>
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org <http://www.freeswitch.org/>
>> http://confluence.freeswitch.org
>> <http://confluence.freeswitch.org/>
>> http://www.cluecon.com <http://www.cluecon.com/>
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> <mailto:FreeSWITCH-users at lists.freeswitch.org>
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> <http://lists.freeswitch.org/mailman/listinfo/freeswitch-users>
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> <http://lists.freeswitch.org/mailman/options/freeswitch-users>
>> http://www.freeswitch.org <http://www.freeswitch.org/>
>>
>>
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org <mailto:consulting at freeswitch.org>
>> http://www.freeswitchsolutions.com
>> <http://www.freeswitchsolutions.com>
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://confluence.freeswitch.org <http://confluence.freeswitch.org>
>> http://www.cluecon.com
>>
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> <mailto:FreeSWITCH-users at lists.freeswitch.org>
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> <http://lists.freeswitch.org/mailman/listinfo/freeswitch-users>
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> <http://lists.freeswitch.org/mailman/options/freeswitch-users>
>> http://www.freeswitch.org
>
>
> _________________________________________________________________________
> Professional FreeSWITCH Consulting Services:
> consulting at freeswitch.org <mailto:consulting at freeswitch.org>
> http://www.freeswitchsolutions.com
> <http://www.freeswitchsolutions.com>
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://confluence.freeswitch.org <http://confluence.freeswitch.org>
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> <mailto:FreeSWITCH-users at lists.freeswitch.org>
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> <http://lists.freeswitch.org/mailman/listinfo/freeswitch-users>
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> <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/2eb0cce3/attachment-0001.html>
More information about the FreeSWITCH-users
mailing list