View Source

h1. NoMa sqlite3 to PostgreSQL Conversion

h4. 1.0 General Information

This document outlines steps to convert NoMa from sqlite3 to PostgreSQL.
{note}* 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.{note}
{note}* Fresh installs of GroundWork Monitor version 7.1.1 do not need to perform this conversion.{note}

h4. 2.0 Conversion Steps

h5. Step 1 - Prerequisites

# Back up existing NoMa database:
{noformat}cp /usr/local/groundwork/noma/var/NoMa.db /tmp/NoMa.db{noformat}
# Source environment variables:
{noformat}source /usr/local/groundwork/scripts/{noformat}
# Stop NoMa prior to database work:
{noformat}service groundwork stop noma{noformat}

h5. Step 2 - Cleanup existing sqlite3

# Compact the database:
{noformat}sqlite3 /usr/local/groundwork/noma/var/NoMa.db
sqlite> VACUUM{noformat}
# Check database integrity:
{noformat}sqlite> PRAGMA integrity_check;{noformat}
# Remove any queued notifications:
{noformat}sqlite> delete from tmp_active;
sqlite> delete from tmp_commands;
sqlite> delete from notification_stati;
sqlite> delete from escalation_stati;{noformat}
# Exit out of sqlite3:

h5. Step 3 - Migrating sqlite3 to PostgreSQL

# Change directory to {{/usr/local/groundwork/core/migration/postgresql}}:
{noformat}cd /usr/local/groundwork/core/migration/postgresql{noformat}
# Convert the existing NoMa database to PostgreSQL using:
{noformat}/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{noformat}
# Validate the PostgreSQL database now has the data from the sqlite3 database:
#* Log into psql command line, the default password is {{nomapass}}:
{noformat}psql -U noma{noformat}
#* Validate data was migrated successfully by examining the notifications table:
{noformat}select * from notifications;{noformat}
#* Run the following commands to set the appropriate sequences for the Postgresql NoMa database:
{noformat}select setval('contacts_id_seq', (select MAX(id) from contacts));
select setval('contactgroups_id_seq', (select MAX(id) from contactgroups));
select setval('escalation_stati_id_seq', (select MAX(id) from escalation_stati));
select setval('escalations_contacts_id_seq', (select MAX(id) from escalations_contacts));
select setval('holidays_id_seq', (select MAX(id) from holidays));
select setval('notification_logs_id_seq', (select MAX(id) from notification_logs));
select setval('notification_methods_id_seq', (select MAX(id) from notification_methods));
select setval('notification_stati_id_seq', (select MAX(id) from notification_stati));
select setval('notifications_id_seq', (select MAX(id) from notifications));
select setval('timeframes_id_seq', (select MAX(id) from timeframes));
select setval('tmp_active_id_seq', (select MAX(id) from tmp_active));
select setval('tmp_commands_id_seq', (select MAX(id) from tmp_commands));{noformat}
#* Exit out of psql:
# 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
# Start NoMa service:
service groundwork start noma