version: '3.1'

services:
  db-master:
    image: postgres:10-alpine
    restart: always
    ports:
      - 5433:5432
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: example
    volumes:
      - pg-data-master:/var/lib/postgresql/data
    command: ['postgres', '-c', 'wal_level=logical']

  db-replica:
    image: postgres:10-alpine
    restart: always
    ports:
      - 5434:5432
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: example
    volumes:
      - pg-data-rep:/var/lib/postgresql/data

volumes:
  pg-data-master:
  pg-data-rep:

# หาตำแหน่งไฟล์ postgresql.conf
# ใน docker ของผม จะอยู่ที่ /var/lib/postgresql/data/
$ psql -U postgres -c 'SHOW config_file'

# เปิดไฟล์มาแก้ไข
$ sudo nano /var/lib/postgresql/data/postgresql.conf

# แก้ที่ #listen_addresses = 'localhost' เป็น
...
listen_addresses = 'localhost, db_master_ip_address'
...

...
wal_level = logical
...

...
# TYPE      DATABASE        USER         ADDRESS                       METHOD
...
host         all            all          db_replica_ip_address/32      md5

$ sudo systemctl restart postgresql

$ createdb -U postgres example

$ createdb -U postgres example

$ psql -U postgres example

example=# CREATE TABLE vitalsigns (id text, weight text, height text, temp text, CONSTRAINT vitalsigns_pkey PRIMARY KEY (id));

$ psql -U postgres example

example=# CREATE TABLE vitalsigns (id text, weight text, height text, temp text, CONSTRAINT vitalsigns_pkey PRIMARY KEY (id));

# on db-master
example=# CREATE ROLE rep_user WITH REPLICATION LOGIN PASSWORD 'rep_password';

# grant full privileges on the example database to the user role you just created
example=# GRANT ALL PRIVILEGES ON DATABASE example TO rep_user;

# grant privileges on all of the tables contained in the database to your user
example=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rep_user;

# on db-master

# create a publication called my_pub:
example=# CREATE PUBLICATION my_pub;

# Add the vitalsigns table you created previously to it:
example=# ALTER PUBLICATION my_pub ADD TABLE vitalsigns;

# on db-replica

# create a subscription called my_sub:
example=# CREATE SUBSCRIPTION my_sub CONNECTION 'host=db-master port=5432 user=rep_user password=rep_password dbname=example' PUBLICATION my_pub;

# You will see the following output confirming the subscription:
# NOTICE:  created replication slot "my_sub" on publisher
# CREATE SUBSCRIPTION

# on db-master

example=# INSERT INTO vitalsigns (id, weight, height, temp) VALUES ('1', '60', '160', '36'), ('2', '70', '170', '37'), ('3', '80', '180', '38');

# on db-replica

example=# SELECT * FROM vitalsigns;

# output
 id | weight | height | temp
----+--------+--------+------
  1 | 60     |  160   | 36
  2 | 70     |  170   | 37
  3 | 80     |  180   | 38
(3 rows)

# on db-replica

example=# INSERT INTO vitalsigns (id, weight, height, temp) VALUES ('4', '90', '190', '37.5');

example=# SELECT * FROM vitalsigns;

# output
 id | weight | height | temp
----+--------+--------+------
  1 | 60     |  160   | 36
  2 | 70     |  170   | 37
  3 | 80     |  180   | 38
  4 | 90     |  190   | 37.5
(4 rows)

# on db-master

example=# SELECT * FROM vitalsigns;

# output
 id | weight | height | temp
----+--------+--------+------
  1 | 60     |  160   | 36
  2 | 70     |  170   | 37
  3 | 80     |  180   | 38
(3 rows)

# on db-master

example=# UPDATE vitalsigns SET temp = '37.7' WHERE id = '3';

example=# DELETE FROM vitalsigns WHERE id = '1';

# on db-replica

example=# SELECT * FROM vitalsigns;

# output
 id | weight | height | temp
----+--------+--------+------
  2 | 70     |  170   | 37
  3 | 80     |  180   | 37.7
  4 | 90     |  190   | 37.5
(3 rows)