Upgrade database

Create a new upgrade delta

  • First create a new branch for your modification on the model
  • Modify the SQL creation code at your convenience
  • Create the corresponding SQL delta file in data-model/update/delta. * file must be named : delta_{tag_number}_{year_month_day}.sql (delta_x.x.x_ddmmyyyy.sql)
  • Push your branch, and check travis result
  • If travis test is OK, then you can create a pull request to merge your modifications in master

Migrate an existing database

If you use an old version of qWat model, you can upgrade it by following the instructions bellow:

Warning

You should always save you current database before upgrading

  • Open a terminal, and move to qwat_project/data-model/update
  • Launch upgrade command: ./upgrade_db.sh
    • This will test the migration process
  • If the migration process is OK, then launch again the command with the parameter to upgrade the real DB : ./upgrade_db.sh --upgrade yes

The way it works

We’ve got a tag for version x.0

In /update/delta/ directory, we find the SQL files list which contain the current evolutions (delta). If we execute init_qwat.sh in the current git repository, we should have the same DB than if we execute init_qwat.sh of the x.0 version. The contributor who add a new delta SQL file must also update the DB creation SQL script.

For a user, the migration process consists in :

  • Get the version of his base (should be given in qWat the system schema).
  • Execute the migration script, which take in parameter that version number. The script execute the following tasks :
    • pg_dump of the qWat DB
    • pg_restore of the previous dump in a test DB (qwat_test)
    • Execute on test DB all SQL delta above or equals to the version number (sql deltas must be named delta_NUM_TAG_date.sql)
    • Execute conformity test script:
      • Create a new DB: qwat_test_conform
      • Produce the reference file (result from the DB test sql)
      • Comparison of that reference file with the one produced by the qwat_test DB previously created
      • If all is OK, user is invited to launch the migration on his real DB
      • If errors occur, the user has to debug himself or contact the qWat team