Скалирање и репликација на базите на податоци

Логичка репликација во PostgreSQL

Логичката репликација е напредна функционалност во PostgreSQL што овозможува реплицирање на податоци помеѓу две или повеќе PostgreSQL инстанци. За разлика од физичката репликација која копира блокови на ниво на диск, логичката репликација работи на ниво на податоци и овозможува поголема флексибилност. Логичката репликација е метод за реплицирање на податоци базиран примарните клучеви на табелите. Во суштина, е пример за Publisher-Subscriber шаблонот каде што главната, оперативна база (master, publisher) ги публикува промените кон репликите (subscriber). Логичката репликација и концептот на „праќање“ на податоци се извршува врз WAL-от (Write Ahead Log), при што оперативната база и репликата се синхнорнизираат според последниот испратен и последниот примен и потврден LSN (Log Sequence Number). Во продолежние следуваат примери за конфиграција на логичка репликација помеѓу две PostgreSQL инстанци.

Подесување параметри

Како што е претходно наведено, логичката репликација работи врз WAL-от, па така најпрвин потребно е да ја зголемиме неговата деталност. Зависно од перформансите на машините каде што се хостирани двете инстанци меѓу кои ја поставуваме репликацијата, дополнително можеме да ги подесуваме параметри за број на процеси, worker-и и слично, кои директно влијаат на перформансите на самиот процес на репликација.

wal_level = logical                    # Задолжително треба да биде поставено на logical за да функционира репликацијата
max_worker_processes = 8               # Број на worker-и кои може да бидат истовремено стартувани, зависи од бројот на јадра на процесорот
max_wal_senders = 10                   # Број на активни реплики
max_logical_replication_workers = 4    # Колку од вкупните worker-и може да се користат за реплиакција
max_sync_workers_per_subscription = 2  # Колку worker-и може да се користат при иницијлната синхронизација на двете бази

Корисник и привилегии за репликација

За да може репликацијата да функционира, потребно е да имаме корисник кој ќе има REPLICATION привилегии и дополнително, истиот тој корисник потребно е да има привилегија да се поврзе на оперативната база, да може да ги користи шемите кои ги реплицираме и да може да извршува SELECT изрази врз табелите од тие шеми

CREATE USER replica_user WITH REPLICATION LOGIN PASSWORD 'replica_pass';

GRANT CONNECT ON DATABASE db_name TO replica_user;
GRANT USAGE ON SCHEMA public TO replica_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replica_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO replica_user;

-- Со оваа команда, се доделува SELECT привилегија на репликацискиот корисник за секоја нова табела во шемата
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replica_user;

Конфигурирање на оперативната база (Publisher)

За да може репликата да се „закачи“ на оперативната база и да слуша на промените кои истата ги публикува потребно е да креираме PUBLICATION, во кој ќе бидат додадени табелите кои сакаме да ги реплицираме. Дополнително, доколку е потребно, можеме да воведеме ограничување на тоа кои колони од табелите сакаме да се реплицираат или пак да реплицираме само филтрирани записи според одреден филтер.

CREATE PUBLICATION test_pub;

ALTER PUBLICATION test_pub ADD TABLE table1;
ALTER PUBLICATION test_pub ADD TABLE table2(id, col1, col2, col3);
ALTER PUBLICATION test_pub SET TABLE test_pub WHERE (col1 > 10));

Доколку сакаме да престанеме со репликација на одредена табела, потребно е истата да се отстрани од PUBLICATION

ALTER PUBLICATION test_pub DROP TABLE table1;

Конфигурирање на реплика (Subscriber)

За успешно извршување на процесот на репликација, потребно е шемата на репликата да биде идентична со онаа на оперативната база, односно табелите да ги имаат сите колони кои се реплицираат и типовите да бидат меѓусебно компатибилни. Ова би значело дека доколку во оперативната база користиме кориснички дефиниран тип (енумерација), истата таа колона би можела да биде од тип текст во репликата. Дополнителни колони во репликата се дозволени.

По креирање на шемата на базата реплика, потребно е да креираме SUBSCRIPTION, каде што ќе ги наведеме конекциските параметри до оперативната база и дополнителни други параметри по потреба.

CREATE SUBSCRIPTION test_sub
CONNECTION 'host=192.168.1.100 port=5432 dbname=db_name user=replica_user password=replica_pass'
PUBLICATION test_pub
WITH (
    copy_data = true,           -- Дали да се започне се синхронизација отпочеток
    enabled = true,             -- Дали слотот веднаш да биде активен
    slot_name = 'test_slot',
    synchronous_commit = off    -- Дали да се овозможи асинхрона репликација
);

Со ова процесот на репликацијата започнува. Доколку правиме некаква промена во PUBLICATION на оперативната база, потребно е на репликата да ја извршиме следната команда

ALTER SUBSCRIPTION test_sub REFRESH PUBLICATION;

Подолу се прикачени SQL скрипти кои треба да се извршат на оперативната база и на репликата со цел да се воспостави логичка репликација