PostgreSQL Logical Replication บน ubuntu22

ก็จาก 2 ข้อที่ผ่านมา ฉันก็ยังเป็นห่วงว่า หาก network ระหว่างsite หลุด db แต่ละ site จะทำงานไม่ได้ และ หาก site ไหนกลับมาข้อมูลจะไม่เท่ากัน

จนทำให้ลืมไปว่า โจทย์ ของงานฉันจริงๆ คือ ส่งข้อมูล table บางอันกลับมาที่ site hq และ update ข้อมูลบางอย่าง HQ ไปที่ site
โดยในหัวข้อนี้ ฉันจะทดสอบ แค่ ข้อมูล บาง tabel จาก site กลับมา update ที่ HQ โดยจะมี node ทั้งหมด HQ,Site1,Site2

โดยหลักการที่ฉันทำ นี้ ที่ว่า ข้อมูล tabel บางอัน ของทุก site มา update ที่ HQ มีข้อเสียว่า หาก network ขาด ที่ hq แล้วมีการ update ข้อมูลที่ site ปกติ พอ HQ กลับมาจะเจอปัญหา duplicate key เพราะแต่ละ site มี id ของ PRIMARY KEY  ที่เหมือนกันทำให้ site ที่ network ช้ากว่า update ข้อมูลที่ HQ ไม่ได้ ทำให้ฉันต้องแยก table ของ site นั้นๆ ออกมา ที่ HQ เช่น แก้ชื่อ tabel ที่ site 1 และเพิ่ม tabel ชื่อเดียวกันใหม่ที่ HQ  ปัญหาต่อมาคือ ฉันอาจจะต้อง ยิงไปupdate ที่ table หลักอีกที หรือเขียนเชื่อมกันทุก table
—————————————————————————————————————–
ติดตั้ง postgres (ทำทุก site)
apt install postgresql postgresql-contrib

systemctl start postgresql

systemctl enable postgresql

passwd postgres

su - postgres

psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

สิทธิ์ ip ที่ไฟล์ นี้ ที่อยู่ไฟล์จะต่างไปตาม ลุ่น postgres (ทำทุก site)

vi /etc/postgresql/14/main/postgresql.conf

แก้ที่ (ทำทุก site)

listen_addresses = '*'

 สิทธิ์ user ที่ไฟล์ นี้ vi /etc/postgresql/14/main/pg_hba.conf (ทำทุก site)

จากนั้นทดสอบ login ทุก site ข้ามกันว่าติดต่อกันได้ไหม

ต่อมาเริ่มตั้งค่า Logical Replication  (ทำทุก site)

vi /etc/postgresql/14/main/postgresql.conf

wal_level = logical
max_replication_slots = 4 # ปรับตามจำนวน slot replication ที่ต้องการ
max_wal_senders = 4 # ปรับตามจำนวนการเชื่อมต่อ replication ที่ต้องการ

และ  (ทำทุก site)

vi /etc/postgresql/14/main/pg_hba.conf 

# HQ: 192.168.1.216, Site 1: 192.168.1.217, Site 2: 192.168.1.220
host replication replicator 192.168.1.216/32 md5
host replication replicator 192.168.1.217/32 md5
host replication replicator 192.168.1.220/32 md5

รีสตาร์ท PostgreSQL บนทุก Node เพื่อให้การตั้งค่าใหม่มีผล: (ทำทุก site)

sudo systemctl restart postgresql

บนทุก Node ให้สร้างผู้ใช้ replicator ซึ่งมีสิทธิ์ในการทำ replication
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD ‘P@ssw0rd’;

ต่อมา table ที่ฉํนจะทดสอบ คือ radpostauth  และ radacct

เปลี่ยนชื่อตารางบน Site 1 และ Site 2 แนะนำให้ run ที่ละคำสั่ง จะดีกว่า เพื่อดูผล

ให้เปลี่ยนชื่อตารางบน Site 1 และ Site 2 เพื่อให้มีชื่อเฉพาะสำหรับแต่ละ site โดยใช้คำสั่ง ALTER TABLE เพื่อ id ไม่ชนกัน
บน Site 1 (pgsite1):


ALTER TABLE radpostauth RENAME TO radpostauth_site1;
ALTER TABLE radacct RENAME TO radacct_site1;

บน Site 2 (pgsite2):

ALTER TABLE radpostauth RENAME TO radpostauth_site2;
ALTER TABLE radacct RENAME TO radacct_site2;

สร้าง Publication บน pgsite1 และ pgsite2 สำหรับตารางที่เปลี่ยนชื่อใหม่
บน Site 1 (pgsite1):
CREATE PUBLICATION pub_site1 FOR TABLE radpostauth_site1, radacct_site1;
บน Site 2 (pgsite2):
CREATE PUBLICATION pub_site2 FOR TABLE radpostauth_site2, radacct_site2;

สร้างตารางแยกบน HQ ให้ตรงกับตารางใหม่ในแต่ละ Site

CREATE TABLE radpostauth_site1 (
LIKE radpostauth INCLUDING ALL
);

CREATE TABLE radacct_site1 (
LIKE radacct INCLUDING ALL
);

CREATE TABLE radpostauth_site2 (
LIKE radpostauth INCLUDING ALL
);

CREATE TABLE radacct_site2 (
LIKE radacct INCLUDING ALL
);
สร้าง Subscription ใหม่บน HQ

— Subscription สำหรับ Site 1
CREATE SUBSCRIPTION sub_from_site1 CONNECTION ‘host=192.168.1.217 dbname=db_radius user=replicator password=P@ssw0rd’
PUBLICATION pub_site1 ;

— Subscription สำหรับ Site 2
CREATE SUBSCRIPTION sub_from_site2 CONNECTION ‘host=192.168.1.220 dbname=db_radius user=replicator password=P@ssw0rd’
PUBLICATION pub_site2 ;

หากต้องการแก้ไข ให้ลบแล้ว สร้างใหม่
บน HQ:
DROP SUBSCRIPTION IF EXISTS sub_from_site1;
DROP SUBSCRIPTION IF EXISTS sub_from_site2;
บน Site 1 (pgsite1):
DROP PUBLICATION IF EXISTS pub_site1;
บน Site 2 (pgsite2):
DROP PUBLICATION IF EXISTS pub_site2;

วิธีการเช็คว่า ใช้งานได้

1. ตรวจสอบรายการ Subscription

บน HQ (หรือ node ที่คุณสร้าง Subscription) ให้รันคำสั่งนี้เพื่อดูรายการ Subscription ทั้งหมด:

คอลัมน์สำคัญที่ควรสังเกต:

• subname: ชื่อ Subscription
• subconninfo: ข้อมูลการเชื่อมต่อ
• subenabled: แสดงสถานะว่า Subscription นี้เปิดใช้งานอยู่หรือไม่ (t หมายถึงเปิดใช้งาน)

2. ตรวจสอบสถานะของ Slot ที่ใช้กับ Subscription

การตรวจสอบนี้ทำให้แน่ใจว่าการเชื่อมต่อ replication slot ถูกสร้างและทำงานอยู่:

คอลัมน์สำคัญ:

• slot_name: ชื่อ replication slot (ควรตรงกับชื่อ Subscription เช่น sub_from_site1)
• active: แสดงสถานะว่ามีการใช้งาน slot นี้อยู่หรือไม่ (t หมายถึงกำลังใช้งาน)

3. ตรวจสอบการ Synchronization ของตาราง

เพื่อดูว่าตารางมีการ sync ข้อมูลแล้วหรือไม่ สามารถตรวจสอบได้โดยการเช็คข้อมูลในตารางปลายทาง (ตารางที่รับ replication) บน HQ:

ถ้าข้อมูลจาก site ต้นทางมีการ replicate มาแสดงใน HQ ก็แสดงว่า replication ทำงานปกติ