Database Migration Best Practices

Introduction#

Module porting gets attention; database work often decides whether cutover finishes on time. Core Odoo handles much of the schema evolution, but custom fields, orphaned data, and third-party tables still need explicit handling.

This article covers database methodology. Custom module engineering belongs in the module migration and Development pillars — cross-link rather than duplicating ORM patterns here.

Backups and restore drills#

Before any dry run, verify backups: PostgreSQL base backup or pg_dump, filestore if attachments matter, and configuration secrets stored separately. Restore to an isolated server at least once per programme — a backup that never restored is a hope.

For cutover, take a final backup immediately before maintenance begins and confirm restore steps align with rollback planning. Document backup location, retention, and who can authorize restore.

• ▸Test restore to non-production hardware or volume
• ▸Include filestore when documents and images are business-critical
• ▸Record backup checksum or size for cutover night verification

Staging copies that reflect reality#

Refresh staging from production on a schedule. Anonymize PII where required but preserve row counts, attachment sizes, and fiscal history needed for realistic script runtime. Subset copies are acceptable for early dev cycles; full copies should precede final UAT.

Align PostgreSQL version, extensions, and locale settings with production targets. Surprises from mismatched extensions belong in dry run 1, not cutover.

Core migration path and custom scripts#

Odoo's upgrade service or open-source migration tooling applies versioned core scripts during -u on the target codebase. Custom work typically adds module migration hooks (pre/post init), SQL for legacy cleanup, and data transforms for fields that changed shape between versions.

Script every repeatable fix. One-off manual SQL in psql during cutover should already have succeeded on the last dry-run snapshot. Prefer ORM migration in module hooks when business rules must run — raw SQL when volume or simplicity demands it.

Performance and runtime estimation#

Large mail_message, stock_move, and account_move tables dominate upgrade time. Monitor autovacuum, index health, and disk IO during dry runs. Batch cleanup scripts may run pre-upgrade to remove obsolete logs if policy allows.

Log start/end timestamps per major step in the runbook. Use dry run 2 and 3 to refine estimates; add buffer for cutover — networks, human verification, and integration restarts add time beyond raw script duration.

Post-upgrade validation#

Automate reconciliation where possible: trial balance tie-out, open PO/SO counts, stock valuation snapshot comparison, partner and product counts. Manual spot checks complement — they do not replace — aggregate validation.

Scan ir_module_module for failed states, review upgrade logs for tracebacks, and confirm cron jobs are active but paused if needed until smoke tests pass.

Practical recommendations#

• ✓Restore-test backups before the first dry run, not the night before cutover.
• ✓Version all custom migration scripts in Git with module or runbook references.
• ✓Measure full upgrade duration on production-sized copies before publishing downtime windows.
• ✓Validate financial and stock aggregates automatically after each dry run.
• ✓Document PostgreSQL and Odoo versions used during successful dry runs.

Common mistakes#

• !Running upgrades only on empty databases with unrealistic runtime.
• !Manual production SQL fixes never tested on a full copy.
• !Omitting filestore from backup and restore planning.
• !Assuming core scripts fix third-party module tables without errors.
• !Skipping log review because the UI loads after upgrade.

Conclusion#

Reliable database migration rests on tested backups, realistic staging copies, versioned scripts, measured dry runs, and automated reconciliation — not optimism.

Pair database practice with structured module porting so schema and code upgrades stay aligned.