pglogical cascading replication (chaining replication)
I apologize if this is wrong place to ask the question.
A quote from pglogical FAQ:
Q. Does pglogical support cascaded replication?
Subscribers can be configured as publishers as well thus cascaded replication can be achieved
by forwarding/chaining (again no failover though).
The only mentions of forwarding on documentation page are:
Cascading replication is implemented in the form of changeset forwarding.
forward_origins - array of origin names to forward, currently only supported values are empty
array meaning don't forward any changes that didn't originate on provider node, or "{all}"
which means replicate all changes no matter what is their origin, default is "{all}"
So my question is how to forward changeset using pglogical?
Here's my setup:
There are 3 identical CentOS 7 servers: p1, p2 and p3. Postgres version is 9.5.3.
p1:
select pglogical.create_node
(
node_name := 'node_p1_provider',
dsn := 'host=192.168.1.101 port=5432 dbname=test'
);
select pglogical.replication_set_add_all_tables('default_insert_only', array['public']);
p2:
select pglogical.create_node(
node_name := 'node_p2_provider_and_subscriber',
dsn := 'host=192.168.1.102 port=5432 dbname=test'
);
select pglogical.replication_set_add_all_tables('default_insert_only', array['public']);
select pglogical.create_subscription
(
subscription_name => 'sub_p1_to_p2_insert_only',
provider_dsn => 'host=192.168.1.101 port=5432 dbname=test',
replication_sets => array['default_insert_only']
);
p3:
select pglogical.create_node(
node_name := 'node_p3_subscriber',
dsn := 'host=192.168.1.103 port=5432 dbname=test'
);
select pglogical.create_subscription
(
subscription_name => 'sub_p2_to_p3_insert_only',
provider_dsn => 'host=192.168.1.102 port=5432 dbname=test',
replication_sets => array['default_insert_only']
);
Result:
p1:
insert into public.test (col1) values (1);
select count(1) from public.test; -- returns 1;
p2:
insert into public.test (col1) values (2);
select count(1) from public.test; -- returns 2;
p3:
select count(1) from public.test; -- returns 1;
Expected:
p3 recieves all inserts, thus the count on p3 should be 2 (same as on p2).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
After checking logs I noticed this error on p3-node:
ERROR: cache lookup failed for replication origin 'pgl_test_node_p1_provider_sub_p1_t06410f8'
pgl_test_node_p1_provider_sub_p1_t06410f8 is origin for p1-node.
Here are the logs from all three server (this happens every time I insert something into p1 table):
p1:
LOG: starting pglogical supervisor
LOG: starting pglogical database manager for database test
LOG: starting logical decoding for slot "pgl_test_node_p1_provider_sub_p1_t06410f8"
DETAIL: streaming transactions committing after 0/3BDBFD0, reading WAL from 0/3BDBFD0
LOG: logical decoding found consistent point at 0/3BDBFD0
DETAIL: There are no running transactions.
p2:
LOG: starting pglogical supervisor
LOG: starting pglogical database manager for database test
LOG: starting apply for subscription sub_p1_to_p2_insert_only
LOG: starting logical decoding for slot "pgl_test_node_p2_9face77_sub_p2_t6fd19a3"
DETAIL: streaming transactions committing after 0/35DD958, reading WAL from 0/35DD958
LOG: logical decoding found consistent point at 0/35DD958
DETAIL: There are no running transactions.
LOG: starting logical decoding for slot "pgl_test_node_p2_9face77_sub_p2_t6fd19a3"
DETAIL: streaming transactions committing after 0/35DDA38, reading WAL from 0/35DDA00
LOG: logical decoding found consistent point at 0/35DDA00
DETAIL: There are no running transactions.
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on standby connection
LOG: starting logical decoding for slot "pgl_test_node_p2_9face77_sub_p2_t6fd19a3"
DETAIL: streaming transactions committing after 0/35DDA38, reading WAL from 0/35DDA00
LOG: logical decoding found consistent point at 0/35DDA00
DETAIL: There are no running transactions.
LOG: could not receive data from client: Connection reset by peer
LOG: unexpected EOF on standby connection
p3:
LOG: starting pglogical supervisor
LOG: starting pglogical database manager for database test
LOG: starting apply for subscription sub_p2_to_p3_insert_only
ERROR: cache lookup failed for replication origin 'pgl_test_node_p1_provider_sub_p1_t06410f8'
LOG: worker process: pglogical apply 13294:1876007473 (PID 14180) exited with exit code 1
LOG: starting apply for subscription sub_p2_to_p3_insert_only
ERROR: cache lookup failed for replication origin 'pgl_test_node_p1_provider_sub_p1_t06410f8'
LOG: worker process: pglogical apply 13294:1876007473 (PID 14189) exited with exit code 1
----- Original Message -----
From: "Nick Babadzhanian" <nb@cobra.ru>
To: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Tuesday, July 12, 2016 5:20:59 PM
Subject: pglogical cascading replication (chaining replication)
I apologize if this is wrong place to ask the question.
A quote from pglogical FAQ:
Q. Does pglogical support cascaded replication?
Subscribers can be configured as publishers as well thus cascaded replication can be achieved
by forwarding/chaining (again no failover though).
The only mentions of forwarding on documentation page are:
Cascading replication is implemented in the form of changeset forwarding.
forward_origins - array of origin names to forward, currently only supported values are empty
array meaning don't forward any changes that didn't originate on provider node, or "{all}"
which means replicate all changes no matter what is their origin, default is "{all}"
So my question is how to forward changeset using pglogical?
Here's my setup:
There are 3 identical CentOS 7 servers: p1, p2 and p3. Postgres version is 9.5.3.
p1:
select pglogical.create_node
(
node_name := 'node_p1_provider',
dsn := 'host=192.168.1.101 port=5432 dbname=test'
);
select pglogical.replication_set_add_all_tables('default_insert_only', array['public']);
p2:
select pglogical.create_node(
node_name := 'node_p2_provider_and_subscriber',
dsn := 'host=192.168.1.102 port=5432 dbname=test'
);
select pglogical.replication_set_add_all_tables('default_insert_only', array['public']);
select pglogical.create_subscription
(
subscription_name => 'sub_p1_to_p2_insert_only',
provider_dsn => 'host=192.168.1.101 port=5432 dbname=test',
replication_sets => array['default_insert_only']
);
p3:
select pglogical.create_node(
node_name := 'node_p3_subscriber',
dsn := 'host=192.168.1.103 port=5432 dbname=test'
);
select pglogical.create_subscription
(
subscription_name => 'sub_p2_to_p3_insert_only',
provider_dsn => 'host=192.168.1.102 port=5432 dbname=test',
replication_sets => array['default_insert_only']
);
Result:
p1:
insert into public.test (col1) values (1);
select count(1) from public.test; -- returns 1;
p2:
insert into public.test (col1) values (2);
select count(1) from public.test; -- returns 2;
p3:
select count(1) from public.test; -- returns 1;
Expected:
p3 recieves all inserts, thus the count on p3 should be 2 (same as on p2).
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 07/12/2016 07:20 AM, Nick Babadzhanian wrote:
I apologize if this is wrong place to ask the question.
A quote from pglogical FAQ:
Q. Does pglogical support cascaded replication?
Subscribers can be configured as publishers as well thus cascaded replication can be achieved
by forwarding/chaining (again no failover though).The only mentions of forwarding on documentation page are:
Cascading replication is implemented in the form of changeset forwarding.
forward_origins - array of origin names to forward, currently only supported values are empty
array meaning don't forward any changes that didn't originate on provider node, or "{all}"
which means replicate all changes no matter what is their origin, default is "{all}"So my question is how to forward changeset using pglogical?
That's a great question. I have tried to figure out that information as
well. Unfortunately it appears that it is locked up in 2ndQuadrant's
head. I suggest contacting them.
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I already tried to contact them, but no luck so far.
Although it seems to me that the change-set is actually forwarded, its the decoding that is failing on the receiving host. Check the log output for p2 and p3 in my previous message.
Regards,
Nick.
----- Original Message -----
From: "Joshua D. Drake" <jd@commandprompt.com>
To: "Nick Babadzhanian" <nb@cobra.ru>, "pgsql-general" <pgsql-general@postgresql.org>
Sent: Wednesday, July 13, 2016 7:16:30 PM
Subject: Re: [GENERAL] pglogical cascading replication (chaining replication)
On 07/12/2016 07:20 AM, Nick Babadzhanian wrote:
I apologize if this is wrong place to ask the question.
A quote from pglogical FAQ:
Q. Does pglogical support cascaded replication?
Subscribers can be configured as publishers as well thus cascaded replication can be achieved
by forwarding/chaining (again no failover though).The only mentions of forwarding on documentation page are:
Cascading replication is implemented in the form of changeset forwarding.
forward_origins - array of origin names to forward, currently only supported values are empty
array meaning don't forward any changes that didn't originate on provider node, or "{all}"
which means replicate all changes no matter what is their origin, default is "{all}"So my question is how to forward changeset using pglogical?
That's a great question. I have tried to figure out that information as
well. Unfortunately it appears that it is locked up in 2ndQuadrant's
head. I suggest contacting them.
Sincerely,
JD
--
Command Prompt, Inc. http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
The solution was found thanks to Petr Jelinek from 2ndQ.
Cascading wasn't much of a priority so far.
Currently you have to create the origin manually using pg_replication_origin_create().
I plan to make this work seamlessly in the future release.
So whats needed to be done is:
on p1:
Discussed here:
https://github.com/2ndQuadrant/pglogical/issues/23
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
The solution was found thanks to Petr Jelinek from 2ndQ.
Cascading wasn't much of a priority so far.
Currently you have to create the origin manually using pg_replication_origin_create().
I plan to make this work seamlessly in the future release.
So whats needed to be done is:
on p2:
select * from pg_replication_origin;
will show all origins on p2, find the origin for p1;
on p3:
select pg_replication_origin_create('[origin name]');
Discussed here:
https://github.com/2ndQuadrant/pglogical/issues/23
Please ignore previous message;
Regards,
Nick.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general