Setting Up a Database for Development

Owned by KevinPalis
Last updated: Feb 20, 2020
2 min read

 

This is for people who need to setup a GOBii database for either development or testing. This should not be used as a guideline for production deployment. 

If you haven't done so, and plan on contributing or testing the data warehouse, please read this primer on liquibase and guidelines on how we use it in GOBII: Liquibase

It is important the conventions are observed and the structure is maintained.

Step-by-step guide

Step 0. Get the latest sources from the GOBii Data Warehouse git repository. Use the master branch for the latest stable version or the development branch for the most up to date code.

As this data warehouse didn't start with Liquibase, the foundation schema is in raw SQL files. To keep things simple and more manageable, we decided to keep it that way. There are two main steps in building the latest database for development and testing: rawbase (to keep consistent with the name) and liquibase.

Follow the steps below and note that the current working directory is the git repository you just cloned in step 0.

New Database

  1. Build the foundation schema

    1. Create your empty database

      1. psql -U kpalis postgres create database your_database_name;

    2. Navigate to the rawbase directory, under builder and run build_gobii_pg.sql against your empty database

      1. cd gobii.db/builder/rawbase psql -U kpalis your_database_name < build_gobii_pg.sql

  2. Run Liquibase to migrate your foundation schema to the latest changes (or latest stable, in case you used the master branch)

    1. Modify the properties file accordingly (or create it if it doesn’t exist) in gobii.db/builder/liquibase/liquibase.properties. Basically, the only things you need to change are: url, username, password, and context (if you don't know what a liquibase context is, read the prerequisite document).

      driver: org.postgresql.Driver classpath: drivers/postgresql-9.4.1209.jar url: jdbc:postgresql://localhost:5432/your_dabase_name username: appuser password: appuser changeLogFile: changelogs/db.changelog-master.xml contexts: general,seed_general,seed_cbsu

      Make sure you change the URL to the correct name of the database you created in step 1.

    2. Run Liquibase against your foundation schema

Verification

You can verify the status of your database by checking these two tables exist: databasechangelog, databasechangeloglock.

You can read more about them on the Liquibase website. But basically, the databasechangelog will tell you what changesets ran to update your database. It also shows which points in the changelog you may be able to rollback to.

 

Existing Database

Existing databases don't need to run the rawbase scripts. Liquibase will know what changes are already in your database. So it is okay to run Liquibase over and over on an existing schema.

So to update an existing database, simply follow the steps 2a and 2b above.