What's the right way to migrate SQLite data to Postgres?

[adoyle-h]

The official documents not mention it.

[gitsult4n]

No built in migration exists
the binary only has memos and memos version and the migrator is per driver only
cmd/memos/main.go:96 store/migrator.go:281

The route that works

1. point memos at the empty Postgres and let it build the schema then stop it

MEMOS_DRIVER=postgres
MEMOS_DSN=postgres://memos:pass@host:5432/memos?sslmode=disable

fresh DB gets store/migration/postgres/LATEST.sql at store/migrator.go:244

2. drop the rows memos just wrote

TRUNCATE system_setting;

instance settings and the recorded schema version live in that table and name is the primary key so the load collides otherwise
store/db/postgres/instance_setting.go:15 store/migration/postgres/LATEST.sql:3

3. load data only with pgloader

LOAD DATABASE
     FROM sqlite://./memos_prod.db
     INTO postgresql://memos:pass@host:5432/memos?sslmode=disable
     WITH data only, disable triggers, preserve index names;

pgloader handles the type deltas for you — pinned INTEGER to BOOLEAN and payload config TEXT to JSONB and blob BLOB to BYTEA
store/migration/sqlite/LATEST.sql:61 vs store/migration/postgres/LATEST.sql:60

4. start memos again

Run the same memos version on both sides or the two schemas will not match

reset sequences is on by default and data only does not disable it
https://pgloader.readthedocs.io/en/latest/ref/sqlite.html
if a new memo still hits a duplicate id then

SELECT setval(pg_get_serial_sequence('memo','id'), (SELECT MAX(id) FROM memo));

Same steps written up earlier
https://github.com/orgs/usememos/discussions/3108

[adoyle-h]

Thanks for your detailed explanation! But it is too complicated for me, and implementing it might be risky. I would create an issue in the hope that there will be a one-click migration method.

[adoyle-h]

#6245