(Slightly?) messy migration between DB engines

While discussing changing the default DB engine to sqlite for ease of setup, roblandry asked my to investigate how to get data out of it into an external DB engine like MariaDB or PostgreSQL. This worked, but a "proper" way involving Laravel would probably be better and avoid some workarounds.

This guide assumes default settings all round.

1) Run your lychee container, connected to your database container (if you're using a docker image for your database) with e.g. --link

2) Open a shell inside your Lychee container

docker exec -it container_name_or_hash /bin/bash

3) Install dependencies

This example covers both targets. I've also included both vim and nano to give some choice later. You might prefer to access the files from the /conf volume directly.

apt update && apt install -y mariadb-client postgresql-client sqlite3 vim nano

4) Edit your .env file

Use your tool of choice. Update DB settings as required.

5) artisan migrate

This will create the structure of the database, tailored to Laravel's expectation of how the DB engine operates.

php artisan migrate

6) Export from sqlite

We'll do this in /conf so that you can save your work.

cd /conf
# Dump everything
sqlite3 database.sqlite .dump > dump.sql
# Or pick and choose tables
sqlite3 database.sqlite .dump albums > dump.sql
sqlite3 database.sqlite .dump photos >> dump.sql

Notes:

• The minimum required tables are albums and photos, and albums needs to be before photos.
• sym_links is useful (though shouldn't be required) if you use the symlink settings.
• migrations should be a waste of time.
• users and user_album may be userful if you have sub-users.
• pages and page_contents will be useful if you use that feature.
• logs probably isn't required, unless you really want to preserve old logs
• configs will save some setup time.

7) Translation

This is the painful bit.

First, if required, remove lines relating to any tables you're not migrating.

Because each DB engine is treated slightly differently, comment out (#) the CREATE lines or move them elsewhere - you may want to refer to them later. PRAGMA lines can go too. TRANSACTION/COMMIT lines you may want to keep.

Unfortunately, the tables may be in different orders, so you can either move the fields manually to match or use the INSERT INTO (...) VALUES (...); SQL syntax. You can also combine multiple records from the same table using ... VALUES (...),(...);.

e.g. for my photos table the lines became:

INSERT INTO photos (id, title, description, url, tags, public, owner_id, width, height, size, iso, aperture, make, model, lens, shutter, focal, latitude, longitude, altitude, takestamp, star, thumbUrl, album_id, checksum, license, created_at, updated_at, medium, medium2x, small, small2x, thumb2x, imgDirection, livePhotoUrl, livePhotoContentID, livePhotoChecksum, location, type) VALUES (...),(...),(...);

If you decide to change a table with default values (e.g. config) you should login to your new database and e.g. DELETE FROM config; so that the INSERT statements will work.

For MariaDB, that should do it. For PostgreSQL you'll also need to find all the boolean fields and change from 0 and 1 to '0' and '1' or 'f' and 't'.

8) Import

For MariaDB:

mysql -h mariadb -u root -ppassword lychee <dump.sql

For PostgreSQL:

psql -h postgres -U postgres -d lychee <dump.sql

If there are errors, hopefully the previous section has the answers.

9) Test

Load Lychee in your browser. Hopefully everything's there!

10) Restart

Depending on the environment variables set on your container, you may need to destroy your container and recreate it, mounting the old volumes for /conf, /uploads and /sym.

11) Done!

All done. Hopefully this is useful. If you found anything worth noting along your way, please add it.