Backing up the database

Requirements

• Please ensure that you have installed pg_dump on your machine.
  • For Windows users, installation of PostgreSQL is required.
    • Alternatively, you can use chocolatey to install it.
  • Linux users can use their package manager to install it.
• We have observed that even small sites can have databases with more than 100GB of data, so please make sure that you have enough space on your machine to store the backup.

Backup

To store the backup, please follow the below steps:

1. Open a terminal and navigate to the folder where you want to store the backup.

2. Run the following command:

   pg_dump -h <REMOTE_HOST> -p <REMOTE_PORT> -U factoryinsight -Fc -f <BACKUP_NAME>.bak factoryinsight
   

   where:

   • <REMOTE_HOST> is the hostname of the server where the database is running.
   • <REMOTE_PORT> is the port of the database (default is 5432).
   • <BACKUP_NAME> is the name of the backup file.

Postgresql is normally not exposed to the outside. In this case, you can port-forward it using Lens

This will not export the Grafana dashboard password. These are stored in Kubernetes secrets.

Restore

This section is untested !

To restore the backup, we assume that you have a new stack running and the database is empty. Please follow the below steps:

1. Upload the database backup to the server where the database is running.

   kubectl cp <BACKUP_NAME>.bak <timescaledb-pod>:/tmp/<BACKUP_NAME>.bak
   

2. Scale the kafkatopostgres deployment to 0.

   Click the Scale button to select the number of replicas for the Deployment.

   

   

3. Open a terminal inside the timescaledb pod

   1. From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.

   2. Click the Pod Shell button to open a shell in the container.

      

      

   3. Enter the postgres shell:

      psql
         

   4. Connect to the database:

      \c factoryinsight
         

4. Delete the auto-generated database

   DROP DATABASE factoryinsight;
   

5. Create a new database

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

6. Put the database into timescale restore mode

   SELECT timescaledb_pre_restore();
   

7. Restore the database

   \! pg_restore -Fc -d factoryinsight /tmp/<BACKUP_NAME>.bak
   

8. Put the database back into normal mode

   SELECT timescaledb_post_restore();
   

Further reading

For more information, please refer to the following resources:

• TimescaleDB backup guide
• pg_dump documentation