sqlite -> postgresql migration

[bjo81]

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".

[bjo81]

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]

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]

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]

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.

[BlackDex]

@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.

[bjo81]

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.

[mprasil]

@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.

[bjo81]

@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.

[mprasil]

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

[pchampio]

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! :fireworks: