Re: pglogical cascading replication (chaining replication)

From: Nick Babadzhanian <nb(at)cobra(dot)ru>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: pglogical cascading replication (chaining replication)
Date: 2016-07-13 16:08:53
Message-ID: 631895653.33412.1468426133393.JavaMail.zimbra@cobra.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)cobra(dot)ru>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)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).

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2016-07-13 16:16:30 Re: pglogical cascading replication (chaining replication)
Previous Message Kevin Brannen 2016-07-13 15:41:56 Trouble starting Postgresql after an upgrade