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. Pirogue is required for views regeneration.
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
andpg_restore
withsudo 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
and pirogue
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.1.5. Pirogue installation
pip3 install pirogue
To update pirogue
pip3 install --upgrade pirogue
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 as1.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 intww_test
.-i
The constraints, views and indexes are ignored (if they are not similar intww_comp
andtww_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 generatepg_restore
errors--exclude-field-pattern 'usr_%'
if custom attributes were added