Re: [MASSMAIL]Re: slony replication
От | gilberto.castillo@etecsa.cu |
---|---|
Тема | Re: [MASSMAIL]Re: slony replication |
Дата | |
Msg-id | 053857460044aaf65c81dc1ee1088354@etecsa.cu обсуждение исходный текст |
Ответ на | Re: slony replication (Alvaro Aguayo Garcia-Rada <aaguayo@opensysperu.com>) |
Список | pgsql-admin |
Please, He looking for support professional. I'm recomendation. El 2018-05-28 12:59, Alvaro Aguayo Garcia-Rada escribió: > Hi. Sorry for the delay. I had to check my notes from last pglogical > setup in one of our customers. > > Just to understand, this customer has several locations, and need to > replicate products, prices, and customers between all their locations. > Some locations may only need to have products and prices, others may > also need the customers data. > > Ths customer has a (supposedly) high speed VPN with their ISP. We use > the postrges user, but it's not a bad idea to use a different > user(with superuser privilege). Always with a password, of course. > > passwords always as ******. Some parameters, such as port, database > name, and other, may be replaced as needed. > > Please remembers this, more than being a tutorial, is just the notes > from last implementation. > > Let's say we have the following setup: > > Master Node: Name 'mainserver' IP 10.0.0.11 > First Slave: Name 'slave1' IP 10.0.1.1 > > First of all, in the postgresql.conf file for either master or slave, > change the following parameters: > max_wal_senders = 10 > max_replication_slots = 10 > track_commit_timestamp = on > wal_level = logical > > You may need to restart postgres for thi changes to take effect. > > In the master node, in psql as postgres user: > > CREATE EXTENSION pglogical; > SELECT pglogical.create_node('mainserver','host=10.0.0.11 port=5432 > dbname=salesdb user=postgres password=*******'); > SELECT > pglogical.create_replication_set('products',true,true,true,true); > SELECT > pglogical.replication_set_add_table('products','public.products',true); > SELECT > pglogical.replication_set_add_table('products','public.productlink',true); > SELECT > pglogical.replication_set_add_table('products','public.pricelist',true); > SELECT > pglogical.replication_set_add_table('products','public.price',true); > SELECT > pglogical.replication_set_add_table('products','public.provider',true); > SELECT > pglogical.replication_set_add_table('products','public.location',true); > SELECT > pglogical.create_replication_set('customers',true,true,true,true); > SELECT > pglogical.replication_set_add_table('customers','public.customers',true); > SELECT > pglogical.replication_set_add_table('customers','public.customercard',true); > > ** Now, very important. The DSN for the master(second arg in > create_node) is the EXTERNAL DSN. So you must be able to connect from > the slave to the master with such parameters. pg_hba entries may be > added as needed. This differs from slony, where the slon processes, > usually in the master, will connect to each slave. > > In this case, the following pg_hba entries must be added on the master: > host all all 10.0.0.0/8 md5 > host replication all 10.0.0.0/8 md5 > Of course, in order to achieve better security, this could be changed > by more rules, to reduce the number of hosts which can connect. > > Again, on the master, but this time in a shell as postgres user: > > pg_dump -a -t products -t productlink -t pricelist -t price -t > provider -t location salesdb > products20180528.sql > pg_dump -a -t customers -t customercard salesdb > customers20180528.sql > > This files are generated in order to restore them on the slaves as an > initial sync. pglogical can also perform the initial sync all alone, > but I've never got to make it work; maybe I was doing something wrong. > At this point, remember all structures in the slaves must be the same > as the master, or, at least, compatible with. > > Now, on the slave. First, transfer the .sql files generated on the > previous step to the slave, in the postgres user home folder. > > Then, on psql, as postgres user: > > SET session_replication_role = replica; > BEGIN; > DELETE FROM products; > DELETE FROM productlink; > DELETE FROM pricelist; > DELETE FROM price; > DELETE FROM provider; > DELETE FROM location; > \i products20180528.sql > DELETE FROM customers; > DELETE FROM customercard; > \i customers20180528.sql > COMMIT; > SET session_replication_role = DEFAULT; > CREATE EXTENSION pglogical; > SELECT pglogical.create_node('slave1','host=10.0.1.1 port=5432 > dbname=salesdb user=postgres password=********'); > SELECT pglogical.create_subscription('slave1_default','host=10.0.0.11 > port=5432 dbname=salesdb user=postgres > password=*******',ARRAY['products','customers'],false,false); > > *** To understand, the "session_replication_role" is used to perform > DELETE even if there are foreign keys. beware that this could lead to > inconsistent foreign keys. > As with the master, here, in create_node, the EXTERNAL DSN must be > used. > > after this, replication should start. As an advantage to other > replicators for postgresql, pglogical runs fully inside postgres, so > it will launch its own worker processed. Thus, there is no need to > start and/or monitor any external process. > > Of course, there is the performance advantage. Other replication > solutions for postgres are trigger based, with some kind of log table; > that makes them write more data when you execute a DML on a replicated > table. pglogical replicated WAL segments, thus doesn't need any > additional write. > > The bad part: By default, pglogical will nos restrict DML on > replicated tables. If you want that, you can achieve it this way: > > CREATE OR REPLACE FUNCTION ocs_dml_deny() RETURNS trigger LANGUAGE > plpgsql AS $BODY$ BEGIN RAISE NOTICE 'Disallowing % on % because of > logical replication',TG_OP,TG_TABLE_NAME; RETURN NULL; END; $BODY$; > CREATE TRIGGER ocs_products_dml_deny BEFORE INSERT OR DELETE OR UPDATE > ON products FOR EACH ROW EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_products_truncate_deny BEFORE TRUNCATE ON products > FOR EACH STATEMENT EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_productlink_dml_deny BEFORE INSERT OR DELETE OR > UPDATE ON productlink FOR EACH ROW EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_productlink_truncate_deny BEFORE TRUNCATE ON > productlink FOR EACH STATEMENT EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_pricelist_dml_deny BEFORE INSERT OR DELETE OR > UPDATE ON pricelist FOR EACH ROW EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_pricelist_truncate_deny BEFORE TRUNCATE ON > pricelist FOR EACH STATEMENT EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_price_dml_deny BEFORE INSERT OR DELETE OR UPDATE ON > price FOR EACH ROW EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_price_truncate_deny BEFORE TRUNCATE ON price FOR > EACH STATEMENT EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_provider_dml_deny BEFORE INSERT OR DELETE OR UPDATE > ON provider FOR EACH ROW EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_provider_truncate_deny BEFORE TRUNCATE ON provider > FOR EACH STATEMENT EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_location_dml_deny BEFORE INSERT OR DELETE OR UPDATE > ON location FOR EACH ROW EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_location_truncate_deny BEFORE TRUNCATE ON location > FOR EACH STATEMENT EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_customers_dml_deny BEFORE INSERT OR DELETE OR > UPDATE ON customers FOR EACH ROW EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_customers_truncate_deny BEFORE TRUNCATE ON > customers FOR EACH STATEMENT EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_customercard_dml_deny BEFORE INSERT OR DELETE OR > UPDATE ON customercard FOR EACH ROW EXECUTE PROCEDURE ocs_dml_deny(); > CREATE TRIGGER ocs_customercard_truncate_deny BEFORE TRUNCATE ON > customercard FOR EACH STATEMENT EXECUTE PROCEDURE ocs_dml_deny(); > > Hope this helps you with setting up pglogical. Sorry if it's not more > like a tutorial. If you don't understand some part or need additional > information, feel free to contact me. > > Regards, > > Alvaro Aguayo > Operations Manager > Open Comb Systems E.I.R.L. > > Office: (+51-1) 3377813 | Mobile: (+51) 995540103 | (+51) 954183248 > Web: www.ocs.pe > > ----- Original Message ----- > From: "arvind chikne" <arvind.chikne@gmail.com> > To: "Anjul Tyagi" <anjul@ibosstech-us.com> > Cc: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com>, > "pgsql-admin" <pgsql-admin@postgresql.org> > Sent: Monday, 28 May, 2018 06:50:28 > Subject: Re: slony replication > > Hi Alvaro, > > Sounds good, it would be great if you can share a step by step doc to > configure the slony for master slave with 2 different location > > > On Mon, May 28, 2018 at 5:19 PM, Anjul Tyagi <anjul@ibosstech-us.com> > wrote: > >> is there any licensing cost involve for pglogical? >> >> >> >> >> >> Regards, >> >> Anjul *TYAGI* >> >> >> >> *ü* *Go Green* >> >> ------ Original Message ------ >> From: "Alvaro Aguayo Garcia-Rada" <aaguayo@opensysperu.com> >> To: "arvind chikne" <arvind.chikne@gmail.com> >> Cc: pgsql-admin@postgresql.org >> Sent: 28-05-2018 17:16:13 >> Subject: Re: slony replication >> >> Hi. >> >> Take a look here: https://www.2ndquadrant.com/en/resources/pglogical/ >> pglogical-docs/ >> >> It's pretty easy to setup, but, if you have any trouble, feel free to >> contact me, as I have some experience with pglogical(as well as >> slony). >> >> Regards, >> >> Alvaro Aguayo >> Operations Manager >> Open Comb Systems E.I.R.L. >> >> Oficina: (+51-1) 3377813 | Cel.: (+51) 995540103 | RPC: (+51) >> 954183248 >> Website: www.ocs.pe >> >> Sent from my Sony Xperia™ smartphone >> >> ---- arvind chikne wrote ---- >> >> >> Hi Alvaro, >> >> Thanks for your quick response, >> >> Actually I have a project in which I have to use slony for >> replication, I >> would be really great if you can share any hands on document to >> configure >> it >> my O/S is ubuntu 16.04 and postgres 9.5 >> >> >> On Mon, May 28, 2018 at 3:16 PM, Alvaro Aguayo Garcia-Rada < >> aaguayo@opensysperu.com> wrote: >> >>> Hi. Any specific reason to use slony? >>> >>> Based on my experience, I would recommend pglogical. It's as easy to >>> configure, and has no need for external processes. >>> >>> Regards, >>> >>> Alvaro Aguayo >>> Operations Manager >>> Open Comb Systems E.I.R.L. >>> >>> Oficina: (+51-1) 3377813 | Cel.: (+51) 995540103 | RPC: (+51) >>> 954183248 >>> Website: www.ocs.pe >>> >>> Sent from my Sony Xperia™ smartphone >>> >>> ---- arvind chikne wrote ---- >>> >>> >>> >>> Hi All, >>> >>> Does any one used slony for replication ... ??, >>> >>> -- >>> *Thanks & Regards* >>> Arvind rao chikne >>> 9818979986 >>> *Think Green!!!* Please consider your environmental responsibility >>> before >>> printing this e-mail >>> >> >> >> >> -- >> *Thanks & Regards* >> Arvind rao chikne >> 9818979986 >> *Think Green!!!* Please consider your environmental responsibility >> before >> printing this e-mail >> >>
В списке pgsql-admin по дате отправления: