Database replication between 2 Odoo 8.0 postgresql databases with different schemas

Question

We have a situation where we need to implement replication between 2 Odoo v8 databases located in different data-centers thousands of miles apart (so latency needs to be taken into account). The databases have different schemas, the tables are pretty much the same, but the columns in these tables are different and the replication needs to happen at database level. All warnings to the client that this will bypass ORM and therefore any Python level constraints, which are different, because the custom code is different (Odoo source will be the same) have fallen on deaf ears. The replication needs to happen only for certain tables (still needs to be clarified), one of which is the sale_order table, however this means that any tables pointed to by relation (foreign key) columns need to be in sync as well. So if a sale order has a partner_id of 5, that means the res_partner table has to have a line with id 5 in both databases and the data in all matching columns needs to match and a lot of those columns will also be foreign key columns that point to other tables and so on and so forth. The code base is not just different, it's vastly different- dozens of completely different modules and thousands of lines of code. I'm a bit at a loss on how to implement this, there is an Odoo module (base_synchro), which could potentially work to get the needed result, except that it works via xml-rpc requests, so not on the database level and therefore not an option. Anyone have any experience with something similar that could weigh-in? Or is this impossible to do without creating a complete mess in both databases?

Answer 1

we having this problem too, we have more than 100 company because of network loss we have to put an odoo instance in every company and we needed a central server to consolidate data for our main company for statistics and so on. the solution that we are doing now is to change the the code of models.py exactly in create method to generate a special id for a company.

let say that a company has a code = 120 so we chaged odoo implementation to add every id should be 1200000000 + id so when you save the firsr record in this company the id != 1 but id = 1200000001 and others company id = 1990000001 with code 199 so when we consolidate data we didn't have to fix foreign keys because we will never have an id that exists in two companies. even this solution was not accepted because we have a lot companies we are losing a lot of data so the max value for our id = 9 999 999 for you. you have 2 company so max id = 999 999 999 now we are changing the type of the id from serial to bigserial but this needs a lot of code editing because we had to modify many2one field and integer fields ...

the code for id is :

def _create(self, cr, user, vals, context=None):
   ...
   ...
   updates = [
        # list of column assignments defined as tuples like:
        #   (column_name, format_string, column_value)
        #   (column_name, sql_formula)
        # Those tuples will be used by the string formatting for the INSERT
        # statement below.
        ('id', "nextval('%s')+%s" % (self._sequence, 1000000000)),
    ]

of course you can make this dynamic by a the code the company_id of the current user this.