MapStore can use 3 types of database:
MapStore uses an H2 in-memory DB as the default DBMS to persist the data. This configuration is useful for development and test purposes, or to evaluate the project but it is obviously NOT RECOMMENDED for production usage; moreover the H2 DB cannot be used for the integration with GeoServer.
In the following guide you will learn how to configure MapStore to use an external database.
Database recommendations are reported in the Requirements page.
Externalize properties files
MapStore has a file called
geostore-datasource-ovr.properties. This file is on the repository in the folder
java/web/src/main/resources, in the final
mapstore.war package it will be copied into
WEB-INF/classes path. It contains the set-up for the database connection. Anyway if you edit the file in
WEB-INF/classes this file will be overridden on the next re-deploy. To preserve your configuration on every deploy you can use an environment variable,
geostore-ovr, to configure the path to an override file in a different, external directory. In this file the user can re-define the default configuration and so set-up the database configuration.
For instance using tomcat on linux you will have to do something like this to add the environment variable to the JAVA_OPTS
where to add your JAVA_OPTS depends on your operating system. For instance the file could be
/etc/default/tomcat8, or similar, in linux debian
# here the path to the ovr file GEOSTORE_OVR_FILE=file:///var/lib/tomcat/conf/geostore-ovr.properties # add the env. variable 'geostore-ovr' to JAVA_OPTS JAVA_OPTS="-Dgeostore-ovr=$GEOSTORE_OVR_FILE [other opts]"
So your file
/var/lib/tomcat/conf/geostore-ovr.properties will contain the overrides to the database set-up.
Database creation Mode
By default MapStore automatically populates the database on it's own. If you want to disable this functionality (e.g. if you don't want to allow the database user to have permission to create tables) then you have to set-up the following property in the ovr file to 'validate'
validate: validate the schema, makes no changes to the database.
update: update the schema.
create: creates the schema, destroying previous data.
create-drop: drop the schema when the SessionFactory is closed explicitly, typically when the > application is stopped.
In this case it is necessary to manually create the required tables using the scripts available here for the needed DBMS.
update mode is usually discouraged in production. On production servers you should always use
validate mode and apply SQL scripts and/or patches manually. Anyway before every update a database backup is strongly suggested.
If you download or build mapStore.war, it's default configuration will be this one:
This configuration creates a file called
geostore in the webapp folder. You can change the
geostoreDataSource.url to set the path to the database you want to use. Make you sure that the user of the project that executes Tomcat has write permissions on the folder where you want to create the database.
All the following configurations will use
geostore as password of the user
geostore. Of course you can change it according to your needings.
Database Creation and Setup
To use postgreSQL DBMS as MapStore you have to create the "geostore" DB.
- Log in as user postgres
- Create the geostore DB:
Create users and schemas:
psql geostore < 001_setup_db.sql
Here below the required part of the file
001_setup_db.sql, available here (creation of test user and schema for
geostore_test in the original file is not strictly required for MapStore)
Write the password you prefer instead of 'geostore'
-- CREATE SCHEMA geostore (set the password you prefer) CREATE user geostore LOGIN PASSWORD 'geostore' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; CREATE SCHEMA geostore; GRANT USAGE ON SCHEMA geostore TO geostore ; GRANT ALL ON SCHEMA geostore TO geostore ; alter user geostore set search_path to geostore , public;
If you need to create the database schema manually (validate mode), you have also this script.
At the end, make you sure that the user
geostore has access to the database from the address of MapStore application. You can give permission by editing pg_hba.conf
Connection to the Database
To configure MapStore to connect it to the new created database you have to edit your override file like below (change the connection parameters accordingly):
# Setup driver and dialect for PostgreSQL database geostoreDataSource.driverClassName=org.postgresql.Driver geostoreVendorAdapter.databasePlatform=org.hibernate.dialect.PostgreSQLDialect # Connection parameters geostoreDataSource.url=jdbc:postgresql://localhost:5432/geostore geostoreDataSource.username=geostore geostoreDataSource.password=geostore geostoreEntityManagerFactory.jpaPropertyMap[hibernate.default_schema]=geostore # Automatic create-update database mode geostoreEntityManagerFactory.jpaPropertyMap[hibernate.hbm2ddl.auto]=update # Other options geostoreVendorAdapter.generateDdl=true geostoreVendorAdapter.showSql=false
Migrate an existing H2 database to PostgreSQL
If you used an H2 database during development, and you want to deploy the application in production, migrating the database to PostgreSQL is not that easy.
For this reason we have created a specific tool for this task, called H2ToPgSQLExport that is part of the GeoStore CLI.
More information on the migration tool is available in the GeoStore CLI documentation page.
Database Creation and Setup
Create a database geostore, a schema called GEOSTORE and a user
geostore that has write access to them.
Use this SQL script to create the DB schema.
Connection to the Database
To configure MapStore to connect to the new created database you have to edit your override file like reported below:
# Setup driver and dialect for Oracle Database geostoreDataSource.driverClassName=oracle.jdbc.OracleDriver geostoreVendorAdapter.databasePlatform=org.hibernate.dialect.Oracle10gDialect # Connection parameters geostoreDataSource.url=jdbc:oracle:thin:@localhost:1521/ORCL geostoreEntityManagerFactory.jpaPropertyMap[hibernate.default_schema]=GEOSTORE geostoreDataSource.username=geostore geostoreDataSource.password=geostore # Automatic create-update database mode geostoreEntityManagerFactory.jpaPropertyMap[hibernate.hbm2ddl.auto]= # Other options geostoreVendorAdapter.generateDdl=true geostoreVendorAdapter.showSql=false