Migrating from SQLite to postgreSQL : missing documentation
Hi, We've recently migrated our lufi instance from sqlite to pgSQL, and we had to play sherlock holmes a bit to find how to do it ;-) I'm happy to contribute to the wiki, but I haven't found a way to do it.
In any case, here's the steps you need to follow ; you should do this with your lufi instance down.
Install pgSQL dependencies
sudo -u lufi carton install –deployment –without=test –without=mysql
Update your lufi.conf
You want to have both db_path
set to your sqlite file path, postgresql
to pgsql
and pgdb
with your credentials, something like this :
# Choose what database you want to use
# Valid choices are sqlite, postgresql and mysql (all lowercase)
# optional, default is sqlite
dbtype => 'postgresql',
# SQLite ONLY - only used if dbtype is set to sqlite
# Define a path to the SQLite database
# You can define it relative to lufi directory or set an absolute path
# Remember that it has to be in a directory writable by Lufi user
# optional, default is lufi.db
db_path => 'lufi.db',
# PostgreSQL ONLY - only used if dbtype is set to postgresql
# These are the credentials to access the PostgreSQL database
# mandatory if you choosed postgresql as dbtype
pgdb => {
database => 'yourdb',
host => 'localhost',
# # optional, default is 5432
#port => 5432,
user => 'youruser',
pwd => 'yourpw',
# # https://mojolicious.org/perldoc/Mojo/Pg#max_connections
# # optional, default is 1
max_connections => 4,
},
Running the migration script
Then, you simply run the following command :
sudo -u lufi carton exec script/lufi sqliteToOtherDB
It took around 5 minutes (with something like 600 row in the files
DB), you'll get a nice loading bar.
After that, you can simply restart lufi.
Cheers,