Odoo Database Migration: Complete Guide for Version Upgrades

# Backup PostgreSQL database
pg_dump -U odoo -h localhost -F c -b -v -f "/backup/odoo16_$(date +%Y%m%d).dump" production_db

# Backup filestore
tar -czvf "/backup/filestore_$(date +%Y%m%d).tar.gz" /opt/odoo/.local/share/Odoo/filestore/production_db

# Verify backup can be restored (on test server)
pg_restore -U odoo -d test_restore -v "/backup/odoo16_20260115.dump"

#!/usr/bin/env python3
"""
Odoo Migration Analysis Script
Compares current database schema against target version requirements.
"""

import psycopg2
from collections import defaultdict


# Known model changes between Odoo 16 and 18
MODEL_RENAMES = {
    'account.invoice': 'account.move',
    'account.invoice.line': 'account.move.line',
    'mrp.workcenter.productivity': 'mrp.workcenter.productivity.loss',
}

# Known field changes
FIELD_CHANGES = {
    'sale.order': {
        'commitment_date': {'new_name': 'expected_date', 'type_change': None},
    },
    'account.account': {
        'code': {'new_name': 'code', 'type_change': 'varchar->jsonb'},
    },
    'product.template': {
        'tracking': {'removed': True, 'migrate_to': 'product.product.tracking'},
    },
}

REMOVED_MODELS = [
    'account.invoice.report',
    'sale.order.line.invoice_status',
]


def connect_database(dbname, user='odoo', host='localhost', port=5432):
    """Establish database connection."""
    return psycopg2.connect(
        dbname=dbname,
        user=user,
        host=host,
        port=port
    )


def get_all_tables(cursor):
    """Get all tables in the database."""
    cursor.execute("""
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_type = 'BASE TABLE'
        ORDER BY table_name
    """)
    return [row[0] for row in cursor.fetchall()]


def get_table_columns(cursor, table_name):
    """Get column details for a table."""
    cursor.execute("""
        SELECT column_name, data_type, is_nullable, column_default
        FROM information_schema.columns
        WHERE table_name = %s
        ORDER BY ordinal_position
    """, (table_name,))
    return cursor.fetchall()


def analyze_migration_needs(dbname):
    """Main analysis function."""
    conn = connect_database(dbname)
    cursor = conn.cursor()
    
    tables = get_all_tables(cursor)
    
    report = {
        'renamed_models': [],
        'removed_models': [],
        'field_changes': [],
        'type_changes': [],
    }
    
    # Check for models that will be renamed
    for table in tables:
        model_name = table.replace('_', '.')
        if model_name in MODEL_RENAMES:
            report['renamed_models'].append({
                'current': model_name,
                'new': MODEL_RENAMES[model_name],
                'table': table,
            })
        
        if model_name in REMOVED_MODELS:
            report['removed_models'].append({
                'model': model_name,
                'table': table,
                'action': 'Backup data before migration',
            })
    
    # Check for field changes
    for model, fields in FIELD_CHANGES.items():
        table = model.replace('.', '_')
        if table in tables:
            columns = get_table_columns(cursor, table)
            column_names = [col[0] for col in columns]
            
            for field, change in fields.items():
                if field in column_names:
                    if change.get('new_name'):
                        report['field_changes'].append({
                            'model': model,
                            'field': field,
                            'new_name': change['new_name'],
                        })
                    if change.get('type_change'):
                        report['type_changes'].append({
                            'model': model,
                            'field': field,
                            'change': change['type_change'],
                        })
    
    cursor.close()
    conn.close()
    
    return report


def print_report(report):
    """Print formatted analysis report."""
    print("
" + "="*60)
    print("ODOO MIGRATION ANALYSIS REPORT")
    print("="*60)
    
    print("
[!] RENAMED MODELS:")
    for item in report['renamed_models']:
        print(f"    {item['current']} -> {item['new']}")
    
    print("
[!] REMOVED MODELS:")
    for item in report['removed_models']:
        print(f"    {item['model']} - {item['action']}")
    
    print("
[!] FIELD RENAMES:")
    for item in report['field_changes']:
        print(f"    {item['model']}.{item['field']} -> {item['new_name']}")
    
    print("
[!] TYPE CHANGES:")
    for item in report['type_changes']:
        print(f"    {item['model']}.{item['field']}: {item['change']}")
    
    print("
" + "="*60)


if __name__ == '__main__':
    import sys
    dbname = sys.argv[1] if len(sys.argv) > 1 else 'production_db'
    report = analyze_migration_needs(dbname)
    print_report(report)

"""
Pre-migration script: Handle model renames
Run this BEFORE upgrading to the new Odoo version.
"""

from odoo import api, SUPERUSER_ID


def migrate(cr, version):
    """Handle model renames and prepare foreign key references."""
    
    # Update ir.model references for renamed models
    model_renames = [
        ('account.invoice', 'account.move'),
        ('account.invoice.line', 'account.move.line'),
    ]
    
    for old_name, new_name in model_renames:
        # Update ir.model
        cr.execute("""
            UPDATE ir_model 
            SET model = %s 
            WHERE model = %s
        """, (new_name, old_name))
        
        # Update ir.model.data
        cr.execute("""
            UPDATE ir_model_data 
            SET model = %s 
            WHERE model = %s
        """, (new_name, old_name))
        
        # Update ir.model.fields
        cr.execute("""
            UPDATE ir_model_fields 
            SET relation = %s 
            WHERE relation = %s
        """, (new_name, old_name))
        
        print(f"Renamed model references: {old_name} -> {new_name}")

-- Handle varchar to jsonb conversion for account.account.code
-- Run this on the source database before migration

-- Step 1: Create backup of current values
CREATE TABLE IF NOT EXISTS _migration_account_code_backup AS
SELECT id, code FROM account_account WHERE code IS NOT NULL;

-- Step 2: Add new column with correct type
ALTER TABLE account_account 
ADD COLUMN IF NOT EXISTS code_store jsonb;

-- Step 3: Migrate data to new format
UPDATE account_account 
SET code_store = jsonb_build_object('en_US', code)
WHERE code IS NOT NULL;

-- Step 4: Verify migration
SELECT COUNT(*) as total,
       COUNT(code_store) as migrated
FROM account_account;

-- Only drop old column after verification
-- ALTER TABLE account_account DROP COLUMN code;

"""
Pre-migration script: Handle field renames
"""

def migrate(cr, version):
    """Rename fields that changed between versions."""
    
    field_renames = [
        # (table, old_column, new_column)
        ('sale_order', 'commitment_date', 'expected_date'),
        ('purchase_order', 'date_approve', 'date_approve'),
    ]
    
    for table, old_col, new_col in field_renames:
        # Check if old column exists and new doesn't
        cr.execute("""
            SELECT column_name 
            FROM information_schema.columns 
            WHERE table_name = %s AND column_name IN (%s, %s)
        """, (table, old_col, new_col))
        
        existing = [row[0] for row in cr.fetchall()]
        
        if old_col in existing and new_col not in existing:
            cr.execute(f"""
                ALTER TABLE {table}
                RENAME COLUMN {old_col} TO {new_col}
            """)
            print(f"Renamed column: {table}.{old_col} -> {new_col}")
        elif old_col in existing and new_col in existing:
            # Both exist - merge data
            cr.execute(f"""
                UPDATE {table}
                SET {new_col} = {old_col}
                WHERE {new_col} IS NULL AND {old_col} IS NOT NULL
            """)
            print(f"Merged data: {table}.{old_col} -> {new_col}")

# Stop the old Odoo service
sudo systemctl stop odoo16

# Run the upgrade (using Odoo 18 binary)
/opt/odoo18/odoo-bin -d production_db \
    --update=all \
    --stop-after-init \
    --logfile=/var/log/odoo/migration.log \
    -c /etc/odoo18.conf

# Check the log for errors
grep -i "error|exception|warning" /var/log/odoo/migration.log

"""
Post-migration script: Recompute stored computed fields
Run this AFTER the main migration is complete.
"""

from odoo import api, SUPERUSER_ID


def migrate(cr, version):
    """Recompute critical computed fields."""
    env = api.Environment(cr, SUPERUSER_ID, {})
    
    # List of (model, field_name) to recompute
    fields_to_recompute = [
        ('sale.order', 'amount_total'),
        ('sale.order', 'amount_untaxed'),
        ('sale.order', 'invoice_status'),
        ('account.move', 'amount_total'),
        ('account.move', 'amount_residual'),
        ('account.move', 'payment_state'),
        ('product.product', 'qty_available'),
        ('product.product', 'virtual_available'),
        ('stock.quant', 'value'),
    ]
    
    for model_name, field_name in fields_to_recompute:
        try:
            Model = env[model_name]
            field = Model._fields.get(field_name)
            
            if field and field.store and field.compute:
                print(f"Recomputing {model_name}.{field_name}...")
                
                # Get all records
                records = Model.search([])
                
                # Invalidate cache and recompute
                records.invalidate_cache([field_name])
                records._compute_field_value(field)
                
                # Commit in batches
                cr.commit()
                
                print(f"  Recomputed {len(records)} records")
                
        except Exception as e:
            print(f"  Error recomputing {model_name}.{field_name}: {e}")
            cr.rollback()

-- Post-migration validation queries
-- Run these to verify data integrity

-- Check for orphaned records in sale_order_line
SELECT COUNT(*) as orphaned_lines
FROM sale_order_line sol
LEFT JOIN sale_order so ON sol.order_id = so.id
WHERE so.id IS NULL;

-- Verify account move totals
SELECT 
    am.name,
    am.amount_total as stored_total,
    SUM(aml.debit) as calculated_debit,
    SUM(aml.credit) as calculated_credit
FROM account_move am
JOIN account_move_line aml ON aml.move_id = am.id
GROUP BY am.id, am.name, am.amount_total
HAVING ABS(am.amount_total - SUM(aml.debit)) > 0.01
LIMIT 20;

-- Check for missing partner references
SELECT COUNT(*) as missing_partners
FROM sale_order so
LEFT JOIN res_partner rp ON so.partner_id = rp.id
WHERE rp.id IS NULL AND so.partner_id IS NOT NULL;

-- Verify product stock levels
SELECT 
    pp.id,
    pt.name,
    pp.qty_available,
    COALESCE(SUM(sq.quantity), 0) as quant_total
FROM product_product pp
JOIN product_template pt ON pp.product_tmpl_id = pt.id
LEFT JOIN stock_quant sq ON sq.product_id = pp.id
GROUP BY pp.id, pt.name, pp.qty_available
HAVING ABS(pp.qty_available - COALESCE(SUM(sq.quantity), 0)) > 0.001
LIMIT 20;