Re: slony replication - Mailing list pgsql-admin

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
alltheir 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
adifferent 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
connectfrom 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
whichcan 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
initialsync all alone, but I've never got to make it work; maybe I was doing something wrong. At this point, remember
allstructures 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
homefolder. 

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
thiscould 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
kindof log table; that makes them write more data when you execute a DML on a replicated table. pglogical replicated
WALsegments, 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
thisway: 

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
PROCEDUREocs_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
partor 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
>
>


--
*Thanks & Regards*
Arvind rao chikne
9818979986
*Think Green!!!* Please consider your environmental responsibility before
printing this e-mail