PostgreSQL replication แบบ physical กับ logical และวิธีเลือกใช้กับแอป Laravel

พอระบบเริ่มมีรายงานหนัก ๆ หรือมีคนอยากต่อ Metabase เข้ามาดูข้อมูล ทางที่ใช้กันมาตลอดคือสร้าง user อ่านอย่างเดียวแล้วให้ต่อเข้าฐานข้อมูล production ตรง ๆ ก็ใช้ได้นะ แต่ query รายงานที่ scan ทั้งตารางจะไปแย่ง CPU กับดิสก์จากหน้าร้าน ยิ่งถ้าแอปกับฐานข้อมูลอยู่เครื่องเดียวกัน ลูกค้าจะรู้สึกได้ทันทีว่าเว็บช้าลง

ทางที่ดีกว่าคือทำสำเนาฐานข้อมูลไว้อีกเครื่อง แล้วให้รายงานไปอ่านที่นั่น PostgreSQL มีวิธีทำสำเนาในตัวสองแบบ คือ physical replication กับ logical replication ชื่อคล้ายกัน แต่ส่งของคนละอย่างและดูแลคนละแบบ ถ้าเลือกไม่เข้ากับวิธี deploy ของเรา ก็จะต้องตามแก้กันทุกรอบ deploy

สองแบบนี้ส่งของคนละอย่าง#

PostgreSQL เขียนทุกการเปลี่ยนแปลงลง WAL (Write-Ahead Log) ก่อนเสมอ replication ทั้งสองแบบเริ่มจาก WAL เหมือนกัน ต่างกันที่ส่งอะไรออกไป

• physical ส่ง WAL ไประดับ byte ฝั่งรับ (standby) เล่นซ้ำแล้วได้ไฟล์ข้อมูลหน้าตาเดียวกับ primary ทุก byte
• logical อ่าน WAL แล้วแปลงเป็นแถวที่ INSERT, UPDATE, DELETE ของตารางที่เราเลือก ฝั่งรับ (subscriber) เอาแถวพวกนั้นไปเขียนลงตารางของตัวเอง

physical ได้สำเนาทั้งเครื่อง ส่วน logical ได้เฉพาะข้อมูลในตารางที่เลือก

physical replication ทำสำเนาทั้งคลัสเตอร์#

คลัสเตอร์ใน PostgreSQL คือ server หนึ่งตัวกับทุกฐานข้อมูลข้างใน (หนึ่ง data directory หนึ่ง port) standby แบบ physical จึงได้ทุกอย่างไปพร้อมกัน

• ทุกฐานข้อมูลในคลัสเตอร์ เลือกเอาแค่บางฐานไม่ได้
• ทุก role ทุกรหัสผ่าน ทุก GRANT
• ตาราง index และ view ที่ migration สร้างใหม่ ตามไปเองภายในไม่กี่วินาที

standby เปิดให้อ่านได้ (hot standby) แต่เขียนอะไรไม่ได้เลย docs ของ Hot Standby ไล่ไว้ว่า INSERT, UPDATE, DELETE, CREATE, ALTER, GRANT, REVOKE ไปจนถึง ANALYZE กับ VACUUM จะ error ทั้งหมด ข้อนี้มีผลตอนออกแบบ อยากได้ view สำหรับรายงาน หรือ role ที่อ่านได้แค่บางตาราง ก็ต้องไปสร้างบน primary แล้วรอให้ตามมา สร้างเฉพาะบน standby ไม่ได้

ฝั่ง primary แทบไม่ต้องแก้อะไร wal_level มีค่าเริ่มต้นเป็น replica ซึ่งพอสำหรับ physical อยู่แล้ว (Replication settings) ที่ต้องทำคือสร้าง role ที่มีสิทธิ์ REPLICATION เปิด pg_hba.conf ให้ IP ของ standby แล้วสร้าง standby ด้วย pg_basebackup -R ถ้า primary ฟังแค่ localhost อยู่ การแก้ listen_addresses ต้องรีสตาร์ต primary หนึ่งครั้ง คำสั่งทั้งชุดผมจดไว้ใน คำสั่ง PostgreSQL Replication ที่ใช้บ่อย แล้ว

standby ต้องใช้ PostgreSQL และ OS ชุดเดียวกับ primary#

physical ก๊อปไฟล์ข้อมูลไปทั้ง byte สองเครื่องจึงต้องอ่านไฟล์ชุดเดียวกันได้

• PostgreSQL major เดียวกัน 17 ส่งให้ 18 ไม่ได้ ส่วน minor ต่างกันได้ แต่ docs แนะนำให้อัปเดต standby ก่อน primary (Planning)
• architecture เดียวกัน docs ยกตัวอย่างว่า 32-bit ส่งให้ 64-bit ไม่ได้
• library ที่ใช้เรียงตัวอักษร (collation) รุ่นเดียวกัน ข้อนี้ไม่ได้อยู่ในหน้า standby แต่พังแบบเงียบที่สุด

index ของคอลัมน์ข้อความเก็บค่าเรียงตามกฎของ glibc หรือ ICU บนเครื่องที่สร้าง index พอ standby รับ index ไปทั้ง byte แต่ตัวเองมี library คนละรุ่นที่เรียงบางตัวอักษรต่างไป query ผ่าน index บน standby ก็อาจหาแถวที่มีอยู่ไม่เจอ โดยไม่มี error สักบรรทัด PostgreSQL wiki เขียนไว้ชัดว่า standby ที่ locale data ต่างจาก primary อาจเสียหายได้ทั้งที่ primary ปกติดี ส่วน pg_dump กับ logical replication ไม่โดนเรื่องนี้

ตัวอย่างที่เจอได้ง่ายคือ primary ลง PostgreSQL จาก PGDG บน Ubuntu 24.04 (libicu74) แล้วอยากรัน standby ใน Docker ด้วย image postgres:18 ซึ่งสร้างบน Debian trixie ได้ libicu คนละรุ่นทันที ทางที่ปลอดภัยคือให้ standby ใช้ OS release เดียวกับ primary และลง PostgreSQL จาก repository เดียวกัน

เช็คได้ด้วย query นี้ รันบนทั้งสองเครื่องแล้วเทียบกัน datcollversion คือรุ่นที่จดไว้ตอนสร้างฐานข้อมูล ส่วนคอลัมน์ถัดไปคือรุ่นของ library บนเครื่องที่รัน บน standby ถ้าสองค่านี้ไม่ตรงกัน แปลว่าเครื่องนั้นเรียงตัวอักษรไม่เหมือนตอนสร้าง index

SELECT datname, datlocprovider, datlocale, datcollversion,
       pg_database_collation_actual_version(oid) AS actual_version
FROM pg_database
WHERE datallowconn;

คอลัมน์ datlocale มีตั้งแต่ PostgreSQL 17 ถ้าเป็น 16 ให้ใช้ daticulocale แทน

ฐานข้อมูลที่ใช้ collation C หรือ C.UTF-8 เรียงตาม byte ล้วน ๆ ไม่ขึ้นกับ library ข้อนี้จึงไม่มีผล ส่วนฐานข้อมูลที่ตั้ง ICU und ไว้ให้เรียงภาษาไทยถูก ต้องเช็คข้อนี้ก่อนทำ standby ทุกครั้ง

ตั้งสามค่านี้ก่อนให้รายงานไปอ่าน standby#

1. จำกัด WAL ที่ slot เก็บไว้ standby ส่วนใหญ่ต่อผ่าน replication slot ซึ่งสั่งให้ primary เก็บ WAL ไว้จนกว่า standby จะรับไป ถ้า standby ดับไปหลายวัน WAL จะกองบน primary จนดิสก์เต็ม ค่าเริ่มต้นของ max_slot_wal_keep_size คือ -1 หรือเก็บไม่จำกัด ตั้งเพดานไว้ดีกว่า ถ้าเกินเพดาน slot จะใช้ต่อไม่ได้และต้องสร้าง standby ใหม่ ซึ่งยังดีกว่าให้ production ดิสก์เต็ม

2. ปล่อย hot_standby_feedback เป็น off ถ้าเปิด primary จะยังไม่ VACUUM แถวที่ query บน standby ยังอ่านอยู่ รายงานที่รันนาน ๆ บน standby ก็ทำให้ตารางบน production บวม (Hot Standby) ปิดไว้ (ค่าเริ่มต้น) แล้วยอมให้ query ยาว ๆ บน standby โดนยกเลิกบ้าง

3. ให้ query จบก่อน replay จะยกเลิก ตอน standby จะ replay WAL ที่ชนกับ query ที่กำลังรัน จะรอได้ไม่เกิน max_standby_streaming_delay (ค่าเริ่มต้น 30 วินาที) แล้วยกเลิก query นั้น สำหรับรายงานผมขยายเป็นไม่กี่นาที แล้วตั้ง statement_timeout ของ role ที่ใช้อ่านให้สั้นกว่า query ที่ช้าผิดปกติจะหมดเวลาเองก่อน ข้อแลกคือ standby อาจตามหลังได้นานสุดเท่าค่านี้

max_slot_wal_keep_size = 16GB

hot_standby = on
hot_standby_feedback = off
max_standby_streaming_delay = 5min

ALTER ROLE report_ro SET statement_timeout = '3min';

ALTER ROLE ต้องรันบน primary เหมือน GRANT แล้วค่าจะตามไปที่ standby เอง

logical replication เลือกได้ว่าจะส่งตารางไหน#

ฝั่งต้นทางสร้าง PUBLICATION บอกว่าจะส่งตารางไหน ฝั่งรับสร้าง SUBSCRIPTION ไปดึง ตั้งแต่ PostgreSQL 15 เลือกคอลัมน์ได้ (Column Lists) และกรองแถวด้วย WHERE ได้ (Row Filters)

-- ส่งสองตาราง โดย orders ส่งแค่คอลัมน์ที่รายงานใช้
CREATE PUBLICATION reporting
    FOR TABLE orders (id, customer_id, total, status, created_at), products;

-- ต้องสร้างตาราง orders กับ products รอไว้ก่อน เช่นจาก pg_dump --schema-only
CREATE SUBSCRIPTION reporting
    CONNECTION 'host=10.0.0.2 dbname=shop user=replicator'
    PUBLICATION reporting;

subscription จะก๊อปข้อมูลที่มีอยู่ไปก่อน แล้วรับการเปลี่ยนแปลงต่อจากนั้นไปเรื่อย ๆ

สิ่งที่ได้มาคือความยืดหยุ่นที่ physical ให้ไม่ได้

• เลือกตาราง คอลัมน์ และแถวได้ เช่นส่งออเดอร์ไปให้ทีมอื่นโดยไม่มีอีเมลลูกค้า
• ฝั่งรับเป็นฐานข้อมูลปกติ สร้าง role view และ index สำหรับรายงานของตัวเองได้
• ข้าม major version และข้าม OS ได้ (Logical Replication) ฝั่งรับสร้าง index ใหม่เองด้วย library ของตัวเอง เรื่อง ICU คนละรุ่นจึงไม่เกี่ยว จะรันใน Docker ก็ได้

แต่มีค่าใช้จ่ายที่ต้องรู้ก่อน

• ต้องตั้ง wal_level = logical บน primary ค่านี้เปลี่ยนแล้วต้องรีสตาร์ต
• ตารางที่ส่ง UPDATE หรือ DELETE ต้องมี primary key หรือตั้ง REPLICA IDENTITY ตารางของ Laravel ที่สร้างด้วย $table->id() มี primary key อยู่แล้ว แต่ตาราง pivot ที่มีแค่ foreignId() สองคอลัมน์ต้องเพิ่ม $table->primary([...]) เอง
• sequence ไม่ตามไป ฝั่งรับได้ค่า id ในแถวครบ แต่ sequence ของตัวเองยังอยู่ที่ค่าเริ่มต้น (Restrictions)
• schema ไม่ตามไป ข้อนี้หนักที่สุด

ทุก migration ต้องไปรันที่ฝั่งรับด้วย#

docs เขียนไว้ตรง ๆ ว่า logical replication ไม่ส่ง DDL ทุกการแก้ schema ต้อง sync เอง (Restrictions) คนที่มาจาก MySQL ต้องระวังจุดนี้เป็นพิเศษ เพราะ replica ของ MySQL รับ ALTER TABLE ไปจาก binlog ด้วย เราจึงแทบไม่เคยต้องคิดเรื่องนี้

ลองนึกถึง deploy ปกติของ Laravel สมมติมี migration เพิ่มคอลัมน์ coupon_code ให้ orders แล้ว deploy รัน php artisan migrate บน primary

1. แถวใหม่ที่มี coupon_code วิ่งไปฝั่งรับ ซึ่งยังไม่มีคอลัมน์นี้
2. apply worker บนฝั่งรับ error แล้ว replication หยุดจนกว่าจะแก้ schema ให้ตรง
3. ระหว่างนั้น slot บน primary เก็บ WAL ไว้รอ ดิสก์ของ production ก็โตขึ้นเรื่อย ๆ

ทางแก้คือเพิ่มขั้นใน deploy ให้รัน migration บนฝั่งรับด้วย และให้ลำดับถูก docs แนะนำให้แก้แบบเพิ่ม (เพิ่มคอลัมน์ เพิ่มตาราง) ที่ฝั่งรับก่อน ส่วนการลบให้ลบที่ต้นทางก่อน นอกจากนี้ยังมีเรื่องปลีกย่อยอีก ถ้า publication ระบุคอลัมน์ไว้ ก็จะไม่ส่งคอลัมน์ใหม่จนกว่าเราจะแก้ publication ส่วนตารางใหม่ต้อง ALTER PUBLICATION ... ADD TABLE แล้ว ALTER SUBSCRIPTION ... REFRESH PUBLICATION ทุกข้อทำได้ แต่เป็นงานที่ต้องคิดทุกครั้งที่แก้ schema และถ้าลืมสักครั้ง replication จะหยุด

ตัวตัดสินคือใครแก้ schema และแก้บ่อยแค่ไหน#

แอป Laravel ส่วนใหญ่แก้ schema ผ่าน migration แทบทุกรอบ deploy ถ้าแค่อยากได้สำเนาไว้อ่านทั้งหมด physical จะไม่มีงานเพิ่มในแต่ละ deploy เลย ขอแค่คุมให้สองเครื่องใช้ OS และ PostgreSQL ชุดเดียวกัน

logical เหมาะเมื่อต้องการสิ่งที่ physical ให้ไม่ได้ เช่น ส่งบางตารางให้ระบบอื่นโดยตัดข้อมูลส่วนตัวออก รวมข้อมูลจากหลายฐานไว้ที่เดียว ย้ายไป PostgreSQL major ใหม่โดยให้ระบบหยุดสั้นที่สุด หรือฝั่งรับต้องอยู่บน OS หรือ Docker ที่คุมให้ตรงกับ primary ไม่ได้ แล้วยอมจ่ายด้วยขั้น sync schema ในทุก deploy

ตัวอย่างจากระบบที่ใช้อยู่ standby ที่ป้อนข้อมูลให้ Metabase#

ระบบหลังบ้านตัวหนึ่งที่เราดูแลเป็น Laravel บน PostgreSQL ฐานข้อมูลหลักอยู่เครื่องเดียวกับแอป และมีอีกเครื่องสำหรับ analytics ที่รัน Metabase กับ PostgreSQL อีกคลัสเตอร์ไว้เก็บข้อมูลจากระบบภายนอก

รายงานทั้งหมดอ่านจาก standby บนเครื่อง analytics ไม่มี query ไหนวิ่งไปที่ primary

• standby เป็นคลัสเตอร์แยกบนเครื่อง analytics ใช้ port ของตัวเอง แยกจากคลัสเตอร์ analytics
• standby ต่อเข้า primary ผ่านเครือข่ายภายใน (VPC) ด้วย physical slot และ user สำหรับ replication โดยเฉพาะ ปกติตามหลัง primary ไม่ถึงวินาที
• Metabase อ่าน standby ผ่าน 127.0.0.1 ส่วนคลัสเตอร์ analytics ใช้ postgres_fdw ไปอ่านตารางของแอปจาก standby ในเครื่องเดียวกัน รายงานจึง join ข้อมูลภายนอกกับข้อมูลแอปได้ใน query เดียว โดยไม่มี traffic ข้ามเครื่องเลย

ระบบนี้ใช้ collation C.UTF-8 เรื่อง ICU ข้างบนจึงไม่กระทบ

postgres_fdw ก็ไม่ตาม schema เหมือนกัน#

foreign table ของ postgres_fdw คือนิยามตารางที่สร้างไว้ครั้งเดียว พอ migration เพิ่มตารางหรือคอลัมน์ในแอป foreign table ฝั่ง analytics ก็ไม่รู้เรื่อง ตอนเราไล่ตามครั้งแรก ขาดไป 21 ตาราง กับอีก 106 คอลัมน์ใน 30 ตาราง

ทางแก้คือเพิ่มขั้นใน deploy หลัง php artisan migrate ให้รันคำสั่งที่เทียบ schema ของสองฝั่ง แล้ว import ตารางใหม่และเพิ่มคอลัมน์ที่ขาด หัวใจของคำสั่งนั้นคือ SQL แบบนี้

IMPORT FOREIGN SCHEMA public LIMIT TO (coupons)
    FROM SERVER app_replica INTO public;

ALTER FOREIGN TABLE orders ADD COLUMN coupon_code varchar(255);

นี่คืองานแบบเดียวกับที่ logical replication จะเรียกเก็บจากเราทุกตาราง ต่างกันที่ตรงนี้กระทบแค่รายงานที่ผ่าน FDW ส่วน standby เองตามทันเสมอโดยไม่ต้องทำอะไร

.pgpass ต้องใช้ host เดียวกับ primary_conninfo#

สูตรแรกที่ใช้ตั้ง standby เขียนรหัสผ่านไว้ใน primary_conninfo ตรง ๆ ใครเปิดไฟล์ config ได้ก็เห็นรหัสผ่าน เราจึงย้ายรหัสผ่านไปไว้ใน ~/.pgpass ของ user postgres บนเครื่อง standby

primary_conninfo = 'host=10.0.0.2 port=5432 user=replicator'
primary_slot_name = 'reporting_standby'

10.0.0.2:5432:replication:replicator:รหัสผ่าน

libpq หาบรรทัดใน .pgpass จากค่า host ที่ส่งมาตอนต่อ (The Password File) ถ้า primary_conninfo ใช้ 10.0.0.2 แต่ .pgpass เขียนเป็นชื่อเครื่องหรือ IP สาธารณะเดิม ก็หาบรรทัดไม่เจอ ตอนย้ายให้ standby ต่อผ่านเครือข่ายภายในแทน IP สาธารณะ จึงต้องแก้ทั้งสองที่ให้ตรงกัน ช่อง database ต้องเป็น replication สำหรับการต่อแบบ streaming และไฟล์ต้อง chmod 600 ไม่อย่างนั้น libpq จะไม่อ่าน

ตอนอัปเกรด 16 เป็น 18 เราสร้าง standby ใหม่ทั้งตัว#

physical ข้าม major version ไม่ได้ ตอนอัปเกรดเครื่องนี้จาก PostgreSQL 16 เป็น 18 เราจึงไม่อัปเกรด standby ตามแบบ primary แต่สร้างใหม่

1. อัปเกรด primary ก่อน แล้วสร้าง slot บน primary ตัวใหม่ทันที ให้ primary เริ่มเก็บ WAL ไว้รอ
2. ลบ standby 16 ตัวเก่าบนเครื่อง analytics เพราะสำเนาใหม่ต้องใช้ดิสก์ราว 19 GB แต่เหลือที่ว่างแค่ 13 GB
3. สร้างคลัสเตอร์ 18 ใหม่ แล้ว pg_basebackup -R -S จาก primary ที่อัปเกรดแล้ว

ไม่นาน standby ก็ streaming ตามทัน แต่ Metabase กลับต่อไม่ได้ เพราะคลัสเตอร์ที่สร้างใหม่ได้ postgresql.conf กับ pg_hba.conf ค่าเริ่มต้นมา ฟังแค่ localhost และยอมรับแค่ 127.0.0.1 ค่าที่เคยปรับไว้หายหมด ไฟล์ config ของ standby ไม่ได้มากับ replication บน Ubuntu ไฟล์พวกนี้อยู่ใน /etc/postgresql/ นอก data directory ที่ pg_basebackup ก๊อปมา และ pg_hba.conf ของแต่ละเครื่องก็เป็นของเครื่องนั้นเอง หลังจากนั้นเราเก็บค่าของ standby ไว้ในไฟล์ conf.d แยกที่สร้างใหม่ได้ทุกครั้ง

เลือก physical หรือ logical จากงานที่ต้องทำ#

• เลือก physical เมื่ออยากได้สำเนาทั้งหมดไว้อ่าน ไม่อยากเพิ่มงานในทุก deploy และคุมให้สองเครื่องใช้ OS กับ PostgreSQL ชุดเดียวกันได้
• เลือก logical เมื่อต้องการแค่บางตาราง บางคอลัมน์ ต้องแปลงข้อมูลระหว่างทาง หรือต้องข้ามเวอร์ชันหรือ OS และพร้อมเพิ่มขั้น sync schema ใน deploy ทุกครั้ง
• ไม่ว่าแบบไหน ให้ทุกคนที่มาอ่านสำเนามี role ของตัวเอง ไม่ใช้ user ร่วมกับแอปหรือเครื่องมืออื่น จะได้ปิดหรือเปลี่ยนรหัสผ่านทีละคนได้