4.7. Manual update of the datamodel

This page explains how to update your datamodel manually. Alternatively, the data-model tool offers a graphical interface that automates these steps and is usually easier to use.

The releases of the datamodel are available here. When a new datamodel is released, a TWW database can be updated with PUM. In this documentation, the main steps and requirements are reproduced.

4.7.1. Requirements

4.7.1.1. Client

Update is done using PUM available on linux and windows environment. It does not require to be run on the server directly but can be run from a remote client or a virtual machine.

4.7.1.2. PostgreSQL

The functions pg_dump, pg_restore must be available from the terminal, they are installed along with PostgreSQL. The version of the client postgresql and the server postgresql must match.

If several pg_dump and pg_restore are installed on the client, PUM has to use the proper version. You can either:

  • Set the default pg_dump and pg_restore with sudo ln -s /usr/lib/postgresql/XX/bin/pg_dump /usr/bin/pg_dump --force

  • Make use of the PUM config file.

4.7.1.3. Python

The default python must be python 3.

4.7.1.4. PUM installation

pip3 install pum

To update PUM

pip3 install --upgrade pum

sudo can be used to install PUM system wide or the --user flag can be used to install they into the current user’s home directory.

If you encounter any problem with PUM installation or usage, check the PUM documentation.

4.7.2. Database update

For the following commands the database is connected with the service tww_prod.

4.7.2.1. PostgreSQL service

The parameters to connect to the databases are provided in a service file (for instance the file ~/.pg_service.conf.)

There are at least three services inside: * tww_prod database to be updated * tww_test database which will store a test of the update * tww_comp current model, after the update the tww_prod and tww_comp models should be similar.

4.7.2.2. Database backup

It is recommended to backup the database before the update. In command line:

pg_dump -Fc -f /path/to/dump/tww_prod.dump -d "service=tww_prod"

4.7.2.3. Download and install current database release

  • Download current datamodel release from https://github.com/TWW/datamodel/archive/refs/tags/<release_number>.zip (replacing <release_number> with the desired version, such as 1.5.4). Run the following commands from the unzipped directory.

  • Create current database. It is used for comparison (target).

psql -h host -p port -U postgres -c 'CREATE DATABASE tww_comp;'

  • Install current datamodel release.

./scripts/db_setup.sh -f -r -p tww_comp Launched from the datamodel folder.

4.7.2.4. Create empty database

  • Create test database. It is used to test the update processus.

psql -h host -p port -U postgres -c 'CREATE DATABASE tww_test;'

4.7.2.5. Launch update

The following command must be launched form the datamodel folder.

pum test-and-upgrade -pp tww_prod -pt tww_test -pc tww_comp -t tww_sys.pum_info -f dump.dump -d delta/ -i constraints views indexes --exclude-schema public --exclude-schema tww_migration -v int SRID 2056 -x.

  • -pp Production database

  • -pt Test database

  • -pc Comparison database

  • -t Table which stores the database versions and update state

  • -f A backup file which is generated during the process. It is restored in tww_test.

  • -i The constraints, views and indexes are ignored (if they are not similar in tww_comp and tww_test after the update, the processus continue anyway.

  • -- exclude-schema schema which is ignored in the comparison

  • -v Parameters of the delta scripts

  • -x Ignore restore errors (materialized views can generate pg_restore errors

  • --exclude-field-pattern 'usr_%' if custom attributes were added