Обсуждение: 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).
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).
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.
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.
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
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.