2.3. Database initialization

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

Note

By clicking the link you can learn how to Install pgAdmin

2.3.1. Process

In pgAdmin4

• Connect to the database server

• 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 :

• Right click the qgep_prod database

  • Click Restore

  • Load your download of qgep_vx.y.z_structure_and_demo_data.backup

  

  • Click the Restore Options Tab and check these options

  

  • Click Restore

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

Note

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

• Right click the database and click Refresh

• Propably you want to rename the database: Right click the database, click Properties… and rename the database.

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

Note

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. Empty data model

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.

Note

If you run the sql in a SQL query 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
  

Note

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

You can also generate the data model under Linux.

• Download the data model:

  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.

• Create the database:

  psql -U postgres -c 'CREATE DATABASE qgep;'
  

• Run the generation script:

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

You can use the -r option to add roles (qgep_viewer, qgep_user, qgep_manager, qgep_sysadmin).

• Viewer: Can consult tables and views.

• User: Can edit data.

• Manager: Can edit data and value lists.

• Admin: Database administrator.