PostgreSQL

Overview

Self-Hosted SimpleLogin runs a Postgresql database.

Warning: previous versions of this repository ran version 12.1. You can safely update the version of the container to 12.22. However this version is no longer supported.

It is recommended to upgrade following instructions from this page.

Database maintenance

• Backup
• Restore

How to upgrade

Minor version

Upgrading to a minor version is always safe.

• Stop the stack ./down.sh
• Update the postgres container version.

  services:
    ## SIMPLE LOGIN
    ## ============

    postgres:
-     image: postgres:12.1
+     image: postgres:12.22
      container_name: sl-db
      env_file: .env

• Start the stack ./up.sh
• Cleanup unused docker images.

Major version (up to 17.7)

It is easier to restore a backup to a database running an updated major version. Follow the steps outlined here to upgrade:

Note: those steps assume you are running version 12.1 by default as initially documented.

• Stop Self-Hosted SimpleLogin (docker compose down)
• Run only the postgres container sl-db (no sl-app, sl-email or sl-job-runner) with version 12.22. (docker compose up -d postgres)
• Backup the database
• Stop sl-db (docker compose down)
• Move the ./db/ folder for safety (mv ./db/ ./db_pre_update/)
• Update simplelogin-compose.yaml file to change postgres version

    postgres:
-     image: postgres:12.22
+     image: postgres:17.7
      container_name: sl-db
      env_file: .env

• Start sl-db with version 17.7: this will create an empty database. (docker compose up -d postgres)
• Move the backup file to the new database folder. (cp ./db_pre_update/simplelogin.backup ./db/)
• Create a new, empty, database folder. (mkdir ./db/)
• Restore the database.
• Stop postgres (docker compose down)
• Start all containers (docker compose up -d)
• Cleanup unused docker images.

Major version (18+)

From versions 18+ onwards, PostgreSQL images will make it simple to upgrade the database in-place across major versions. Upgrading from version 17.7 to version 18.x, however, still requires a full database backup and restore.

Perform the following steps to upgrade:

Note: those steps assume you are running version 17.7.

• Stop Self-Hosted SimpleLogin (docker compose down)
• Run only the postgres container sl-db (no sl-app, sl-email or sl-job-runner) with version 17.7. (docker compose up -d postgres)
• Backup the database
• Stop sl-db (docker compose down)
• Move the ./db/ folder for safety (mv ./db/ ./db_pre_update/)
• Create a new, empty, database folder. (mkdir ./db/)
• Update simplelogin-compose.yaml file to change postgres version

  postgres:
-    image: postgres:17.7
+    image: postgres:18.1
    container_name: sl-db
    env_file: .env
    volumes:
-      - ./db:/var/lib/postgresql/data
+      - ./db:/var/lib/postgresql
    restart: unless-stopped

• Start sl-db with version 18.1: this will create an empty database with data file in the ./db/18/docker/ subfolder. (docker compose up -d postgres)
• Move the backup file to the new database folder. (cp ./db_pre_update/simplelogin.backup ./db/)
• Restore the database (from the container pg_restore -U user -F c --dbname simplelogin /var/lib/postgresql/simplelogin.backup)
• Stop postgres (docker compose down)
• Start all containers (docker compose up -d)
• Cleanup unused docker images.