Search code examples
luafreeswitch

Trying to connect to a PostgreSQL DB via "pgsql" DSN returns "ERR [unable to open database file]" on FreeSWITCH 1.8

When I try to connect to a remote PostgreSQL server, I get

2019-08-23[ERR] switch_core_db.c:223 SQL ERR [unable to open database file]

when using the FreeSWITCH Lua API with a pgsql connect string:

conn_string =
  "pgsql://hostaddr=1.2.3.4"                 ..
  " dbname=my-db"                            ..
  " user=postgres"                           ..
  " password=postgres"                       ..
  " options='-c client_min_messages=NOTICE'" ..
  " application_name='freeswitch'"

freeswitch.Dbh(conn_string)

(as recommended in the docs)

This setup has been working flawlessly, even across FreeSWITCH service restarts, then I foolishly did a sudo apt update && sudo apt upgrade on Debian 9, and that was it.

Solution

  • In my final frustration I resorted to re-create the entire VM with a new FreeSWITCH installation, and found a new section in the Debian 9 Stretch install guide:

    1.10 incompatible change

    because now Fs1.10 packages are installed even if you still use the 1.8 repo, you better aware of an incompatible change:

    pgsql is no more in core, but in aptly named mod_pgsql.

    You MUST NOT load mod_pgsql in modules.conf.xml (do not work), but in a special additional xml file, in same "autoload_configs" directory.

    Filename: pre_load_modules.conf.xml

    <configuration name="pre_load_modules.conf" description="Modules">
  <modules>
    <!-- Databases -->
    <!-- <load module="mod_mariadb"/> -->
    <load module="mod_pgsql"/>
  </modules>
</configuration>

    So when I did sudo apt upgrade, FreeSWITCH got upgraded from 1.8 to 1.10.

    (2019/08/23) Update: Updating pre_load_modules.conf.xml didn't work, despite the warning above. It only connected after adding mod_pgsql to modules.conf.xml.

    The full solution on Debian 9 Stretch:

    1. Add <load module="mod_pgsql"/> to modules.conf.xml.
    2. sudo systemctl stop freeswitch
    3. sudo systemctl start freeswitch

    (2019/09/03) Update and warning, from Andrey Volk, SignalWire Platform Engineer:

    Andrey Volk: "Do not add mod_pgsql to modules.conf.xml unless you know what you are doing. Use pre_load_modules.conf.xml. And if there is any problem with that, create a Jira ticket."

    toraritte: "why would it be an issue to load mod_pgsql in module.conf.xml?"

    Andrey Volk: "The difference is that the Core may want to use that database as well. But it's the Core, it loads BEFORE any module. So we need a special place where database modules (just in case one of them is for the Core) are loaded BEFORE any other module type. On the other hand it does not matter what file to use if that database module is not used by the Core. It should just go first. If it's for LUA, load it before LUA. But we don't recommend/document that.

    If pre_load_modules.conf.xml is not recognized, then it's probably a result of a bad upgrade. Make sure all freeswitch packages were upgraded (including main one)."