============================ Tryton and PostgreSQL Schema ============================ :Author: Cédric Krier :Contact: cedric.krier@b2ck.com :Date: 21/08/2015 The goal is to run 2 Tryton servers on the same database but sharing only the parties. For this purpose we will use the `schemas of PostgreSQL `_. Of course this example can be extended to share more tables but you must ensure that shared tables have not any foreign-key constraints to non-shared tables. This howto requires a trytond version which includes the `issue4924 `_, `issue4925 `_ and `issue4926 `_. .. contents:: Setup ===== Users creation -------------- We create one user for each Tryton instance: .. code:: sh $ createuser --createdb dbuser1 $ createuser dbuser2 We must configure PostgreSQL to be able to connect using both users. Database setup -------------- Now, we create the database with dbuser1: .. code:: sh $ createdb --encoding=utf-8 --owner=dbuser1 --username=dbuser1 trytondb And give access to dbuser2: .. code:: sql # GRANT ALL ON DATABASE trytondb TO dbuser2; We create a schema per user and the common tables will be stored in the default `public` schema: .. code:: sql # CREATE SCHEMA dbuser1; # CREATE SCHEMA dbuser2; We change the owner of the schema: .. code:: sql # ALTER SCHEMA dbuser2 OWNER TO dbuser2; Each database user must have this `search_path` (PostgreSQL default): .. code:: sql # SHOW search_path; search_path ---------------- "$user",public (1 row) Tryton configuration -------------------- We must create a configuration file per instance with each one having the right database uri. For example: `uri=postgresql://dbuser1@/` and `uri=postgresql://dbuser2@/` (see `Configuration `_). We must also make them listen on different host or port. Initialize the first instance database -------------------------------------- We use the default command line `setup procedure `_ using the `dbuser1` configuration file: .. code:: sh $ trytond -c trytond1.conf -d trytondb --all $ trytond -c trytond1.conf -d trytondb -u party country Once the initialization is done, we have to change the schema of the table we want to share to `public`: .. code:: sql # ALTER TABLE dbuser1.party_party SET SCHEMA public; # ALTER TABLE dbuser1.party_party_id_seq SET SCHEMA public; # ALTER TABLE dbuser1.party_address SET SCHEMA public; # ALTER TABLE dbuser1.party_address_id_seq SET SCHEMA public; # ALTER TABLE dbuser1.party_contact_mechanism SET SCHEMA public; # ALTER TABLE dbuser1.party_contact_mechanism_id_seq SET SCHEMA public; # ALTER TABLE dbuser1.country_country SET SCHEMA public; # ALTER TABLE dbuser1.country_country_id_seq SET SCHEMA public; # ALTER TABLE dbuser1.country_subdivision SET SCHEMA public; # ALTER TABLE dbuser1.country_subdivision_id_seq SET SCHEMA public; # ALTER TABLE dbuser1.country_zip SET SCHEMA public; # ALTER TABLE dbuser1.country_zip_id_seq SET SCHEMA public; We must also grant access on those table to `public`: .. code:: sql # GRANT ALL ON public.party_party TO public; # GRANT ALL ON public.party_party_id_seq TO public; # GRANT ALL ON public.party_address TO public; # GRANT ALL ON public.party_address_id_seq TO public; # GRANT ALL ON public.party_contact_mechanism TO public; # GRANT ALL ON public.party_contact_mechanism_id_seq TO public; # GRANT ALL ON public.country_country TO public; # GRANT ALL ON public.country_country_id_seq TO public; # GRANT ALL ON public.country_subdivision TO public; # GRANT ALL ON public.country_subdivision_id_seq TO public; # GRANT ALL ON public.country_zip TO public; # GRANT ALL ON public.country_zip_id_seq TO public; Initialize the second instance database --------------------------------------- First, we initialize the base modules: .. code:: sh $ trytond -c trytond2.conf -d trytondb --all Before installing the modules we must tell to Tryton that the XML data for the shared tables are already created (using postgres user): .. code:: sql # INSERT INTO dbuser2.ir_model_data (create_uid, noupdate, db_id, create_date, write_date, fs_values, module, write_uid, values, fs_id, model) SELECT create_uid, noupdate, db_id, create_date, write_date, fs_values, module, write_uid, values, fs_id, model FROM dbuser1.ir_model_data WHERE model IN ('party.party', 'party.address', 'party.contact_mechanism', 'country.country', 'country.subdivision', 'country.zip'); Now we can initialize the modules: .. code:: sh $ trytond -c trytond2.conf -d trytondb -u party country Party configuration ------------------- As there is a unique constraint on the code of party, we must ensure that the sequence used on each configuration will not generate the same code. So we just put '1-' and '2-' as prefix of the party code sequence. Usage ===== Now we can connect to each instance. If we create a party on one instance, it will be automatically available on the other instance. Update/Upgrade ============== We must always process with the `dbuser1` and after reset the `ir_model_data` of `dbuser2` before processing: .. code:: sh $ trytond -c trytond1.conf -d trytondb --all .. code:: sql # DELETE FROM dbuser2.ir_model_data WHERE model IN ('party.party', 'party.address', 'party.contact_mechanism', 'country.country', 'country.subdivision', 'country.zip'); # INSERT INTO dbuser2.ir_model_data (create_uid, noupdate, db_id, create_date, write_date, fs_values, module, write_uid, values, fs_id, model) SELECT create_uid, noupdate, db_id, create_date, write_date, fs_values, module, write_uid, values, fs_id, model FROM dbuser1.ir_model_data WHERE model IN ('party.party', 'party.address', 'party.contact_mechanism', 'country.country', 'country.subdivision', 'country.zip'); .. code:: sh $ trytond -c trytond2.conf -d trytondb --all