Backing Up & Restoring FusionPBX with PostgreSQL

Not a fan of PostgreSQL, but lately many people have come to me regardless I have published I work with MySQL/MariaDB. Because of this, I want to share with you a set of scripts to backup and restore your deployment. I am not the author of these scripts, I am just doing little modifications to clean them up and to do some more professional and advanced ones that will work not only with PostgreSQL but with MySQL and MariaDB.

These scripts are thought to work with two stand-alone servers. No clustering or load-balancing is involved.

Back Up your FusionPBX

First, the script that does the backup. You can put it in a crontab if you wish. Modify it to fit your paths and needs.

#!/bin/sh 
now=$(date +%Y-%m-%d) 
database_host=127.0.0.1 
database_port=5432 
export PGPASSWORD="YOUR_SUPER_SECRET_PASSWORD" 

echo "Server Maintenance" 
mkdir -p /var/backups/fusionpbx/postgresql 
#delete backups older 3 days 
find /var/backups/fusionpbx/postgresql/fusionpbx_pgsql* -mtime +2 -exec rm {} \;
find /var/backups/fusionpbx/*.tgz -mtime +2 -exec rm {} \;

#delete postgres logs older than 7 days 
find /var/log/postgresql/postgresql-9.4-main* -mtime +7 -exec rm {} \; 

#delete freeswitch logs older 3 days 
find /var/log/freeswitch/freeswitch.log.* -mtime +2 -exec rm {} \; 

#delete fax older than 90 days 
#find /var/lib/freeswitch/storage/fax/*  -name '*.tif' -mtime +90 -exec rm {} \; 
#find /var/lib/freeswitch/storage/fax/*  -name '*.pdf' -mtime +90 -exec rm {} \; 
#find /var/lib/freeswitch/recordings/*/archive/*  -name '*.wav' -mtime +7 -exec rm {} \; 
#find /var/lib/freeswitch/recordings/*/archive/*  -name '*.mp3' -mtime +7 -exec rm {} \; 
#psql --host=$database_host --port=$database_port --username=fusionpbx -c "delete from v_fax_files WHERE fax_date < 
NOW() - INTERVAL '90 days'"

#delete voicemail older than 90 days 
#find /usr/local/freeswitch/storage/voicemail/*  -name 'msg_*.wav' -mtime +90 -exec rm {} \; 
#psql --host=$database_host --port=$database_port --username=fusionpbx -c "delete from v_voicemail_messages WHERE to
_timestamp(created_epoch) < NOW() - INTERVAL '90 days'" 

#delete call detail records older 90 days 
#psql --host=$database_host --port=$database_port --username=fusionpbx -c "delete from v_xml_cdr WHERE start_stamp <
NOW() - INTERVAL '90 days'" 

echo "Starting Backup" 
#backup the database 
pg_dump --verbose -Fc --host=$database_host --port=$database_port -U fusionpbx fusionpbx --schema=public -f /var/bac
kups/fusionpbx/postgresql/fusionpbx_pgsql_$now.sql

#backup the files and directories 
#tar -zvcf /var/backups/fusionpbx/backup_$now.tgz /var/backups/fusionpbx/postgresql/fusionpbx_pgsql_$now.sql /var/ww
w/fusionpbx

Restore your FusionPBX

This script should be run on the backup server.

#!/bin/sh
now=$(date +%Y-%m-%d)
ssh_server=PRIMARY_SERVER_IP
database_host=127.0.0.1
database_port=5432
export PGPASSWORD="YOUR_SUPER_SECRET_PASSWORD"

#run the remote backup
ssh -p 22 root@$ssh_server "nice -n -20 /usr/loca/sbin/fusionpbx-backup.sh"

#delete freeswitch logs older 7 days
find /var/log/freeswitch/freeswitch.log.* -mtime +7 -exec rm {} \; 

#synchronize the backup directory
#rsync -avz -e 'ssh -p 22' root@$ssh_server:/var/backups/fusionpbx /var/backups 
rsync -avz -e 'ssh -p 22' root@$ssh_server:/var/backups/fusionpbx/postgresql/fusionpbx_pgsql_$now.sql /var/backups/fusionpbx/postgresql 
rsync -avz -e 'ssh -p 22' root@$ssh_server:/var/www/fusionpbx /var/www 
rsync -avz -e 'ssh -p 22' root@$ssh_server:/etc/fusionpbx /etc 
find /var/backups/fusionpbx/postgresql -mtime +2 -exec rm {} \; 

rsync -avz -e 'ssh -p 22' root@$ssh_server:/etc/freeswitch/ /etc
rsync -avz -e 'ssh -p 22' root@$ssh_server:/var/lib/freeswitch/storage /var/lib/freeswitch/ 
rsync -avz -e 'ssh -p 22' root@$ssh_server:/var/lib/freeswitch/scripts /var/lib/freeswitch/ 
rsync -avz -e 'ssh -p 22' root@$ssh_server:/var/lib/freeswitch/sounds /var/lib/freeswitch/ 
rsync -avz -e 'ssh -p 22' root@$ssh_server:/var/lib/freeswitch/recordings /var/lib/freeswitch/ 

echo "Restoring the Backup" 
#extract the backup from the tgz file 
#tar -xvpzf /var/backups/fusionpbx/backup_$now.tgz -C / 

#remove the old database 
psql --host=$database_host --port=$database_port  --username=fusionpbx -c 'drop schema public cascade;' 
psql --host=$database_host --port=$database_port  --username=fusionpbx -c 'create schema public;' 
#restore the database 
PGPASSWORD="YOUR_SUPER_SECRET_PASSWORD" pg_restore -v -Fc --host=$database_host --port=$database_port --dbname=fusionpbx --username=fusionpbx /var/backups/fusionpbx/postgresql/fusionpbx_pgsq
l_$now.sql 

#restart freeswitch 
service freeswitch restart 
echo "Restore Complete";