Upgrade PostgreSQL

[Char-Al]

Here I will develop how to update the PostgreSQL database for SEAL.

Some usefull link :

• https://docs.postgresql.fr/14/pgupgrade.html

[Char-Al]

This may be a bit tricky.

Sometimes, upgraded SEAL (eg. 1.0.0 -> 1.1.0) comes with an upgrade of postgreSQL too (eg. 13 -> 14). This upgrade may be the cause of several issues.

Here I list an help of how I upgrade without any issues.

Test current version

First of alI test the current version of SEAL (1.0.0):

cd seal/
conda activate seal
export FLASK_ENV=prod
export FLASK_APP=seal
export PYTHONPATH=${PWD}
pg_ctl -D ${PWD}/seal/seal.db -l ${PWD}/seal/seal.db.log start
flask run -h 0.0.0.0

Then try to import a new sample. If all is OK, stop the database and quit the conda env :

pg_ctl -D ${PWD}/seal/seal.db -l ${PWD}/seal/seal.db.log stop
conda deactivate

Upgrade SEAL

To upgrade SEAL I use a git pull but it may be a good option to fix the version you want to use :

git pull
# GO TO THE SPECIFIC VERSION :
# git fetch --all
# git checkout 1.1.0

Create and activate a new conda env, but don't remove the old one yet :

conda create --name seal_old --clone seal
conda remove --name seal --all
conda env create -f environment.yml
conda activate seal

Create and upgrade the database :

mv seal/seal.db seal/seal.db.13
initdb -D seal/seal.db
pg_upgrade -b ~/miniconda3/envs/seal_old/bin/ -B ~/miniconda3/envs/seal/bin/ -d seal/seal.db.13 -D seal/seal.db
pg_ctl -D ${PWD}/seal/seal.db -l ${PWD}/seal/seal.db.log start
vacuumdb --all --analyze-in-stages
mv migrations migrations_old
flask db init
cp migrations_old/versions/* migrations/versions/
flask db upgrade
flask db migrate -m "Upgrade to 1.1.0"
flask db upgrade

Now clear the repo and launch the app :

rm -rf migrations_old seal/seal.db.13
conda remove --name seal_old --all
flask run -h 0.0.0.0