Migration Guide: Grafana’s Internal Tables to the visualizer Schema in ThingsHub 6

This article explains the automatic migration of Grafana's internal tables from the public schema to a dedicated visualizer schema in ThingsHub 6.

Background

In prior versions of ThingsHub (before version 6), Grafana’s internal tables were created in the public schema of the PostgreSQL database shared by other services of ThingsHub. From ThingsHub 6 onwards, these tables have been moved to a separate visualizer schema, which is used exclusively by Grafana.

Why the migration to a Dedicated Schema?

The decision to move Grafana’s internal tables to their own schema comes with several benefits, including:

1. Improved Isolation:

   • Grafana’s internal tables are isolated from other application data.

   • This isolation enhances security by preventing cross-schema access issues and reducing the risk of conflicts between tables used by different services.

2. Cleaner Public Schema:

   • By removing Grafana’s tables from the public schema, the overall database structure becomes cleaner and easier to navigate.

   • This is particularly useful in the context of the thingsHub application’s database architecture, where the same database hosts multiple services.

3. Enhanced Performance:

   • With isolated workloads in their own schema, query performance is also improved.

4. Greater Flexibility for Future Updates:

   • Using a separate schema provides flexibility for future upgrades and changes specific to Grafana’s database layer without disrupting the broader ThingsHub database.

Automatic Migration Process

This migration is handled automatically during the upgrade to ThingsHub 6. No manual intervention is required for the migration process itself, but it is important to verify that the migration was successful.

Verifying the Migration

After upgrading to ThingsHub 6, follow these steps to verify the migration:

1. Query the Migration Tables: Grafana tracks its migrations through the gorp_migrations and thub_migrations tables. To ensure that the migration completed successfully, run the following SQL queries against the PostgreSQL database:

   CODE

   select * from dashboards.gorp_migrations; 
   select * from dashboards.thub_migrations;

2. Verify Migration IDs:

   • The gorp_migrations table should contain the following migration IDs:

     CODE

     2024-04-17_public_schema_to_visualizer_schema.sql 
     2024-09-05_public_schema_cleanup.sql

   • The thub_migrations table should contain:

     CODE

     public-to-visualizer-schema-duplication

If these entries are present, the migration was successfully completed, and you can proceed with using ThingsHub 6 with Grafana 10!

If you encounter any issues during the migration (e.g., missing migration entries or problems with the visualizer service), contact the SmartMakers Support Team for assistance.