Document database migration process

[dpw13]

Is your feature request related to a problem? Please describe.
I recently migrated from SQLite to postgresql 17.6 for my database backend on PartDB. I ran into a few small issues along the way that others might encounter in the future.

Describe the solution you'd like
The process is relatively standard for those who are familiar with that kind of migration, but it would be ideal to have a little more info in the docs besides the existing backup/restore documentation, which really only applies if you are restoring using the same database backend as the backup.

I have notes on my process below, but whoever picks up this issue should identify the recommended migration process and document it (maybe under Usage, maybe under Upgrade? I'm not actually sure where it should go).

Describe alternatives you've considered
The alternative would be to leave the docs unchanged.

Additional context
NOTE: See below. I do not recommend using this method!

I was able to migrate from SQLite to pg using pgloader (after manually provisioning a new pg user and creating the database):

pgloader .../partdb/db/app.db 'postgresql://partdb:<password>@<postgres-ip>:5432/partdb'

This worked super well except that it migrated all the timestamps to timestamptz which Symfony did not like. For anyone else searching for a similar error, this was the log produced:

Uncaught PHP Exception Doctrine\DBAL\Types\Exception\InvalidFormat: "Could not convert database value "2025-09-08 14:13:22+00" to Doctrine Type App\Doctrine\Types\UTCDateTimeImmutableType. Expected format "Y-m-d H:i:s"." at InvalidFormat.php line 26 {"exception":"[object] (Doctrine\\DBAL\\Types\\Exception\\InvalidFormat(code: 0): Could not convert database value \"2025-09-08 14:13:22+00\" to Doctrine Type App\\Doctrine\\Types\\UTCDateTimeImmutableType. Expected format \"Y-m-d H:i:s\". at /var/www/html/vendor/doctrine/dbal/src/Types/Exception/InvalidFormat.php:26)

I manually altered all the timestamptz columns to timestamp, but I think a better solution would be to configure pgloader to use the correct type (see https://pgloader.readthedocs.io/en/latest/ref/sqlite.html):

type timestamp to timestamp using sqlite-timestamp-to-timestamp

I also received the following error because the collation did not exist:

ERROR: collation \"numeric\" for encoding \"UTF8\" does not exist

which was solving by manually recreating the collation:

CREATE COLLATION numeric (provider = icu, locale = 'en-u-kn-true');

Both of these probably may have been avoided had I used the php command-line to recreate the database before importing the data using pgloader, though I haven't tested that. Note that I don't guarantee that either of the above fixes are "correct" and match the schema Symfony would create by running doctrine:database:create, only that the changes above allowed me to log in and interact with my parts.

Note that #164 discusses migrating from SQLite to MariaDB using a somewhat generic (and slightly tedious) method, though it should guarantee the proper database schema.

[dpw13]

OK, after running bin/console doctrine:schema:validate and seeing that there were 533 mismatches, I started over with my migration. I had difficulty with bin/console doctrine:database:drop --force and bin/console doctrine:database:create because my database user did not have the permissions necessary to drop or create the database, but that's only due to my setup. Instead, I dropped and recreated the database separately and was able to run:

docker compose exec -u www-data -it partdb php bin/console doctrine:schema:create

which I verified did create the schemas. Then I was able to use pgloader with the following config:

load database
        from sqlite:///<path>/app.db
        into postgresql://partdb:<password>@<postgres-ip>:5432/partdb

 with truncate, data only
  set work_mem to '16MB', maintenance_work_mem to '512 MB';

which ensured that data was copied and the schema was unmodified. The only issue I had with this is that two of the users had an empty string for users.backup_codes and users.settings instead of []. I manually changed the users schema from json to text, imported the users, changed those entries to [], then altered the schema back:

ALTER TABLE "public"."users" ALTER COLUMN "backup_codes" TYPE json USING "backup_codes"::JSON;
ALTER TABLE "public"."users" ALTER COLUMN "settings" TYPE json USING "settings"::JSON;

However I think it would have been much simpler to update the sqlite database first with valid JSON and not mess with the users schema at all. There may be a way to tell pgloader to interpret "" as an empty JSON array but I'm not that familiar with the tool.

I still needed to create the collation, but after that partdb appears to be fully functional. There did appear to be one small FK missing:

$ docker compose exec -u www-data -it partdb php bin/console doctrine:schema:validate -v
Mapping
-------

[OK] The mapping files are correct.

Database
--------

[ERROR] The database schema is not in sync with the current mapping file.

// 1 schema diff(s) detected:
ALTER TABLE log ADD CONSTRAINT FK_8F3F68C56B3CA4B FOREIGN KEY (id_user) REFERENCES "users" (id) ON DELETE SET NULL NOT DEFERRABLE;

Which I was able to easily apply using bin/console doctrine:schema:update --force. I'm not sure why that one FK got left out but it appears to be fixed now. I'm now much more confident that future schema updates will apply cleanly. With the manually imported schema, I guarantee that future updates would have broken things even though the system was working at the time.