Migrate/Upgrade database selfhost

Help
1

Is there any official or recommended documentation that explains how to upgrade an existing Atuin database from PostgreSQL 14 to PostgreSQL 18?

Additionally, is there guidance on migrating from PostgreSQL to the recently implemented SQL backend, if applicable?

2

There’s nothing special about our Postgres usage, you can follow any Postgres upgrade guide! It’s a bit out of scope for our docs, and something you’ll have to handle yourself if you self host

No issue with just staying on 14

3

I just migrated from PostGreSQL 14 to 18 with the following steps:

1: start backing up with prodrigestivill/postgres-backup-local:

compose.yaml:

services:
  atuin:
    ...
    depends_on:
      database:
        condition: service_healthy

  database:
    image: postgres:14
    ...
    volumes:
      - /data/database/:/var/lib/postgresql/data/

  backup:
    image: prodrigestivill/postgres-backup-local
    env_file:
      - .env
    user: postgres:postgres
    environment:
      POSTGRES_HOST: database
      POSTGRES_DB: ${ATUIN_DB_NAME}
      POSTGRES_USER: ${ATUIN_DB_USERNAME}
      POSTGRES_PASSWORD: ${ATUIN_DB_PASSWORD}
      SCHEDULE: "@daily"
      BACKUP_DIR: /db_dumps
    volumes:
      - /data/config/atuin/backups/:/db_dumps
    depends_on:
      - database

docker compose up -d

2: create a manual backup:

docker compose exec backup /backup.sh

3: stop services

docker compose down

4: move the database data to conform to what postgres 18 expects:

mv /data/database /data/database.14
mkdir /data/database
mv /data/database.14 /data/database/14

5: update the compose file:

compose.yaml:

services:
  database:
    image: postgres:18
    ...
    volumes:
      #- /data/database/:/var/lib/postgresql/data/
      - /data/database/:/var/lib/postgresql/

6: bring up the database

docker compose up -d database

7: check the logs that postgres is happy and you’re not getting an error about the data being in the wrong location (you’re still mounting to …/data)

8: run the restore

#!/usr/bin/env bash

set -e
set -x

. .env

DB_CONTAINER=atuin-db-1
DB_HOST=db
DB_PORT=5432

BACKUP_FILE="$1"

zcat "$BACKUP_FILE" | \
docker exec \
    --interactive \
    $DB_CONTAINER \
    /bin/sh -c "psql --username=$ATUIN_DB_USERNAME --dbname=$ATUIN_DB_NAME -W"

./restore.sh /data/backups/last/atuin-latest.sql.gz

9: bring up the rest of the stack

docker compose up -d