Migrations... postgres to sqlite

[darkone23]

I am moving from a postgres installation to a sqlite installation...

I ported the database using the ruby tool sequel:

sequel -C <src-postgres> <target-sqlite>

The sqlite file doesn't have the right migrations so the system blows up on startup. This makes sense looking at the migrations directory - each directory has slightly different migration histories.

I think I will try to create dummy migrations and see if that works...
Anyone else tried this?

[BlackDex]

I suggest to try if you are able to only generate all the inserts. And remove all the schema stuff from your conversion.

Then also remove the migration table from your export.

After that, start Vaultwarden with no sqlite file at all so that it will create a new empty one.

Stop Vaultwarden and then use that sqlite file and your converted file together.

Some basics are also mentioned here.
https://github.com/dani-garcia/vaultwarden/wiki/Migrating-from-MariaDB-%28MySQL%29-to-SQLite#how-to-migrate-from-mariadb-to-sqlite

[M0nk3yOo]

Sorry to ask for one more detail. You said "updated your old db environment, let all migrations complete.", do you mean upgrade to 16.0 and... What do you mean with "let all migrations complete".

Upgrade to 16.0 and do an pg_upgrade? (Which I need to work more deeply, because it's not so easy to upgrade docker from 15.4 to 16 and can't run the pg_upgrade afterwards, because the container stops working because of the different DB-version)

[BlackDex]

First, Vaultwarden build by this project uses Pg v15.x as a client, so not sure if v16 as a server could pose an issue.

What i meant was the following, all from a Vaultwarden's perspectief.

1. Update Vaultwarden to the latest version using your current database type (pg) and let if run/finish all migrations.
2. Stop Vaultwarden, backup the database.
3. Configure Vaultwarden to use the wanted database type (sqlite) and start Vaultwarden to let it create the database and schema, and directly stop it again.
4. Export all records from the old database (not the schema) excluding the migrations table.
5. Convert this to the target database type and import it into the target database.
6. Start Vaultwarden again, which should now use the new database with the imported data.

[M0nk3yOo]

I got it!
Many thanks for your help! The trick was indeed the "blob"-Format(?). After exporting the DB with:

I need to replace another string, too:

pg_dump --inserts --data-only -U vaultwardenuser vaultwarden > /var/lib/postgresql/data/db-$(date +%d-%m-%y).backup
sed -i "s/'\\\\x/x'/g" /var/lib/postgresql/data/db-$(date +%d-%m-%y).backup

After that I removed any "SET" in the first block, replaced "true" with "'t'" (both without double quotes), and set an "BEGIN;" and "END";
Last I replaced any "public." with "nothing" (NULL/REMOVE/EMPTY)
After that I imported the dump to the sqlite DB, created by vaultwarden

I hope this could help anyone else :-)

[M0nk3yOo]

I need to correct one point:

The organization-passwords wasn't visible. The reason for that was to NOT set "true" to "'t'", correct way is to replace that with number "1" (without quotes).

[bfren]

+1 to replacing 'public.' - my whole process (with docker compose) was:

1. Set DATABASE_URL environment variable to a SQLite file, bring down the whole stack then start only Vaultwarden so it creates a fresh db.sqlite3 with the correct schema, then stop it again. (My SQLite file is ./v/server/db.sqlite3)

2. Export from PostgreSQL:

pg_dump --inserts --data-only -U vaultwardenuser vaultwarden > pg_export.sql

3. Strip the migrations table, fix PG hex blob format, and remove the public. schema prefix:

grep -v "__diesel_schema_migrations" pg_export.sql \
  | grep "^INSERT INTO" \
  | sed "s/'\\\\x/x'/g" \
  | sed 's/public\.\([a-zA-Z_]*\)/\1/g' \
  > pg-to-sqlite.sql

4. Import into SQLite:

sqlite3 ./v/server/db.sqlite3 < pg-to-sqlite.sql

5. Start Vaultwarden and verify.