SOLVED edit db via bash

[s2svoip]

Anyone know if it would it be possible to edit the db via bash?, I am looking to modify internal & external ext-rtp-ip/ext-sip-ip

 

[Adrian Fretwell]

SIP profile data is held using three tables: v_sip_profile_domains, v_sip_profiles, and v_sip_profile_settings. Work out what records you need to change and issue an update statement using psql.

root@a2estest2:~# su - postgres
postgres@a2estest2:~$ psql fusionpbx
psql (11.9 (Debian 11.9-0+deb10u1))
Type "help" for help.

fusionpbx=# select * from v_sip_profiles;
fusionpbx=# \q
postgres@a2estest2:~$ exit
logout
root@a2estest2:~#

 

[s2svoip]

Thanks for the pointers Adrian, got the records identified and ready to update, however scripting this, the script just halts at:

postgres@servername:~$

and does not process the next commands, below is what I have in the .sh file, would I need a prefix before the psql commands?


su - postgres
psql fusionpbx
UPDATE v_sip_profile_settings SET sip_profile_setting_value = '123.123.123.123' WHERE sip_profile_setting_value = '123.123.123.999';
\q
exit

sudo service freeswitch restart

 

[Adrian Fretwell]

There are many different ways you could do this. Switching to the postgres user is convenient because you do not need credentials. You can use psql as any user of you supply credentials.

Here is an example of a simple bash script to execute some SQL contained in a file, the name of which is passed on the command line.

#!/bin/bash

#settings
export PGPASSWORD="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
db_host=127.0.0.1
db_port=5432

if [ "$#" -lt 1 ]; then
    echo "You must supply the name of a file containing SQL name as a command line argument."
    exit 1
fi

if [ ! -f "$1" ]
then
  echo "The SQL file does not exist."
  exit 1
fi

echo ""

#show message to the console
psql --host=$db_host --port=$db_port --username=fusionpbx < $1
echo " "
echo "SQL Completed."

 

[s2svoip]

thanks, Adrian, that works perfect! as always thank you!

 

[s2svoip]

@Adrian Fretwell one last question, at the end of this I issue a service freewitch restart - however I see some nat audio issues unless I flush the cache, reload XML, restart internal & external profiles - are there bash commands to do all that?

 

[Adrian Fretwell]

Ref: https://freeswitch.org/confluence/display/FREESWITCH/Reloading

Reloading XML:

root@a2estest2:~# fs_cli -x "reloadxml"
+OK [Success]

root@a2estest2:~# fs_cli -x "xml_flush_cache"
+OK cleared 0 entries

Sofia profile rescan - Less intrusive - no calls dropped:

root@a2estest2:~# fs_cli -x "sofia profile internal rescan"
Reload XML [Success]
+OK scan complete

Sofia profile restart - More intrusive - all profile calls dropped:

root@a2estest2:~# fs_cli -x "sofia profile internal restart"
Reload XML [Success]
restarting: internal

 

[s2svoip]

amazing! thank you!