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=5432 dbname=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=5432 dbname=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=5432 dbname=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.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
