Database in Watcher

This section provides the instructions on how to maintain the database used by Watcher.

• Install and primary settings
• Backup
• Migrate to PostgreSQL (necessary starting from version 19.03)
• Working with the database structure

Install and primary settings

• To install PostgreSQL, with root access in the console execute the command:

apt install postgresql

• Create a PostgreSQL user and a database. Type two commands in the console one by one. First, create the user vsaas:

sudo -u postgres createuser -P vsaas

• The system will prompt you to enter the password that will be used for the user vsaas:
  Enter password for new role: (come up with and enter the password of the Watcher main administrator)

• Enter the password one more time:
  Enter it again: (re-enter the password)

• Create the database vsaas_production with the created user vsaas as the owner:

sudo -u postgres createdb -O vsaas -e -E UTF8 -T template0 vsaas_production

• System's response if the database was created successfully: CREATE DATABASE vsaas_production OWNER vsaas ENCODING 'UTF8' TEMPLATE template0

Edit the database line in the Flussonic configuration file /etc/flussonic/flussonic.conf:

database postgresql://vsaas:VSAAS_PASSWORD@localhost/vsaas_production;

Replace VSAAS_PASSWORD with the real password of the vsaas user that you created when installing PostgreSQL. You can edit text files with the text editor nano.

Backup

Use the pg_dump tool to back up a database:

sudo -u postgres pg_dump vsaas_production > vsaas_production-$(date +%s)backup_dump.sql

As a result, a file with the name like vsaas_production-1630050597backup_dump.sql is created where the number represents Unix time at the moment of the backup creation.

Restore from the backup:

sudo -u postgres psql vsaas_production < vsaas_production-1630050597backup_dump.sql

Example scheduling auto backup

The easiest way to schedule a backup is cron tool.

Run this command in the command line:

crontab -e

At the first run, the system will prompt you to select the editor to open the cron configuration file. We recommend using nano which is first in the list:

Choose 1-4 [1]: 1

Add a line like this to the end of the file:

0 5 * * 1 sudo -u postgres pg_dump vsaas_production > /root/vsaas_production-backup-$(date +\%s).sql

This rule will allow to create a database backup and save it in the /root directory at 5 AM every Monday. You can setup other period by editing the numbers before the command. Please refer here for details.

After editing, press Ctrl+O to save and then Ctrl+X to exit the editor.

Migration from SQLite to PostgreSQL

The migration is necessary in Watcher 19.03 and higher.

1. Back up these files:

   /etc/flussonic/flussonic.conf

   /opt/flussonic/priv/vsaas.db

2. Install the latest version of Flussonic Watcher with SQLite support (19.05). Run the following commands:

   apt update

   apt install flussonic-watcher=19.05 flussonic=19.05 flussonic-erlang=21.3.6

   service flussonic restart

   Learn more about the update process

3. Make Backup

4. Install PostgreSQL

   apt install postgresql

5. Reload the Flussonic service:

   service flussonic restart

6. Restore the database from the backup.

7. Open the Watcher web interface and check that everything works (the data is present).

Upgrading the database structure

In some cases, your newly installed version of Flussonic Watcher may require some changes in the database structure. In this case you will see the following message in the web UI: