Add Database Migration Tools for PostgreSQL and MongoDB by Dozgulbas · Pull Request #112 · AD-SDL/MADSci

Dozgulbas · 2025-09-15T22:50:24Z

PR Info

Implements database migration tools for MADSci's Resource Manager (PostgreSQL), Event Manager (MongoDB), and Data Manager (MongoDB) with automated version checking and safe schema evolution.

Breaking Changes

All the managers listed below will refuse to start unless the migration is completed.
Workcell Manager, Data Manager, Experiment Manager, Event Manager and Resource Manager

PostgreSQL Migration (Resource Manager)

Alembic-based migrations with automatic schema generation and type conversion safety
Version compatibility checking between MADSci package and database schema versions
Automated backup/restore using pg_dump/psql with rollback on migration failure
Server startup validation - Resource server refuses to start on version mismatch

MongoDB Migration (Event & Data Managers)

Schema-driven migrations for collection and index management
Version tracking using schema_versions collection in each database
Automated backup/restore using mongodump/mongorestore
Safe migration process with transactional rollback on failure

Server Integration
All servers now perform version validation on startup:

Resource Server: Validates PostgreSQL schema version using Alembic version table
Event Server: Validates MongoDB schema version using madsci_events.schema_versions
Data Server: Validates MongoDB schema version using madsci_data.schema_versions

Servers refuse to start on version mismatch and display clear migration instructions.

Migration Commands

PostgreSQL (Resource Manager)

# Migrate to current MADSci version
python -m madsci.resource_manager.migration_tool --db-url postgresql://user:pass@host/db

# Create backup only  
python -m madsci.resource_manager.migration_tool --db-url 'postgresql://user:pass@postgres:5432/resources' --backup-only

# Restore from backup
python -m madsci.resource_manager.migration_tool --db-url 'postgresql://user:pass@postgres:5432/resources' --restore-from /path/to/backup.sql

# Generate new migration file
python -m madsci.resource_manager.migration_tool --db-url 'postgresql://user:pass@postgres:5432/resources' --generate-migration "Add new feature"

Docker Usage

# Run migration to current MADSci version in Docker
docker-compose run --rm -v $(pwd)/src:/home/madsci/MADSci/src resource-manager python -m madsci.resource_manager.migration_tool --db-url 'postgresql://user:pass@postgres:5432/resources'

# Migrate to specific version in Docker
docker-compose run --rm -v $(pwd)/src:/home/madsci/MADSci/src resource-manager python -m madsci.resource_manager.migration_tool --db-url 'postgresql://user:pass@postgres:5432/resources' --target-version 1.0.0

# Create backup only in Docker
docker-compose run --rm -v $(pwd)/src:/home/madsci/MADSci/src resource-manager python -m madsci.resource_manager.migration_tool --db-url 'postgresql://user:pass@postgres:5432/resources' --backup-only

# Restore form a backup file in Docker
docker-compose run --rm -v $(pwd)/src:/home/madsci/MADSci/src resource-manager python -m madsci.resource_manager.migration_tool --db-url 'postgresql://user:pass@postgres:5432/resources' --restore-from /path/to/backup.sql

MongoDB (Event/Data Managers)

# Run migration for events database (auto-detects schema file)
python -m madsci.common.mongodb_migration_tool --db-url mongodb://localhost:27017  --database madsci_events

# Migrate with explicit database URL
python -m madsci.common.mongodb_migration_tool --db-url mongodb://localhost:27017 --database madsci_events

# Use custom schema file
python -m madsci.common.mongodb_migration_tool --db-url mongodb://localhost:27017  --database madsci_events --schema-file /path/to/schema.json

# Create backup only
python -m madsci.common.mongodb_migration_tool --db-url mongodb://localhost:27017  --database madsci_events --backup-only

# Restore from backup
python -m madsci.common.mongodb_migration_tool --db-url mongodb://localhost:27017  --database madsci_events --restore-from /path/to/backup

# Check version compatibility without migrating
python -m madsci.common.mongodb_migration_tool --db-url mongodb://localhost:27017  --database madsci_events --check-version

Docker Usage

# Run migration for events database in Docker
docker-compose run --rm event-manager python -m madsci.common.mongodb_migration_tool --db-url 'mongodb://mongodb:27017' --database 'madsci_events' --schema-file '/app/madsci/event_manager/schema.json'

# Create backup only in Docker
docker-compose run --rm event-manager python -m madsci.common.mongodb_migration_tool --db-url 'mongodb://mongodb:27017' --database 'madsci_events' --schema-file '/app/madsci/event_manager/schema.json' --backup-only

# Check version compatibility in Docker
docker-compose run --rm event-manager python -m madsci.common.mongodb_migration_tool --db-url 'mongodb://mongodb:27017' --database 'madsci_events' --schema-file '/app/madsci/event_manager/schema.json' --check-version

Schema Management

PostgreSQL: Uses Alembic auto-generation from SQLModel table definitions
MongoDB: Uses JSON schema files (schema.json) for collection/index definitions
Version source: MADSci package version for all databases (single source of truth)

Safety Features

Pre-migration database backups with automatic restore on failure
Version tracking prevents accidental downgrades
Background index creation to avoid blocking operations

Closes: #92

Developer Checklists

I have:

Run Pre-commit and Unit Tests, and ensured that they pass
Created or updated documentation relevant to your change
Created or updated unit tests relevant to your change

… step method

…r executed

Dozgulbas · 2025-11-13T21:07:53Z

Oh, one other thing: we should consider having a pydantic model for our mongo schema definition, so we can easily validate a schema file and the mongo db's existing schema definition

Added schema validators for this.

Dozgulbas · 2025-11-13T21:08:31Z

@LuckierDodge Should be ready to review for the second time

Refactor: Separate DB backup into standalone tool

Dozgulbas added 30 commits August 8, 2025 14:57

Added an example plotting notebook for utilization reports

7248ae3

Added an example plotting notebook for utilization reports

61e7d90

Added an example plotting notebook for utilization reports

dd371fd

Merge branch 'main' of https://github.com/AD-SDL/MADSci into util_graphs

57bb7d1

Adding a place holder

1e08c8a

Adding a place holder

1fc67ba

adding the first version

bda6d97

adding the first version

b15ffde

Added transfer manager types

2421fc0

Added transfer manager types

59af2a3

Added the transfer manager

469707c

Added a step to handle transfer steps through transfer manager in run…

bcc7771

… step method

Transfer manager

19fb9b6

Transfer manager types

e3ef939

Transfer manager client

c1681d4

Graph that handles the transfer manager logic

480ab81

Server for transfer manager

e9bebd6

Example robot definitions config

6bc7770

Tests

316466e

transfer manager

1ad1623

pulling updates from main

2798a7b

handling transfer config file

e1718b2

Tests

c8e43f2

Handling location argument types

d774fb8

handling transfer config file

4196131

Bug: After transfer manager returns resolved steps, next step is neve…

eaf7475

…r executed

Added trasnfer manager

2e0b607

Combined transfer manager configs under single file

cf892b7

Updated the server to use module nodetype

d982f65

Updated the server to use module nodetype

60d9a5e

Dozgulbas added 2 commits November 13, 2025 13:05

Added validate schema field to use with paydentic args

e89a44e

Fixed bug with validate_schema arg

3ca2d02

RyanTheRobothead self-requested a review November 17, 2025 18:12

RyanTheRobothead and others added 19 commits November 17, 2025 14:40

Merge branch 'main' into db_migrations

9b3ef6e

Fixing tests post-merge

af3a009

Add backup and restore validation and testing

502535b

Merge branch 'main' into db_migrations

e40fcdf

Merge branch 'main' into db_migrations

d2e88af

WIP: standalone backup tooling phase 1

1d10887

WIP: standalone backup tooling phase 2

ba8b583

WIP: standalone backup tooling phase 3

29fd16a

WIP: standalone backup tooling phase 4

fac29ca

Unify CLI and add documentation

1c3881b

Migrate postgres tooling to common

3173e98

Final: backup cli tooling separated from migration tool

71f807b

Fix race condition/postgres backup path issue

53832bf

Remove references to not-implemented features

585be52

Remove unneccessary check

6ae09e5

Update configuration

8d9a98e

Fix default values for db urls

f4403c7

Fix some cli naming issues for clarity

4c87e19

Merge pull request #192 from AD-SDL/backup_tooling

f89b680

Refactor: Separate DB backup into standalone tool

RyanTheRobothead previously approved these changes Dec 4, 2025

View reviewed changes

Merge branch 'main' into db_migrations

0fc3650

RyanTheRobothead dismissed their stale review via 0fc3650 December 4, 2025 18:58

RyanTheRobothead approved these changes Dec 4, 2025

View reviewed changes

RyanTheRobothead merged commit 2663540 into main Dec 4, 2025
5 checks passed

RyanTheRobothead deleted the db_migrations branch December 27, 2025 15:03

Navigation Menu

Uh oh!

Add Database Migration Tools for PostgreSQL and MongoDB - #112

Add Database Migration Tools for PostgreSQL and MongoDB#112
RyanTheRobothead merged 167 commits into
mainfrom
db_migrations

Dozgulbas commented Sep 15, 2025 •

edited

Loading

Uh oh!

Dozgulbas commented Nov 13, 2025

Uh oh!

Dozgulbas commented Nov 13, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Uh oh!

Conversation

Dozgulbas commented Sep 15, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

PR Info

Developer Checklists

Uh oh!

Dozgulbas commented Nov 13, 2025

Uh oh!

Dozgulbas commented Nov 13, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

Dozgulbas commented Sep 15, 2025 •

edited

Loading