OHDSI Home | Forums | Wiki | Github

How to migrate existing MS SQL database to PostgreSQL database (from ATLAS version 2.7.0 to 2.11.1)


We are currently using ATLAS version 2.7.0 with Microsoft SQL Server databases, and are trying to migrate it to the latest version 2.11.1.

Our questions are:

  1. How do we migrate the existing tables from Microsoft SQL Server to PostgreSQL server?
  2. What do we do about the differing tables between old ATLAS version 2.7.0 and the latest version 2.11.1?

Thank you!

I would do the following:

  1. Migrate your MSSql database from sql server to postgreSQL using the tools suggested in this search. I read through a few of the suggestions, and it seems that there’s a process of exporting the schema from MSSQL using sql mangment studio and then running it through a tool to create the DDL for postgresql. This should be a straight-forward task because we do not leverage any special MS-SQL specific functions like stored procedures or triggers. You will need to migrate the webAPI schema, including tables, views and sequences.

  2. Re-build WebAPI but configure it to use PostgreSQL (described in the installation docs) and point it to your 2.7.0 database. The goal is to start up a new instance of WebAPI that’s pointing to the 2.7.0 version of your current data but using the postgresql configuration.

  3. Once you verify that 2.7.0 is working on postgresql, you can then install 2.11.1 and all the webapi tables will be automatically migrated. So the concern you have in your second point is addressed via migration scripts that will automatically be applied with the 2.11.1 version starts.

I suggest making backups of everything you have so that you don’t lose anything, but since we just us simple DB constructs (tables, views and sequences) so the only thing you want to double check in your migration of schema and data is that the sequences have the correct ‘next’ value (ie: if you have cohort definitions with max id = 375, then your cohort_definition_sequence should show 376 (at least) as the next value. We migrated away from MSSQL auto-number fields in version 2.7, so that should simplify some of the data/schema migration tasks.

Thank you very much for the detailed information! We’ll try your approach.