Schema Migration Process

Add columns to sdm_schemas

First, add the requested database additions, justifications, and where they are generated to our confluence entry table. Then, create a ticket and edit the repository to apply your schema changes to any of the cdb_*.yml schemas.

If your sdm_schemas PR has issues, check that the schema conforms to Felis’s data model and valid SQL tables can be created with felis validate/create Alembic migrations should be automatically created by a git workflow after your sdm_schemas pull request completes.

Create an Alembic Migration (manually)

Alembic keeps track of versioning by autogenerated migrations to sync the test stands and summit databases. Versioning our database schema changes allows us to apply edits and move the database’s state forward or backward as needed.

  1. Create an Alembic migration on your ConsDB ticket branch.

  2. Use the script consdb/alembic-autogenerate.py to generate Alembic migrations.

  3. Follow the directions in the header of the script, then run python alembic-autogenerate.py to create version files in respective database-named directories in consdb/alembic/.

  4. Manually edit the generated files in consdb/alembic/<table-name>/ to:

  • Remove the visit1 and ccdvisit1 views.

  • Ensure constraints and renamed columns are correct.

Test alembic migration

Before merging your ConsDB migration PR or applying this migration to the Summit, you must test applying the migration in a test environment. Test both applying the migration and any code that populates the new columns/tables at TTS/BTS if Summit schema is changing.

1. Update the deployment on the test stand:

  1. Choose the appropriate test stand (TTS, BTS)

  2. Create a branch in phalanx and edit the corresponding test stand environment file phalanx/applications/consdb/values-<test stand>.yaml to point to your branch’s built docker image (tickets-DM-###).

  3. Coordinate and announce in the appropriate slack channel that you will begin testing your migrations.

  4. Update the ConsDB deployment in <url.to.teststand>/argo-cd to use your phalanx branch in the Target Revision. Refresh and check pod logs.

  5. Verify the tables that you will be upgrading exist using psql

  6. From the consdb/ directory, (where alembic.ini file is) use the alembic commands to upgrade the existing database tables: alembic upgrade head -n <database name>

  7. Deploy new ConsDB software (hinfo, pqserver) and check the initial logs.

2. Test with LATISS imaging in ATQueue:

See TTS Start Guide for guidelines on using the test stands.

Access LOVE via <url.to.teststand>/love and use the 1Password admin information to sign in, or your SLAC username and password. Navigate to the ATQueue or Auxillary Telescope (AuxTel) Script Queue.

  • Before editing these scripts, note their starting configurations, as we will return the scripts to this configuration when we are done.

Take a test/simulated picture with LATISS through the ATQueue using these three scripts:

  1. set_summary_state.py Change the configuration to set ATHeaderService and ATCamera to ENABLED.

  2. enable_latiss.py Remove any existing configuration.

  3. take_image_latiss.py Update the configuration to remove anything that is not ‘nimages’ (1) and ‘image_type’ (BIAS or DARK or FLAT)

Once you have put these three scripts in the queue, click run. Watch for errors in both the Script Queue and the Argo-CD ConsDB pod logs and hinfo-latiss deployment. Address any errors and retest.

Check the database by using psql commands like \dt to display the table names and maybe even SELECT * from cdb_latiss.exposure where day_obs == <YYYYMMDD>; to view the most recent data.

Run set_summary_state to set ATHeaderService and ATCamera back to STANDBY, and return LATISS back to STANDBY. Then return these three scripts to their original configurations.

If you have encountered errors in this process, do not proceed to the summit, but address those errors and retest them with your phalanx branch pointing to your ConsDB branch with the fix to these errors.

If tests are successful, create a pull request for the Alembic migration in ConsDB. Tag the release according to standards-practices guidelines. Update your existing phalanx branch to point the environment based deployments to this ConsDB tag.

You are able to retest on the test stand at this point, hopefully there were no changes to your ConsDB pull request so this step is trivial.

Deploy migration in synchrony at Summit (if necessary), USDF, and Prompt Release (if necessary)

See deployment page for specific environment deployment steps