| From: | JaeWon Lee <thejaewon(at)gmail(dot)com> |
|---|---|
| To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
| Subject: | Can I synchronize two master DB clusters using logical replication? |
| Date: | 2018-05-09 11:51:06 |
| Message-ID: | CAC+TV7F3D_vo2VNSOQJL-u1NxMVSDpWX_RPA8u7Y_BHELi+cpg@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
Hi ^^
I have two DB clusters. One is in South Korea, and the other is in US.
Each cluster generates its own DML commands. (INSERT, UPDATE, DELETE)
And I want to synchronize this two DB clusters using logical replication.
I tried this, but it failed.
Specifically, I succeeded to replicate one DB cluster to the other.
But when I set logical replication in both cluster, only the former which I
set first works.
Can I use logical replication to synchronize two master DB clusters?
The following is what I did.
[ In Korea cluster ]
------------------------------------------------------------------------------------
- postgresql.conf
wal_level = logical
- pg_hba.conf
# IPv4 local connections:
host <KR DB name> postgres <US server IP>/32 md5
CREATE PUBLICATION mypubKR FOR ALL TABLES;
CREATE SUBSCRIPTION mysubKR CONNECTION 'host=<US server IP> port=5432
user=postgres dbname=<US DB name> password=<password>' PUBLICATION mypubUS;
[ In US cluster ]
-----------------------------------------------------------------------------------------
- postgresql.conf
wal_level = logical
- pg_hba.conf# IPv4 local connections:
host <US DB name> postgres <KR server IP>/32 md5
CREATE PUBLICATION mypubUS FOR ALL TABLES;
CREATE SUBSCRIPTION mysubUS CONNECTION 'host=<KR server IP> port=5432
user=postgres dbname=<KR DB name> password=<password>' PUBLICATION mypubKR;
---------------------------------------------------------------------------------------------------------------------
If I make 'mysubKR' first and 'mysubUS' later, replication from US to Korea
works well. But Korea to US doesn't work.
If I make 'mysubUS' first and 'mysubKR' later, replication from Korea to US
works well. But US to Korea doesn't work.
I hope both direction work well.
How can I solve this problem?
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Scott Whitney | 2018-05-09 12:14:08 | Re: How to rename current database? |
| Previous Message | Walter Nordmann | 2018-05-09 11:21:40 | Re: How to rename current database? |