Backup and Restore Database

This page describes how to backup and restore the database.

  4 minute read  

Before you begin

For this task, you need to have PostgreSQL installed on your machine. Make sure that its version is compatible with the version installed on the UMH.

Also, enough free space is required on your machine to store the backup. To check the size of the database, ssh into the system and follow the steps below:

sudo $(which kubectl) exec -it $(sudo $(which kubectl) get pods --kubeconfig /etc/rancher/k3s/k3s.yaml -n united-manufacturing-hub -l app.kubernetes.io/component=timescaledb -o jsonpath="{.items[0].metadata.name}") --kubeconfig /etc/rancher/k3s/k3s.yaml -n united-manufacturing-hub -- psql -U postgres

This command will open a psql shell connected to the default postgres database.

Connect to the umh_v2 or factoryinsight database:

\c <database-name>

Run the following command to get the size of the database:

SELECT pg_size_pretty(pg_database_size('<database-name>'));

If you need, check the version of PostgreSQL with this command:

\! psql --version

Backing up the database

Follow these steps to create a backup of the factoryinsight database on your machine:

  1. Open a terminal, and using the cd command, navigate to the folder where you want to store the backup. For example:

    
   cd C:\Users\user\backups
   

    
   cd /Users/user/backups
   

    
   cd /home/user/backups

    If the folder does not exist, you can create it using the mkdir command or your file manager.

  2. Run the following command to backup pre-data, which includes table and schema definitions, as well as information on sequences, owners, and settings:

    pg_dump -U factoryinsight -h <remote-host> -p 5432 -Fc -v --section=pre-data --exclude-schema="_timescaledb*" -f dump_pre_data.bak factoryinsight

    Then, enter your password. The default for factoryinsight is changeme.

    • <remote-host> is the server’s IP where the database (UMH instance) is running.

    The output of the command does not include Timescale-specific schemas.

  3. Run the following command to connect to the factoryinsight database:

    psql "postgres://factoryinsight:<password>@<server-IP>:5432/factoryinsight?sslmode=require"

    The default password is changeme.

  4. Check the table list running \dt and run the following command for each table to save all data to .csv files:

    \COPY (SELECT * FROM <TABLE_NAME>) TO <TABLE_NAME>.csv CSV

Grafana and umh_v2 database

If you want to backup the Grafana or umh_v2 database, you can follow the same steps as above, but you need to replace any occurence of factoryinsight with grafana.

In addition, you need to write down the credentials in the grafana-secret Secret, as they are necessary to access the dashboard after restoring the database.

The default username for umh_v2 database is kafkatopostgresqlv2, and the password is changemetoo.

Restoring the database

For this section, we assume that you are restoring the data to a fresh United Manufacturing Hub installation with an empty database.

Temporarly disable kafkatopostrgesql, kafkatopostgresqlv2, and factoryinsight

Since kafkatopostrgesql, kafkatopostgresqlv2, and factoryinsight microservices might write actual data into the database while restoring it, they should be disabled. Connect to your server via SSH and run the following command:

sudo $(which kubectl) scale deployment united-manufacturing-hub-kafkatopostgresql --replicas=0 -n united-manufacturing-hub --kubeconfig /etc/rancher/k3s/k3s.yaml;
sudo $(which kubectl) scale deployment united-manufacturing-hub-kafkatopostgresqlv2 --replicas=0 -n united-manufacturing-hub --kubeconfig /etc/rancher/k3s/k3s.yaml;
sudo $(which kubectl) scale deployment united-manufacturing-hub-factoryinsight-deployment --replicas=0 -n united-manufacturing-hub --kubeconfig /etc/rancher/k3s/k3s.yaml

Restore the database

This section shows an example for restoring factoryinsight. If you want to restore grafana, you need to replace any occurence of factoryinsight with grafana.

For umh_v2, you should use kafkatopostgresqlv2 for the user name and changemetoo for the password.

  1. Make sure that your device is connected to server via SSH and run the following command:

    sudo $(which kubectl) exec -it $(sudo $(which kubectl) get pods --kubeconfig /etc/rancher/k3s/k3s.yaml -n united-manufacturing-hub -l app.kubernetes.io/component=timescaledb -o jsonpath="{.items[0].metadata.name}") --kubeconfig /etc/rancher/k3s/k3s.yaml -n united-manufacturing-hub -- psql -U postgres

    This command will open a psql shell connected to the default postgres database.

  2. Drop the existing database:

    DROP DATABASE factoryinsight;

  3. Create a new database:

    CREATE DATABASE factoryinsight;
\c factoryinsight
CREATE EXTENSION IF NOT EXISTS timescaledb;

  4. Put the database in maintenance mode:

    SELECT timescaledb_pre_restore();

  5. Now, open a new terminal and restore schemas except Timescale-specific schemas with the following command:

    pg_restore -U factoryinsight -h 10.13.47.205 -p 5432 --no-owner -Fc -v -d factoryinsight <path-to-dump_pre_data.bak>

  6. Connect to the database:

    psql "postgres://factoryinsight:<password>@<server-IP>:5432/factoryinsight?sslmode=require"

  7. Restore hypertables:

    • Commands for factoryinsight:
      SELECT create_hypertable('productTagTable', 'product_uid', chunk_time_interval => 100000);
SELECT create_hypertable('productTagStringTable', 'product_uid', chunk_time_interval => 100000);
SELECT create_hypertable('processValueStringTable', 'timestamp');
SELECT create_hypertable('stateTable', 'timestamp');
SELECT create_hypertable('countTable', 'timestamp');
SELECT create_hypertable('processValueTable', 'timestamp');
    • Commands for umh_v2
      SELECT create_hypertable('tag', 'timestamp');
SELECT create_hypertable('tag_string', 'timestamp');
    • Grafana database does not have hypertables by default.

  8. Run the following SQL commands for each table to restore data into database:

    \COPY <table-name> FROM '<table-name>.csv' WITH (FORMAT CSV);

  9. Go back to the terminal connected to the server and take the database out of maintenance mode. Make sure that the databsae shell is open:

    SELECT timescaledb_post_restore();

Enable kafkatopostgresql, kafkatopostgresqlv2, and factoryinsight

Run the following command to enable kafkatopostgresql, kafkatopostgresqlv2, and factoryinsight:

sudo $(which kubectl) scale deployment united-manufacturing-hub-kafkatopostgresql --replicas=1 -n united-manufacturing-hub --kubeconfig /etc/rancher/k3s/k3s.yaml;
sudo $(which kubectl) scale deployment united-manufacturing-hub-kafkatopostgresqlv2 --replicas=1 -n united-manufacturing-hub --kubeconfig /etc/rancher/k3s/k3s.yaml;
sudo $(which kubectl) scale deployment united-manufacturing-hub-factoryinsight-deployment --replicas=2 -n united-manufacturing-hub --kubeconfig /etc/rancher/k3s/k3s.yaml

What’s next

Last modified December 19, 2024: Merge pull request #306 from united-manufacturing-hub/feat/eng-1713/rename-data-model-v1 (667ff8b)