db

db

Work with a MDS provider Postgres database.

Configuration

This container uses the following environment variables to connect to the MDS database:

POSTGRES_HOSTNAME=server
POSTGRES_DB=postgres
POSTGRES_USER=postgres
POSTGRES_PASSWORD=postgres_password

MDS_DB=mds_provider
MDS_USER=mds_provider
MDS_PASSWORD=mds_provider_password

Container commands

This container comes with a number of sub-commands for interacting with a database using the configured environment.

Initialize the database from scratch

docker-compose run db reset

docker-compose run db init

Functions

Setup postgres functions used in other views and calculations.

docker-compose run db functions

Migrations

Run a migration script with the given version number.

docker-compose run db migrate VERSION

Where VERSION is a version number like x.y.z.

Availability

Create the availability view and associated infrastructure.

Trips must be configured first.

Run the intialization scripts

docker-compose run db availability

Refresh the materialized views

docker-compose run db availability refresh

Deployments

Create the deployments views.

Run the intialization scripts

docker-compose run db deployments

Refresh the materialized view

From the current contents of the status_changes table.

docker-compose run db deployments refresh

Trips

Create additional trips and routes views.

Run the intialization scripts

docker-compose run db trips

Run the incremental processing job

docker-compose run db trips refresh

psql

Run psql commands in the container against $MDS_DB as $MDS_USER.

Interactive prompt

Enter into the psql prompt:

$ docker-compose run db psql

server is available
psql <version info>
Type "help" for help.

mds_provider=> help

You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

mds_provider=>

Query/command

Run a single query or command (e.g. the psql --command option):

$ docker-compose run db query "select distinct provider_name from trips;"

server is available
 provider_name
---------------
 bird
 Lime
 JUMP
 Lyft
(4 rows)

File

Execute queries or commands from a file (e.g. the psql --file option):

$ docker-compose run db file trips/daily.sql

server is available
psql:trips/daily.sql:1: NOTICE:  view "trips_daily" does not exist, skipping
DROP VIEW
CREATE VIEW