How to convert NoMa sqlite3 to PostgreSQL

NoMa sqlite3 to PostgreSQL Conversion

1.0 General Information

This document outlines steps to convert NoMa from sqlite3 to PostgreSQL.

  • This procedure is for a system that was upgraded from a pre-7.1.x version of GroundWork Monitor with NoMa.
  • These steps can only be performed on a system running 7.1.x.
  • Prior to 7.1.x, the NoMa connection was hardcoded to use sqlite3. In 7.1.x the database selection in the NoMa.yml file is honored.
  • Fresh installs of GroundWork Monitor version 7.1.1 do not need to perform this conversion.

2.0 Conversion Steps

Step 1 - Prerequisites
  1. Back up existing NoMa database:
    cp /usr/local/groundwork/noma/var/NoMa.db /tmp/NoMa.db
  2. Source environment variables:
    source /usr/local/groundwork/scripts/
  3. Stop NoMa prior to database work:
    service groundwork stop noma
Step 2 - Cleanup existing sqlite3
  1. Compact the database:
    sqlite3 /usr/local/groundwork/noma/var/NoMa.db
    sqlite> VACUUM
  2. Check database integrity:
    sqlite> PRAGMA integrity_check;
  3. Remove any queued notifications:
    sqlite> delete from tmp_active;
    sqlite> delete from tmp_commands;
    sqlite> delete from notification_stati;
    sqlite> delete from escalation_stati;
  4. Exit out of sqlite3:
Step 3 - Migrating sqlite3 to PostgreSQL
  1. Change directory to /usr/local/groundwork/core/migration/postgresql:
    cd /usr/local/groundwork/core/migration/postgresql
  2. Convert the existing NoMa database to PostgreSQL using:
    /usr/local/groundwork/core/migration/postgresql/dump_sqlite_data_for_postgresql /usr/local/groundwork/noma/var/NoMa.db | /usr/local/groundwork/postgresql/bin/psql -q -h "$dbhost" -v ON_ERROR_STOP= -d noma
  3. Validate the PostgreSQL database now has the data from the sqlite3 database:
  4. If you were using sqlite3 after the 7.1.x upgrade, edit the /usr/local/groundwork/etc/NoMa.yml file to use postgresql instead of sqlite3:
      type: postgresql
  5. Start NoMa service:
    service groundwork start noma