Обсуждение: slony replication

Поиск
Список
Период
Сортировка

slony replication

От
arvind chikne
Дата:
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

RE: slony replication

От
Alvaro Aguayo Garcia-Rada
Дата:
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-13377813 | 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

Re: slony replication

От
arvind chikne
Дата:
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

Re: slony replication

От
Shreeyansh Dba
Дата:
Hi Arvind,

Any specific reason to use slony?


We would recommend  streaming  asynchronous/synchronous forward backward  replication.

It's easy to configure along with the pgpool if having any pgpool setup.




On Mon, May 28, 2018 at 3:10 PM, arvind chikne <arvind.chikne@gmail.com> 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


Re: slony replication

От
arvind chikne
Дата:
Hi Shreeyansh,

Its just for the R&D purpose, If you have any good document pls share  

On Mon, May 28, 2018 at 3:31 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Arvind,

Any specific reason to use slony?


We would recommend  streaming  asynchronous/synchronous forward backward  replication.

It's easy to configure along with the pgpool if having any pgpool setup.




On Mon, May 28, 2018 at 3:10 PM, arvind chikne <arvind.chikne@gmail.com> 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

Re: slony replication

От
Shreeyansh Dba
Дата:
Hi Arvind,

Go through the below link..

http://www.slony.info/documentation/2.5/tutorial.html

Hope this helps..



On Mon, May 28, 2018 at 3:35 PM, arvind chikne <arvind.chikne@gmail.com> wrote:
Hi Shreeyansh,

Its just for the R&D purpose, If you have any good document pls share  

On Mon, May 28, 2018 at 3:31 PM, Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
Hi Arvind,

Any specific reason to use slony?


We would recommend  streaming  asynchronous/synchronous forward backward  replication.

It's easy to configure along with the pgpool if having any pgpool setup.




On Mon, May 28, 2018 at 3:10 PM, arvind chikne <arvind.chikne@gmail.com> 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


Re: slony replication

От
Alvaro Aguayo Garcia-Rada
Дата:
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-13377813 | 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

Re: slony replication

От
"Anjul Tyagi"
Дата:
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>
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-13377813 | 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

Re: slony replication

От
arvind chikne
Дата:
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>
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-13377813 | 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

Re: slony replication

От
Alvaro Aguayo Garcia-Rada
Дата:
Hi. No, it's free. Guys at 2ndQuadrant offer commercial support if you need it.

Saludos,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-13377813 | Cel.: (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone

---- Anjul Tyagi 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>
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-13377813 | 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

Re: slony replication

От
Om Prakash Jaiswal
Дата:
Hi,
Good tool is Bucardo asynchronous replication.
I am using it.
You can also try SymmetricDS.
It is also very good.

Regards
Om Prakash


On Mon, May 28, 2018 at 17:24, Alvaro Aguayo Garcia-Rada
<aaguayo@opensysperu.com> wrote:
Hi. No, it's free. Guys at 2ndQuadrant offer commercial support if you need it.

Saludos,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

Oficina: (+51-13377813 | Cel.: (+51) 995540103 | RPC: (+51) 954183248
Website: www.ocs.pe

Sent from my Sony Xperia™ smartphone

---- Anjul Tyagi 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>
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-13377813 | 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

Re: slony replication

От
Alvaro Aguayo Garcia-Rada
Дата:
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


Re: [MASSMAIL]Re: slony replication

От
gilberto.castillo@etecsa.cu
Дата:
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
>> 
>> 


Re: slony replication

От
SAS
Дата:
Le 28/05/2018 à 11:40, arvind chikne a écrit :
Hi All, 

Does any one used slony for replication ... ??,  


Hello,

Slony has had great times by the past. It is rock-solid, but needs you to configure it in much details than you would with internal streaming replication.

You could also have a look at tools that can help you configure it much more easily, like slony_ctl, for instance.

There is still a version on pgfoundry : http://pgfoundry.org/projects/slony1-ctl/

Best,
--
Dr Stéphane Schildknecht
Contact régional PostgreSQL pour l'Europe francophone
+33 617 11 37 42