English (US) Italiano
Follow

Hotspot Database Recovery (Legacy Versions)

Versions 2.5 & 3.0

Applies to Platform: UTM 2.5, UTM 3.0
Updated on: 13th of August 2013

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.

hotspot_db_recovery.png

 

Warning

Before proceeding with this article, please make sure your Endian UTM Appliance is full up to date. For safety reasons, you are strongly encouraged to export your critical backups to external storage devices. In case that critical updates have recently been installed, please reboot the machine. Following the advices within this article means that you are aware of possible data loss and assumes that you know what you are doing. Consider also the possibility  to create copies of your backups.

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/efw/pgsql/
total 20
-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/efw/pgsql/psql-*.bz2

1a. Copying the database dump

The next step is to copy the database dump from the /var/lib/pgsql/backups/ directory:

Note

The /var/lib/pgsql/backups/ directory may contain multiple files, so select the most recent one. In case it still does not work, then you should try all of them, one by one, until you find the most recent working backup. In the command below replace YYYY-MM-DD-HH with the actual values for the filename, where YYYY stands for year, MM for month, DD for day, and HH for the hour at which the backup was created. With these values you can see the order of creation of the backups, from the newest to oldest one and pick the most recent one that works.
root@endian:~ # cp /var/lib/pgsql/backups/psql-YYYY-MM-DD-HH.dump.bz2 /var/efw/pgsql/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

Warning

This procedure will reset Hotspot to a clean database. The advantage of this procedure is that you can always follow it, letting you restore your backups. This means that even in the case that some backups contain items that trigger these problems (corruption etc.) you can repeat this procedure trying to restore one backup by one, going back until a clean one is found. Do not forget to export your backups to an external storage device so you can import them later in the GUI!

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/efw/pgsql/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.

Have more questions? Submit a request

Comments