Database ‐ restore

SensorThings - restore

This page describes the procedure to restore the SensorThings database (PostgreSQL / TimescaleDB) from a backup file.

ATTENTION
Database restore operations are destructive and irreversible. Always verify that you are restoring the correct backup into the correct environment (local vs server).

When should a database restore be performed?

A database restore may be required in the following situations:

• Recovery after data corruption or accidental deletion
• Migration to a new server or environment
• Reproducing a production state in a local or test environment

Prerequisites

Before starting the restore process, ensure that:

• You have the backup file to restore (.bak)
• You have access to the host where SensorThings is running
• You have permission to modify the database and API credentials
• You know whether the restore is being performed in:
  • a local/development environment, or
  • the OBSEA/production environment

Restore procedure

1) Identify the backup file

Locate the backup file you want to restore:

ls *.bak

Example:

sensorthings_20260109_083347.bak

2) Configure database credentials

Before executing the restore script, database credentials must be configured. The restore script (sensorthings_restore.sh) must use the same credentials that are defined in secrets.env:

Example configuration:

DB_NAME="sensorthings"
DB_USER="sensorthings"
DB_HOST="localhost"        # Use the appropriate host (e.g. docker host or server IP)
DB_PORT="${PGPORT:-5432}"  # Default PostgreSQL port
DB_PASSWORD="password"     # Set your database password

IMPORTANT These values must be adapted to your environment before running the script.

3) Run the restore script

Execute the restore script, passing the backup file as argument:

./sensorthings_restore.sh sensorthings_20260109_083347.bak

During execution:

• The database structure and data are restored
• The script will ask for confirmation before proceeding

Configure pgAdmin

After restoring the database, it must be registered again in pgAdmin.

1) Register the database server

Open pgAdmin and register a new server:

• Name: any name you want (e.g. sensorthings3)
• Go to the Connection tab and set:
  • Host name/address: your database host (e.g. localhost, Docker IP, or server IP)
  • Port: 5432
  • Maintenance database: sensorthings
  • Username: sensorthings
  • Password: value from secrets.env (DB_PASSWORD)

Click Save.

Registering a new database server in pgAdmin.
Here you can choose any name for the connection (for example: sensorthings3).

Configuring the database connection parameters.
Fill in the host, port, database name and credentials using the values defined in secrets.env.

2) Update API users credentials

After registering the database server in pgAdmin, API users must be updated to match the credentials defined in secrets.env.

Navigate to:

• Double-click on the registered server (e.g. sensorthings3)
• Databases
  • sensorthings
  • Schemas
    • public
    • Tables
      • USERS

Right-click on the USERS table and select:

View/Edit Data → All Rows

You should see users such as:

• admin
• injector
• readonly

For each user, update the password so that it matches the values defined in secrets.env:

# API read-only user
STA_API_READ_USER=readonly
STA_API_READ_PASSWORD=password

# API write user
STA_API_WRITE_USER=injector
STA_API_WRITE_PASSWORD=password

# API administrator
STA_API_ADMIN_USER=admin
STA_API_ADMIN_PASSWORD=password

Click on the password field for each user, update the value, and save the changes using the database save icon in pgAdmin.