Backup and Restore Database

This page describes how to backup and restore the database.

3 minute read

Before you begin

For this task, you need to have PostgreSQL installed on your machine.

You also need to have enough space on your machine to store the backup. To check the size of the database, follow the steps below:

From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Enter the postgres shell:
```
psql
```
Connect to the database:
```
\c factoryinsight
```

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

SELECT pg_size_pretty(pg_database_size('factoryinsight'));

Backing up the database

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

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.
Run the following command:
```
pg_dump -h <REMOTE_HOST> -p 5432 -U factoryinsight -Fc -f <BACKUP_NAME>.bak factoryinsight
```
- <REMOTE_HOST> is the IP of the server where the database is running. Use localhost if you installed the United Manufacturing Hub using k3d.
- <BACKUP_NAME> is the name of the backup file.

Grafana database

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

Additionally, you also need to write down the credentials in the grafana-secret Secret, as they will be needed to access the dashboard after restoring the database.

Restoring the database

This section is untested. Please report any issues you encounter.

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

Copy the backup file to the database pod

Open UMHLens / OpenLens.
Launch a new terminal sesstion by clicking on the + button in the bottom-left corner of the window.
Run the following command to copy the backup file to the database pod:
```
kubectl cp /path/to/local/backup.bak united-manufacturing-hub-timescaledb-0:/tmp/backup.bak
```
Replace /path/to/local/backup.bak with the path to the backup file on your machine.

This step could take a while depending on the size of the backup file.

Temporarly disable kafkatopostrgesql

Navigate to Workloads > Deployments.
Select the united-manufacturing-hub-kafkatopostgresql Deployment.
Click the Scale button to select the number of replicas for the Deployment.
Scale the number of replicas to 0.

Open a shell in the database pod

From the Pod section in UMHLens / OpenLens, click on united-manufacturing-hub-timescaledb-0 to open the details page.
Click the Pod Shell button to open a shell in the container.
Enter the postgres shell:
```
psql
```
Connect to the database:
```
\c factoryinsight
```

Restore the database

Drop the existing database:
```
DROP DATABASE factoryinsight;
```

Create a new database:

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

Put the database in maintenance mode:
```
SELECT timescaledb_pre_restore();
```

Restore the database:

\! pg_restore -Fc -d factoryinsight /tmp/backup.bak

Take the database out of maintenance mode:
```
SELECT timescaledb_post_restore();
```

Enable kafkatopostgresql

Navigate to Workloads > Deployments.
Select the united-manufacturing-hub-kafkatopostgresql Deployment.
Click the Scale button to select the number of replicas for the Deployment.
Scale the number of replicas to the original value, usually 1.

What’s next

See the official TimescaleDB backup guide
See the official pg_dump documentation

Last modified April 17, 2023: build: version 0.9.13 (6cb0a01)