What is logical replication in PostgreSQL?
PostgreSQL ma built-in powerful and flexible feature 6e jene kehvai 6e: Logical Replication.
Aa PostgreSQL version 10 ma introduce thayel hatu and tyar thi aa khub use thaay 6e.
Aane physical replication na jevuj kahi sakay. physical replication ma aapde master server ma execute thai gayela serial commands ne aapde bytes data na form ma mokalie 6ie.
Logical replication ma aapde actually ma table level change ne logical change record (LCR) tarike output plugin dwara convert kare 6e ae j subscriber ne stream dwara mokalie 6ie (LCR mokle 6e). jyare physical ma ek prakare WAL ni jem file moklta je again replica ma jai ne replay thatu.
Physical replication ma aapde badha databases, tables, roles, tables, bases etc... aa badhu mokalvu padtu hatu.
Physical replication na case ma aapdne ka badhu male 6e athva kai maltu nathi.
Jo aapde matra ek single table nej replicate karvu hoi to? aa case ma logical replication no use thay 6e.
Ahiya aapde matra ek subset ne choose karie 6ie jene aapde replicate karvu 6e and PostgreSQL matra ae subset mate DML (Data Manipulation Language) commands j replica par replay karse.
Logical replication ma aapdi pase 2 entities hoi 6e.
- publisher
- subscriber
thoda tables na group je nodes tarike represent kare 6e, tene publisher kehvay 6e.
aa particular group of tables na changes ne receive karva mate, ek subscription signup kare 6e.
Biji ek vaat je logical replication na favor ma 6e ae 6e amount of data replicate karvo pade 6e ae.
Physical replication na case ma aapde data ma kai change na karya hoi to bhi, for example, aapde ek table ma index create kari 6e to aa matra create index no command j nahi jay replica standby server ma, but sathe sathe badhi related items je index sathe relevent hoi ae badhu send thai 6e. ghani vakhat aa khub vadhare network heavy load mokle 6e j standby system mate bottleneck problem sabit thay jaay 6e. To aa case ma jyare physical replication karie to badhu send thatu hoi to aapde simply select na kari sakie k matra aa command nej replica par execute karo.
Aa same problem thay 6e Vaccum command ma, and cluster command ma bhi aam j thay 6e.
Logical replication DDL command ne support nathi kartu, jem k create index, aapde aene separately replica ma execute kari sakie 6ie.
Aa case ma Logical replication ma je data transfer karsu primary to standby server ma ae comparision ma khub o66o hase.
Physical replication na case ma je sync ma 6eni complete configuration identical hovi jaruri 6e, kem k data same location maj place thase. aa case ma aapde primary and replica server mate alag alag version na PostgreSQL server version use nathi kari sakta.
Aana thi ulat, Logical replication ma transfer je 6e ae SQL na level ae thay 6e, to apde alag data structure wada subscriber ne set kari sakie 6ie.
Aa karne aapde logical replication no use migration and updates mate kari sakie 6ie.
Mani lo, publisher server ma ek table 6e jema columns 6e a, b pan subscriber server ma same table ma columns 6e a, b, c, d. to koi problem nathi c & d mate default value set thai jase.
Most important: Jyare aapde vaat karie 6ie Logical Replication ni, to aema kyarey master and slave nahi hoi. Ahi hammesa publisher and subscriber hase. jethi aapde better control kari sakie 6ie and aapdi jaruriyat mujab multiple architecture set kari sakie.
For example, aapde ek subscriber set kari sakie je ek karta vadhare server mathi data pull kare 6e (accept the stream) and ae data ma pa6i aggregate kari sake 6e. athva ek publisher ek karta vadhare subscription server ne data stream kari sake 6e.
Logical replication ni madad thi, Aapda database na darek table mate bhi replication set karvu possible 6e. aa case ma, koi bhi new table banse to ae automatically replicate thai jase, pan logical replication schema objects jem k sequences, indexes and views ne affect kartu nathi.
Understanding the process​
- PostgreSQL ne logically WAL file ne decode karvi pade
- Tyar pa6i, aemathi SQL statements create kare.
- Aa SQL Statements LCR na form ma network par stream thay ane standby replica server par male 6e.
- Finally aa statements standby replica servers par correct transaction order ma apply thai 6e.