Database Migration in Odoo

# Create database backup
pg_dump -Fc -f /backups/odoo_v16_$(date +%Y%m%d).dump your_database

# Backup filestore
tar -czvf /backups/filestore_$(date +%Y%m%d).tar.gz /var/lib/odoo/filestore/your_database

# Verify backup can be restored
createdb test_restore
pg_restore -d test_restore /backups/odoo_v16_$(date +%Y%m%d).dump

# Test Odoo starts with restored database
./odoo-bin -d test_restore --stop-after-init

import psycopg2

def audit_database(db_name):
    """Extract all models and fields from current database."""
    conn = psycopg2.connect(dbname=db_name)
    cur = conn.cursor()
    
    # Get all tables (models)
    cur.execute("""
        SELECT table_name 
        FROM information_schema.tables 
        WHERE table_schema = 'public' 
        AND table_type = 'BASE TABLE'
        ORDER BY table_name
    """)
    tables = [row[0] for row in cur.fetchall()]
    
    schema = {}
    for table in tables:
        cur.execute("""
            SELECT column_name, data_type, is_nullable
            FROM information_schema.columns
            WHERE table_name = %s
            ORDER BY ordinal_position
        """, (table,))
        schema[table] = [
            {'name': row[0], 'type': row[1], 'nullable': row[2]}
            for row in cur.fetchall()
        ]
    
    conn.close()
    return schema

# Compare schemas
old_schema = audit_database('odoo_v16')
new_schema = audit_database('odoo_v18_empty')

# Find removed tables
removed = set(old_schema.keys()) - set(new_schema.keys())
print(f"Removed tables: {removed}")

# Find changed columns per table
for table in old_schema:
    if table in new_schema:
        old_cols = {c['name']: c['type'] for c in old_schema[table]}
        new_cols = {c['name']: c['type'] for c in new_schema[table]}
        
        removed_cols = set(old_cols.keys()) - set(new_cols.keys())
        if removed_cols:
            print(f"{table}: removed columns {removed_cols}")

-- Example: account.account.code changed from varchar to jsonb in Odoo 17+
-- Check current type
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'account_account' AND column_name = 'code';

-- If varchar, prepare transformation script
-- This runs during migration phase
ALTER TABLE account_account 
ADD COLUMN code_new jsonb;

UPDATE account_account 
SET code_new = jsonb_build_object('en_US', code);

ALTER TABLE account_account DROP COLUMN code;
ALTER TABLE account_account RENAME COLUMN code_new TO code;

from openupgradelib import openupgrade

@openupgrade.migrate()
def migrate(env, version):
    """Pre-migration script for custom module."""
    cr = env.cr
    
    # Rename deprecated field before ORM loads
    if openupgrade.column_exists(cr, 'sale_order', 'old_field_name'):
        openupgrade.rename_columns(cr, {
            'sale_order': [('old_field_name', 'new_field_name')],
        })
    
    # Rename model (table) if it changed
    if openupgrade.table_exists(cr, 'old_model_name'):
        openupgrade.rename_tables(cr, [
            ('old_model_name', 'new_model_name'),
        ])
    
    # Handle removed field - preserve data temporarily
    if openupgrade.column_exists(cr, 'res_partner', 'deprecated_field'):
        cr.execute("""
            ALTER TABLE res_partner 
            RENAME COLUMN deprecated_field TO deprecated_field_backup
        """)

-- List all pivot tables (usually named model1_model2_rel)
SELECT table_name 
FROM information_schema.tables 
WHERE table_name LIKE '%_rel' 
AND table_schema = 'public';

-- Rename pivot table if model renamed
-- Example: mail_group renamed to discuss_channel
ALTER TABLE mail_group_res_users_rel 
RENAME TO discuss_channel_res_users_rel;

-- Update foreign key column names
ALTER TABLE discuss_channel_res_users_rel 
RENAME COLUMN mail_group_id TO discuss_channel_id;

-- Recreate foreign key constraint
ALTER TABLE discuss_channel_res_users_rel 
DROP CONSTRAINT mail_group_res_users_rel_mail_group_id_fkey;

ALTER TABLE discuss_channel_res_users_rel 
ADD CONSTRAINT discuss_channel_res_users_rel_discuss_channel_id_fkey 
FOREIGN KEY (discuss_channel_id) REFERENCES discuss_channel(id);

from openupgradelib import openupgrade

@openupgrade.migrate()
def migrate(env, version):
    """Post-migration: recompute stored fields."""
    
    # Recompute sale order totals
    orders = env['sale.order'].search([])
    orders._compute_amounts()
    
    # Recompute invoice status
    orders._compute_invoice_status()
    
    # Recompute partner display_name
    partners = env['res.partner'].search([])
    partners._compute_display_name()
    
    # Recompute stock move quantities
    moves = env['stock.move'].search([('state', '=', 'done')])
    moves._compute_product_qty()
    
    # Force recompute using SQL (faster for large datasets)
    env.cr.execute("""
        UPDATE sale_order_line 
        SET price_subtotal = product_uom_qty * price_unit * (1 - discount/100)
        WHERE price_subtotal IS NULL OR price_subtotal = 0
    """)

def validate_all_views(env):
    """Check all views render without errors."""
    View = env['ir.ui.view']
    errors = []
    
    for view in View.search([('type', 'in', ['form', 'tree', 'kanban'])]):
        try:
            # Attempt to read arch with field validation
            view._check_xml()
        except Exception as e:
            errors.append({
                'view_id': view.id,
                'name': view.name,
                'model': view.model,
                'error': str(e)
            })
    
    return errors

# Run validation
errors = validate_all_views(env)
for err in errors:
    print(f"View {err['name']} ({err['model']}): {err['error']}")

# Fix by removing problematic view inheritance
# Or update the view to use correct field names