Versions Compared

Old Version 2

changes.mady.by.user Uwe Wahser

Saved on

compared with

New Version 3

changes.mady.by.user Michel Borer

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Report of difficulties, attention points ('hacks' put in place),… is more important than the result itself as it would anticipate a difficulty to migrate the related ‘module’ in new architecture later on.

Preliminary Notes

  • Using a different database system requires edits to the settings.py file within the folder “<openIMIS base directory>\openIMIS-be\openimis-be_py\openIMIS\openIMIS”

    • We still need to look into this: Can we have one universal settings.py file that can figure out dynamically what type of database server is being used and load drivers and preferences accordingly? Otherwise the file needs to be changed manually whenever the database is migrated. I suspect that doing this manually in all facilities in a country could lead to issues.

    • PostgreSQL - unlike SQL Server - does not require a specific driver to be named within the database options of that settings file. But Django / Python still require a driver to be installed.

      • For a new system this can be done by adding a new line to requirements.txt (where Python / Pip gets all requirements for an openIMIS installation): The package psycopg2 should be added.

      • In a system that already has openIMIS installed, running the command “pip install psycopg2“ from within openIMIS’s virtual environment satisfies the requirement.

      • Django requires psycopg2 version 2.5.4 or higher (from the official documentation: https://docs.djangoproject.com/en/3.0/ref/databases/#postgresql-notes ). Currently we’re using version 2.8.3

      • More information on the driver, should You be interested: https://www.psycopg.org/

  • PostgreSQL can be installed alongside an “Application Stack Builder“. This allows PostgreSQL users to install tools and extensions for their server.

    • One of those is the Migration Toolkit (mtk). mtk allows us to migrate a database from one type of SQL to another (For us: From SQL Server to PostgreSQL). However: It can only migrate tables, views, and the data in those tables and view. Stored Procedures still need to be migrated and adapted manually.

      • Considering these limitations: It may be preferable to create our own tool which can be more flexible (e.g: Have an option to not migrate historic data or only migrate data that is newer than a specified date). Also, if we have an in-house tool it can easily be adapted in the future.

    • Another important tool is pgAdmin4. This is PostgreSql’s equivalent to the Microsoft SQL Server Management Studio program. pgAdmin4 uses a web interface.

  • Currently, we are working with PostgreSQL version 10.12. It is the newest version with considerable compatibility for older operating systems: It supports both 32-bit and 64-bit architectures (newer versions have dropped 32-bit support) and it supports Windows versions all the way back to Windows 7 and Windows Server versions all the way back to Windows Server 2008 R1. It also supports Mac OSX 10.10 and newer as well as many different flavours of Linux.

    • Upgrading to a newer version should easily be possible. As far as we know our setup also supports newer versions (we can even use the same driver). However, if this is a concern, it should preferably be done before we send this to our clients

  • Migration should be possible and simple from within Django. However, we need to be careful because all our modules have many rows in their tables commented out (in each module’s models.py file): Are these rows still required (e.g. for backwards compatibility) or can they be safely ignored? This needs to be investigated. Keeping the new database as lean as possible will certainly help with speed.