SOLVED edit db via bash

Status
Not open for further replies.

s2svoip

Member
Dec 9, 2019
252
7
18
44
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

Well-Known Member
Aug 13, 2017
1,388
364
83
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.

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

Member
Dec 9, 2019
252
7
18
44
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

Well-Known Member
Aug 13, 2017
1,388
364
83
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.

Code:
#!/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

Member
Dec 9, 2019
252
7
18
44
@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?
 
Last edited:

Adrian Fretwell

Well-Known Member
Aug 13, 2017
1,388
364
83
Ref: https://freeswitch.org/confluence/display/FREESWITCH/Reloading

Reloading XML:

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

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


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

Code:
root@a2estest2:~# fs_cli -x "sofia profile internal restart"
Reload XML [Success]
restarting: internal
 
  • Like
Reactions: s2svoip
Status
Not open for further replies.