Two ways to migrate¶

Every CyVerse schema is versioned as a directory of migrations applied with golang-migrate.¹ You will use one of two paths:

Path	When
Ansible tags	Normal deployments and upgrades — the playbooks migrate every DE database in one pass
`migrate` by hand	Bootstrapping a single database, or debugging a failed migration

With Ansible¶

# create the databases
ansible-playbook -i /path/to/inventory --tags setup-databases   kubernetes.yml

# apply outstanding migrations
ansible-playbook -i /path/to/inventory --tags update-databases  kubernetes.yml

Run update-databases after every service upgrade. It is idempotent: with nothing outstanding it reports no changes.

By hand¶

Install migrate once. On Debian and Ubuntu:

curl -s https://packagecloud.io/install/repositories/golang-migrate/migrate/script.deb.sh | sudo bash
apt-get update
apt-get install -y migrate
migrate -help

Then, from a checkout of the repository that owns the schema:

migrate -database "postgres://<USER>:<GENERATED_SECRET>@<DATABASE_HOST>/<DBNAME>?sslmode=disable" \
        -path migrations up

sslmode=disable and credentials on the command line

Both appear in existing CyVerse notes and both are compromises. Prefer sslmode=require where the server supports it, and put the URL in an environment variable rather than in the command, so the password stays out of shell history and process listings.

Where the migrations live¶

Database	Repository
`de`, `notifications`, `metadata`	de-database
`qms`	QMS (`prod` branch)
`portal`	portal2
`unleash`	Applied by Unleash itself at startup
`keycloak`	Applied by Keycloak itself at startup
`grouper`	Applied by the Grouper installer

Shared extensions¶

Most CyVerse databases need the same extensions, created as a superuser in the target database before migrating:

\c <DBNAME>
create extension "uuid-ossp";
create extension "moddatetime";
create extension "btree_gist";

The QMS database also needs insert_username. A migration that fails on a missing function almost always means an extension was not created first.

Troubleshooting¶

Migrations reported as applied but the schema is wrong. Check standard_conforming_strings; when it is on, some DE migrations are skipped. See troubleshooting.

A migration failed halfway. migrate records a dirty version. Inspect schema_migrations, fix the cause, then force the version back to the last good one before re-running — do not delete the table.

golang-migrate ↩