Replication — Streaming Replication, asoslar

~20 daq · PostgreSQL · Xavfsizlik va Boshqaruv

Production serverda bitta PostgreSQL. Server ishdan chiqsa — ilova to'xtaydi. Yoki o'qish so'rovlari juda ko'p — bitta server yetmayapti.

Replication — ma'lumotni real vaqtda boshqa serverga nusxalash. Birida xato bo'lsa — ikkinchisi ishlaydi.

Replication nima?

Primary (yozish)  ──WAL──►  Standby (o'qish)
     ↓ o'zgarishlar            ↓ nusxa
  yoziladi                  olinadi

Primary — yozish va o'qish. Standby — faqat o'qish (yoki failover uchun).

Streaming Replication

WAL (Write-Ahead Log) fayllarini real vaqtda standby ga uzatish:

Primary serverni sozlash

postgresql.conf:

wal_level = replica
max_wal_senders = 3

pg_hba.conf:

host  replication  replication_user  standby_ip/32  md5

-- Replication foydalanuvchisi
CREATE USER replication_user WITH REPLICATION PASSWORD 'parol';

Standby serverni sozlash

# Primary dan nusxa olish
pg_basebackup -h primary_ip -U replication_user \
  -D /var/lib/postgresql/data -P -Xs -R
# -R — standby.signal va postgresql.auto.conf yaratadi

# postgresql.auto.conf (avtomatik)
primary_conninfo = 'host=primary_ip user=replication_user password=parol'

Standby ni ishga tushirish:

pg_ctlcluster 16 main start

Replikatsiya holati

Primary da:

SELECT client_addr, state, sent_lsn, write_lsn, replay_lsn, sync_state
FROM pg_stat_replication;

-- state: streaming — normal ishlayapti
-- replay_lsn — standby qancha orqada

Standby da:

SELECT now() - pg_last_xact_replay_timestamp() AS lag;
-- Standby qancha orqada (soniyalar)

Failover — standby ni primary ga aylantirish

Primary ishdan chiqqanda:

# Standby ni promote qilish
pg_ctlcluster 16 main promote
# yoki
touch /var/lib/postgresql/data/failover.signal

Endi standby yozish ham qabul qiladi. Ilova connection string ni yangi primary ga o'zgartiradi.

Read replica — o'qishni taqsimlash

Hisobot so'rovlari read replica ga yuboriladi — primary yukini kamaytiradi:

const primaryPool = new Pool({ host: 'primary' });   // yozish
const replicaPool = new Pool({ host: 'replica' });   // o'qish

// Yozish — primary
await primaryPool.query('INSERT INTO orders ...');

// O'qish (hisobot) — replica
const { rows } = await replicaPool.query('SELECT * FROM orders WHERE ...');

Tez-tez uchraydigan xatolar

Replikatsiya lag — kechikish:

Sekin tarmoq yoki ko'p yozish — standby orqada qoladi. Kritik o'qishlar primary dan olinsin.

Standby ni o'qish uchun ulanishni unutish:

-- Standby da yozishga urinish
INSERT INTO ... -- ERROR: cannot execute INSERT in a read-only transaction

Xulosa

Streaming Replication:
  Primary  → WAL → Standby
  Primary  → yozish va o'qish
  Standby  → faqat o'qish (read replica)
           → failover uchun zaxira

Sozlash:
  wal_level = replica
  max_wal_senders = 3
  pg_basebackup + standby.signal

Monitoring:
  pg_stat_replication — primary da lag ko'rish

Keyingi dars

57-dars: Monitoring — pg_stat_activity, pg_stat_statements, slow queries