Skip to main content

Database

The backend database is Postgres and it's running as a container.

Connectivity​

By default, containers must be connectable only within the Docker network. For debugging reasons, instances publish their IP.

Container nameEnvPort
simops-dbProd35433
simops-db-devDev35431

Use ufw to block ports when containers are normally not accessible from the public network.

sudo ufw allow 35433/tcp
sudo ufw deny 35433/tcp

See also Database as a datastore for Simops API.

Local Database Upgrade​

For the local instance of database, say from PostgreSQL 16 to 17, the steps was:

  1. With DataGrip, do an export using pg_dump (pg_dump can be installed via the Enterprise DB Postgres Downloads and selecting only Command-line tools in wizard) 
    pg_dump --dbname=simops \
      --file=D:/path/to/pg_dump/simops-dump.sql \
      --username=postgres --host=localhost --port=5432
  2. Stop the current container, not removing it.
  3. Start a new container with a new volume. The volume is using the version in its name (ie: _17) 
    docker run -d --name simops-db-local-17 -p 5432:5432 --restart=always \
      -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=postgres -e POSTGRES_DB=simops \
      -e PGDATA=/var/lib/postgresql/data/pgdata \
      -v simops-api_simops-db-data_17:/var/lib/postgresql/data \
      postgres:17.2
  4. Connect to database and import the dump file with psql (can also do that in DataGrip). 
    psql --file=D:/simops/dump.sql \
      --username=postgres --host=localhost --port=5432 simops
  5. Check if all is working correctly, including running API and Web client
  6. If working correctly, we can delete old container and rename the new one

Dev instance upgrade​

  1. Do a backup of the dev database: 
    pg_dump --dbname=simops \
      --file=./simops-dump.sql \
      --username=simopsdb --host=localhost --port=35431
  2. Remove container and volume (check which volume it's using with docker inspect).
  3. Change version of Postgre service on docker compose file and push changes.
  4. Run Development pipeline
  5. Check if container is up and running with correct version of image.
  6. Restore data from SQL file/backup. 
    psql --file=./simops_dump.sql \
      --username=simopsdb --host=localhost --port=35431 simops

Prod instance upgrade​

  1. Change Postgre image version in docker compose and push changes
  2. Do a backup of the prod database: 
    pg_dump --dbname=simops \
      --file=./dump.sql \
      --username=simopsdb --host=localhost --port=35433
  3. Remove container and volume (check which volume it's using with docker inspect).
  4. Run production pipeline
  5. Check if container is up and running with correct version of image.
  6. Restore data from SQL file/backup. 
    psql --file=./dump.sql \
      --username=simopsdb --host=localhost --port=35433 simops