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 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

There are two ways to back up a database.

  • using the built-in utility:

    /opt/flussonic/contrib/watcher backup create

    The following file will be created:

    /var/lib/flussonic/watcher-backups/20190215201434-b62d21842ab7-WatcherBackup.gz

    To restore the backup use next command:

    /opt/flussonic/contrib/watcher backup restore -d 20190215201434

    Learn more about Watcher utility at the following article: watcher tool

  • PostgreSQL utility pg_dump:

    pg_dump -h localhost -d vsaas_production -U vsaas > backup.sql

    The system will ask for user vsaas password:

    Password:

    Restore from the backup.sql file:

    sudo -u postgres psql vsaas_production < dump.sql

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-9.6

  5. Reload the Flussonic service:

    service flussonic restart

  6. Restore data from the file that was created by the migration tool:

    /opt/flussonic/contrib/watcher backup restore -d 20190215201434

  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.

You can use the Watcher tool for backing up the database.