Flussonic Watcher documentation

Database Migration and Updating

This section provides the instructions on how to maintain the database used by Watcher.
  • Migrate to PostgreSQL (necessary starting from version 19.03)
  • Update the database structure manually (migth be necessary in some cases, Watcher will inform you about it)

Migration from SQLite to PostgreSQL Anchor Anchor x2

  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
    
    /etc/init.d/flussonic restart
    

    Learn more about the update process

  3. Back up all the data by using our migration tool:

    /opt/flussonic/contrib/watcher backup create
    

    The tool creates a file like this: /var/lib/flussonic/watcher-backups/20190215201434-b62d21842ab7-WatcherBackup.gz

  4. Install PostgreSQL

    • 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
  5. 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.

  6. Reload the Flussonic service:

    /etc/init.d/flussonic restart
    
  7. Restore data from the file that was created by the migration tool:

    /opt/flussonic/contrib/watcher backup restore -d 20190215201434
    
  8. Open the Watcher web inrweface and check that everything works (the data is present).

Updating the database structure Anchor Anchor x2

Some Flussonic Watcher updates include structural changes to the database. In this case, you will see the following message:

Updating the Database Structure

The script /opt/flussonic/contrib/watcher_db_migrate.sh adds changes to the database structure.

Important! Back up your database before running the watcher_db_migrate.sh script.

For backing up the database, you can use the watcher tool.

After you have created a database backup, run the script watcher_db_migrate.sh manually on the server with Watcher:

/opt/flussonic/contrib/watcher_db_migrate.sh

Here is a log of the successful execution of the script:

# /opt/flussonic/contrib/watcher_db_migrate.sh
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade dfd74e510414 -> 1822b8f25e20, agent:model, agent:camera
INFO  [alembic.runtime.migration] Running upgrade 1822b8f25e20 -> 1a71a9477bbb, streamer: cluster_key
INFO  [alembic.runtime.migration] Running upgrade 1a71a9477bbb -> 7a3ab2550cab, streamer_fkey cascade
Restarting Watcher

After Flussonic Watcher has restarted, you will be able to use the Watcher web interface.

If while running watcher_db_migrate.sh you encounter an error similar to this one

alembic.util.exc.CommandError: Can't locate revision identified by 'ebdce5515b6d

then install the previously used version (that you used before Watcher update or downgrade) and run:

cd /opt/flussonic/apps/vsaas
/opt/flussonic/contrib/watcher db downgrade