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