[Freeswitch-users] Connecting freeswitch to postgresql

Blackhold blackholdmailer at gmail.com
Thu May 24 19:33:07 UTC 2018


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>:

> 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> 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
> @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>:
>
>> # 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_u
>>> ser: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_u
>>> ser: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/Postgre
>>> SQL+in+the+core
>>> https://freeswitch.org/confluence/display/FREESWITCH/ODBC+DSN
>>> https://freeswitch.org/confluence/display/FREESWITCH/FreeSWI
>>> TCH+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/free
>>>> switch-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
>>>
>>
>>
> _________________________________________________________________________
> 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/e6aaa800/attachment-0001.html>


More information about the FreeSWITCH-users mailing list