Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Command to migrate MariaDB database #61

Merged
merged 54 commits into from
Sep 4, 2024
Merged

Command to migrate MariaDB database #61

merged 54 commits into from
Sep 4, 2024

Conversation

amyfromandi
Copy link
Collaborator

@amyfromandi amyfromandi commented Jul 17, 2024 •
edited
Loading

This branch contains migration code that enables a complete MariaDB to PostgreSQL database migration, as detailed in #60. The script in this pull request should make several key data evolutions:

  • Migrate the macrostrat MariaDB database to PostgreSQL, retaining all database tables, rows, etc.
  • Merge the resulting database (macrostrat_two, currently) into Macrostrat's PostgreSQL database/macrostrat schema, overwriting tables if needed
  • Feature/move fixtures to migrations #73: Run PostgreSQL migrations to ensure all constraints/sequences/foreign keys are defined
  • Add foreign keys from maps to macrostrat #80: Regenerate foreign keys from other schemas (e.g., maps) as needed. This has been moved to future work, based on some complexities identified by @mwestphall.

A successful operation of this script will result in full integration of all data from MariaDB, allowing the Macrostrat API to operate off of a single PostgreSQL database (UW-Macrostrat/macrostrat-api#229)

Architecturally, this should be implemented within the macrostrat command-line application:

  • Changes to MariaDB migration to integrate with Macrostrat CLI #74:
    • Implement as a macrostrat subcommand, e.g., macrostrat migrate-mariadb (we can figure out the "right" name later)
    • Use database credentials, etc. from macrostrat.toml rather than replicating them elsewhere
    • Use the macrostrat.database module for running SQL commands
    • As much as possible, we should use Docker for command-line processing
  • Once this is all validated, we can remove the macrostrat v1 schlep scripts that were the initial version of this process

Latest update:

  • restored maria-migrate branch
  • added .gitignore file
  • refactored compare_data_counts() to compare any two databases passed through as parameters.

Next steps:

  • Resolve all discrepancies between the PostgreSQL macrostrat_two database and the development/production macrostrat PostgreSQL database.

@amyfromandi amyfromandi requested a review from davenquinn July 17, 2024 20:54
@davenquinn davenquinn mentioned this pull request Jul 17, 2024
Closed
@davenquinn
Copy link
Member

For data validation summary with these scripts (see #60), can we print row counts, etc. as tables to improve readability (maybe using the rich Python library)?

@davenquinn davenquinn changed the title restoring maria-migrate branch Command to migrate MariaDB database Jul 17, 2024
@davenquinn davenquinn mentioned this pull request Jul 17, 2024
Open
amyfromandi and others added 23 commits July 22, 2024 13:46
@amyfromandi
Refactored some code
871128b
@amyfromandi
updated utils.py
66c161a
@amyfromandi
added liths prescript query
b2700bd
@davenquinn
Merge branch 'main' into maria-migrate
ce87076 
* main: (158 commits)
  Got rid of unnecessary spacing changes
  Centralized GRANTs in a single post-update hook
  New subsystem and hook function that updates permissions after all schema updates
  Removed some old commands that may not be useful anymore
  Updated JetBrains files
  Update and include new tables
  Update and include new tables
  Started upgrading macrostrat migration scripts
  Updated Macrostrat subsystem definitions
  Moved 'column-builder' views into new subsystem architecture
  Started re-working v2-transition code into various places
  Move sql alteration functions to database-upgrades directory
  Remove dependencies on local packages in pyproject.toml
  Update README.md
  Storage scheme migration
  Updated storage migrations
  Updated maps migrations
  Create some explicit migrations
  Remove unused function
  Improved organization of paleogeography sub-app
  ...
@davenquinn
Moved entire mariadb_migration system to subdirectory
b4da967
@davenquinn
Incorporated legacy migration command into new directory
28c062a
@davenquinn
Created basic command to run MariaDB CLI
00c1a02
@davenquinn
Remove need to connect with --net=host
1f234f9
@davenquinn
Add command that restores a database onto the MariaDB server
7f9193c
@davenquinn
Removed overly complex logging approach
93c7fb6
@amyfromandi
find_row_variances() compares maridb data to macrostrat data to ensur…
225e016 
…e all data exists before migration in dev
@davenquinn
Refactor - move stream reader to a utils file
12f5509
@davenquinn
Fix reference to decoder
76b1b2b
@davenquinn
Rearrange CLI utils
4c58f5e
@davenquinn
Moved pre- and post- scripts to external SQL files
8d7eccd
@davenquinn
Removed extra files created by IntelliJ
9b8ba34
@davenquinn
Set up namespaced packages correctly in intellij
6245e56
@davenquinn
Fix a bit more sql
8161b71
@amyfromandi
added find_col_variance() method
985bab4
@amyfromandi
Merge branch 'maria-migrate' of https://github.com/UW-Macrostrat/macr…
884814b 
…ostrat into maria-migrae
@davenquinn
Move change assessment methods to separate file
acf35b9
@davenquinn
Updated MariaDB dump/restore functions
aa4c84d
@davenquinn
PostgreSQL migration now mostly works
1262ea3
davenquinn and others added 22 commits July 29, 2024 12:34
@davenquinn
Fixed MYSQLDump command and deadlock in dump/restore
0ab3678
@amyfromandi
added find_col_variances() function
1a9fbf3
@davenquinn
Updated some formatting
5fc2636
@davenquinn
Streamlined migration scripts and renamed files
4fdc534
@davenquinn
Added the ability to run different steps of the migration process
795395c
accommodated code for port missing in macrostrat.toml. also added ssl…
5064029 
… mode parameters for pgloader command to work
@amyfromandi
updated pgloader code to use new pg_temp_engine url and creds
5152d49
@amyfromandi
Merged maria-migrate into this branch
fb9e628
@amyfromandi
Added most up-to-date find_row_variances() and find_col_variances
02d7112
@amyfromandi
Got find_row_variances() and find_col_variances to function!
6f3774f
@davenquinn
Created utility function we might use to run PGLoader
15d7a90
@davenquinn
Updated utility functions for creating temporary database users
88c0191
@davenquinn
Updated function names somewhat
0c6dda9
@amyfromandi
fixed pgloader issue by adding mariadb_migrator as superuser
cb633f6
@amyfromandi
Fixed pgloader and check-data stats
761903c
@davenquinn
Merge pull request #74 from UW-Macrostrat/maria-migrate-cli
22214cf 
Changes to MariaDB migration to integrate with Macrostrat CLI
@amyfromandi
repointed migration scripts to point to macrostrat.macrostrat_temp sc…
daa13ad 
…hema
@amyfromandi
added preserve_macrostrat_data() function for the final steps in the …
454129f 
…mariadb-migrate-postgres step
@amyfromandi
Merge branch 'maria-migrate-cli', remote-tracking branch 'origin' int…
1f1cf02 
…o maria-migrate
@amyfromandi
Added code to resolve table and column variances across macrostrat an…
fd039a5 
…d macrostrat_temp
@amyfromandi
post script and preserve-macrostrat-data script is finalized!
4c729c3
@amyfromandi
refactored a little bit of data
f03941c
@amyfromandi amyfromandi linked an issue Aug 13, 2024 that may be closed by this pull request
MariaDB to PostgreSQL data variance tracker #81
Open
@amyfromandi
removing unnecessary schlep scripts. index files in schlep-index.sql …
265feaf 
…and kep the schlep scripts for the tables that are modified in the pre/post migratino scripts.
@amyfromandi
Copy link
Collaborator Author

amyfromandi commented Aug 30, 2024 •
edited
Loading

Removed v1 schlep scripts and moved all index files here schlep-index.
Kept schlep scripts for tables that are modified in the pre/post migration scripts in case they are needed for future references i.e. the process scripts. remaining schlep scripts

These changes are referenced from #81

@amyfromandi amyfromandi merged commit 240d0f6 into main Sep 4, 2024
@davenquinn davenquinn deleted the maria-migrate branch September 7, 2024 08:35
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers

@davenquinn davenquinn Awaiting requested review from davenquinn

Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.

MariaDB to PostgreSQL data variance tracker
2 participants
@amyfromandi @davenquinn