How to backup the internal H2 database

Lenses stores its configuration either in an external PostgreSQL database (preferred) or in an internal H2 database. You can find more info about storage options and their configuration

If you are using the internal database, it is higly recommended to create a backup before upgrading to a new Lenses version

The default location of the database files is /data/storage, unless it has been overridden in lenses.conf by setting the lenses.storage.directory configuration option. For this article we assume, the directory to be the default one, please adjust accordingly if necessary.

The process to backup your database varies, depending on your installation method and Persistent storage configuration.

Installation method: Archive 

To guarantee the integrity of the backup we are about to take, we need to make sure Lenses is not running during this process:

  1. Gain access to a terminal on the host serving Lenses.
  2. Stop Lenses (usually by stopping the relevant systemd service).
  3. Copy all *.db files from the database directory to a safe location
  4. Start Lenses

If you need to restore the database

  1. Gain access to a terminal on the host serving Lenses.
  2. Stop Lenses (usually by stopping the relevant systemd service).
  3. Delete all *.db files from the database directory and copy back your backup
  4. Start Lenses

Installation method: Docker or Kubernetes with Persistence ENABLED 

In these cases, we follow a different process in order to have Lenses running ( so as to be able to access the database files) but also ensure it is not writing to the database.

This is done by re-deploying after adding an environment variable PAUSE_EXEC with a value of 1.

In Docker, we do that by adding -e PAUSE_EXEC=1 to the docker run command. In Kubernetes, we can add it to the container’s env in the Deployment manifest. If deploying via Lenses Helm Chart we can set lenses.pauseExec.enable: true in the values.yaml

This variable delays the execution of Lenses for 10 minutes, providing enough time to perform the backup operation.

Afterwards

  1. Gain access to a terminal in the Lenses container ( by docker exec or kubectl exec respectively)
  2. Copy all *.db files from the database directory to a safe location
  3. Remove the PAUSE_EXEC variable and re-deploy

If you need to restore the database, again add the aforementioned variable, and then

  1. Gain access to a terminal in the Lenses container ( by docker exec or kubectl exec respectively)
  2. Delete all *.db files from the database directory and copy back your backup ( via docker cp or kubectl cp respectively)
  3. Remove the PAUSE_EXEC variable and re-deploy

Installation method: Docker or Kubernetes with Persistence DISABLED 

In such cases we cannot stop Lenses from running because the database files would be lost during the restart.

  1. Gain access to a terminal in the Lenses container ( by docker exec or kubectl exec respectively)
  2. Execute
apt update && apt install unzip

# get h2 required jars
wget https://h2database.com/h2-2019-03-13.zip
unzip h2-2019-03-13.zip

# backup
java -cp h2/bin/h2-1.4.199.jar org.h2.tools.Script -url 'jdbc:h2:/data/storage/lensesdb;AUTO_SERVER=TRUE' -script lensesdb.zip -options compression zip
mv lensesdb.zip ${YOUR_PREFERRED_LOCATION} # optional

Afterwards, please consider enabling persistence via a docker volume or by adding the required values in the Helm Chart

Assuming persistence is enabled, in order to restore:

  1. Add the PAUSE_EXEC environment variable as outlined above and re-deploy
  2. Copy your backup in the container ( via docker cp or kubectl cp respectively)
  3. Gain access to a terminal in the Lenses container ( by docker exec or kubectl exec respectively)
  4. Execute
apt update && apt install unzip

# get h2 required jars
wget https://h2database.com/h2-2019-03-13.zip
unzip h2-2019-03-13.zip

# delete existing database files
rm /data/storage/*.db

# restore
java -cp h2/bin/h2-1.4.199.jar org.h2.tools.RunScript -url 'jdbc:h2:/data/storage/lensesdb;AUTO_SERVER=TRUE' -script lensesdb.zip -options compression zip
  1. Remove PAUSE_EXEC env var and re-deploy
--
Last modified: November 18, 2024