Setup database schema for configurations and permissions of QWC services using Alembic database migrations.
Uses PostgreSQL connection service qwc_configdb (ConfigDB) (cf. sqlalchemy.url and version_table_schema in alembic.ini).
Setup PostgreSQL connection service file pg_service.conf:
[qwc_configdb]
host=localhost
port=5439
dbname=qwc_demo
user=qwc_admin
password=qwc_admin
sslmode=disable
Place this file in your home directory, or set the PGSERVICEFILE environment variable to point to the file.
Create user according to service qwc_configdb:
CREATE ROLE qwc_admin LOGIN PASSWORD 'qwc_admin';
Create schema for migration table and ConfigDB in qwc_configdb:
CREATE SCHEMA qwc_config AUTHORIZATION qwc_admin;
Run commands from this directory.
Create a migration script:
alembic revision -m "create sample table"
Edit generated migration script alembic/versions/123456abcdef_create_sample_table.py
Run migrations:
alembic upgrade head
Upgrade one version:
alembic upgrade +1
Downgrade one version:
alembic downgrade -1
docker run -it sourcepole/qwc-config-db
docker run -it -v pg_service.conf:/root/.pg_service.conf:ro sourcepole/qwc-config-db setup-qwc_config-schema.sh
docker run -it -v pg_service.conf:/root/.pg_service.conf:ro sourcepole/qwc-config-db alembic upgrade head
Create SQL DB dump:
docker run -it -v pg_service.conf:/root/.pg_service.conf:ro sourcepole/qwc-config-db pg_dumpall --globals-only
docker run -it -v pg_service.conf:/root/.pg_service.conf:ro sourcepole/qwc-config-db pg_dump --create
Install Python module for PostgreSQL:
apt-get install python3-psycopg2
Create a virtual environment:
virtualenv --python=/usr/bin/python3 --system-site-packages .venv
Without system packages:
virtualenv --python=/usr/bin/python3 .venv
Activate virtual environment:
source .venv/bin/activate
Install postgres headers needed to build psycopg2 (part of requirements.txt below):
apt-get install libpq-dev
Install requirements:
pip install -r requirements.txt