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).
Original GitHub issue: https://github.com/dani-garcia/vaultwarden/issues/656
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".
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"
<!-- gh-comment-id:540671447 -->
@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"
```
<!-- gh-comment-id:540693869 -->
@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
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.
<!-- gh-comment-id:540748229 -->
@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
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.
<!-- gh-comment-id:540764426 -->
@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.
I will put some migration instructions into the wiki when I find some time.
<!-- gh-comment-id:541004832 -->
@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.
I agree with the rest of what @BlackDex said, sounds like a solid plan. Needs to be tested.
<!-- gh-comment-id:541005174 -->
@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.
@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.
<!-- gh-comment-id:541005969 -->
@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.
It would be cool if you did create some howto wiki! Sounds very straightforward.
<!-- gh-comment-id:541018278 -->
@mprasil commented on GitHub (Oct 11, 2019):
It would be cool if you did create some howto wiki! Sounds very straightforward.
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! 🎆
<!-- gh-comment-id:593023264 -->
@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:
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
Originally created by @bjo81 on GitHub (Oct 10, 2019).
Original GitHub issue: https://github.com/dani-garcia/vaultwarden/issues/656
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 commented on GitHub (Oct 10, 2019):
Trying to insert the "cleaned up" sqlite dump results in:
@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):
After changing some integers to boolean and vice versa all data from the dump could be inserted, but bitwarden still crashes:
I'm wondering where migration 20190912100000 comes from.
@bjo81 commented on GitHub (Oct 10, 2019):
Finally got it working:
bitwarden.loadwith the following contentspgloader bitwarden.loadI 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 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.
@bjo81 commented on GitHub (Oct 11, 2019):
Ok, usually
would be created in
__diesel_schema_migrationsI will put some migration instructions into the wiki when I find some time.
@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.
@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.
@mprasil commented on GitHub (Oct 11, 2019):
It would be cool if you did create some howto wiki! Sounds very straightforward.
@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! 🎆