sqlite -> postgresql migration #4082

New Issue

2026-03-07T19:27:48-06:00

GiteaMirror commented

2026-03-07 19:27:48 -06:00

Originally created by @bjo81 on GitHub (Oct 10, 2019).

I'm trying to figure out the best way to migrate from sqlite to postgresql. I've read #497 (export SQL, remove "__diesel_schema_migrations", import SQL), but this fails. Also using pgloader fails, the migration itself works, but afterwards bitwarden_rs crashes due to "formatter errors".

Originally created by @bjo81 on GitHub (Oct 10, 2019). I'm trying to figure out the best way to migrate from sqlite to postgresql. I've read #497 (export SQL, remove "__diesel_schema_migrations", import SQL), but this fails. Also using [pgloader](https://pgloader.readthedocs.io) fails, the migration itself works, but afterwards bitwarden_rs crashes due to "formatter errors".

GiteaMirror closed this issue

2026-03-07 19:27:48 -06:00

GiteaMirror commented

2026-03-07 19:27:49 -06:00

@bjo81 commented on GitHub (Oct 10, 2019):

Trying to insert the "cleaned up" sqlite dump results in:

ERROR:  insert or update on table "ciphers_collections" violates foreign key constraint "ciphers_collections_cipher_uuid_fkey"
DETAIL:  Key (cipher_uuid)=(065ae3f7-89d2-4868-80ba-90cf049ce4c7) is not present in table "ciphers".
ERROR:  insert or update on table "ciphers_collections" violates foreign key constraint "ciphers_collections_cipher_uuid_fkey"
DETAIL:  Key (cipher_uuid)=(7dd96305-d70d-4da5-b5b7-14e24cba7e7e) is not present in table "ciphers".
ERROR:  insert or update on table "ciphers_collections" violates foreign key constraint "ciphers_collections_cipher_uuid_fkey"
DETAIL:  Key (cipher_uuid)=(a4601d49-e098-4694-a8eb-23eb13140260) is not present in table "ciphers"

@bjo81 commented on GitHub (Oct 10, 2019): Trying to insert the "cleaned up" sqlite dump results in: ``` ERROR: insert or update on table "ciphers_collections" violates foreign key constraint "ciphers_collections_cipher_uuid_fkey" DETAIL: Key (cipher_uuid)=(065ae3f7-89d2-4868-80ba-90cf049ce4c7) is not present in table "ciphers". ERROR: insert or update on table "ciphers_collections" violates foreign key constraint "ciphers_collections_cipher_uuid_fkey" DETAIL: Key (cipher_uuid)=(7dd96305-d70d-4da5-b5b7-14e24cba7e7e) is not present in table "ciphers". ERROR: insert or update on table "ciphers_collections" violates foreign key constraint "ciphers_collections_cipher_uuid_fkey" DETAIL: Key (cipher_uuid)=(a4601d49-e098-4694-a8eb-23eb13140260) is not present in table "ciphers" ```

GiteaMirror commented

2026-03-07 19:27:49 -06:00

@bjo81 commented on GitHub (Oct 10, 2019):

Adding the ciphers before fails as column "favorite" is of type boolean but expression is of type integer, maybe it has to be changed like in https://stackoverflow.com/questions/7947814/pgerror-error-column-is-required-is-of-type-boolean-but-expression-is-of-typ

@bjo81 commented on GitHub (Oct 10, 2019): Adding the ciphers before fails as column "favorite" is of type boolean but expression is of type integer, maybe it has to be changed like in https://stackoverflow.com/questions/7947814/pgerror-error-column-is-required-is-of-type-boolean-but-expression-is-of-typ

GiteaMirror commented

2026-03-07 19:27:49 -06:00

@bjo81 commented on GitHub (Oct 10, 2019):

After changing some integers to boolean and vice versa all data from the dump could be inserted, but bitwarden still crashes:

Running migration 20190912100000
thread 'main' panicked at 'Can't run migrations: MigrationError(IoError(Cu>
stack backtrace:
   0:     0x556815de37f2 - <unknown>
   1:     0x556815de34d6 - <unknown>
   2:     0x556815de3f45 - <unknown>
   3:     0x556815de3ae2 - <unknown>
   4:     0x556815de39d6 - <unknown>
   5:     0x556815e0550a - <unknown>
   6:     0x556815e05607 - <unknown>
   7:     0x556815863c04 - <unknown>
   8:     0x55681574879f - <unknown>
   9:     0x5568158366a3 - <unknown>
  10:     0x556815de3973 - <unknown>
  11:     0x556815ded67a - <unknown>
  12:     0x556815de44fd - <unknown>
  13:     0x55681575ac92 - <unknown>
  14:     0x7f0d73fd3153 - __libc_start_main
  15:     0x55681558219e - <unknown>
  16:                0x0 - <unknown>

I'm wondering where migration 20190912100000 comes from.

@bjo81 commented on GitHub (Oct 10, 2019): After changing some integers to boolean and vice versa all data from the dump could be inserted, but bitwarden still crashes: ``` Running migration 20190912100000 thread 'main' panicked at 'Can't run migrations: MigrationError(IoError(Cu> stack backtrace: 0: 0x556815de37f2 - <unknown> 1: 0x556815de34d6 - <unknown> 2: 0x556815de3f45 - <unknown> 3: 0x556815de3ae2 - <unknown> 4: 0x556815de39d6 - <unknown> 5: 0x556815e0550a - <unknown> 6: 0x556815e05607 - <unknown> 7: 0x556815863c04 - <unknown> 8: 0x55681574879f - <unknown> 9: 0x5568158366a3 - <unknown> 10: 0x556815de3973 - <unknown> 11: 0x556815ded67a - <unknown> 12: 0x556815de44fd - <unknown> 13: 0x55681575ac92 - <unknown> 14: 0x7f0d73fd3153 - __libc_start_main 15: 0x55681558219e - <unknown> 16: 0x0 - <unknown> ``` I'm wondering where migration 20190912100000 comes from.

GiteaMirror commented

2026-03-07 19:27:50 -06:00

@bjo81 commented on GitHub (Oct 10, 2019):

Finally got it working:

created a build without migrations
imported the SQL structure from https://github.com/dani-garcia/bitwarden_rs/tree/master/migrations/postgresql/
created a file bitwarden.load with the following contents

load database
     from sqlite:///var/lib/bitwarden_rs/db.sqlite3 
     into postgresql://user:pass@localhost/database
     with data only, include no drop, reset sequences
;

run pgloader bitwarden.load

I assume usually it should not be necessary to use a build without migrations, but I'm confused why it tries to run also the first migration with "CREATE TABLE users" which has to fail when the structure already exists. Maybe the devs can shed up some light how to prevent this.
Inserting the data like in #497 and like I tried before seems to use wrong blobs, so a login is not possible.

@bjo81 commented on GitHub (Oct 10, 2019): Finally got it working: - created a build without migrations - imported the SQL structure from https://github.com/dani-garcia/bitwarden_rs/tree/master/migrations/postgresql/ - created a file `bitwarden.load` with the following contents ``` load database from sqlite:///var/lib/bitwarden_rs/db.sqlite3 into postgresql://user:pass@localhost/database with data only, include no drop, reset sequences ; ``` - run `pgloader bitwarden.load` I assume usually it should not be necessary to use a build without migrations, but I'm confused why it tries to run also the first migration with "CREATE TABLE users" which has to fail when the structure already exists. Maybe the devs can shed up some light how to prevent this. Inserting the data like in #497 and like I tried before seems to use wrong blobs, so a login is not possible.

GiteaMirror commented

2026-03-07 19:27:50 -06:00

@BlackDex commented on GitHub (Oct 10, 2019):

@bjo81
These migrations happen because the application does not know they happened. Because you removed this as stated in your first post.

I think you should first create an empty database, run bitwarden once, see that the migrations run. This will create all the tables. Now try to import the backup.
But changing integers to bools and vise versa doesn't seem the right way.

btw: with empty database i really mean just a "create database bitwarden", nothing more.

@BlackDex commented on GitHub (Oct 10, 2019): @bjo81 These migrations happen because the application does not know they happened. Because you removed this as stated in your first post. I think you should first create an empty database, run bitwarden once, see that the migrations run. This will create all the tables. Now try to import the backup. But changing integers to bools and vise versa doesn't seem the right way. btw: with empty database i really mean just a "create database bitwarden", nothing more.

GiteaMirror commented

2026-03-07 19:27:50 -06:00

@bjo81 commented on GitHub (Oct 11, 2019):

Ok, usually

20190912100000	2019-10-11 09:22:08.214825
20190916150000	2019-10-11 09:22:08.290952

would be created in __diesel_schema_migrations

I will put some migration instructions into the wiki when I find some time.

@bjo81 commented on GitHub (Oct 11, 2019): Ok, usually ``` 20190912100000 2019-10-11 09:22:08.214825 20190916150000 2019-10-11 09:22:08.290952 ``` would be created in `__diesel_schema_migrations` I will put some migration instructions into the wiki when I find some time.

GiteaMirror commented

2026-03-07 19:27:50 -06:00

@mprasil commented on GitHub (Oct 11, 2019):

@BlackDex the integer to bool might actually be okay, because SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

I agree with the rest of what @BlackDex said, sounds like a solid plan. Needs to be tested.

@mprasil commented on GitHub (Oct 11, 2019): @BlackDex the integer to bool might actually be okay, because [SQLite does not have a separate Boolean storage class](https://www.sqlite.org/datatype3.html). Instead, Boolean values are stored as integers 0 (false) and 1 (true). I agree with the rest of what @BlackDex said, sounds like a solid plan. Needs to be tested.

GiteaMirror commented

2026-03-07 19:27:51 -06:00

@bjo81 commented on GitHub (Oct 11, 2019):

@mprasil Yes, that was the issue, in the export it was just 0 or 1 what psql interpreted as integer and not as boolean. But nevermind, letting bitwarden create the tables and pgloader copy the data seems to be the best solution.

@bjo81 commented on GitHub (Oct 11, 2019): @mprasil Yes, that was the issue, in the export it was just 0 or 1 what psql interpreted as integer and not as boolean. But nevermind, letting bitwarden create the tables and pgloader copy the data seems to be the best solution.

GiteaMirror commented

2026-03-07 19:27:51 -06:00

@mprasil commented on GitHub (Oct 11, 2019):

It would be cool if you did create some howto wiki! Sounds very straightforward.

@mprasil commented on GitHub (Oct 11, 2019): It would be cool if you did create some howto wiki! Sounds very straightforward.

GiteaMirror commented

2026-03-07 19:27:51 -06:00

@pchampio commented on GitHub (Mar 1, 2020):

Just went to the process of moving from sqlite to postgresql, I followed the 4 first steps in the wiki, used admin mode to export a backup database and the above snippet, everything worked properly! 🎆

@pchampio commented on GitHub (Mar 1, 2020): Just went to the process of moving from sqlite to postgresql, I followed the 4 first steps in the [wiki](https://github.com/dani-garcia/bitwarden_rs/wiki/Using-the-MySQL-Backend), used admin mode to export a backup database and the [above](https://github.com/dani-garcia/bitwarden_rs/issues/656#issuecomment-540748229) snippet, everything worked properly! :fireworks:

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/vaultwarden#4082