Data migration

Portable data

The current version of Memos stores assets in a portable format.

All data can be seamlessly moved between Memos containers and Memospot instances, regardless of the operating system.

Assets, attachments, resources, and objects are interchangeable terms that refer to the same thing: uploaded files.

Data storage location

Relevant files

• memos_prod.db
• memos_prod.db-shm (may not exist)
• memos_prod.db-wal (may not exist)
• assets directory

The .thumbnail_cache directory and its contents are generated as needed. You may ignore it when migrating data.

Memospot

Windows

%LocalAppData%\Memospot

$Env:LocalAppData\Memospot

Linux/macOS

~/.memospot

Hidden folder in user’s Home. Toggle visibility with Ctrl+H or Cmd+Shift+.

Optional locations (if moved manually by the user)

~/.config/memospot

$XDG_CONFIG_HOME/memospot

Alternate location

If you haven’t found your data on the predefined directories, check your memos.data setting.

Memos server

Memos container Host

~/.memos

/root/.memos

docker-compose

If you are using a custom docker-compose.yml file, check if your data is in the same directory as it.

Inside Memos container

/var/opt/memos

Windows Server

%ProgramData%\memos

$Env:ProgramData\memos

Basic data migration

1. Close Memospot and/or stop the Memos Docker container.

2. Copy your assets folder from source to the destination data directory.

3. Copy memos_prod.db to the destination data directory.

   Journal files

   Always copy existing journal files (memos_prod.db-shm and memos_prod.db-wal) along with the database file.

   They may contain recent data that has not been committed to the database yet.

   Note that these files won’t exist if the database was closed properly.

4. That’s it! Start Memospot or the Memos container on the destination.

Migrating data from old Memos versions

Automatic migration

If you are coming from an old Memos version, consider installing Memospot v1.0.2. It will fix all asset paths in the database automatically on the first start.

This versions also contains the last Memos server version (v0.25.2) that can upgrade very old database schemas.

Exceptions

• You have never stored assets outside the database (default initial configuration).
• You have used Memospot v0.1.3+ in the past, since it contains a built-in database migrator.

If one of this is true, all your data is already portable.

Legacy instructions

Valid up to Memos v0.18.1. Superseded on Memos v0.18.2+.

Why this is documented here

• 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.

Introduction

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.

Database drivers

MySQL and PostgreSQL are not supported by Memospot, so this guide will only cover SQLite migrations.

Refer to the Memos database documentation if you need to work with other drivers.

Backup

Do not proceed without taking an offline backup of your database.

Stop the server and copy the database files and assets to a location outside your working scope for extra safety.

Requirements

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

Data migration

1. Close Memospot and stop your Docker container.

2. Copy your assets folder from source to destination host.

3. Copy the database files to a work directory on your local machine:

   Database files

   • memos_prod.db
   • memos_prod.db-shm (may not exist)
   • memos_prod.db-wal (may not exist)

4. Open the copied memos_prod.db with your SQLite client.

5. Execute the appropriate SQL queries.

6. Write the changes to the database and close it.

7. Copy the modified memos_prod.db file to the destination.

Data paths

The default Memos data path on a Docker host is ~/.memos/.

Within the container, the path is /var/opt/memos.

Unless you changed it, ~/.memos/ is where you’ll find your assets and database.

Replacing assets paths in the database

The following queries assume the following:

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

If any of these assumptions do not hold for your setup, you must adjust the queries.

Path separator

Pay attention to the leading and trailing slashes when editing the queries below.

Backup

Remember to always back up your database before running SQL queries.

Queries

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

Pick one path style and click to expand:

Using absolute paths (up to Memos v0.18.0)

Edition needed

There are some __PLACEHOLDERS__ in the following queries that you must replace with resolved absolute paths from your system, as SQL clients won’t resolve system environment variables.

Getting the absolute Memospot data path:

Linux/macOS

echo "$HOME/.memospot"

Windows

Write-Host "$Env:LocalAppData\memospot"

Caution

When migrating data to a Memos Docker container, you must specify the internal Docker volume path, which is /var/opt/memos.

The host-accessible data path is set later (~/.memos/ in the example), when you launch the container:

docker run -d \
--init \
--name memos \
--publish 5230:5230 \
--volume ~/.memos/:/var/opt/memos \
ghcr.io/usememos/memos:latest

Choose what best suits your migration scenario, then copy and adjust the SQL query:

Windows to Docker

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

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

Linux/macOS to Docker

-- Replace Linux/macOS host paths with default internal Docker paths
UPDATE resource SET internal_path = REPLACE(
  internal_path,
  '__MEMOS_DATA_POSIX__',
  '/var/opt/memos'
);

Linux/macOS to Windows

-- Replace Linux/macOS paths with Windows paths
UPDATE resource SET internal_path = REPLACE(
  internal_path,
  '__MEMOS_DATA_POSIX__',
  '__MEMOS_DATA_WINDOWS__'
);

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

Docker to Linux/macOS

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

Memos Docker to Windows

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

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

Using relative paths (starting from Memos v0.18.1)

These queries will replace absolute paths with relative paths.

Compatibility

While this method works fine and is simpler (queries work as shown), it will mix path styles in your database if you keep running versions older than v0.18.2, as new assets will be created with absolute paths.

Constraints

• This only works if you have the default prefix assets in all your paths.
• Assets with relative paths will be resolved relatively to the MEMOS_DATA environment variable.

Choose what best suits your scenario:

POSIX to POSIX

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

POSIX to Windows

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

Windows to POSIX

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

Sanity check

Remember to browse the resource table and check if the new values in the internal_path column are adequate to the destination host filesystem.

Execute the following query to check the first 100 rows:

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