[Freeswitch-users] Connecting freeswitch to postgresql
Madovsky
infos at madovsky.org
Thu May 24 20:41:59 UTC 2018
remove these 2 lines
local all postgres peer
local all all peer
and replace with
local all all trust
in your pg_hba.conf
On 5/24/2018 1:26 PM, Blackhold wrote:
> Hi,
>
> I'm trying both ways, switching between one and the other... I
> initially want to use psql directly, not using odbc, but when using
> directly I get less info, well... logs files don't say nothing :( if
> the correct way is to use that:
>
> <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'" />
>
> so then, I only will use that, and the problem is my system, not
> freeswitch.
>
> odbc configuration is an old odbc configuration that I used some time
> ago when I had to use it... so... for actual psql db wich version is
> the ok?!
>
> Here you have the versions of libraries I have:
>
> # debian 8 fresh install (x86_64)
>
> root at freeswitch-capa8:~# cat /etc/debian_version
> 8.10
> root at freeswitch-capa8:~# dpkg -l |grep postgresql
> ii odbc-postgresql:amd64 1:09.03.0300-1
> amd64 ODBC driver for PostgreSQL
> ii postgresql 9.4+165+deb8u3
> all object-relational SQL database (supported version)
> ii postgresql-9.4 9.4.15-0+deb8u1
> amd64 object-relational SQL database, version 9.4 server
> ii postgresql-client 9.4+165+deb8u3
> all front-end programs for PostgreSQL (supported version)
> ii postgresql-client-9.4 9.4.15-0+deb8u1
> amd64 front-end programs for PostgreSQL 9.4
> ii postgresql-client-common 165+deb8u3
> all manager for multiple PostgreSQL client versions
> ii postgresql-common 165+deb8u3
> all PostgreSQL database-cluster manager
> root at freeswitch-capa8:~# dpkg -l |grep libpq
> ii libpq-dev 9.4.15-0+deb8u1
> amd64 header files for libpq5 (PostgreSQL library)
> ii libpq5:amd64 9.4.15-0+deb8u1
> amd64 PostgreSQL C client library
>
> all these packages are downloaded from sources
>
> the lines enabled at pg_hba.conf are these (the debian default ones)
>
> local all postgres peer
> local all all peer
> host all all 127.0.0.1/32 <http://127.0.0.1/32>
> md5
> host all all ::1/128 md5
>
> the script that I have used to do the installation is the one located
> in source code
>
> root at freeswitch-capa8:/usr/src/freeswitch/scripts# ls -l |grep
> raspbian-installer
> -rwxr-xr-x 1 root root 7922 May 23 20:26
> FreeSWITCH-debian-raspbian-installer.sh
>
>
>
>
> - 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:58 GMT+02:00 Madovsky <infos at madovsky.org
> <mailto:infos at madovsky.org>>:
>
> 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 <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/6380ec80/attachment-0001.html>
More information about the FreeSWITCH-users
mailing list