Databse conversion (SQLite to PostgreSQL)

Questions, comments, discussions. Over time certain topics might be moved to their own category.
User avatar
winstonsmith
Posts: 4
Joined: Sun Aug 26, 2018 4:52 pm

Re: Databse conversion (SQLite to PostgreSQL)

Post by winstonsmith » Tue Feb 26, 2019 4:15 pm

sorry to bump this, but i could really use some help. any idea on why the database conversion outlined in my previous post failed?

is there a chance an official tutorial on migrating database in a docker environment could be posted soon?

KevinPawsey
Posts: 81
Joined: Wed Aug 22, 2018 2:52 pm

Re: Databse conversion (SQLite to PostgreSQL)

Post by KevinPawsey » Wed Mar 06, 2019 1:20 pm

Hi @winstonsmith,

I think that you are most of the way there, but Mayan does rely on the environment variables to get the connection parameters for the python database... so you will need to run it with the environment variables to tell Mayan where to find the database, and what the connection parameters are (name, username, password, etc).

Hopefully if you use the "docker run" command with the "-e [environment variable 1] -e [environment variable 2] ..." when running Mayan-EDMS it will connect to the db and will do the conversion successfully.

I think that the reason that @rosarior hasn't done the documentation is that I believe that he is mid-merge with other codebases trying to get the new version of Mayan-EDMS out the door (v4!)... it is a very limited team, hence the reason why I have been sharing my experience with things to try and ease some of the things that he is working on. Hopefully the db conversion will be a little more automated in the new version... there have also been discussions about the advantage of using later versions of python... so maybe that will be something else that will come with the new version :)

Anyway... let me know if you have any more success now with this.


Kevin
Running Mayan-EDMS on: OpenMediaVault, (Docker plugin), on x86 dual-core

User avatar
winstonsmith
Posts: 4
Joined: Sun Aug 26, 2018 4:52 pm

Re: Databse conversion (SQLite to PostgreSQL)

Post by winstonsmith » Thu Mar 07, 2019 7:11 pm

ok thanks kevin.

i've tried every permutation of passing the env variarbles to mayan inside the container while running, while launching the container, and so on.

it still fails. my new plan is to run a new postrges-enabled docker container, import the sqlite database into that container, and run the migration. if i ever get it to work i will post a writeup, but i am not holding my breath. incidentally, it looks like the python migration script does zero logging, so it's not like i can dig into the issue with any clarity. just "killed" at stdout. nice.

so a new version is on the way? i hope any upgrades to database back-ends or python underpinnings will be -slightly- better documented this time around. i still haven't been able to successfully migrate from 2 to 3!!

i want to recommend that my employers purchase enterprise support, but with such a shaky upgrade process i am not sure this is the way to go. i love the software, it does exactly what we need, but once bitten, twice shy...

on another note, is there an irc channel for mayan? i've looked on freenode but don't see it.

User avatar
rosarior
Posts: 231
Joined: Tue Aug 21, 2018 3:28 am

Re: Databse conversion (SQLite to PostgreSQL)

Post by rosarior » Fri Mar 08, 2019 10:38 pm

There appears to be some confusions so I'll post some clarifications below:

We take data consistency very seriously. We don't ship a new version until all tests pass. If an error with data integrity is found in a stable version (even if theoretical), we stop all work and release an emergency hotfix (example: https://gitlab.com/mayan-edms/mayan-edm ... 3a32a69f40). This is why series 3.1 has 9 bugfix releases and is at version 3.1.9 right now. When it comes to data integrity and consistency Mayan has an impeccable track record.

We also take the upgrade process very seriously and we write extensive release notes for each release. We include the rationale for changes, upgrade steps, possible pitfalls, etc (https://docs.mayan-edms.com/releases/3.0.html).

Upgrades are so important that Mayan was one of the first Django projects to implement database migrations. This was done in version 0.11 back in Feb 2012 (https://gitlab.com/mayan-edms/mayan-edm ... s/0.11.rst). Mayan supported database schema and database data migrations even before the framework upon which is built. That's 7 years of experience with database upgrades which we draw upon to ensure new versions upgrade without problems.

We take upgrades so seriously that when the crowdfunding to add native migrations to Django was started in 2013, we (myself, Mayan and my employer) became gold sponsors to help make it happen. Our combined donation exceeded $1,000 USD (http://www.aeracode.org/2013/04/11/whol ... kstarting/). We have consistently done the same with crowdfunding that add major features to Django.
- Gold sponsor REST API framework (as CrypticoCorp https://web.archive.org/web/20150108204 ... nouncement).
- Silver sponsor Django multi template engine (as myself https://myks.org/en/multiple-template-e ... o/funders/)
- Same for books like High Performance Django and Two Scoops of Django among others.
- Cryptico Corp was the first latin american company to become a Django corporate member (https://web.archive.org/web/20150906103 ... e-members/)
- Since Mayan is one of the projects pushing the limits of Django, we share our many solutions for stuff like packaging, testing, scalability, failure tolerance, and background tasks via talks at Django and Python events. Some slides survive here: https://speakerdeck.com/siloraptor.
This is how involved we are in helping improve Django and by extension Mayan.

For many years and very frequently we have warned about not using SQLite in production. It is not a bad database (in fact it is quite good) but its simplicity has some trade off in write concurrency and locking. These trade offs are amplified by the way Mayan uses the database in production. Still we receive complains and see posts about problems with Mayan using SQLite. To make it absolutely clear that SQLite must not be used in production, we added in version 3.0 an intrusive banner whenever Mayan detects that SQLite is being used. We still continue to see posts about problems with SQLite in production. Short of having Mayan refuse to work if it detects SQLite in production mode, we can't do more.

Our testing methodology is very thorough. We test everything individually and combined against SQLite, MySQL and PostgreSQL. We tests the models, the views, the API, links, drivers, backends, classes. Out test suite has 895 tests (1035 now in the development branch). These are executed against SQLite, then MySQL then again with PostgresSQL. If they all pass we build the Python package. If the Python package builds without error we build the Docker image. The Docker image is deployed and we run the test suite once again. Only then the new version is released. (https://gitlab.com/mayan-edms/mayan-edm ... s/35061759) (https://gitlab.com/mayan-edms/mayan-edm ... /115212244). All this is done automatically using our own continuous integration setup (https://gitlab.com/mayan-edms/mayan-edm ... lab-ci.yml).

What is being discussed here is a database conversion issue and not a database upgrade one. Database upgrades are the responsibility of the app and the framework. Database conversions are not the responsibility of the app (Mayan), they are the responsibility of the framework. Database conversion is outside the scope of what Mayan does but we added the code, management command, instructions and testing setup to provide this to our users until the framework (Django) decided to add this themselves (like they did with migrations). If this causes confusion about the code quality of Mayan we'll have not choice but to remove it.

I hope this clears up the confusion and provides more context about the lengths we go to ensure data is never lost or corrupted even during upgrades and database schema changes, and that issues with database conversion have no relation to the code quality of Mayan or the development process.

eriggs
Posts: 10
Joined: Fri Sep 07, 2018 3:32 am

Re: Databse conversion (SQLite to PostgreSQL)

Post by eriggs » Sat Mar 09, 2019 6:12 am

I agree. Besides database conversion is a task for database admins, devops, or the platform software, putting it in the app is asking for trouble.

I vote for the removal of the database converter in time for version 4.0.

Post Reply