Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Toggle Dark/Light/Auto mode Back to homepage

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.

Git Branches

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:


docker-compose.yamlCompose Deployment File
config/main.envExample config file for deployment
dockerfileDockerfile used to build production docker image
openais/processing/integrated-database:latestProduction docker image built and hosted on Gitlab


docker-compose-staging.yamlCompose Deployment File for dev work
config/stagin.envExample config file for dev deployment
dockerfile-stagingDockerfile used to build dev docker image with limited schema
openais/processing/integrated-database:stagingDev 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.

Running this locally without all the extra bits

Test it out via the following steps.

  • Clone project and switch to the directory: git clone and cd integrated-database
  • Move (and/or edit) the example config file ./config/dev.env to the project base dir: cp ./config/sample2.env .env
  • Start-up: docker-compose build docker-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?

Upgrading Existing Databases

How is this done?


How is this done? I know PostGres docker images generally have a backup tool.