Databse conversion (SQLite to PostgreSQL)

Questions, comments, discussions. Over time certain topics might be moved to their own category.
Post Reply
User avatar
rosarior
Posts: 159
Joined: Tue Aug 21, 2018 3:28 am

Databse conversion (SQLite to PostgreSQL)

Post by rosarior » Fri Sep 28, 2018 5:59 am

We've published an entry in the Wiki outlining the step to convert installations of Mayan EDMS running on SQLite to PostgreSQL.

Be sure to backup your existing SQLite database and media files before attempting the database conversion.

https://wiki.mayan-edms.com/index.php?t ... conversion

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

Re: Databse conversion (SQLite to PostgreSQL)

Post by winstonsmith » Fri Sep 28, 2018 3:13 pm

hello roberto thanks for the update on the database conversion process.

i have one question though, do i need to migrate from my docker container install to the 'basic deployment' outlined in the wiki. ie, do i need to install 3.1.x via apt-get (on debian) to be able to migrate my database from the docker install of 2.7.x that we are currently running?

thanks

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

Re: Databse conversion (SQLite to PostgreSQL)

Post by rosarior » Fri Sep 28, 2018 10:39 pm

No, there is no need to change the deployment time. We published this method first because it is the one we've used the most. We'll be writing another procedure using Docker.

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

Re: Databse conversion (SQLite to PostgreSQL)

Post by KevinPawsey » Sat Nov 03, 2018 12:17 am

I am looking to migrate my SQLite to Postgres... but that link to the wiki page is dead now... is there another source for the migration procedure. I am sure that I have read about it somewhere, but just can't remember where :)

Thanks


Kevin

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

Re: Databse conversion (SQLite to PostgreSQL)

Post by rosarior » Sat Nov 03, 2018 12:41 am

The information was indeed in the Wiki. But information in the Wiki matures we migrate it to the documentation. The conversion process is now under the "Administraion" topic of the reorganized documentation: https://docs.mayan-edms.com/topics/admi ... conversion

The conversion process here is for direct deployments. We are strill experimenting to get the best and shortest process for Docker installations.

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

Re: Databse conversion (SQLite to PostgreSQL)

Post by KevinPawsey » Sat Nov 03, 2018 10:29 am

Ah good... I am not going crazy thinking I read it somewhere :D

So should I not migrate my Docker to Postgres yet... will this process not work? I am still in the early stages of deployment, so I don't mind giving it a try... or if there is a different process that you want to test, let me know.

At the moment I have just the official Docker Image, and I have the Postgres 9.5 image downloaded, but not setup yet. My Environment settings for Mayan don't include the Postgres settings, which I know I will need to do before the conversion.

Thanks again for producing such a project, and thank you for your help.

Regards


Kevin

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

Re: Databse conversion (SQLite to PostgreSQL)

Post by rosarior » Mon Nov 05, 2018 5:34 am

You should migrate to PostgreSQL regardless of the installation method. It is just that we don't have a step by step guide yet for Docker, the guide we published and tested was using the Python package on a direct install. We hope to get some time soon to test and publish the same procedure using Docker commands.

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

Re: Databse conversion (SQLite to PostgreSQL)

Post by KevinPawsey » Mon Nov 05, 2018 3:15 pm

I shall put on my guinea pig costume and give it a bash very soon and will report back ;)

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

Re: Databse conversion (SQLite to PostgreSQL)

Post by KevinPawsey » Mon Nov 12, 2018 6:07 pm

OK... I have donned my guinea pig costume, and I have given this a /bin/bash ;)

So... this is me, migrating from SQLite to Postgres, with a Docker deployment of Mayan-EDMS and Docker deployment of Postgres on the same host.

firstly, I believe that there is an error in the documentation for migrating db in the documentation there is the following in the updating config.yml section:

Code: Select all

# After

DATABASES:
  old:
    ATOMIC_REQUESTS: false
    AUTOCOMMIT: true
    CONN_MAX_AGE: 0
    ENGINE: django.db.backends.sqlite3
    HOST: ''
    NAME: /opt/mayan-edms/media/db.sqlite3
    OPTIONS: {}
    PASSWORD: ''
    PORT: ''
    TEST: {CHARSET: null, COLLATION: null, MIRROR: null, NAME: null}
    TIME_ZONE: null
    USER: ''
  default:
    ATOMIC_REQUESTS: false
    AUTOCOMMIT: true
    CONN_MAX_AGE: 0
    ENGINE: django.db.backends.postgresql
    HOST: '127.0.0.1'
    NAME: /opt/mayan-edms/media/db.sqlite3
    OPTIONS: {}
    PASSWORD: 'mayanuserpass'
    PORT: ''
    TEST: {CHARSET: null, COLLATION: null, MIRROR: null, NAME: null}
    TIME_ZONE: null
    USER: 'mayan'
However, this appears to be incorrect:

Code: Select all

   ENGINE: django.db.backends.postgresql
    HOST: '127.0.0.1'
    NAME: /opt/mayan-edms/media/db.sqlite3
should surely be:

Code: Select all

   ENGINE: django.db.backends.postgresql
    HOST: '127.0.0.1'
    NAME: mayan
this then points the postgres to the mayan Postgres database, rather than a bogus sqlite database.

Firstly I used the following command from the docker host to deploy a Postgres 9.5 container:

Code: Select all

docker run -d \
--name mayan-edms-postgres \
--restart=always \
-p 5432:5432 \
-e POSTGRES_USER=mayan \
-e POSTGRES_DB=mayan \
-e POSTGRES_PASSWORD=mayanuserpass \
-v [your_path_to_postgres_data_volume]:/var/lib/postgresql/data \
-d postgres:9.5
I then added an extra step of finding the IP address of the Postgres docker container:

Code: Select all

docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mayan-edms-postres
This should return a 172.17.0.X address... if you have a standard install... but this is the IP address of the postgres container.

Then I modified my Mayan-EDMS docker container to include the environment variables to enable Postgres:

Code: Select all

MAYAN_DATABASE_ENGINE=django.db.backends.postgresql
MAYAN_DATABASE_HOST=[postgres_ip_address_found_above]
MAYAN_DATABASE_NAME=mayan
MAYAN_DATABASE_PASSWORD=mayanuserpass
MAYAN_DATABASE_USER=mayan
MAYAN_DATABASE_CONN_MAX_AGE=360
then followed the steps from the following page:
https://docs.mayan-edms.com/topics/administration.html from the section named "Database conversion" ... ignoring the first few steps to install postgress, create the database, etc (as you have already done all of it via the docker deployment above)

I then changed to the containers CLI with the following command:

Code: Select all

docker exec -it [mayan-edms_docker_image_name] /bin/bash
beginning from the step "Copy the newly created fallback config file:" This step makes the config_backup.yml the current config file (config.yml). You will find this file in the /var/lib/mayan/ folder... or in the mayan data volume (same place, just mapped). The reason for this is when Mayan starts, on completion of coming up, it copies the config.yml to config_backup.yml, as the backup file is a "known working" config file. The config.yml is then loaded on next startup... so if you break something, you can then revert back to the config_backup.yml, which is a known working file. This is a similar mechanism that Windows uses for it's startup file *goes and washes mouth out with soap*. So although it seems like it is counter-intuitive, it is actually the correct way round.

Then, I edited the config.yml, replacing the following in the suggested config lines:
in the #After section as it is on the website...

Code: Select all

    ENGINE: django.db.backends.postgresql
    HOST: '127.0.0.1'
    NAME: /opt/mayan-edms/media/db.sqlite3
    OPTIONS: {}
Changed to...

Code: Select all

    ENGINE: django.db.backends.postgresql
    HOST: '[postgres_ip_address_found_above]'
    NAME: mayan
    OPTIONS: {}
See above for the change of the Name: field

I then ran the command to migrate the database, with the only parts being changed as:

Code: Select all

MAYAN_DATABASE_HOST=[postgres_ip_address_found_above] MAYAN_MEDIA_ROOT=[your_path_to_MAYAN-EDMS_data_volume]
I then ran the Convert, again only changing the options outlined above for the previous migrate command

The final step is not needed, as this is dealt with by the Docker Environment variables changed at the start.

To test, I made a note of the datestamp on the db.sqlite3 file in the Mayan data volume... then restarted Mayan.
Firstly... the annoying yellow box was gone, bugging about the SQLite database...
Secondly... the datestamp didn't change when 'doing stuff' in Mayan (like adding test documents, etc).

So... all that without destroying all my data... all in a days work! lol

Post Reply