Integrated Database
The databased used by the OpenAIS system is a Postgres system with PostGIS and TimeScaleDB extensions enabled. This makes use of the TimescaleDB HA docker image, since it has the newer version of PostGIS , while extending it with custom scripts to automatically setup the database schema and initialise it with some extra datasets.
The initialising scripts are held in the ./build/db_init_scripts directory while extra data files, mostly AIS protocol definitions are held in ./build/db_init_data. These folders are mounted into a location where a new DB will run them upon first boot. This allows for the quick deployment of new databases for development purposes.
The database image is built, using a Gitlab Pipeline, and stored here. Check which tags are available and pull the desired one.
- AIS: Contains AIS position and voyage reports as well as AIS derived data
- Geo: Contains geographical tables representing maritime objects like ocean boundaries, port locations, grids to use in areas of interest
- TimescaleDB: There are multiple internal schemas generated by the TimescaleDB plugin. It’s best to leave these alone…
- PostgisFTW: This is the default schema that exposes functions and collections to the web, via a rest API managed by Pg_Featureserv.
The base schema is defined by a set of scripts in the ./build/db_init_data and can be extended by extra scripts in the ./build/db_init_scripts_prod directory. Postgres runs these scripts in alphabetical order so any additional scripts can be added and times by changing their filename to put them in the correct order during DB build. This includes several files types like .sql and .sh.
NOTE: The ordering of the schema files can be used for versioning of the database. It is recommended to not change the files already in the repository but instead to insert new files that change the schema, that way they can be applied to already running instances of the DB.
There are two git branches that are specified in the git pipeline: the default main branch and a staging branch. The intention here is that the default branch is used for production deployments with the associated config and docker files:
| File | Purpose |
|---|---|
| docker-compose.yaml | Compose Deployment File |
| config/main.env | Example config file for deployment |
| dockerfile | Dockerfile used to build production docker image |
| openais/processing/integrated-database:latest | Production docker image built and hosted on Gitlab |
| File | Purpose |
|---|---|
| docker-compose-staging.yaml | Compose Deployment File for dev work |
| config/stagin.env | Example config file for dev deployment |
| dockerfile-staging | Dockerfile used to build dev docker image with limited schema |
| openais/processing/integrated-database:staging | Dev docker image built and hosted on Gitlab |
The deployment of the AIS database, along with tools to support it, is described here or here. It is not expected to deploy from this repository for production purposes but it might be useful for local development.
Test it out via the following steps.
- Clone project and switch to the directory:
git clone https://gitlab.com/eosit/integrated-databaseandcd integrated-database - Move (and/or edit) the example config file
./config/dev.envto the project base dir:cp ./config/sample2.env .env - Start-up:
docker-compose builddocker-compose up -d - When complete, open “localhost:8080” in a browser to see the API in action.
The API used in the Docker-Compose file What API endpoints exist?
How is this done?
How is this done? I know PostGres docker images generally have a backup tool.