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 skip 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 configuration (see memos.data).

Memos server

Memos container Host

~/.memos

/root/.memos

Tip

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

• Close Memospot and/or stop the Memos Docker container
• Copy your assets folder from source to the destination data directory
• Copy memos_prod.db (with the sidecar journal files, if they exist) 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.

Migrating data from earlier Memos versions

Caution

None of the following is needed if you have never stored assets outside the database (default behavior) or if you have used Memospot v0.1.3+ in the past since it contains a built-in database migrator.

If that’s the case, all your data is already portable.

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.

Tip

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.

Legacy instructions (before v0.18.2)

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.

Note

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

Check Memos’ database documentation if you need to work with other drivers.

Danger

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

• A SQLite3 client, like DB Browser for SQLite

• SSH access to your Docker host, if applicable

• An 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 the modified memos_prod.db file to the destination

Tip

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:

• You are using the default internal Docker volume path /var/opt/memos

• All your relative paths are using the default assets folder

Caution

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

Danger

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

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

Pick one path style:

Using absolute paths (Memos v0.18.0)

Danger

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.

Linux/macOS

echo "$HOME/.memospot"

Windows

Write-Host "$Env:LocalAppData\memospot"

Windows (standalone server)

Write-Host "$Env:ProgramData\memos"

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 (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;