Migrate sqlite DB to postgres

[hubertu...@5x2.de]

I have updated mayan to 3.0 via docker and I am getting the message, that i should not use sqlite in production. But when I start the docker image with postgresql, the database is empty. i assume, mayan will not automatically convert any existing sqlite databases.

I tried to export the sqlite data and import on the posgresql installation with mayan-edms.py loaddata , but im getting errors about duplicat keys, etc. (i did a mayan-edms.py flush   before.).

How do I properly migrate my database from >3.0 mayan db.sqlite3 to posgresql?

--

Hubi

[Roberto Rosario]

Django itself doesn't provide a method for direct database migrations. What you tried to do is the recommended procedure by the Django project. However for it to work there are some stuff that need to be added to the models so they use unique natural keys and not numeric primary keys which can be repeated (which is the error you encountered). We started adding natural keys a while back but that work is unfinished. What apps reported repeated keys when you did the loaddata command? We can try to work on those to see if the migration finishes without error.

[Jay 1972]

Hi,

I'm running in the same issue, that I'm using the SQLite3 DB and getting this message on every site in the WebUI.

I'm wondering if there is a standard routine to migrate the DB from SQLite3 to PostgreSQL, mySQL or MariaDB?

Maybe other users already done so and have a migration path or useful hints, which migration works best?

Kind regards,

Michael

[Roberto Rosario]

The method that most approaches a migration solution is using dumpdata and loaddata (https://docs.djangoproject.com/en/2.0/ref/django-admin/#dumpdata) (https://docs.djangoproject.com/en/2.0/ref/django-admin/#django-admin-loaddata). dumpdata create a platform independent JSON file of the data using serialization. This method requires that support for natural keys is added to all database models (https://docs.djangoproject.com/en/2.0/topics/serialization/#topics-serialization-natural-keys). This process was started but is not yet complete.

If you tried to do a dumpdata/loaddata migration and it failed, reporting the apps that failed will help us complete support for this and provide an official migration process.

[Nicholas Loscalzo]

Unfortunately running mayan-edms.py dumpdata from inside the docker container gives me [CommandError: Unable to serialize database: unable to open database file and I'm unsure what's going on.  Is it because Mayan has a lock on the database file ?

The warning messages against using SQLite got me a bit scared, so I'd like to move over to Postgres from SQLite, but I already have a good number of documents in Mayan.  In the worst case scenario, can the database be re-built from scratch if I move the document cache folder into place ?  In that scenario I'd understand if it lost the cabinets, etc

Thanks !

-Nick

[Roberto Rosario]

We are working on adding and official method to convert the database use from SQLite to another like Postgres. Some changes already made it to the next version branch and we've execute successful migrations with some large document databases.

We have included a "convertdb" command (https://gitlab.com/mayan-edms/mayan-edms/blob/versions/next/mayan/apps/common/management/commands/convertdb.py) and will available in the next version.