[Mayan EDMS: 2541] Migrate sqlite DB to postgres

[hubertus.peters via Mayan EDMS]

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.

Post by hubertus.peters via Mayan EDMS
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

[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

Post by 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.

Post by hubertus.peters via Mayan EDMS
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]

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.

Post by 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

Post by 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.

Post by hubertus.peters via Mayan EDMS
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

[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

Post by 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.

Post by 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

Post by 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.

Post by hubertus.peters via Mayan EDMS
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]

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.

Post by 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

Post by 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.

Post by 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

Post by 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.

Post by hubertus.peters via Mayan EDMS
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