stacomi_db

Scripts related with database development in stacomi.

Docker

This repository also serves to build docker images with an example database. These images are on this gitlab repository. Tags are given as follows:

• a database version ;
• a postgresql version ;
• a postgis version ;
• an optional dev tag. Any image with this tag is aimed to be used in a development context and should not be used in a production environment.

This the tag 0.6.0-pg9.6-ps3.4 means that the database version is 0.6.0, the postgresql version is 9.6 and the postgis version is 3.4.

The image is build on a postgis docker.

The arguments (ARGor when building the image --build-arg) postgresql_version and postgis_version allow to use the right version of docker postgis image.

How to use the image

• You should have docker installed in your machine (computer or server).
• You should pull the desired image using one of the following line depending if you want the latest version or a given version, eventually after being authenticate to the registry

docker pull registry.forgemia.inra.fr/stacomi/stacomi_db/stacomi_db:latest
docker pull registry.forgemia.inra.fr/stacomi/stacomi_db/stacomi_db:0.6.0-pg9.6-ps3.2

• Run a docker (example with latest version)

docker run --name postgres_stacomi --rm  -e POSTGRES_PASSWORD=yourpwd -d -p 5435:5432 registry.forgemia.inra.fr/stacomi/stacomi_db/stacomi_db:latest

postgres_stacomi can be replaced by any name (it's the docker container name on your machine) ; yourpwd should be changed to your custom password for the postgres user (refer to the docker documentation if you need more options but this one is mandatory) ; 5435 can be replaced by your custom port on your machine ; latest can be replaced by any valid version of our docker.

• your docker is now up. That means that you have a postgresql server running with postgis enabled and our sample database installed. You can use any postgresql client to access to it. Note that it can take few minutes to have the database fully setup (and thus the postgresql server really running and accepting connection).

For example you can use psql to connect to the database server :

psql -h localhost -p 5435 -U postgres

you can even use the psql from the docker (replace postgres_stacomi by your container name) :

docker exec postgres_stacomi psql --version

• it can also be used inside a gitlab CI/CD pipeline. We recommend to use the image as a service (note that POSTGRES_PASSWORD is required and any other postgres options can be defined under the variables). The hostname is defined by the alias that can be changed to what you want.

variables:
  POSTGRES_PASSWORD: yourpwd
  
services:
  - name:  registry.forgemia.inra.fr/stacomi/stacomi_db/stacomi_db:latest
    alias: db

The database can then be called inside your script.

test_psql:
  image: ubuntu
  stage: test_psql
  script:
    - apt update
    - apt install -y postgresql-client
    - psql --version
    - export PGPASSWORD=$POSTGRES_PASSWORD
    - psql -U postgres -h db  -c "SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';"
    - psql -U postgres -h db -c "select * from iav.t_dispositifcomptage_dic" bd_contmig_nat