คำสั่ง PostgreSQL Replication ที่ใช้บ่อย

โน้ตคำสั่งที่ผมใช้ตอนทำ streaming replication ของ PostgreSQL แบบ primary กับ standby ใช้ได้กับ PostgreSQL 16 ถึง 18 ตัวอย่าง path เป็นของ Ubuntu ที่ลง PostgreSQL 17 ถ้าเป็นเวอร์ชันอื่นก็เปลี่ยนเลข 17 ใน path

เปิดให้ standby ต่อเข้า primary#

สร้าง role สำหรับ replication บน primary

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'change-me';

แล้วอนุญาตให้ IP ของ standby ต่อเข้ามาแบบ replication ใน pg_hba.conf

host    replication    replicator    10.0.0.2/32    scram-sha-256

แล้ว reload config ให้ค่าใหม่มีผล

SELECT pg_reload_conf();

ค่าเริ่มต้นของ wal_level (replica) กับ max_wal_senders (10) ใช้ทำ replication ได้อยู่แล้ว เช็คแค่ว่า listen_addresses ใน postgresql.conf เปิดรับ IP ที่ standby ต่อเข้ามา (Replication settings)

สร้าง standby ด้วย pg_basebackup -R#

รันบนเครื่อง standby โฟลเดอร์ปลายทางของ -D ต้องว่างหรือยังไม่มี เราจึงย้ายของเดิมออกไปก่อน

sudo systemctl stop postgresql
sudo -u postgres mv /var/lib/postgresql/17/main /var/lib/postgresql/17/main.old

sudo -u postgres pg_basebackup -h 10.0.0.1 -U replicator \
    -D /var/lib/postgresql/17/main \
    -X stream -C -S standby1 -R -P -c fast

sudo systemctl start postgresql

• -X stream ส่ง WAL มาพร้อมกันระหว่าง backup (เป็นค่าเริ่มต้นอยู่แล้ว) ส่วน -x ของบทความเก่า ๆ เลิกใช้ไปตั้งแต่ PostgreSQL 10
• -C -S standby1 สร้าง replication slot ชื่อ standby1 บน primary ให้ primary เก็บ WAL ไว้จนกว่า standby จะรับไป
• -R สร้างไฟล์ standby.signal และเขียนค่าเชื่อมต่อ primary ลง postgresql.auto.conf ให้ ไม่ต้องแก้ config เอง
• -c fast สั่ง checkpoint ทันที ไม่ต้องรอ ส่วน -P แสดง progress

หลังรันเสร็จ postgresql.auto.conf บน standby จะมีค่าประมาณนี้

primary_conninfo = 'user=replicator host=10.0.0.1 port=5432 ...'
primary_slot_name = 'standby1'

ดูรายละเอียดทุก option ได้ที่ pg_basebackup

เช็คว่า standby ตามทัน#

บน primary ดูจาก pg_stat_replication standby ที่ต่ออยู่จะมีหนึ่งแถว state ควรเป็น streaming

SELECT application_name, client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_behind,
       replay_lag
FROM pg_stat_replication;

บน standby ดูจาก pg_stat_wal_receiver แล้วเช็คว่ายังอยู่ในโหมด recovery

SELECT status, sender_host, slot_name, flushed_lsn, last_msg_receipt_time
FROM pg_stat_wal_receiver;

SELECT pg_is_in_recovery(),
       now() - pg_last_xact_replay_timestamp() AS replay_delay;

replay_delay นับจาก transaction สุดท้ายที่ standby replay ถ้า primary ไม่มีใครเขียนข้อมูลเลย ค่านี้ก็จะโตขึ้นเรื่อย ๆ ทั้งที่ standby ตามทันแล้ว (Monitoring stats)

ดูและลบ replication slot#

รันบน primary ดูว่ามี slot อะไรบ้าง ใช้งานอยู่ไหม และแต่ละ slot ค้าง WAL ไว้เท่าไร

SELECT slot_name, slot_type, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

หา slot ที่ไม่มีใครต่ออยู่ คอลัมน์ inactive_since มีตั้งแต่ PostgreSQL 17 ถ้าเป็น 16 ให้ตัดออก

SELECT slot_name, slot_type, inactive_since
FROM pg_replication_slots
WHERE NOT active;

แล้วลบ slot ที่ไม่ใช้แล้ว

SELECT pg_drop_replication_slot('standby1');

slot ที่ active อยู่ลบไม่ได้ PostgreSQL จะฟ้อง error ให้หยุด standby หรือ subscriber ที่ใช้ slot นั้นก่อน ถ้าชื่อ slot หน้าตาเป็น pg_31677_sync_… คือ slot ที่ logical replication สร้างไว้ตอน sync ตาราง ปกติลบตัวเองเมื่อ sync เสร็จ แต่ถ้า subscription พังกลางทางก็อาจค้างอยู่

ถ้าจะสร้าง physical slot เอง โดยไม่ใช้ -C ของ pg_basebackup ก็สั่งแบบนี้ แล้วใส่ชื่อเดียวกันใน primary_slot_name ของ standby

SELECT pg_create_physical_replication_slot('standby1');

slot ที่ไม่มีใครใช้ ทำให้ WAL โตจนดิสก์เต็ม#

slot บอก primary ว่าห้ามลบ WAL ที่ standby ยังไม่ได้รับ ถ้า standby ตายไปหรือเราเลิกใช้แล้วลืมลบ slot primary ก็จะเก็บ WAL ไว้ใน pg_wal ไปเรื่อย ๆ จนดิสก์เต็ม (Replication slots) ตั้งเพดานไว้ใน postgresql.conf ของ primary

# ค่าเริ่มต้น -1 คือเก็บ WAL ให้ slot ได้ไม่จำกัด
max_slot_wal_keep_size = 50GB

# PostgreSQL 18 ขึ้นไป ค่าเริ่มต้น 0 คือปิด
idle_replication_slot_timeout = 2d

• max_slot_wal_keep_size ให้ slot ค้าง WAL ได้ไม่เกินขนาดนี้ เกินแล้ว primary จะลบ WAL ส่วนเกินทิ้ง และ slot นั้นจะกลายเป็น wal_status = lost
• idle_replication_slot_timeout ยกเลิก slot ที่ไม่มีใครต่อนานเกินค่านี้ PostgreSQL ทำตอน checkpoint และใส่ invalidation_reason = idle_timeout ไว้ให้

ทั้งสองค่าเปลี่ยนแล้วสั่ง SELECT pg_reload_conf(); ได้เลย ไม่ต้อง restart แต่ standby ที่ slot หลุดไปแล้วจะตามต่อไม่ได้ ต้องทำ pg_basebackup ใหม่

promote standby ขึ้นเป็น primary#

ตอน primary ล่ม สั่งบน standby

SELECT pg_promote();

standby จะออกจากโหมด recovery แล้วรับเขียนข้อมูลได้ทันที (หรือสั่ง pg_ctl promote จาก shell ก็ได้) ส่วนถ้าจะกู้ข้อมูลกลับไปถึงเวลาที่ต้องการ (point-in-time recovery) จาก WAL archive ให้ใช้ไฟล์ recovery.signal คู่กับ restore_command และ recovery_target_time แทน (Continuous archiving)