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:

Download demo data * https://github.com/QGEP/datamodel/releases/latest * download qgep_vx.y.z_structure_and_demo_data.backup

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
- Click the Restore Options Tab and check these options
- 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

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).

Download the data model by going to https://github.com/QGEP/datamodel/releases/latest and by downloading the latest qgep_vx.y.z_structure_with_value_lists.sql.

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.