Connected business operations. Flexible deployment.
Learn how to safely handle sequence numbers and ID conflicts during Odoo database migrations. This guide covers technical strategies for preserving data integrity, avoiding primary key collisions, and ensuring a smooth cutover.
To safely handle sequence numbers and ID conflicts during an Odoo database migration, you must explicitly synchronize PostgreSQL sequences with the maximum existing ID values in the target database before and after data transfer. The primary risk is primary key collisions, which occur when the auto-increment sequence generates an ID that already exists in the target table, causing insertion failures and data loss. This issue is critical because Odoo relies heavily on integer primary keys for relational integrity across modules like Sales, Inventory, and Accounting. Failure to manage these sequences results in broken foreign key references, application errors, and potential downtime. The recommended approach involves a rigorous pre-migration audit of all sequence values, a controlled data transfer process that preserves or remaps IDs, and a post-migration validation step that resets sequences to the correct maximum value plus one. This ensures that new records created after migration do not conflict with historical data. By treating sequence management as a first-class component of the migration plan, you protect data integrity and maintain operational continuity.
Odoo uses PostgreSQL as its underlying database, relying on serial columns and sequences for auto-incrementing primary keys. In standard PostgreSQL, a sequence is an independent object that generates a series of numbers. When a new record is inserted into an Odoo table, the database engine fetches the next value from the associated sequence. However, if data is migrated from a source database to a target database, the target database's sequences may not reflect the highest ID present in the migrated data. For example, if the source database has records with IDs up to 10,000, but the target database's sequence is still at 1, the next new record will attempt to use ID 1, causing a conflict if ID 1 already exists in the target. This is a common pitfall in manual data migrations or when using tools that do not automatically synchronize sequences. Understanding this mechanism is essential for planning a safe migration. You must recognize that Odoo does not automatically adjust sequences based on imported data unless specific migration tools or scripts are used to do so.
Beyond primary key conflicts, ID management is critical for maintaining foreign key integrity. Odoo tables are heavily interconnected; for instance, a sales order line references a product, a customer, and a warehouse. If you remap IDs during migration (for example, to merge two databases), you must ensure that all foreign key references are updated consistently. If a product ID changes from 100 to 200, all sales order lines referencing product 100 must be updated to reference 200. Failure to do so results in orphaned records or broken relationships, leading to application errors and data inconsistency. This complexity increases the risk of migration failure. Therefore, the migration strategy must account for both primary key uniqueness and referential integrity. A robust migration plan includes a mapping table that tracks old IDs to new IDs, ensuring that all relationships are preserved during the transfer.
Before executing the migration, conduct a comprehensive audit of the source and target databases. Identify all tables with auto-incrementing primary keys and determine the current maximum ID value for each table. Use SQL queries to retrieve the maximum ID and the current sequence value for each relevant table. Compare these values to identify discrepancies. If the current sequence value is lower than the maximum ID, a conflict is imminent. Document these findings in a migration report. Additionally, profile the data to identify any existing ID conflicts within the source database, such as duplicate IDs or gaps in the sequence. This step helps you understand the baseline state of the data and plan for any necessary cleansing or remapping. It is also important to identify any custom modules or third-party add-ons that may have modified the default ID generation logic. These modules may use custom sequences or ID generation strategies that require special handling during migration.
Some Odoo modules or custom developments may use custom sequences for specific fields, such as document numbers (e.g., invoice numbers, sales order numbers). These sequences are stored in the ir_sequence table and are managed by Odoo's ORM. During migration, you must ensure that these custom sequences are also synchronized with the maximum values in the corresponding fields. For example, if the highest invoice number is INV/2023/00100, the sequence for invoice numbers must be set to 101 to avoid generating duplicate invoice numbers. Failure to do so results in duplicate document numbers, which can cause accounting errors and compliance issues. Review the ir_sequence table and any custom sequence definitions to ensure they are included in the migration plan. This step is often overlooked but is critical for maintaining business process integrity.
The technical execution of the migration involves transferring data from the source to the target database and synchronizing sequences. There are two main approaches: preserving original IDs or remapping IDs. Preserving original IDs is simpler but requires that the target database is empty or that there are no ID conflicts. If the target database contains existing data, you must remap IDs to avoid conflicts. Remapping IDs is more complex and requires updating all foreign key references. After data transfer, you must synchronize the sequences. This can be done using SQL commands to set the sequence value to the maximum ID plus one. For example, use the setval function to set the sequence to the maximum ID. This ensures that the next generated ID is unique. It is important to perform this step within a transaction to ensure consistency. If the migration fails, the transaction can be rolled back, leaving the database in its original state.
| Step | Action | SQL Command Example | Purpose |
|---|---|---|---|
| 1 | Identify Max ID | SELECT MAX(id) FROM res_partner; | Determine highest existing ID |
| 2 | Identify Sequence | SELECT last_value FROM res_partner_id_seq; | Check current sequence value |
| 3 | Synchronize Sequence | SELECT setval('res_partner_id_seq', 1001); | Set sequence to max ID + 1 |
| 4 | Verify | SELECT nextval('res_partner_id_seq'); | Confirm next ID is correct |
Before performing the production migration, test the entire process in a staging environment. Create a copy of the source database and the target database in the staging environment. Execute the migration script, including data transfer and sequence synchronization. Validate the results by checking for ID conflicts, foreign key integrity, and sequence values. Create new records in the staging environment to ensure that the sequences generate unique IDs. Run business process tests to verify that the application functions correctly with the migrated data. Check for any errors or warnings in the Odoo logs. This step is critical for identifying and resolving issues before they impact production. It also allows you to refine the migration script and ensure that it is reliable and repeatable. Document the results of the testing and any issues encountered. This documentation will be useful for the production migration and for troubleshooting if issues arise.
Perform reconciliation checks to ensure that the data in the target database matches the source database. Compare record counts, total values, and key fields between the source and target databases. Use SQL queries to generate reports that highlight any discrepancies. For example, compare the total number of sales orders, the total value of inventory, and the total balance of accounts. Any discrepancies must be investigated and resolved before proceeding to production. This step ensures that the migration is accurate and complete. It also provides an audit trail for the migration, which is important for compliance and accountability. Reconciliation checks should be performed at multiple stages of the migration, including after data transfer, after sequence synchronization, and after application testing.
Plan the cutover carefully to minimize downtime and risk. Define a cutover window during which the migration will be performed. Communicate the cutover plan to all stakeholders, including IT, finance, and operations. Prepare a rollback plan in case the migration fails. The rollback plan should include steps to restore the database from a backup and to revert any changes made during the migration. Ensure that the backup is verified and can be restored successfully. Define go/no-go criteria based on the results of the staging tests and the pre-migration audit. If any critical issues are identified, do not proceed with the production migration. Instead, resolve the issues and re-test in the staging environment. This approach reduces the risk of a failed migration and ensures that the production environment is stable and reliable.
After the migration is complete, monitor the system closely for any issues. Check the Odoo logs for errors or warnings. Monitor database performance to ensure that the sequences are functioning correctly and that there are no performance regressions. Validate that new records are being created with unique IDs. Check for any duplicate document numbers or other data integrity issues. Provide support to users who may encounter issues during the initial period after migration. Document any issues and their resolutions. This post-migration stabilization period is critical for ensuring that the migration is successful and that the system is stable and reliable. It also provides an opportunity to identify and address any remaining issues before they become critical.
Handling sequence numbers and ID conflicts safely is a critical aspect of Odoo database migration. By understanding the mechanics of ID generation, conducting a thorough pre-migration audit, executing the migration with careful sequence synchronization, and validating the results in a staging environment, you can ensure data integrity and avoid primary key collisions. This approach protects your business data, maintains operational continuity, and reduces the risk of migration failure. Remember to document each step of the process and to prepare a rollback plan in case of issues. By following these best practices, you can achieve a successful and safe Odoo database migration.
Common questions on this topic. Talk to our team for anything specific to your business.
If sequence numbers are not updated, the next new record will attempt to use an ID that already exists, causing a primary key conflict and insertion failure. This leads to data loss and application errors.
Use the SQL query SELECT MAX(id) FROM table_name; to retrieve the highest existing ID in a specific table. Repeat this for all tables with auto-incrementing primary keys.
Yes, you can use the setval function in PostgreSQL to manually set the sequence value to the maximum ID plus one. This ensures that the next generated ID is unique.
ID conflicts occur when two databases contain records with the same ID. If you merge them without remapping IDs, the target database will have duplicate primary keys, causing integrity errors.
The Odoo upgrade tool is designed for version upgrades, not for merging or migrating data between different databases. For data migration, use specialized tools or scripts that handle ID remapping and sequence synchronization.
Foreign keys reference primary keys in other tables. If you remap IDs, you must update all foreign key references to maintain referential integrity. Failure to do so results in broken relationships and data inconsistency.
Learn how professional services firms use ERP analytics to bridge the gap between project delivery and financial forecasting, improving cash flow visibility and operational decision-making.
A practical guide for CIOs and IT leaders to assess how ERP version upgrades affect custom workflows, including dependency analysis, testing strategies, and risk mitigation.
Learn how to design a cloud ERP strategy that standardizes retail operations, unifies store and warehouse data, and aligns finance with supply chain execution for scalable growth.
Discuss your workflows, modules and deployment options with our team.
Back to resources