Odoo Migration: Managing Sequence Numbers and ID Mapping Across Databases

Direct Answer: Preserving Data Integrity Through Strategic ID Mapping

Managing sequence numbers and ID mapping is the most critical technical challenge in Odoo migrations. The recommended approach is to create a comprehensive mapping table that links source database IDs to target database IDs before any data is loaded. This ensures that foreign key relationships remain intact, preventing broken references and data corruption. Ignoring this step leads to orphaned records, failed transactions, and significant operational downtime. The primary risks to control are ID collisions, sequence gaps that cause future conflicts, and the loss of referential integrity across modules like Sales, Inventory, and Accounting. By treating ID mapping as a first-class data object rather than an afterthought, you protect business continuity and ensure that custom modules and integrations function correctly in the new environment.

Understanding the Role of IDs and Sequences in Odoo

In Odoo, every record is identified by a unique integer primary key (ID). These IDs are generated by PostgreSQL sequences. When migrating data, the target database will generate new IDs unless explicitly specified. If you do not control these IDs, the relationships between records (e.g., a Sales Order line referencing a Product) will break because the new IDs will not match the old ones. Furthermore, Odoo uses sequences for human-readable codes (like invoice numbers or product references). If these sequences are not synchronized with the migrated data, the system may generate duplicate codes or skip numbers, causing compliance and audit issues.

The Risk of Automatic ID Generation

By default, database inserts allow the database to assign the next available ID. In a migration context, this is dangerous. If you migrate a Product with ID 100 in the source, and the target assigns it ID 500, any Sales Order line that references Product ID 100 will fail to link correctly. This results in data loss or application errors. Therefore, the migration strategy must explicitly define the target ID for every record to be migrated.

Step 1: Preparation and Data Profiling

Before writing any migration code, you must profile the source data. Identify all models that have interdependencies. For example, `res.partner` is referenced by `account.move`, `sale.order`, and `stock.picking`. You need to determine the migration order. Generally, you migrate master data first (Partners, Products, UoM), then transactional data (Orders, Invoices). During profiling, identify any custom fields or modules that might store IDs in text fields or JSON structures, as these are not automatically handled by standard foreign key constraints.

Identifying Sequence Dependencies

List all sequences used in your Odoo instance. This includes standard sequences like `ir_sequence` for invoice numbers and custom sequences defined in third-party modules. You must record the current last value of each sequence in the source database. This value is critical for resetting the sequence in the target database after migration to prevent future ID or code collisions.

Step 2: Building the ID Mapping Table

The core of the migration is the ID mapping table. This is a temporary table (or set of tables) that stores the relationship between `source_id` and `target_id` for each model. You can create this table in the target database or in a separate migration database. The structure should include: `model_name`, `source_id`, `target_id`, and `migration_timestamp`. As you migrate each record, you insert the source ID and the newly assigned target ID into this table. This table becomes the reference for all subsequent migrations that depend on the previous ones.

Step 3: Technical Execution of Data Migration

The migration script must be executed in a strict dependency order. For each record, the script should: 1) Look up the source ID in the mapping table to find the target ID of any referenced records. 2) Construct the new record with the mapped target IDs for foreign keys. 3) Insert the record into the target database, explicitly setting the `id` field if your migration tool supports it, or capturing the new ID if the database assigns it. 4) Update the mapping table with the new `source_id` to `target_id` pair. This process must be transactional to ensure that if a failure occurs, the entire batch is rolled back, maintaining consistency.

Handling Custom Modules and Add-ons

Custom modules often store IDs in non-standard ways. For example, a custom field might store a list of partner IDs as a comma-separated string. The migration script must parse these fields, look up each ID in the mapping table, and replace them with the new target IDs. This requires custom logic for each affected module. Always verify the compatibility of custom modules with the target Odoo version before migration, as API changes may require code updates in addition to data mapping.

Step 4: Synchronizing Sequence Numbers

After all data is migrated, you must reset the sequences in the target database. If you migrated records with explicit IDs, the database sequences may still be at their default values (e.g., 1). If you do not reset them, the next new record created in the target system will have an ID that already exists, causing a primary key violation. You must set the sequence to the maximum ID found in the migrated data for that model. For human-readable sequences (like invoice numbers), you must set the sequence to the last used number in the source data to ensure continuity.

Step 5: Validation and Reconciliation

Validation is not optional. You must perform a three-way reconciliation: 1) Record Count: Ensure the number of records in the source matches the target for each model. 2) Referential Integrity: Run SQL queries to check for orphaned records (e.g., Sales Order lines with a Product ID that does not exist in the Product table). 3) Business Logic: Create test transactions in the target system to ensure that workflows (e.g., creating an invoice from a sales order) function correctly. Any discrepancies must be investigated and resolved before proceeding to the next phase.

Automated Testing Scripts

Develop automated scripts that compare key fields between source and target databases. For example, compare the total amount of all invoices in the source and target. If the totals do not match, there is a data loss or transformation error. These scripts should be run after every trial migration to catch issues early. Do not rely solely on manual spot checks, as they are prone to human error and do not cover the entire dataset.

Step 6: Deployment and Cutover

The cutover phase involves freezing the source system, performing the final data migration, and switching users to the target system. The cutover plan must include a detailed runbook with step-by-step instructions, responsible owners, and time estimates for each step. The go/no-go decision should be based on the results of the final trial migration and validation. If any critical errors are found, the migration should be aborted, and the rollback plan should be executed. The rollback plan typically involves restoring the source database from a backup taken before the cutover.

Step 7: Post-Migration Stabilization

After go-live, monitor the system closely for the first 48-72 hours. Watch for errors in the logs, particularly related to ID conflicts or sequence issues. Provide support to users who may encounter unexpected behavior. Document any issues found and their resolutions. This period is critical for identifying any edge cases that were not covered in testing. Once the system is stable, you can decommission the source system and archive the migration mapping tables.

Common Pitfalls and How to Avoid Them

• Ignoring Custom Fields: Always audit custom modules for fields that store IDs in non-standard formats.
• Skipping Sequence Resets: Failing to reset sequences leads to primary key violations on new record creation.
• Inconsistent Migration Order: Migrating child records before parent records causes foreign key constraint errors.
• Lack of Reconciliation: Not comparing record counts and totals leads to undetected data loss.
• No Rollback Plan: Without a tested rollback plan, a failed migration can result in prolonged downtime.

Conclusion

Managing sequence numbers and ID mapping is a complex but manageable aspect of Odoo migration. By following a structured approach that includes data profiling, ID mapping, careful execution, sequence synchronization, and rigorous validation, you can ensure a successful migration that preserves data integrity and business continuity. The key is to treat ID mapping as a core component of the migration strategy, not an afterthought. Invest time in preparation and testing to avoid costly errors during cutover. Always verify compatibility with the specific Odoo versions involved and consult official documentation for version-specific details.