Postgres database setup

PostgreSQL is one of the databases Union VMS does support. Union VMS use geographical information that identifies the geographic location of features and boundaries on Earth. To support the geospatial data you have to install PostGIS, an extender for PostgresSQL to support geographical objects.

Setup PostgreSQL

1. Download PostgreSQL from here: https://www.postgresql.org/download/windows/
2. Install PostgresSQL by following the installation wizard. Make sure that Stack builder is launched when the installation is completed.
3. Select PostgresSQL 9.5 and press next.
4. Select Categories à Database Server and select PostgreSQL, see the picture below
   
   
5. Scroll down to Spatial Extensions part, select PostGIS Bundle for PostgreSQL, see picture below. Press next.
   
6. Select Download path for the packages and click next and follow the remaining installation instructions.
7. When PostgresSQL setup is shown click next several times so that the PostgreSQL installation phase starts.
   
8. The PostgresSQL installation is completed when following printout is presented. Select the checkbox and click on the finish button to close the PostgreSQL setup and continue with the Stack Builder installation.
   
9. The PostGIS installation setup starts.
   
10. Select “Create spatial database and click next.
    
11. Click next on installation location.
    
12. Enter the admin password where you have set during the installation in step 2. If you encounter a spatial database installation error similar to the below, please try installing again from step 2:
    “createdb: could not connect to database postgres: FATAL: password authentication failed for user "postgres"”
    
13. Click on install.
14. Click on Yes if following popup windows occurs.
    
15. Click on Yes if following popup windows occurs.
    
16. Click on Yes if following popup windows occurs.
    
17. Click on close to finish PostGIS installation.
18. Click on the abort button in the stack builder setup and reboot the computer.
    
    
    

Configure PostgresSQL with PostGIS extensions

1. Startup pgAdmin III application.
2. Double click on the PostgreSQL 9.5 (localhost:5432) and enter the admin password. It is the one you set during installation of PostgresSQL in the chapter before. See picture below.
   
3. Right click on Databases and create a new database with following credentials.
             Name: uvms
             Owner: postgres
4. Expand the uvms database and right click on Extensions and select New Extensions. In the popup window select postgis from the dropdown list, then click OK. Repeat this step to add extension postgis_topology. When you are done you will see those two new extensions you have added under Extensions as the picture below.
   
5. Add a new Owner/Login Role by right click on Login Roles. Enter role name asset
   
   
   
6. Click on the Definition tab and enter password asset  in the password fields and click OK.
   
7. Please repeat steps 5 and 6 for following Login Roles:

8. Add a new schema by right click on Schemas. In the popup window fill in the name and select an owner, see the table below.