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?