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:

$ 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:

$ createdb --encoding=utf-8 --owner=dbuser1 --username=dbuser1 trytondb

And give access to dbuser2:

# 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:

# CREATE SCHEMA dbuser1;
# CREATE SCHEMA dbuser2;

We change the owner of the schema:

# ALTER SCHEMA dbuser2 OWNER TO dbuser2;

Each database user must have this search_path (PostgreSQL default):

# 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:

$ 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:

# 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:

# 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:

$ 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):

# 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:

$ 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:

$ trytond -c trytond1.conf -d trytondb --all
# 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');
$ trytond -c trytond2.conf -d trytondb --all