Applies to Platform: UTM 3.0.5
Updated on: 19th of May 2015
This lesson guides you in the recovery or reset of the Endian Hotspot's database. The following diagram represents the flow of the three procedures that lead to different recovery and restoration modalities of the Hotspot Database. According to your situation you should adhere to a specific chapter to ensure your recovery is successful, though it is suggested to read the whole article.
A. How to restore the most recent Hotspot database dump
This procedure will restore the latest automatic backup of the database. Verify that your Endian UTM Appliance is up to date, then follow all the points in the procedure:
1. Make sure the automatic backup exists
Execute the following command to verify you have a backup: you should obtain an output like this:
root@endian:~ # ls -l /var/pgsql_backup/
-rw-r--r-- 1 root root 17808 Jul 31 01:25 psql-latest.dump.bz2
Check that the size of the database is not zero (it is 17808 bytes in this case) since a size of 0 would mean that something's wrong with the database.
2. Stop the postgresql database
In order to make sure that postgresql doesn't intervene with our procedure, you should stop it with the following command:
root@endian:~ # /etc/init.d/postgresql stop
2a. Kill postgresql (optional)
Issue the following command ONLY if stopping postgresql daemon fails (you get a "FAILED" message as output of the previous command), by sending a KILL signal to postmaster using the command:
root@endian:~ # killall -9 postmaster
3. Delete all database data and configuration
The /var/lib/pgsql/data contains the WAL (Write-Ahead Logging) database structure as well as its indexes and configuration file. Remove this structure, which will be repaired in the next step, with the following command:
root@endian:~ # rm -rf /var/lib/pgsql/data
4. Recreate the configuration
To recreate the configuration, WAL database indexes, and structure, you simply need to restart the postgresql database. This is accomplished when you issue the following command:
root@endian:~ # /etc/init.d/postgresql start
5. Restart the Hotspot service
Last step is to ensure that the modifications take effect and occur by entering the next command:
root@endian:~ # jobcontrol restart hotspot --force
B. How to restore a Hotspot database dump from a specific backup
There are a few situations in which a database may be corrupted and restoring it causes the issues to happen again. Signs of a corrupted database recovery include the display oferror messages while using the hotspot, the impossibility to carry out simple administrative tasks (e.g., creating tickets), and the occurrence of the same issues you experienced before.
1. Remove the latest database dump
The following command will clean the /var/efw/pgsql directory of the latest database dump.
root@endian:~ # rm /var/pgsql_backup/psql-*.bz2
1a. Copying the database dump
The next step is to copy the database dump from the /var/lib/pgsql/backups/ directory:
root@endian:~ # cp /var/lib/pgsql/backups/psql-YYYY-MM-DD-HH.dump.bz2 /var/pgsql_backup/psql-latest.dump.bz2
In order to restore the database dump, issue the commands from point 2 to 5 in chapter A. Verify carefully that everything is working as expected.
In the uncommon case that something is wrong or this procedure has failed, then please follow the procedure described in the section D.
C. Restore the Hotspot to a clean database
1. Removing backup entries and recreating a clean database
Use the following commands to clear out the backup files. Now it is very important, before proceeding further, to export your backups from the GUI to an offline storage from where you can import them back later on!
root@endian:~ # mv /var/lib/pgsql/backups/* /var/tmp/
root@endian:~ # mv /var/pgsql_backup/psql-*.bz2 /var/tmp/
In order to restore the clean database, issue the commands from point 2 to 5 in chapter A.
1a. Restore Hotspot backup from GUI
After the database was checked and validated, the backup can be restored from the GUI. Make sure that the backup which is restored is not a corrupted one.
If the issues still arise, repeat the procedure with another backup whose integrity has been verified.
D. Troubleshooting: errors when navigating to Hotspot > Administration Interface > Settings or failure to recover
If you have accomplished all the steps before and still having issues, then please restore a backup, wait for the appliance to reboot and proceed again with the steps from 2 to 5 in chapter A. This amounts to restore again the backup, but in the end you should find that the database is online again and working properly.