Data migration

Data storage location

Windows: %LocalAppData%\memospot
POSIX systems: ~/.memospot (a hidden folder in the user home)
Inside Memos container: /var/opt/memos
Memos' container host:
- ~/.memos
- /root/.memos
- Near your docker-compose.yml file if you're using it.

The .thumbnail_cache directory and its contents are generated as needed.

Migrating data from earlier Memos versions

Brief history

Up to Memos v0.18.1, databases and assets were not portable. They pointed to absolute paths at the host's file system, and moving data between systems required the instructions written on this page.
Memos v0.18.2 started saving new assets with relative paths, but shipped a very slow path migrator for pre-existing assets.
The asset path migrator was removed from Memos v0.19.1+.
Memospot v0.1.3/v0.1.4 (bundled with Memos v0.20.0, compatible up to v0.21.0) shipped with its own fast asset migrator.

Legacy instructions

This guide will help you migrate your Memos database and assets to a new host.

It's possible to migrate between Windows and POSIX hosts, and between Memos Docker and Memospot, in any combination or direction. Just follow the appropriate steps.

If you only used the default Database object storage, you can skip the assets migration part and just copy the database files to the new host.

Requirements

A SQLite3 client, like DB Browser for SQLite
SSH access to your Docker host, if applicable
A SCP/SFTP client, like WinSCP and Cyberduck: to copy files to/from host, if needed

Basic migration

Close Memospot and stop your Docker container
Copy your assets folder from source to destination host
Copy memos_prod.db (and sidecar files memos_prod.db-shm and memos_prod.db-wal, if they exist) to a work directory on your local machine
Open the copied memos_prod.db with your SQLite client
Execute the appropriate SQL queries
Write the changes to the database and close it
Copy modified memos_prod.db to the destination

Replacing assets paths in the database

The following queries assume the following:

You are using the default internal Docker volume path /var/opt/memos
All your relative paths are using the default assets folder

The following sections contains queries to replace the assets paths in the database.

Pick one path style:

Using absolute paths (Memos <= v0.18.0)

__MEMOSPOT_POSIX_PATH__
Linux/macOS Terminal:
echo "$HOME/.memospot"
__MEMOSPOT_WINDOWS_PATH__
Windows Powershell:
Write-Host "$Env:LocalAppData\memospot"
__MEMOS_SERVER_WINDOWS_PATH__
Windows Powershell:
Write-Host "$Env:ProgramData\memos"

Choose what best suits your migration scenario:

Windows Memospot -> Memos Docker

SQL query:

-- Replace Windows Memospot paths with
-- default internal Docker volume paths
UPDATE resource
  SET internal_path = REPLACE(internal_path,
                              '__MEMOSPOT_WINDOWS_PATH__',
                              '/var/opt/memos');

-- Replace remaining Windows path separators
UPDATE resource
  SET internal_path = REPLACE(internal_path, '\', '/');

Windows Memos Server -> Memos Docker

SQL query:

-- Replace Windows Memos Server paths with
-- default internal Docker volume paths
UPDATE resource
  SET internal_path = REPLACE(internal_path,
                              '__MEMOS_SERVER_WINDOWS_PATH__',
                              '/var/opt/memos');

-- Replace remaining Windows path separators
UPDATE resource
  SET internal_path = REPLACE(internal_path, '\', '/');

Windows Memos Server -> Windows Memospot

SQL query:

-- Replace Windows Memos Server paths with
-- default internal Docker volume paths
UPDATE resource
  SET internal_path = REPLACE(internal_path,
                              '__MEMOS_SERVER_WINDOWS_PATH__',
                              '__MEMOSPOT_WINDOWS_PATH__');

Linux/macOS Memospot -> Memos Docker

SQL query:

-- Replace Linux/macOS Memospot paths with
-- default Docker volume paths
UPDATE resource
  SET internal_path = REPLACE(internal_path,
                              '__MEMOSPOT_POSIX_PATH__',
                              '/var/opt/memos');

Linux/macOS Memospot -> Windows Memospot

-- Replace Linux/macOS Memospot paths with Windows Memospot paths
UPDATE resource
  SET internal_path = REPLACE(internal_path,
                              '__MEMOSPOT_POSIX_PATH__',
                              '__MEMOSPOT_WINDOWS_PATH__');

-- Replace remaining POSIX path separators
UPDATE resource
  SET internal_path = REPLACE(internal_path, '/', '\');

Memos Docker -> Linux/macOS Memospot

-- Replace default Docker volume paths with Linux/macOS Memospot paths
UPDATE resource
  SET internal_path = REPLACE(internal_path,
                              '/var/opt/memos',
                              '__MEMOSPOT_POSIX_PATH__');

Memos Docker -> Windows Memospot

-- Replace default Docker volume paths with Windows Memospot paths
UPDATE resource
  SET internal_path = REPLACE(internal_path,
                              '/var/opt/memos',
                              '__MEMOSPOT_WINDOWS_PATH__');

-- Replace remaining POSIX path separators
UPDATE resource
  SET internal_path = REPLACE(internal_path, '/', '\');

Using relative paths (Memos >= v0.18.1)

These queries will replace absolute paths with relative paths.

While this method works fine and is simpler (queries may be run as-is), it will let the database with mixed path styles, as new assets will be created with absolute paths.

Choose what best suits your scenario:

From a POSIX host to another POSIX host (relative)

UPDATE resource
  SET internal_path = REPLACE(internal_path,
                              SUBSTR(internal_path, 1,
                                     INSTR(internal_path, '/assets')
                              ), '');

From a POSIX host to a Windows host (relative)

UPDATE resource
  SET internal_path = REPLACE(internal_path,
                              SUBSTR(internal_path, 1,
                                     INSTR(internal_path, '/assets')
                              ), '');
UPDATE resource
  SET internal_path = REPLACE(internal_path, '/', '\');

From a Windows host to a POSIX host (relative)

UPDATE resource
  SET internal_path = REPLACE(internal_path,
                              SUBSTR(internal_path, 1,
                                     INSTR(internal_path, '\assets')
                              ), '');
UPDATE resource
  SET internal_path = REPLACE(internal_path, '\', '/');

After migration

Execute the following query to check the first 100 rows:

SELECT type, filename, internal_path
  FROM resource WHERE internal_path IS NOT '' LIMIT 100;

Last modified: 19 May 2024