short tutorial to setup replication using pglogical

pglogical

Edit /var/lib/postgres/data/postgresql.conf:

# change IP on subscriber
listen_addresses = '*'
wal_level = logical
shared_preload_libraries = 'pglogical'
max_worker_processes = 16
max_wal_senders = 16
max_replication_slots = 16
track_commit_timestamp = on
log_min_messages = debug3

Add to /var/lib/postgres/data/pg_hba.conf:

local   replication     all                                trust
host    replication     all        127.0.0.1/32            trust
host    replication     all        ::1/128                 trust

# change IP on subscriber 
host    all             all         all                    trust
host    replication     all         all                    trust


Create user and database:

sudo -u postgres createuser -s --replication -P eax
sudo -u postgres createdb -O eax eax

Run psql as user eax and execute:

create extension pglogical;

On this point both provider and subscriber have user, database, and enabled pglogical.

(4) On provider:

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

insert into test (v) values ('aaa');

select pglogical.create_node(
  node_name := 'provider',
  dsn := 'host=10.128.0.11 port=5432 user=eax dbname=eax'
);

select pglogical.create_replication_set('replication_set');

select pglogical.replication_set_add_table(
  set_name := 'replication_set',
  relation := 'test',
  synchronize_data := true
);

(5) On subscriber:

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

select pglogical.create_node(
  node_name := 'subscriber',
  dsn := 'host=10.128.0.12 port=5432 dbname=eax user=eax'
);

select pglogical.create_subscription(
  subscription_name := 'subscription',
  replication_sets := array['replication_set'],
  provider_dsn := 'host=10.128.0.11 port=5432 dbname=eax user=eax'
);



# schema as table array
SELECT pglogical.replication_set_add_all_tables('<my replication set>', ARRAY['<a schema>']);


## drop node
  SELECT pglogical.drop_node(
      node_name := '<node name>'
  );
  
# in second node, create tables, replication set and in first node, create subscription

https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/