Pglogical questions and problems - Mailing list pgsql-hackers

Hello,

Alright -- here we go PostgreSQL 9.5.2, Ubuntu Trusty, All packages from 
apt.postgresql.org except PgLogical which is from 2Q:

I have the following setup:

Origin0->Replica0 Table: logical_test(id bigserial primary key)

Origin0:

SELECT pglogical.create_node(  node_name := 'origin',  dsn := 'host=192.168.1.65 port=5432 dbname=logical'
);

CREATE TABLE logical_test (id bigserial primary key);

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

Replica0:

SELECT pglogical.create_node(  node_name := 'replica0',  dsn := 'host=192.168.1.66 port=5432 dbname=logical'
);

SELECT pglogical.create_subscription(  subscription_name := 'replica0relations',  provider_dsn := 'host=192.168.1.65
port=5432dbname=logical',  synchronize_structure := TRUE,  synchronize_data := TRUE
 
);

Replicating from Origin0->Replica0 works. I then added Replica1:

SELECT pglogical.create_node(  node_name := 'replica2',  dsn := 'host=192.168.1.67 port=5432 dbname=logical'
);

SELECT pglogical.create_subscription(  subscription_name := 'logical_subscriber2',  provider_dsn := 'host=192.168.1.66
port=5432dbname=logical',  synchronize_data := TRUE,  synchronize_structure := TRUE
 
);

The initial sync works, I end up with the table and all rows. However if 
I perform an update and add or modify rows, only the origin and replica0 
update. Replica1 provides the following:

2016-04-12 15:38:10 PDT [25712-2] ERROR:  cache lookup failed for 
replication origin 'pgl_logical_origin_replica084e3989'
2016-04-12 15:38:10 PDT [1192-89105] LOG:  worker process: pglogical 
apply 16384:1108649370 (PID 25712) exited with exit code 1

And continues to provide this rather non-useful message continuously in 
a loop.

I tried dropping the subscription:

logical=# select pglogical.drop_subscription(subscription_name := 
'logical_subscriber2'); drop_subscription
-------------------                 1

And dropping the tables:

logical=# drop table logical_test;
DROP TABLE

logical=# \d
No relations found.

Then add the subscription:

SELECT pglogical.create_subscription(  subscription_name := 'logical_subscriber2',  provider_dsn := 'host=192.168.1.66
port=5432dbname=logical',  synchronize_data := TRUE,  synchronize_structure := TRUE
 
);

logical=# \d                 List of relations Schema |        Name         |   Type   |  Owner
--------+---------------------+----------+---------- public | logical_test        | table    | postgres public |
logical_test_id_seq| sequence | postgres
 
(2 rows)

logical=# select count(*) from logical_test; count
-------  1100
(1 row)

That is accurate but if I try to add rows from the Origin:

logical=# truncate logical_test;
TRUNCATE TABLE
logical=# select count(*) from logical_test; count
-------     0
(1 row)

Replica0:

logical=# select count(*) from logical_test; count
-------     0
(1 row)

Replica2:

logical=# select count(*) from logical_test; count
-------  1100
(1 row)

Replica2 log:

2016-04-12 15:43:39 PDT [4881-1] LOG:  starting apply for subscription 
logical_subscriber2
2016-04-12 15:43:39 PDT [4881-2] ERROR:  cache lookup failed for 
replication origin 'pgl_logical_origin_replica084e3989'
2016-04-12 15:43:39 PDT [1192-100644] LOG:  worker process: pglogical 
apply 16384:1108649370 (PID 4881) exited with exit code 1

So what am I missing?

Origin pg_hba.conf:

hostssl  replication    postgres    192.168.1.66/32    md5
hostssl     logical    postgres    192.168.1.66/32    md5

Replica0 pg_hba.conf:

hostssl     logical     postgres     127.0.0.1/32        md5
hostssl  logical     postgres     192.168.1.66/32    md5
hostssl  logical     postgres     192.168.1.67/32    md5
hostssl     replication     postgres     192.168.1.67/32    md5

Replica2 pg_hba.conf:

hostssl     logical     postgres     127.0.0.1/32        md5
hostssl     logical     postgres     192.168.1.66/32    md5
hostssl  logical     postgres     192.168.1.67/32    md5
hostssl  replication     postgres        192.168.1.67/32        md5

All auth is done via .pgpass.

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.