Re: [MASSMAIL]Re: slony replication - Mailing list 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
>> 
>>