2.3. Inicialización de la base de datos

You can use pgAdmin to access and manage the database server.

Nota

Haciendo clic sobre el enlace podrás saber cómo instalar-pgAdmin

2.3.1. Proceso

In pgAdmin4

  • Conectar al servidor de la base de datos

  • Create a new database with UTF8 encoding (e.g. qgep_prod).

2.3.1.1. Restore demo datamodel

Restore the latest data model that also includes demo data:

Back in pgAdmin :

  • Clic derecho sobre la base de datos qgep_prod

    • Clic Restablecer

    • Carga tu descarga de qgep_vx.y.z_structure_and_demo_data.backup

    ../_images/demodata-restore.jpg
    • Click the Restore Options Tab and check these options

    ../_images/demodata-restore_options.jpg
    • Clic Restablecer

    • Check whether in Message window Restoring backup on the server is successfully completed.

Nota

If the Restore is failed and the detail reads something like «pg_restore: [archiver] unsupported version (1.13) in file header» or in German «pg_restore: [Archivierer] nicht unterstützte Version (1.13) im Dateikopf» try updating your PostgreSQL, see https://stackoverflow.com/questions/49064209/getting-archiver-unsupported-version-1-13-in-file-header-when-running-pg-r

  • Close the Restoring-Window

  • Clic derecho sobre la base de datos y luego Actualizar

../_images/demodata-refresh.jpg
  • Probablemente desee renombrar la base de datos: clic derecho sobre la base de datos, clic en Propiedades… y renómbrela.

There are now 7 schemas in the database (public, qgep_import, qgep_network, qgep_swmm, qgep_od, qgep_sys, qgep_vl)

2.3.1.2. Create minimal roles and access

Nota

The QGEP roles are defined in the https://github.com/QGEP/datamodel/blob/master/12_0_roles.sql (per cluster) and https://github.com/QGEP/datamodel/blob/master/12_1_roles.sql (per database) files. It is recommended to use these when using QGEP in a production environment.

Copy paste and run the two .sql one after the other in the query editor of pgAdmin4 (Tools > Query Tool).

2.3.1.3. Vaciar modelos de datos

You also have the option to restore the latest empty data model (no demo data).

Nota

If you run the sql in a Consulta SQL Window, you will get an error. You have to use a BAT-File.

  • Use a BAT-File like that, to create the database, the extensions and the schemas with valuelist (replace x.y.z with your version):

    @echo off
    
    set filename="qgep_vx.y.z_structure_with_value_lists.sql"
    
    if not exist %filename% (
       echo "File %filename% does not exist. Please download the latest datamodel from https://github.com/QGEP/datamodel/releases (structure_with_value_lists.sql) and adjust filename in this batch file."
       PAUSE
       exit -1
    )
    
    set /p db="Please enter the database name? (e.g. qgep_community) "
    set /p password="Please enter the password for user postgres? "
    
    set port=5432
    set PATH=%PATH%;C:\Program Files\PostgreSQL\12\bin
    set PGPASSWORD=%password%
    
    createdb -U postgres -p %port% %db%
    
    psql -U postgres -h localhost -p %port% -d %db% -f %filename%
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA public TO ""qgep"""
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA qgep_vl TO ""qgep"""
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA qgep_od TO ""qgep"""
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA qgep_import TO ""qgep"""
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA qgep_swmm TO ""qgep"""
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON SCHEMA qgep_sys TO ""qgep"""
    
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA qgep_od TO ""qgep""";
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA qgep_swmm TO ""qgep""";
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA qgep_import TO ""qgep""";
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT ON ALL TABLES IN SCHEMA qgep_vl TO ""qgep""";
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT ON ALL TABLES IN SCHEMA qgep_sys TO ""qgep""";
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO ""qgep"""
    
    psql -U postgres -h localhost -p %port% -d %db% -c "GRANT USAGE ON ALL SEQUENCES IN SCHEMA qgep_od TO ""qgep"""
    
    psql -U postgres -h localhost -p %port% -d %db% -c "REFRESH MATERIALIZED VIEW qgep_od.vw_network_node"
    psql -U postgres -h localhost -p %port% -d %db% -c "REFRESH MATERIALIZED VIEW qgep_od.vw_network_segment"
    
    PAUSE
    

Nota

You are free to choose any database name.

  • Update privileges for the qgep_od, qgep_sys, qgep_vl, qgep_network, qgep_import, qgep_swmm schema as described in the chapter Create minimal roles and access.

2.3.1.4. Generate the data model under Linux

También puedes generar el modelo de datos bajo Linux.

  • Descargar el modelo de datos:

    git clone https://github.com/QGEP/datamodel
    cd datamodel
    
  • Setup the pg_service.conf file and add the pg_qgep service as in the Linux/macOS pg_service section.

  • Crear la base de datos:

    psql -U postgres -c 'CREATE DATABASE qgep;'
    
  • Ejecutar el comando de generación:

    ./scripts/db_setup.sh
    

If you want to use a different SRID you need to use the -s option. For instance, run ./scripts/db_setup.sh -s 2056 for the 2056 SRID.

If you already have a data model and you want to force the regeneration of the model you can also use the -f option: ./scripts/db_setup.sh -f.

Puedes usar la opción -r para agregar roles (qgep_viewer, qgep_user, qgep_manager, qgep_sysadmin).

  • Visualizador: Puede consultar tablas y vistas.

  • Usuario: Puede modificar datos.

  • Administrador: Puede modificar datos y alistar valores.

  • Admin: Database administrator.