Content
Table of Contents | ||
---|---|---|
|
Objective
Explore the migration of openIMIS database from Microsoft SQL Server to PostgreSQL
...
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 columns in their tables commented out (in each module’s models.py file): Are these columns 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.
Updates to Preliminary Notes
PostgreSQL versions 11.5 and newer should be considered instead of version 10 that was chosen. Considering You are already requiring 64-bit installations for openIMIS, losing support for 32-bit systems is no downside. Additionally, only versions 11.5 and newer support stored procedures. In older versions these SPs are stored as functions instead.
Some of the commented out columns are still required by some functions or procedures, most notably the startcyle1 - startcycle4 columns in the tblProduct table. Thankfully, most of them are correctly defined so that no additional work (beyond uncommenting the lines) is needed. The migration tool also had no issue filling the tables with data, suggesting that the columns are properly defined.
Child Pages
Child pages (Children Display) | ||
---|---|---|
|