18. Database Implementation Details¶
18.1. Changes for OpenDSA-DevStack¶
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¶
- 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
- 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
Implement the postgres changes
Start OpenDSA with docker
- Once running exec into opendsa-lti and to the following commands
rake db:drop
rake db:create
rake db:schema:load
Now copy the postgres_format.sql script and you MySQL dump to docker using the Put file in docker command below
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
18.4. Helpful Links¶
https://www.postgresql.org/about/