Public | Automated Build

Last pushed: an hour ago
Short Description
PostgreSQL with PostGIS and temporal extensions, built on vrtsystems/baseimage.
Full Description

VRT Systems Docker PostgreSQL with spatiotemporal extensions

PostgreSQL with spatial and temporal goodness (PostGIS and temporal_tables extensions).

How to use this image

This image is based on the vrtsystems/postgres image so can be called in the same way:

    docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

...or

    docker run --name some-app --link some-postgres:postgres -d application-that-uses-postgres

..or with psql

    docker run -it --link some-postgres:postgres --rm postgres sh -c 'exec psql -h "$POSTGRES_PORT_5432_TCP_ADDR" -p "$POSTGRES_PORT_5432_TCP_PORT" -U postgres'

Environment Variables

The same base PostgreSQL environment variables apply: POSTGRES_USER, POSTGRES_PASSWORD etc. (See vrtsystems/postgres )

How to extend this image

This image can be extended in the same way the base vrtsystems/postgres image can. In particular:

Shell scripts in poststart-init.d

Creation of the PostGIS EXTENSION in postgres requires features not available in the postgres --single mode which is used in the prestart-init.d customisation feature of the postgres image. Consequently, the spatial and temporal extensions are activated in poststart-init.d with the file 10_create_extensions.sh.

If you are adding your own initialisation scripts, be aware that if they rely on the extensions existing, they need to be named such that they will execute after 10_create_extensions.sh. A good way to manage the sequencing of your init is to continue the numbered naming convention.

Loading initial data into versioned tables.

The temporal tables extension assumes (once activated on a table) that every edit is to be versioned and timestamped with the current system time. If you are deploying an application that requires a set of tables to be versioned against system time but the data relates to application time beginning before your data load, then you may wish to load this reference data with a system period starting at some earlier point.

To do this, use the poststart-init.d feature of the vrtsystems/postgres image on github, and:

  1. Create your tables and history tables as per the temporal_tables extension.
  2. Load your data, setting the system time period column with the time range of your choice
  3. Activate the tgemporal table trigger as per the temporal extension documentation.

You can sequence these activities with psql inside a single script, or by numbering your init scripts so they load in order. If you wish to have the start date for all reference data loaded set to a common application epoch start, you can use an environment variable. The following example shows the use of an INIT_EPOCH variable that can be overriden at container runtime, but defaults to the Unix timestamp epoch start as follows (and then set it when you run the container):

#!/bin/sh
: ${INIT_EPOCH:="'1970-01-01 00:00:00.000000-00'"}
psql -d $POSTGRES_DB -U $POSTGRES_USER<<- EOSQL
INSERT INTO tag_kind (name, sys_period, description, is_haystack) VALUES 
    ('Marker', tstzrange($INIT_EPOCH, NULL), 'Marker annotation', 't'),
    ('Bool', tstzrange($INIT_EPOCH, NULL), 'Boolean (True/False)', 't'),
    ('Number', tstzrange($INIT_EPOCH, NULL), 'Number (integer or floating point)', 't'),
    ('Str', tstzrange($INIT_EPOCH, NULL), 'Unicode character string', 't'),
    ('Uri', tstzrange($INIT_EPOCH, NULL), 'Unversial Resource Identifier', 't'),
    ('Ref', tstzrange($INIT_EPOCH, NULL), 'Reference to another entity', 't'),
    ('Bin', tstzrange($INIT_EPOCH, NULL), 'Binary blob', 't'),
    ('Date', tstzrange($INIT_EPOCH, NULL), 'Date (no time of day)', 't'),
    ('Time', tstzrange($INIT_EPOCH, NULL), 'Time of day (no date)', 't'),
    ('DateTime', tstzrange($INIT_EPOCH, NULL), 'A timestamp with timezone', 't'),
    ('Coord', tstzrange($INIT_EPOCH, NULL), 'Geographic coordinate (latitude/longitude)', 't'),
    ('Obj', tstzrange($INIT_EPOCH, NULL), 'Object', 't');
EOSQL
Docker Pull Command
Owner
vrtsystems
Source Repository

Comments (0)