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: 

https://freeswitch.org/confluence/display/FREESWITCH/PostgreSQL+in+the+core

https://freeswitch.org/confluence/display/FREESWITCH/ODBC+DSN

https://freeswitch.org/confluence/display/FREESWITCH/FreeSWITCH+Databases

- Blackhold
http://blackhold.nusepas.com
@blackhold_
~> cal lluitar contra el fort per deixar de ser febles, i contra nosaltres mateixos quan siguem forts (Xirinacs)
<°((( ><

2018-05-24 18:21 GMT+02:00 Michael Jerris <mike@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@gmail.com> wrote:
>
> Hi,
> I have just installed freeswitch 1.9
>
> freeswitch@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@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@lists.freeswitch.org
http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
http://www.freeswitch.org