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@xxxxxxxxx>
To: "Anjul Tyagi" <anjul@xxxxxxxxxxxxxxxx>
Cc: "Alvaro Aguayo Garcia-Rada" <aaguayo@xxxxxxxxxxxxxxx>,
"pgsql-admin" <pgsql-admin@xxxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxx>
wrote:
is there any licensing cost involve for pglogical?
Regards,
Anjul *TYAGI*
*ü* *Go Green*
------ Original Message ------
From: "Alvaro Aguayo Garcia-Rada" <aaguayo@xxxxxxxxxxxxxxx>
To: "arvind chikne" <arvind.chikne@xxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxx> 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
