Repository: AlertaDengue/AlertaDengue (language: python)

Issue:
Add production database in docker workflow



Discussion / hints:

Fix patch:
diff --git a/.containers-sugar.yaml b/.containers-sugar.yaml
index 089de1d2..f663d1b1 100644
--- a/.containers-sugar.yaml
+++ b/.containers-sugar.yaml
@@ -24,9 +24,9 @@ service-groups:
     compose-path: containers/compose.yaml
     env-file: .env_prod
     services:
-      default: memcached,rabbitmq,web,worker,denguedb
+      default: dengue_db,memcached,rabbitmq,web,worker
       list:
-        - name: denguedb
+        - name: dengue_db
           health-check: true
         - name: web
           health-check: true
diff --git a/.dockerignore b/.dockerignore
index 4bd3955d..f73af35b 100644
--- a/.dockerignore
+++ b/.dockerignore
@@ -1,6 +1,6 @@
 ci/*
 docs/*
-containers/postgres/dumps/*
+
 notebooks_tmp/*
 notebooks/*
 rever/*
@@ -9,3 +9,5 @@ rever/*
 .coverage
 .vscode
 .pre-commit*
+
+containers/postgres/dumps/*
diff --git a/.github/workflows/linux.yml b/.github/workflows/linux.yml
index 681d395b..6c5f2a62 100644
--- a/.github/workflows/linux.yml
+++ b/.github/workflows/linux.yml
@@ -14,6 +14,7 @@ env:
   PSQL_DBF: dengue
   PSQL_USER: infodenguedev
   PSQL_PASSWORD: infodenguedev
+  POSTGRES_USER: postgres
   QUERY_CACHE_TIMEOUT: 86400
   MEMCACHED_HOST: localhost
   MEMCACHED_PORT: 11211
@@ -80,10 +81,6 @@ jobs:
     - name: Create env file
       run: envsubst < .env.tpl > .env_dev
 
-    - name: Build containers
-      run: |
-        containers-sugar build --group dev
-
     - name: Start containers
       run: |
         containers-sugar start --group dev
@@ -92,10 +89,6 @@ jobs:
       run: |
         makim wait-all
 
-    - name: Setup tmate session
-      if: "${{ failure() }}"
-      uses: mxschmitt/action-tmate@v3
-
     - name: Linting
       run: |
         pre-commit install
diff --git a/.gitignore b/.gitignore
index dcb41859..294ac0e2 100644
--- a/.gitignore
+++ b/.gitignore
@@ -54,3 +54,4 @@ rever/
 *latest_dengue.sql.gz
 containers/postgres/setup/dumps/*
 staging_data/
+*/dumps/
diff --git a/conda/dev.yaml b/conda/dev.yaml
index 022abcb2..14cf36ef 100644
--- a/conda/dev.yaml
+++ b/conda/dev.yaml
@@ -9,5 +9,5 @@ dependencies:
   - psycopg2-binary
   - python 3.9.*
   - pip:
-    - containers-sugar ==1.4.0
+    - containers-sugar ==1.4.1
     - makim ==1.5.0
diff --git a/conda/prod.yaml b/conda/prod.yaml
new file mode 100644
index 00000000..14cf36ef
--- /dev/null
+++ b/conda/prod.yaml
@@ -0,0 +1,13 @@
+name: alertadengue
+channels:
+  - nodefaults
+  - conda-forge
+dependencies:
+  - gdal 3.3.*
+  - pip
+  - poetry
+  - psycopg2-binary
+  - python 3.9.*
+  - pip:
+    - containers-sugar ==1.4.1
+    - makim ==1.5.0
diff --git a/containers/compose.yaml b/containers/compose.yaml
index ab38f5f4..a2134738 100644
--- a/containers/compose.yaml
+++ b/containers/compose.yaml
@@ -1,10 +1,20 @@
 version: "3.4"
 services:
-  postgres:
-    hostname: postgres
+  base_postgres:
     build:
       context: ".."
       dockerfile: "containers/postgres/Dockerfile"
+      args:
+        ENV: ${ENV}
+    env_file:
+      - ../.env_${ENV}
+    profiles:
+      - donotstart
+
+  postgres:
+    hostname: postgres
+    extends:
+      service: base_postgres
     env_file:
       - ../.env_${ENV}
     environment:
@@ -13,8 +23,8 @@ services:
     ports:
       - ${SERVICES_NUMBER}${PSQL_PORT}:5432
     volumes:
-      - pg_data:/var/lib/postgresql/data/
-      - dumps:/dumps
+      - ../containers/postgres/setup/:/docker-entrypoint-initdb.d/
+      - ../containers/postgres/schemas/:/schemas/
     command: -p ${PSQL_PORT} # postgres -c max_wal_size=3GB
     restart: always
     healthcheck:
@@ -26,9 +36,8 @@ services:
 
   dengue_db:
     hostname: dengue_db
-    build:
-      context: ".."
-      dockerfile: "containers/postgres/Dockerfile-prod"
+    extends:
+      service: base_postgres
     env_file:
       - ../.env_${ENV}
     environment:
@@ -37,15 +46,17 @@ services:
     ports:
       - ${SERVICES_NUMBER}${PSQL_PORT}:5432
     volumes:
-      - postgres_data:/var/lib/postgresql/data/
-      - ./dev_dumps:/dumps
+      - pg_data:/var/lib/postgresql/data/
+      - ../containers/postgres/setup/:/docker-entrypoint-initdb.d/
+      - ../containers/postgres/dumps/:/dumps/
     command: -p ${PSQL_PORT} # postgres -c max_wal_size=3GB
     restart: always
     healthcheck:
       test: ["CMD-SHELL", "pg_isready -p ${PSQL_PORT} -U ${PSQL_USER}"]
-      interval: 5s
+      interval: 10s
       timeout: 5s
-      retries: 20
+      retries: 10
+    command: -p ${PSQL_PORT}
 
   memcached:
     hostname: memcached
@@ -80,6 +91,8 @@ services:
         HOST_UID: ${HOST_UID}
         HOST_GID: ${HOST_GID}
         ENV: ${ENV}
+    env_file:
+      - ../.env_${ENV}
     environment:
       - MEMCACHED_HOST=memcached
       - CELERY_BROKER_URL=amqp://guest:guest@rabbitmq:5672
diff --git a/containers/postgres/Dockerfile b/containers/postgres/Dockerfile
index 70624ad4..b17c8d60 100644
--- a/containers/postgres/Dockerfile
+++ b/containers/postgres/Dockerfile
@@ -13,6 +13,5 @@ RUN apt-get update \
            postgresql-$PG_MAJOR-postgis-$POSTGIS_MAJOR-scripts \
       && rm -rf /var/lib/apt/lists/*
 
-COPY containers/postgres/postgres_setup/* /docker-entrypoint-initdb.d/
 
 USER postgres
diff --git a/containers/postgres/postgres_setup/00-infodengue_roles.sql b/containers/postgres/postgres_setup/00-infodengue_roles.sql
deleted file mode 100644
index e454f63c..00000000
--- a/containers/postgres/postgres_setup/00-infodengue_roles.sql
+++ /dev/null
@@ -1,36 +0,0 @@
---
--- PostgreSQL database cluster dump
---
-
-SET default_transaction_read_only = off;
-
-SET client_encoding = 'UTF8';
-SET standard_conforming_strings = on;
-
---
--- Roles
---
-
-CREATE ROLE "infodenguedev";
-ALTER ROLE "infodenguedev" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity';
-
-CREATE ROLE "Dengue";
-ALTER ROLE "Dengue" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity';
-CREATE ROLE administrador;
-ALTER ROLE administrador WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD NULL;
-CREATE ROLE dengue;
-ALTER ROLE dengue WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity';
-CREATE ROLE forecast;
-ALTER ROLE forecast WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION VALID UNTIL 'infinity';
-ALTER ROLE forecast SET search_path = forecast, public;
-CREATE ROLE dengueadmin;
-ALTER ROLE dengueadmin WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD NULL VALID UNTIL 'infinity';
---CREATE ROLE postgres;
---ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION NOBYPASSRLS;
-CREATE ROLE "Read_only";
-ALTER ROLE "Read_only" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity';
---
--- Role memberships
---
-GRANT "Dengue" TO dengue GRANTED BY administrador;
-GRANT "Dengue" TO dengueadmin GRANTED BY administrador;
diff --git a/containers/postgres/postgres_setup/01-prepare_database.sh b/containers/postgres/postgres_setup/01-prepare_database.sh
deleted file mode 100644
index 495d68df..00000000
--- a/containers/postgres/postgres_setup/01-prepare_database.sh
+++ /dev/null
@@ -1,20 +0,0 @@
-#!/usr/bin/env bash
-
-set -e
-
-
-PSQL_CMD="psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER""
-
-# PostgreSQL databases
-for dbname in "$PSQL_DBF"; do
-
-    echo "CREATE DATABASE "${dbname}" WITH OWNER '""$PSQL_USER""';" | ${PSQL_CMD}
- 
-done
-
-# PostgreSQL roles
-for dbusers in "$DB_ROLES"; do
-
-    echo "ALTER ROLE ""$PSQL_USER"" WITH PASSWORD '""$PSQL_PASSWORD""';" | ${PSQL_CMD}
- 
-done
diff --git a/containers/postgres/postgres_setup/03-grant-user-readonly.sh b/containers/postgres/postgres_setup/03-grant-user-readonly.sh
deleted file mode 100644
index b9eef107..00000000
--- a/containers/postgres/postgres_setup/03-grant-user-readonly.sh
+++ /dev/null
@@ -1,16 +0,0 @@
-#!/usr/bin/env bash
-
-set -e
-
-DB_NAME="$PSQL_DB"
-PSQL_CMD="psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER""
-DB_USER="$PSQL_USER"
-
-echo "ALTER ROLE "${DB_USER}" WITH PASSWORD 'infodenguedev';" | ${PSQL_CMD}
-
-for schema in '"Dengue_global"' '"Municipio"' forecast public; do
-
-    echo "GRANT USAGE ON SCHEMA "${schema}" TO "${DB_USER}";" | ${PSQL_CMD} -d ${DB_NAME}
-    echo "GRANT SELECT ON ALL TABLES IN SCHEMA "${schema}" TO "${DB_USER}";" | ${PSQL_CMD} -d ${DB_NAME}
- 
-done
diff --git a/containers/postgres/schemas/grant-user-readonly.sh b/containers/postgres/schemas/grant-user-readonly.sh
new file mode 100755
index 00000000..f4e28e03
--- /dev/null
+++ b/containers/postgres/schemas/grant-user-readonly.sh
@@ -0,0 +1,10 @@
+#!/usr/bin/env bash
+
+set -e
+
+PSQL_CMD="psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER""
+
+for schema in '"Dengue_global"' '"Municipio"' forecast public; do
+    echo "GRANT USAGE ON SCHEMA "${schema}" TO "${PSQL_USER}";" | ${PSQL_CMD} -d ${PSQL_DB}
+    echo "GRANT SELECT ON ALL TABLES IN SCHEMA "${schema}" TO "${PSQL_USER}";" | ${PSQL_CMD} -d ${PSQL_DB}
+done
diff --git a/containers/postgres/postgres_setup/02-dengue_schema.sql b/containers/postgres/schemas/schemas_dengue.sql
similarity index 100%
rename from containers/postgres/postgres_setup/02-dengue_schema.sql
rename to containers/postgres/schemas/schemas_dengue.sql
diff --git a/containers/postgres/setup/00-infodengue_roles.sql b/containers/postgres/setup/00-infodengue_roles.sql
new file mode 100644
index 00000000..eb24889c
--- /dev/null
+++ b/containers/postgres/setup/00-infodengue_roles.sql
@@ -0,0 +1,38 @@
+--
+-- PostgreSQL database
+--
+SET client_encoding = 'UTF8';
+SET default_transaction_read_only = off;
+SET standard_conforming_strings = on;
+
+--
+-- Roles
+-- Create Roles
+--
+CREATE ROLE "Dengue";
+CREATE ROLE "Read_only";
+CREATE ROLE "infodenguedev";
+CREATE ROLE "administrador";
+CREATE ROLE "dengueadmin";
+CREATE ROLE "dengue";
+CREATE ROLE "forecast";
+-- CREATE ROLE "postgres";
+
+--
+-- Alter Roles
+--
+ALTER ROLE "Dengue" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity';
+ALTER ROLE "dengueadmin" WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD NULL VALID UNTIL 'infinity';
+ALTER ROLE "Read_only" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity';
+ALTER ROLE "infodenguedev" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity';
+ALTER ROLE "dengue" WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS VALID UNTIL 'infinity';
+ALTER ROLE "administrador" WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD NULL;
+ALTER ROLE "forecast" WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION VALID UNTIL 'infinity';
+ALTER ROLE "postgres" WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION NOBYPASSRLS;
+ALTER ROLE "forecast" SET search_path = forecast, public;
+
+--
+-- Role memberships
+--
+GRANT "Dengue" TO dengue GRANTED BY "administrador";
+GRANT "Dengue" TO dengueadmin GRANTED BY "administrador";
diff --git a/containers/postgres/setup/01-prepare_database.sh b/containers/postgres/setup/01-prepare_database.sh
new file mode 100755
index 00000000..fc5a5c5e
--- /dev/null
+++ b/containers/postgres/setup/01-prepare_database.sh
@@ -0,0 +1,21 @@
+#!/usr/bin/env bash
+
+set -e
+
+PSQL_CMD="psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER""
+
+# PostgreSQL databases
+
+for dbname in "${PSQL_DB}" "${PSQL_DBF}"; do
+   echo "SELECT 'CREATE DATABASE "${dbname}" 
+        WITH OWNER "${PSQL_USER}"
+        ENCODING "UTF8"' WHERE NOT EXISTS (
+      SELECT FROM pg_database WHERE datname = '"${dbname}"')\gexec" | ${PSQL_CMD}
+
+done
+
+# PostgreSQL roles
+
+for dbusers in "dengueadmin" "infodenguedev"; do
+    echo "ALTER ROLE "${dbusers}" WITH PASSWORD '"${PSQL_PASSWORD}"';" | ${PSQL_CMD}
+done
diff --git a/containers/postgres/setup/02-create_dengue_db.sh b/containers/postgres/setup/02-create_dengue_db.sh
new file mode 100755
index 00000000..df7b6a77
--- /dev/null
+++ b/containers/postgres/setup/02-create_dengue_db.sh
@@ -0,0 +1,16 @@
+#!/usr/bin/env bash
+
+set -e
+
+PSQL_CMD="psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER""
+
+if [[ "${ENV}" = "prod" ]]; then
+    echo "[II] restore database from dump to production..."
+    gunzip -c /dumps/latest_dengue.sql.gz | ${PSQL_CMD} -d ${PSQL_DB}
+    gunzip -c /dumps/latest_infodengue.sql.gz | ${PSQL_CMD} -d ${PSQL_DBF}
+else
+    echo "[II] running script with schemas for demo database."
+    psql -d ${PSQL_DB} < /schemas/schemas_dengue.sql
+    echo "[II] giving access to the dev user."
+    ./schemas/grant-user-readonly.sh
+fi
diff --git a/containers/web/Dockerfile b/containers/web/Dockerfile
index 3dab27e0..2bcd57e7 100644
--- a/containers/web/Dockerfile
+++ b/containers/web/Dockerfile
@@ -57,7 +57,7 @@ USER deploy
 # Copy code to the container with permissions to user
 COPY --chown=deploy:deploy AlertaDengue  /opt/services/AlertaDengue
 COPY --chown=deploy:deploy .env.tpl pyproject.toml README.md /opt/services/
-COPY --chown=deploy:deploy conda/dev.yaml /tmp/environment.yaml
+COPY --chown=deploy:deploy conda/"${ENV}".yaml /tmp/environment.yaml
 COPY --chown=deploy:deploy containers/celery/run.sh  /opt/services/celery-run.sh
 COPY --chown=deploy:deploy containers/entrypoint.sh  /entrypoint.sh
 
@@ -67,11 +67,11 @@ SHELL ["/bin/bash", "-c"]
 # Use environment to update the env alertadengue
 RUN mamba env create -n $ENV_NAME \
     --file /tmp/environment.yaml \
-  && conda clean --all \
+  && mamba clean --all \
   && find /opt/conda/ -type f,l -name '*.a' -delete \
   && find /opt/conda/ -type f,l -name '*.pyc' -delete \
-  && find /opt/conda/ -type f,l -name '*.js.map' -delete \
-  && rm -rf /opt/conda/pkgs /tmp/*
+  && find /opt/conda/ -type f,l -name '*.js.map' -delete
+  # && rm -rf /home/deploy/.conda/pkgs
 
 COPY --chown=deploy:deploy containers/scripts/poetry-install.sh /tmp/poetry-install.sh
 


Test patch:
diff --git a/containers/postgres/dev_dumps/latest_infodengue.sql.gz b/containers/postgres/dumps/latest_infodengue.sql.gz
similarity index 100%
rename from containers/postgres/dev_dumps/latest_infodengue.sql.gz
rename to containers/postgres/dumps/latest_infodengue.sql.gz