PostgreSQL: difficulties with basic pglogical configuration

Question

I find official pglogical documentation very confusing. There are too many things that are not clear. Perhaps someone who configured pglogical before could explain how to setup a basic logical replication.

There are two PostgreSQL 9.5 instances - 10.128.0.8 (archlinux1) and 10.128.0.9 (archlinux2). Extension is already installed, CREATE EXTENSION succeeded. There is a table on each instance:

create table test (k text primary key, v text);

I would like to replicate it from archlinux1 to archlinux2.

According to the documentation I should create a provider node:

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=providerhost port=5432 dbname=db'
);

Should it be executed on master? Should providerhost be 127.0.0.1 or 10.128.0.8? Currenlty replication is allowed only from localhost (accordingly to docs) - should it be changed? My best guess - it should be executed on master like this:

SELECT pglogical.create_node(
    node_name := 'provider1',
    dsn := 'host=127.0.0.1 port=5432 dbname=eax'
);
 create_node 
-------------
  2976894835
(1 row)

Next:

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

Should it be executed on master, replica, or both? My best guess - only on master:

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);
 replication_set_add_all_tables 
--------------------------------
 t
(1 row)

Next:

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=thishost port=5432 dbname=db'
);

Apparently it should be executed on replica:

SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=127.0.0.1 port=5432 dbname=eax'
);
 create_node 
-------------
   330520249
(1 row)

Next step:

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=providerhost port=5432 dbname=db'
);

Best guess - execute it on replica like this:

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=10.128.0.8 port=5432 dbname=eax'
);
ERROR:  could not connect to the postgresql server: could not connect to server: Connection refused
    Is the server running on host "10.128.0.8" and accepting
    TCP/IP connections on port 5432?

DETAIL:  dsn was:  host=10.128.0.8 port=5432 dbname=eax

Oops. OK, modifying pg_hba.conf and postgresql.conf properly on master:

# pg_hba.conf
host    all    all    10.128.0.0/16    md5
# postgresql.conf
listen_addresses = 'localhost,10.128.0.8

Still no luck:

# SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=10.128.0.8 port=5432 dbname=eax user=eax password=qwerty'
);
ERROR:  could not connect to the postgresql server in replication mode: FATAL:  no pg_hba.conf entry for replication connection from host "10.128.0.9", user "eax", SSL off

DETAIL:  dsn was:  host=10.128.0.8 port=5432 dbname=eax user=eax password=qwerty

Adding to pg_hba.conf on master:

host    replication     eax        10.128.0.0/16            md5

On replica (success!):

SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=10.128.0.8 port=5432 dbname=eax user=eax password=qwerty'
);
 create_subscription 
---------------------
          1763399739
(1 row)

Now on master:

eax=# insert into test values ('aaa', 'bbb');
INSERT 0 1
eax=# select * from test;
  k  |  v  
-----+-----
 aaa | bbb
(1 row)

On replica:

eax=# select * from test;
 k | v 
---+---
(0 rows)

Naturally it didn't work. Nothing helpful in logs. Any advice?

UPD: I also created a corresponding issue in pglogical issue tracker.

Bowerbird

Answer 1

Depesz published an excellent blog post about pglogical configuration https://www.depesz.com/2016/11/08/major-version-upgrading-with-minimal-downtime/

Bowerbird

Answer 2

Perhaps this is a better guide: http://bonesmoses.org/2016/10/14/pg-phriday-perfectly-logical/ - at least it tells you which nodes to execute each command on.

Sapodilla

Answer 3

When creating nodes you need to specify an external IP (not localhost)

On the provider

SELECT pglogical.create_node(
node_name := 'provider1',
dsn := 'host=subscriberhost port=5432 dbname=db'
);

On the subscriber

SELECT pglogical.create_node(
node_name := 'subscriber1',
dsn := 'host=providerhost port=5432 dbname=eax'
);

Kathaleenkatharevusa

If you have to use suscriberhost when creating node on provider, I would guess then that you'll have to create a node for each suscriber. What am I missing? – Mattie