18. Database Implementation Details

18.1. Changes for OpenDSA-DevStack

  1. Changed the db service to be a PostgreSQL image and added a new dbmysql service for the MySQL database.

18.2. Changes for OpenDSA-LTI

  1. Dump the opendsa MySQL database using the MySQL command shown below.
    • The output file will have a bunch of locks and unlocks and postgres does not like this
    • That command will output a file directly to your machine so there is no need to worry about the volume running out of space
  2. Use my created python script mysql_to_psql.py to format the file so that postgres will read it.
    • The script is for python 3 and can be invoked by calling py mysql_to_psql.py MySQL_dump_filename.sql target.sql
  3. Implement the postgres changes
  4. Start OpenDSA with docker
  5. Once running exec into opendsa-lti and to the following commands
    • rake db:drop
    • rake db:create
    • rake db:schema:load
  6. Now copy the postgres_format.sql script and you MySQL dump to docker using the Put file in docker command below
  7. Exec into db and run the postgres_format.sql script first and then your MySQL dump file afterwards using the Run postgres file command below

Note: During my initial set up of postgres I ran into some issues with ruby not finding the pg gem. If this happens exec into opendsa-lti and force a gem bundle install.

MySQL dump command: docker-compose -f docker-compose.yml -f docker-dev.yml exec mysqldb mysqldump -h dbmysql -u root -p –no-create-info –complete-insert –compatible=postgresql opendsa > mysql_dump.sql

Start docker: docker-compose -f docker-compose.yml -f docker-dev.yml up (in a separate terminal)

Put file in docker: docker cp opendsa-ltiYOURFILENAME opendsa-devstack_db_1:/

Connect to db: docker-compose -f docker-compose.yml -f docker-dev.yml exec db bash

Run PostgreSQL file: psql opendsa opendsa -d opendsa -a -f YOURFILENAME

Reference commit to LTI: LTI-Commit.

Reference commit to DevStack: DevStack-Commit.

18.3. Database controls

  • To drop a table just run the drop TABLENAME command
    • Dropping a partitioned table will drop all its partitioned tables as well
  • To detach a partition but keep the table
    • ALTER TABLE TABLENAME DETACH PARTITION PARTIONEDTABLENAME
    • The partition table will still exist but no longer be included in the table partitioning
  • See Partitioning for any questions on partitioning