Is there any way to sync only few tables of two different postgresql database?

Question

There are two postgresql table in two different region, and I would like to sync just one table inside both database. Is it possible?

Answer 1

I figured out his with pgloical.

(Docker PostgreSQL) Bi-directional Replication with pglogical

1. Launch two PostgreSQL container with different port
   1. docker volume create sync_test_1
   2. docker run --name sync_test_1 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=qwerty1234 -p 5433:5432 -d -v sync_test_1:/var/lib/postgresql/data postgres:9.5.23
   3. docker volume create sync_test_2
   4. docker run --name sync_test_2 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=qwerty1234 -p 5434:5432 -d -v sync_test_2:/var/lib/postgresql/data postgres:9.5.23
2. Get into PostgreSQL container shell and update and install essential tools
   1. docker exec -it sync_test_1 /bin/bash
   2. apt -y update
   3. apt install -y vim
   4. apt install -y procps
   5. do step 1 to 3 with sync_test_2 too
3. Install pglogical package
   1. apt install -y postgresql-9.5-pglogical
4. Change postgresql.conf
   1. vi /var/lib/postgresql/data/postgresql.conf
      1. wal_level = logical
      2. track_commit_timestamp = onenter code here
      3. shared_preload_libraries = ‘pglogical’
      4. max_replication_slots = 10
5. Restart PostgreSQL
   1. docker restart sync_test_1
6. Use pglogical
   1. create database localharvest
   2. psql -U postgres localharvest
   3. CREATE EXTENSION pglogical;
   4. SELECT pglogical.create_node(node_name := 'db1_local', dsn := 'host=127.0.0.1 port=5432 dbname=localharvest user=postgres password=qwerty1234');
   5. SELECT * FROM pglogical.replication_set;
   6. SELECT pglogical.replication_set_add_table(‘default’, ‘users’);
   7. ALTER SEQUENCE users_id_seq RESTART WITH 1 INCREMENT 20;
   8. add ‘host replication postgres 172.17.0.1/32 trust’ in /var/lib/postgresql/data/pg_hba.conf
   9. subscribe each other
   10. SELECT pglogical.create_subscription(subscription_name := 'db2_subscription', forward_origins := '{}', provider_dsn := 'host=host.docker.internal port=5434 dbname=localharvest user=postgres password=qwerty1234');

ref: https://eradman.com/posts/active-active-pglogical.html From inside of a Docker container, how do I connect to the localhost of the machine? https://github.com/2ndQuadrant/pglogical https://www.2ndquadrant.com/en/resources/pglogical/pglogical-docs/#Replication-sets