Hi,
The tables are:
ostgres@freeswitch-capa8:~/sql$ psql freeswitch_db
freeswitch_db-# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------------------------+-------+----------
public | aliases | table | postgres
public | calls | table | postgres
public | channels | table | postgres
public | complete | table | postgres
public | db_data | table | postgres
public | fifo_bridge | table | postgres
public | fifo_callers | table | postgres
public | fifo_outbound | table | postgres
public | group_data | table | postgres
public | interfaces | table | postgres
public | json_store | table | postgres
public | limit_data | table | postgres
public | nat | table | postgres
public | recovery | table | postgres
public | registrations | table | postgres
public | sip_authentication | table | postgres
public | sip_dialogs | table | postgres
public | sip_presence | table | postgres
public | sip_registrations | table | postgres
public | sip_shared_appearance_dialogs | table | postgres
public | sip_shared_appearance_subscriptions | table | postgres
public | sip_subscriptions | table | postgres
public | tasks | table | postgres
public | voicemail_msgs | table | postgres
public | voicemail_prefs | table | postgres
(25 rows)
I continued testing and found that if I configure switch.conf.xml file like this:
<param name="core-db-dsn" value="pgsql://hostaddr=127.0.0.1 dbname=freeswitch_db user=freeswitch_user password='****' options='-c client_min_messages=NOTICE'" />
When I turn on freeswitch, postgresql don't output nothing. Then I tried to configure freeswitch using odbc I then YES I get something in postgresql logs
For odbc I have something not clear, it is mandatory to put files in /usr/local/freeswitch/etc ? or leaving them at default directory (/etc) is enough?
Here I show the config of odbc files:
root@freeswitch-capa8:/etc# cat odbc.ini
[freeswitch]
; WARNING: The old psql odbc driver psqlodbc.so is now renamed psqlodbcw.so
; in version 08.x. Note that the library can also be installed under an other
; path than /usr/local/lib/ following your installation.
; Driver = /usr/lib64/odbc/psqlodbcw.so
; Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
Description=Connection to LDAP/POSTGRESQL
Servername=localhost
Port=5432
Protocol=6.4
FetchBufferSize=99
Username=freeswitch_user
Password=XXX
Database=freeswitch_db
ReadOnly=no
Debug=0
CommLog=0
root@freeswitch-capa8:/etc# cat odbcinst.ini
[PostgreSQL]
Description = PostgreSQL driver for Linux & Win32
;Driver = /usr/lib64/odbc/psqlodbcw.so
Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
Threading = 0
MaxLongVarcharSize=65536
And then connect to psql through odbc
root@freeswitch-capa8:/etc# isql -v freeswitch
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
Then in file switch.conf.xml I changed db connection configuration
root@freeswitch-capa8:/usr/local/freeswitch/conf/autoload_configs# cat switch.conf.xml |grep core-db-dsn
<param name="core-db-dsn" value="
pgsql://hostaddr=127.0.0.1 dbname=freeswitch_db user=freeswitch_user password='XXX' options='-c client_min_messages=NOTICE'" />
<!--<param name="core-db-dsn" value="freeswitch:freeswitch_user:XXX"/>-->
Last night SwK user at freenode channel told me that freeswitch on turning on, it creates tables at database (thanks SwK).
I spect that configuration of odbc is working but drops lot of errors (here you have some lines):
2018-05-24 02:03:25 CEST [3099-338780] freeswitch_user@freeswitch_db STATEMENT: PREPARE "_PLAN0x140bf90" as CREATE TABLE nat (
sticky INTEGER,
port INTEGER,
proto INTEGER,
hostname VARCHAR(256)
);
;EXECUTE "_PLAN0x140bf90"
2018-05-24 02:03:25 CEST [3099-338781] freeswitch_user@freeswitch_db WARNING: there is already a transaction in progress
2018-05-24 02:03:25 CEST [3099-338782] freeswitch_user@freeswitch_db ERROR: relation "registrations" does not exist at character 41
2018-05-24 02:03:25 CEST [3099-338783] freeswitch_user@freeswitch_db STATEMENT: PREPARE "_PLAN0x140bf90" as delete from registrations where reg_user='';EXECUTE "_PLAN0x140bf90"
2018-05-24 02:03:25 CEST [3099-338784] freeswitch_user@freeswitch_db WARNING: there is already a transaction in progress
2018-05-24 02:03:25 CEST [3099-338785] freeswitch_user@freeswitch_db ERROR: syntax error at or near "DROP" at character 29
2018-05-24 02:03:25 CEST [3099-338786] freeswitch_user@freeswitch_db STATEMENT: PREPARE "_PLAN0x140bf90" as DROP TABLE registrations;EXECUTE "_PLAN0x140bf90"
2018-05-24 02:03:25 CEST [3099-338787] freeswitch_user@freeswitch_db WARNING: there is already a transaction in progress
2018-05-24 02:03:25 CEST [3099-338788] freeswitch_user@freeswitch_db ERROR: syntax error at or near "CREATE" at character 29
2018-05-24 02:03:25 CEST [3099-338789] freeswitch_user@freeswitch_db STATEMENT: PREPARE "_PLAN0x140bf90" as CREATE TABLE registrations (
reg_user VARCHAR(256),
realm VARCHAR(256),
token VARCHAR(256),
url TEXT,
expires INTEGER,
network_ip VARCHAR(256),
network_port VARCHAR(256),
network_proto VARCHAR(256),
hostname VARCHAR(256),
metadata VARCHAR(256)
);
;EXECUTE "_PLAN0x140bf90"
2018-05-24 02:03:25 CEST [3099-338790] freeswitch_user@freeswitch_db WARNING: there is already a transaction in progress
2018-05-24 02:03:25 CEST [3099-338791] freeswitch_user@freeswitch_db LOG: unexpected EOF on client connection with an open transaction
I tested with tables in psql database and without tables in psql database.
freeswitch@freeswitch-capa8> version
FreeSWITCH Version 1.9.0+git~20180516T211208Z~3c7db639fd~64bit (git 3c7db63 2018-05-16 21:12:08Z 64bit)
As I told in last mail, I'm working with last git version.
Then I have a last question, related to that (db), when it will work, all mods will save the information to pgsql or a mix some to sqlite and some to pgsql? I saw in other files the availibility to configure also db, like /usr/local/freeswitch/conf/sip_profiles/internal.xml
root@freeswitch-capa8:/usr/local/freeswitch/conf# cat /usr/local/freeswitch/conf/sip_profiles/internal.xml |grep db
<!-- Name of the db to use for this profile -->
<!--<param name="dbname" value="share_presence"/>-->
<!--<param name="odbc-dsn" value="dsn:user:pass"/>-->
<!--<param name="odbc-dsn" value="freeswitch:freeswitch_user:XXX"/>-->
<!-- <param name="odbc-dsn" value="
pgsql://hostaddr=127.0.0.1 dbname=freeswitch_db user=freeswitch_user password='XXX' options='-c client_min_messages=NOTICE' application_name='freeswitch'" /> -->
<!--all inbound reg will stored in the db using this domain -->
<param name="force-register-db-domain" value="$${domain}"/>
The config you see in the mail is the last configuration I defined. Yesterday I get lost with wich config work or wich one no...
I feel I'm almost at end of this installation! I'm doing a step by step manual in spanish to how to install last version of freeswitch, this will be public and if you want I could upload to wiki.
Thanks you much for your help :)
References: