Database migration manually for memos

[wbchn]

Describe the solution you'd like

I'm glad to see memos support database now, very thanks to all the contributors

Here is my migration note about how to migrate sqlite to postgres database, I think it also works on mysql.

Type of feature

Documentation

Additional context

pre migration
backup sqlite file to local

# docker 
docker cp memos:/var/opt/memos/memos_prod.db ./

# k8s, copy from pvc or backup

step1. create database and then set memos enviornment

- MEMOS_DRIVER: postgres
- MEMOS_DSN: 'postgresql://memos:password@xxxx:5432/memos?sslmode=disable'

step2. memos startup
start memos, auto create table/schema in postgres db, after service start finish(the console show memos logo), stop memos now.

step3. truncate data in db(conflict with server_setting)

truncate system_setting;

step4. install pgloader and load data to postgres

# https://pgloader.readthedocs.io/en/latest/install.html
# mac
brew install pgloader
# or linux
apt-get install pgloader

edit and save below to sqlite.load, only load data to postgres:

LOAD DATABASE
	FROM sqlite://./memos_prod.db
	INTO postgresql://memos:password@xxxx:5432/memos?sslmode=disable
	WITH data only, disable triggers, preserve index names
set work_mem to '16MB', maintenance_work_mem to '512 MB';

after that, run command, and make sure there is no error

pgloader sqlite.load

Tips: if you met error like ERROR Database error 23505: duplicate key value violates unique constraint "system_setting_pkey", please review step3.

step5. restart memos service, it works now.

[MehadND]

I think it's best to post about this in discussions page

[jpoggi]

Hi,
First of all, thanks for writing this piece of procedure.
I am trying to migrate from Sqlite to PostgreSQL and for now I'm facing an issue.
When I try to load the data to the PostgreSQL database I'm having, this error message.

root@3a0d7cd83a85:/# pgloader memos_prod.db 
KABOOM!
STREAM-DECODING-ERROR: :UTF-8 stream decoding error on #<SB-SYS:FD-STREAM for "file /memos_prod.db" {1005C78663}>: the octet sequence #(137) cannot be decoded.
An unhandled error condition has been signalled: :UTF-8 stream decoding error on #<SB-SYS:FD-STREAM for "file /memos_prod.db" {1005C78663}>: the octet sequence #(137) cannot be decoded.



2024-05-19T07:07:09.008000Z LOG pgloader version "3.6.7~devel"

What I am doing here?

:UTF-8 stream decoding error on
#<SB-SYS:FD-STREAM for "file /memos_prod.db" {1005C78663}>:
  the octet sequence #(137) cannot be decoded.

I suspect a a problem with a character on the dump.

I will try to sort this out and post the solution if I found it, meanwhile if someone is willing to help you can :-)

Have a nice sunday.

[rdhhb0513]

请问有没有SQLite to MySQL的教程或者工具可以一键转换？

[wbchn]

follow this doc in Chinese

https://geek-docs.com/mysql/mysql-ask-answer/117_mysql_quick_easy_way_to_migrate_sqlite3_to_mysql.html

[player2019]

I got error like

root@3f02ba15de52:/var/opt/memos/sqlitebak# mysql -u memos -p memos < memos_replace.sql
Enter password:
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the                    right syntax to use near 'PRAGMA foreign_keys=OFF' at line 1

Could you help me?

[farhaduneci]

THANK YOU @wbchn, YOU SAVED ME. <3

[asano69]

I manually migrated from SQLite3 to PostgreSQL.
Because the Memos tables are mostly independent from each other, it was relatively easy to move the data.

First, I started a new Memos instance connected to PostgreSQL and saved a few test memos.

Then, using a web app called DbGate, I compared the schemas of SQLite3 and PostgreSQL.

In the memo table, the data types of the pinned and payload fields had changed:

• pinned: integer → boolean
• payload: text → jsonb

So, I exported the data from the memo table as a JSON file and rewrote it with the following command:

jq '
def fix:
  if has("pinned") then .pinned = (.pinned == 1) else . end
  | if has("payload") and (.payload | type) == "string"
    then .payload |= fromjson
    else .
    end;

if type == "array" then
  map(fix)
elif type == "object" then
  fix
else
  .
end
' input.json > output.json

After that, I imported the converted file using DbGate.

In my case, I didn’t use the memo_relation, reaction, or resource tables very much, so I didn’t copy them to PostgreSQL.

After copying the necessary data, I started Memos and PostgreSQL and checked whether the memos were displayed correctly.

At this point, when saving new memos, the auto-increment of the memo ID did not work correctly, so I set the counter to a value larger than the maximum existing ID in the memo table.

-- Check the name of the sequence
SELECT pg_get_serial_sequence('memo', 'id');

-- Check the current value of the sequence
SELECT last_value FROM public.memo_id_seq;

-- Restart numbering from 794
SELECT setval('public.memo_id_seq', 794, false);

Even with this roundabout method, the migration worked successfully.