schema-nwis-ws-star

Liquibase scripts for creating the NWIS_WS_STAR database schema objects in a Postgres database. They are used for the Water Quality Portal (WQP).

Docker

Also included are Docker Compose scripts to create PostgreSQL and Liquibase containers for testing the scripts.

Docker Network

A named Docker Network is required for local running of the containers. Creating this network allows you to run all of the WQP locally in individual containers without having to maintain a massive Docker Compose script encompassing all of the required pieces. (It is also possible to run portions of the system locally against remote services.) The name of this network is provided by the LOCAL_NETWORK_NAME environment variable. The following is a sample command for creating your own local network. In this example the name is wqp and the ip addresses will be 172.25.0.x

docker network create --subnet=172.25.0.0/16 wqp

Environment variables

In order to use the docker compose scripts, you will need to create a .env file in the project directory containing the following (shown are example values):

POSTGRES_PASSWORD=<changeMe>

NWIS_DATABASE_ADDRESS=<nwis_database_host>
NWIS_DATABASE_NAME=<nwis_db>
NWIS_DB_OWNER_USERNAME=<wqp_core>
NWIS_DB_OWNER_PASSWORD=<changeMe>

NWIS_SCHEMA_NAME=<nwis>
NWIS_SCHEMA_OWNER_USERNAME=<nwis_owner>
NWIS_SCHEMA_OWNER_PASSWORD=<changeMe>

WQP_SCHEMA_NAME=<wqp>
WQP_SCHEMA_OWNER_USERNAME=<wqp_core>
WQP_SCHEMA_OWNER_PASSWORD=<changeMe>

OBSERVATION_SCHEMA_NAME=<obs>

WDFN_DB_READ_ONLY_USERNAME=<wdfn>
WDFN_DB_READ_ONLY_PASSWORD=<changeMe>

CONTEXTS=<skipWQP>

LOCAL_NETWORK_NAME=<wqp>

DB_IPV4=<172.25.0.2>
DB_PORT=<5434>
LIQUIBASE_IPV4=<172.25.0.11>

LIQUIBASE_VERSION=<3.8.5>
JDBC_JAR=<postgresql-42.2.5.jar>

Environment variable definitions

• POSTGRES_PASSWORD - Password for the postgres user.

• NWIS_DATABASE_ADDRESS - Host name or IP address of the PostgreSQL database.

• NWIS_DATABASE_NAME - Name of the PostgreSQL database to create for containing the schema.

• NWIS_DB_OWNER_USERNAME - Role which will own the database.

• NWIS_DB_OWNER_PASSWORD - Password for the NWIS_SCHEMA_OWNER_USERNAME role.

• NWIS_SCHEMA_NAME - Name of the schema to create for holding database objects.

• NWIS_SCHEMA_OWNER_USERNAME - Role will own the database objects.

• NWIS_SCHEMA_OWNER_PASSWORD - Password for the NWIS_SCHEMA_OWNER_USERNAME role.

• WQP_SCHEMA_NAME - Name of the schema for the WQP objects.

• WQP_SCHEMA_OWNER_USERNAME - Role which owns the WQP database objects.

• WQP_SCHEMA_OWNER_PASSWORD - Password for the WQP_SCHEMA_OWNER_USERNAME role.

• OBSERVATION_SCHEMA_NAME - Name of the schema to create for holding WDFN specific objects.

• WDFN_DB_READ_ONLY_USERNAME - Role which has limited access to WDFN objects.

• WDFN_DB_READ_ONLY_PASSWORD - Password for the WDFN_DB_READ_ONLY_PASSWORD role.

• CONTEXTS - Either empty to run all scripts or skipWQP to not run scripts for objects in other schema.

• LOCAL_NETWORK_NAME - The name of the local Docker Network you have created for using these images/containers.

• DB_IPV4 - The IP address in your Docker Network you would like assigned to the database container used for testing the Liquibase scripts.

• DB_PORT - The localhost port on which to expose the script testing database container.

• LIQUIBASE_IPV4 - The IP address you would like assigned to the Liquibase runner container.

• LIQUIBASE_VERSION - The version of Liquibase to install.

• JDBC_JAR - The jdbc driver to install.

Testing Liquibase scripts

The Liquibase scripts can be tested locally by spinning up the generic database (db) and the liquibase container.

% docker-compose up -d db
% docker-compose up liquibase

The local file system is mounted into the liquibase container. This allows you to change the liquibase and shell scripts and run the changes by just re-launching the liquibase container. Note that all standard Liquibase caveats apply.

The PostGIS database will be available on your localhost's port $DB_PORT, allowing for visual inspection of the results.

Other Helpful commands include:

• docker-compose up to create and start the containers
• docker-compose ps to list the containers
• docker-compose stop or docker-compose kill to stop the containers
• docker-compose start to start the containers
• docker-compose rm to remove all containers
• docker network ls to get a list of local docker network names
• docker network inspect XXX to get the ip addresses of the running containers
• docker-compose ps -q to get the Docker Compose container ids
• docker ps -a to list all the Docker containers
• docker rm to remove a container
• docker rmi to remove an image
• docker logs to view the Docker Compose logs in a container