This repository contains the migration files for the metadata database used by the CyVerse Discovery Environment.
The migrations use the golang-migrate/migrate system and are stored within
the migrations folder. They're written as pairs of simple SQL files, one each
for applying and reverting each migration.
Before migrations can be run, you need the following:
- PostgreSQL installed somewhere.
- A local installation of https://github.com/golang-migrate/migrate.
- The
metadatadatabase exists. - An account used to run the migrations, which must be the owner of the database and schema.
- The
uuid-osspextension must be installed.
If you are running migrations for an external site, make sure you install all of
the old conversions using the facepalm tool, available at
https://github.com/cyverse-de/facepalm.
The conversions are available in this repository in the old directory.
If the database contains the schema_migrations table prior to running the
migrate tool, then it was already modified with that tool at some point in the
past and it should be safe to perform the up migrations with it now. However,
if it's missing then you should make sure that the database is already at the
maximum version managed by the facepalm tool before running any migrations
with the migrate tool. The version table contains the versions applied by
the old facepalm tool and should contain 2.36.0:20210218.01 as the latest
version before running migrate.
The migrate command needs a connection URL when running migrations. It must be
in the format:
postgres://user:password@host:port/database-name?query-params
The password component of the URL is optional, so if you have a correctly
configured .pgpass file available then it can be omitted. Also, we haven't
implemented SSL for any of our PostgreSQL instances, so the sslmode parameter
should always be set to disable at this time. Here's an example of a database
URL that we might use in one of our deployments (with a bogus host name).
postgres://[email protected]/de?sslmode=disable.
These examples use the environment variable, DBURL, to indicate where the URL
should be specified on the command line.
Update the database to the latest version:
$ migrate -database "$DBURL" -path migrations up
Remove all database migrations, effectively clearing the database:
$ migrate -database "$DBURL" -path migrations down
Delete everything in the database schema (extensions are unaffected by this):
$ migrate -database "$DBURL" -path migrations drop
Remove the most recent migration from the database:
$ migrate -database "$DBURL" -path migrations down 1