Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PostgreSQL: difficulties with basic pglogical configuration

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.

like image 257
Aleksander Alekseev Avatar asked Oct 31 '16 15:10

Aleksander Alekseev


People also ask

What is Pglogical?

pglogical is an open source PostgreSQL extension that helps customers replicate data between independent Aurora PostgreSQL databases while maintaining consistent read-write access and a mix of private and common data in each database.

How does Postgres logical replication work?

Logical replication uses a publish and subscribe model with one or more subscribers subscribing to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe to and may subsequently re-publish data to allow cascading replication or more complex configurations.

What is replication in PostgreSQL?

PostgreSQL replication is defined as the process of copying data from a PostgreSQL database server to another server. The source database server is also known as the “primary” server, whereas the database server receiving the copied data is known as the “replica” server.


1 Answers

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.

like image 199
Jarym Avatar answered Oct 18 '22 02:10

Jarym