Skip to content

Database in Watcher

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

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 watcher with the created user vsaas as the owner:
sudo -u postgres createdb -O vsaas -e -E UTF8 -T template0 watcher
  • System's response if the database was created successfully:

CREATE DATABASE watcher OWNER vsaas ENCODING 'UTF8' TEMPLATE template0

Backup

Use the pg_dump tool to back up a database:

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

Note

If run under root, this command may result in error like could not change directory to "/root": Permission denied. The backup will still be created and saved. You may run the command pg_dump -h localhost -d watcher -U vsaas > backup.sql instead to avoid the error.

As a result, a file with the name like watcher-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 watcher < watcher-1630050597backup_dump.sql

Caution

It is strongly recommended that you configure auto backup on a regular basis.

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 watcher > /root/watcher-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:

Updating the Database Structure

Caution

First of all, make sure to backup the database and contact our technical support service at support@flussonic.com.

Please refer here for instructions on backing up the database.