Difficulties migrating from sqlite to postgresql [SOLVED]

Help
1

Hi, I was looking over the instructions how to convert from sqlite to postgresql. I ran across the pgloader command:

load database
     from sqlite://yoursqliteuser:yoursqlitepassword@yoursqliteserver:yoursqliteport/yoursqlitedatabase
     into postgresql://yourpgsqluser:yourpgsqlpassword:yourpgsqlserver:yourpgsqlport/yourpgsqldatabase
     WITH data only, include no drop, reset sequences
     EXCLUDING TABLE NAMES MATCHING '__diesel_schema_migrations'
     ALTER SCHEMA 'bitwarden' RENAME TO 'public'
;

I have a bitwardenrs server setup by default using sqlite and I have a db.sqlite3 file. I’m not sure how to complete the migration since I don’t have a container specifically.

I’m looking for any additional instructions on this matter.

Here is what I tried and the resulting output:

bitwarden.load

load database
from sqlite:///var/data/bw-data/db.sqlite3
into postgresql://postgres:password:domain.com:5432/bitwarden
WITH data only, include no drop, reset sequences
EXCLUDING TABLE NAMES MATCHING ‘__diesel_schema_migrations’
ALTER SCHEMA ‘bitwarden’ RENAME TO ‘public’
;

Here is the output (i’m guessing things didn’t go so well – OUCH!!!):

pgloader bitwarden.load
2020-10-17T23:31:40.029000-05:00 LOG pgloader version "3.6.2"
KABOOM!
FATAL error: At end of input

  ;

  ^ (Line 8, Column 0, Position 327)

In context DSN-USER-PASSWORD:

While parsing DSN-USER-PASSWORD. Expected:

     the character @ (COMMERCIAL_AT)
  or the string "@@"
  or <end of input>
  or anything but the character @ (COMMERCIAL_AT)
An unhandled error condition has been signalled: At end of input

  ;

  ^ (Line 8, Column 0, Position 327)

In context DSN-USER-PASSWORD:

While parsing DSN-USER-PASSWORD. Expected:

     the character @ (COMMERCIAL_AT)
  or the string "@@"
  or <end of input>
  or anything but the character @ (COMMERCIAL_AT)




What I am doing here?

At end of input

  ;

  ^ (Line 8, Column 0, Position 327)

In context DSN-USER-PASSWORD:

While parsing DSN-USER-PASSWORD. Expected:

     the character @ (COMMERCIAL_AT)
  or the string "@@"
  or <end of input>
  or anything but the character @ (COMMERCIAL_AT)
2

Ok - I’ve made some progress, but the bitwarden.loader syntax is incorrect as referenced: https://github.com/dani-garcia/bitwarden_rs/wiki/Using-the-PostgreSQL-Backend

I’m working with following loader file syntax:

load database
     from sqlite://yoursqliteuser:yoursqlitepassword@yoursqliteserver:yoursqliteport/yoursqlitedatabase
     into postgresql://yourpgsqluser:yourpgsqlpassword@yourpgsqlserver:yourpgsqlport/yourpgsqldatabase
     WITH data only, include no drop, reset sequences
     EXCLUDING TABLE NAMES MATCHING '__diesel_schema_migrations'
     ALTER SCHEMA 'bitwarden' RENAME TO 'public'
;

I’m getting the following error on the EXCLUDING line:

KABOOM!
FATAL error: At

       WITH data only, include no drop, reset sequences
       EXCLUDING TABLE NAMES MATCHING '__diesel_schem
                             ^ (Line 5, Column 27, Position 238)

In context EXCLUDING-LIKE:

While parsing EXCLUDING-LIKE. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"
  or the string "like"

I’ll try to see what correct syntax should be

3

Migration complete. The correct syntax for the loader file is the following:

load database
from sqlite://yoursqliteuser:yoursqlitepassword@yoursqliteserver:yoursqliteport/yoursqlitedatabase
into postgresql://yourpgsqluser:yourpgsqlpassword@yourpgsqlserver:yourpgsqlport/yourpgsqldatabase
WITH data only, include no drop, reset sequences
EXCLUDING TABLE NAMES LIKE ‘__diesel_schema_migrations’
ALTER SCHEMA ‘bitwarden’ RENAME TO ‘public’
;

I made a change to the official wiki to reflect the configuation: https://github.com/dani-garcia/bitwarden_rs/wiki/Using-the-PostgreSQL-Backend

2 Likes