{"id":54366,"date":"2019-12-24T00:00:00","date_gmt":"2019-12-23T21:00:00","guid":{"rendered":"https:\/\/prohoster.info\/blog\/blog_prohoster\/kak-vyzhit-sql-baze-v-21-veke-oblaka-kubernetes-i-postgresql-multimaster"},"modified":"2020-02-18T14:02:22","modified_gmt":"2020-02-18T11:02:22","slug":"kak-vyzhit-sql-baze-v-21-veke-oblaka-kubernetes-i-postgresql-multimaster","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/kak-vyzhit-sql-baze-v-21-veke-oblaka-kubernetes-i-postgresql-multimaster","title":{"rendered":"Ako pre\u017ei\u0165 SQL datab\u00e1ze v 21. storo\u010d\u00ed: Cloudy, Kubernetes a PostgreSQL multimaster","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><i>Ahoj, Habr\u00e1ci. Dnes za\u010d\u00ednaj\u00ed lekce prvn\u00ed skupiny kurzu <noindex><a rel=\"nofollow\" href=\"https:\/\/otus.pw\/nwhU\/\">\u00abPostgreSQL\u00bb<\/a><\/noindex>. V souvislosti s t\u00edm bychom v\u00e1m cht\u011bli \u0159\u00edct, jak prob\u00edhal otev\u0159en\u00fd webin\u00e1\u0159 o tomto kurzu.<\/i><\/p>\n<p><img decoding=\"async\" alt=\"Ako pre\u017ei\u0165 SQL datab\u00e1ze v 21. storo\u010d\u00ed: Cloudy, Kubernetes a PostgreSQL multimaster\" src=\"\/wp-content\/uploads\/2019\/12\/e66e0d4919d45284dbdc4696d6cab555.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nV <noindex><a rel=\"nofollow\" href=\"https:\/\/www.youtube.com\/watch?v=yiXP8ihL2eA\">dal\u0161\u00ed otev\u0159enou lekci<\/a><\/noindex> pohovo\u0159ili jsme o tom, s jak\u00fdmi v\u00fdzvami se SQL datab\u00e1ze pot\u00fdkaj\u00ed v \u00e9\u0159e cloud\u016f a Kubernetes. Tak\u00e9 jsme se pod\u00edvali na to, jak se SQL datab\u00e1ze p\u0159izp\u016fsobuj\u00ed a vyv\u00edjej\u00ed pod vlivem t\u011bchto v\u00fdzev.<\/p>\n<p>Webin\u00e1\u0159 vedl <noindex><a rel=\"nofollow\" href=\"https:\/\/otus.pw\/IVe0\/\">Valerij Bezrukov<\/a><\/noindex>, Google Cloud Practice Delivery Manager ve spole\u010dnosti EPAM Systems.<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h3>Kdy\u017e byly stromy mal\u00e9...<\/h3>\n<p>\nNejprve si p\u0159ipome\u0148me, jak za\u010d\u00ednal v\u00fdb\u011br DBMS na konci minul\u00e9ho stolet\u00ed. A nen\u00ed to zase tak t\u011b\u017ek\u00e9, proto\u017ee v\u00fdb\u011br DBMS v t\u011bch dob\u00e1ch za\u010d\u00ednal a kon\u010dil <b>Oracle<\/b>. <\/p>\n<p><img decoding=\"async\" alt=\"Ako pre\u017ei\u0165 SQL datab\u00e1ze v 21. storo\u010d\u00ed: Cloudy, Kubernetes a PostgreSQL multimaster\" src=\"\/wp-content\/uploads\/2019\/12\/5fdf02523892e7477fd47dd01eb21d04.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nNa konci 90. let - na za\u010d\u00e1tku nul se v podstat\u011b neobjevovalo nic jin\u00e9ho, pokud jde o pr\u016fmyslov\u011b \u0161k\u00e1lovateln\u00e9 datab\u00e1ze. Ano, existovaly IBM DB2, Sybase a n\u011bjak\u00e9 dal\u0161\u00ed datab\u00e1ze, kter\u00e9 p\u0159ich\u00e1zely a odch\u00e1zely, ale celkov\u011b nebyly tak n\u00e1padn\u00e9 na pozad\u00ed Oracle. Odpov\u00eddaj\u00edc\u00ed tomu byly dovednosti in\u017een\u00fdr\u016f t\u00e9 doby v\u00e1z\u00e1ny na jedin\u00fd v\u00fdb\u011br, kter\u00fd existoval.<\/p>\n<p>Oracle DBA m\u011bl um\u011bt:<\/p>\n<ul>\n<li> instalovat Oracle Server z distribuce;<\/li>\n<li> nastavovat Oracle Server:<\/li>\n<\/ul>\n<p><\/p>\n<ul>\n<li>init.ora;<\/li>\n<li>listener.ora;<\/li>\n<\/ul>\n<p>\n\u2014 vytv\u00e1\u0159et:<\/p>\n<ul>\n<li>tabulkov\u00e1 prostory;<\/li>\n<li>sch\u00e9mata;<\/li>\n<li>u\u017eivatele;<\/li>\n<\/ul>\n<p>\u2014 prov\u00e1d\u011bt z\u00e1lohov\u00e1n\u00ed a obnovu;<br \/>\n\u2014 prov\u00e1d\u011bt monitorov\u00e1n\u00ed;<br \/>\n\u2014 \u0159e\u0161it neoptim\u00e1ln\u00ed dotazy.<\/p>\n<p>P\u0159itom se od Oracle DBA zejm\u00e9na neo\u010dek\u00e1valo:<\/p>\n<ul>\n<li>um\u011bt vyb\u00edrat optim\u00e1ln\u00ed DBMS nebo jinou technologii pro ukl\u00e1d\u00e1n\u00ed a zpracov\u00e1n\u00ed dat;<\/li>\n<li>zaji\u0161\u0165ovat vysokou dostupnost a horizont\u00e1ln\u00ed \u0161k\u00e1lovatelnost (to nebyla v\u017edy ot\u00e1zka pro DBA);<\/li>\n<li>dob\u0159e zn\u00e1t p\u0159edm\u011bt, infrastrukturu, aplika\u010dn\u00ed architekturu, OS;<\/li>\n<li>prov\u00e1d\u011bt na\u010d\u00edt\u00e1n\u00ed a ukl\u00e1d\u00e1n\u00ed dat, migraci dat mezi r\u016fzn\u00fdmi DBMS.<\/li>\n<\/ul>\n<p>Obecn\u011b plat\u00ed, \u017ee pokud se mluv\u00ed o v\u00fdb\u011bru v t\u011bch dob\u00e1ch, p\u0159ipom\u00ednal v\u00fdb\u011br v sov\u011btsk\u00e9m obchod\u011b na konci 80. let:<\/p>\n<p><img decoding=\"async\" alt=\"Ako pre\u017ei\u0165 SQL datab\u00e1ze v 21. storo\u010d\u00ed: Cloudy, Kubernetes a PostgreSQL multimaster\" src=\"\/wp-content\/uploads\/2019\/12\/70b37faf295170d8507d5c605f4b2047.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<\/p>\n<h3>Na\u0161e doba<\/h3>\n<p>\nOd t\u00e9 doby se samoz\u0159ejm\u011b stromy vyrostly, sv\u011bt se zm\u011bnil a n\u011bjak to vypad\u00e1 takto:<\/p>\n<p><img decoding=\"async\" alt=\"Ako pre\u017ei\u0165 SQL datab\u00e1ze v 21. storo\u010d\u00ed: Cloudy, Kubernetes a PostgreSQL multimaster\" src=\"\/wp-content\/uploads\/2019\/12\/e8698d658bd09e0071f02a672a5cb81e.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nTak\u00e9 se zm\u011bnil trh DBMS, co\u017e je dob\u0159e vid\u011bt na \u010derstv\u00e9 zpr\u00e1v\u011b spole\u010dnosti Gartner:<\/p>\n<p><img decoding=\"async\" alt=\"Ako pre\u017ei\u0165 SQL datab\u00e1ze v 21. storo\u010d\u00ed: Cloudy, Kubernetes a PostgreSQL multimaster\" src=\"\/wp-content\/uploads\/2019\/12\/745f3969a3ad2e65162ef41cda6c0658.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nA zde nelze opomenout, \u017ee svou niku si na\u0161ly cloudy, jejich\u017e popularita roste. Pokud si p\u0159e\u010dtete stejnou zpr\u00e1vu spole\u010dnosti Gartner, uvid\u00edme n\u00e1sleduj\u00edc\u00ed z\u00e1v\u011bry:<\/p>\n<ol>\n<li>Mnoho z\u00e1kazn\u00edk\u016f je na cest\u011b k p\u0159evodu aplikac\u00ed do cloudu. <\/li>\n<li>Nov\u00e9 technol\u00f3gie sa najprv objavuj\u00fa v cloude a nie je ist\u00e9, \u010di sa niekedy presun\u00fa do neoblakovej infra\u0161trukt\u00fary.<\/li>\n<li>Model cenotvorby pod\u013ea princ\u00edpu pay-as-you-go sa stal be\u017en\u00fdm. V\u0161etci chc\u00fa plati\u0165 len za to, \u010do vyu\u017e\u00edvaj\u00fa, a to u\u017e nie je len trend, ale jednoducho kon\u0161tatovanie faktu. <\/li>\n<\/ol>\n<p><\/p>\n<h3>\u010co teraz?<\/h3>\n<p>\nDnes sme v\u0161etci v cloude. Ot\u00e1zky, ktor\u00e9 m\u00e1me, s\u00fa ot\u00e1zkami v\u00fdberu. A ten je obrovsk\u00fd, aj ke\u010f hovor\u00edme len o v\u00fdbere technol\u00f3gi\u00ed DBMS form\u00e1tu On-premises. A m\u00e1me aj spravovan\u00e9 slu\u017eby a SaaS. T\u00fdmto sa v\u00fdber ka\u017ed\u00fdm rokom len komplikuje.<\/p>\n<p>Okrem ot\u00e1zok v\u00fdberu p\u00f4sobia aj <b>obmedzuj\u00face faktory<\/b>:<\/p>\n<ul>\n<li><b>cena<\/b>. Mnoh\u00e9 technol\u00f3gie st\u00e1le nie\u010do stoja;<\/li>\n<li><b>zru\u010dnosti<\/b>. Ak hovor\u00edme o slobodnom softv\u00e9ri, objavuje sa ot\u00e1zka zru\u010dnost\u00ed, ke\u010f\u017ee bezplatn\u00fd softv\u00e9r vy\u017eaduje od \u013eud\u00ed, ktor\u00ed ho nasadzuj\u00fa a prev\u00e1dzkuj\u00fa, dostato\u010dn\u00fa kompetenciu;<\/li>\n<li><b>funkcionalita<\/b>. Nie v\u0161etky slu\u017eby, ktor\u00e9 s\u00fa k dispoz\u00edcii v cloude a postaven\u00e9 napr\u00edklad na z\u00e1klade PostgreSQL, maj\u00fa rovnak\u00e9 funkcie ako PostgreSQL On-premises. To je podstatn\u00fd faktor, ktor\u00fd je potrebn\u00e9 pozna\u0165 a ch\u00e1pa\u0165. Navy\u0161e, tento faktor nadob\u00fada v\u00e4\u010d\u0161\u00ed v\u00fdznam ne\u017e poznanie nejak\u00fdch skryt\u00fdch mo\u017enost\u00ed konkr\u00e9tneho DBMS. <\/li>\n<\/ul>\n<p>\n<b>\u010co sa teraz o\u010dak\u00e1va od DA\/DE:<\/b><\/p>\n<ul>\n<li>dobr\u00e9 porozumenie predmetnej oblasti a aplika\u010dnej architekt\u00fary;<\/li>\n<li>schopnos\u0165 spr\u00e1vne vybra\u0165 vhodn\u00fa technol\u00f3giu DBMS s oh\u013eadom na stanoven\u00fa \u00falohu;<\/li>\n<li>schopnos\u0165 zvoli\u0165 optim\u00e1lny sp\u00f4sob realiz\u00e1cie zvolenej technol\u00f3gie v s\u00favislosti s existuj\u00facimi obmedzeniami;<\/li>\n<li>schopnos\u0165 vykon\u00e1va\u0165 prenos a migr\u00e1ciu d\u00e1t;<\/li>\n<li>schopnos\u0165 realizova\u0165 a prev\u00e1dzkova\u0165 vybran\u00e9 rie\u0161enia.<\/li>\n<\/ul>\n<p>\nNasleduj\u00faci pr\u00edklad <b>na b\u00e1ze GCP<\/b> demonstrates how the choice of a specific technology for data processing is determined by its structure:<\/p>\n<p><img decoding=\"async\" alt=\"Ako pre\u017ei\u0165 SQL datab\u00e1ze v 21. storo\u010d\u00ed: Cloudy, Kubernetes a PostgreSQL multimaster\" src=\"\/wp-content\/uploads\/2019\/12\/fe324d8af9ed8f6afb58ed930dc21327.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nV\u0161imnite si, \u017ee v sch\u00e9me ch\u00fdba PostgreSQL, a to preto, \u017ee sa skr\u00fdva pod terminol\u00f3giou <b>Cloud SQL<\/b>. A ke\u010f sa dostaneme do Cloud SQL, mus\u00edme op\u00e4\u0165 urobi\u0165 v\u00fdber:<\/p>\n<p><img decoding=\"async\" alt=\"Ako pre\u017ei\u0165 SQL datab\u00e1ze v 21. storo\u010d\u00ed: Cloudy, Kubernetes a PostgreSQL multimaster\" src=\"\/wp-content\/uploads\/2019\/12\/bdf523e0b10fbfe753318a02a686a5af.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nJe potrebn\u00e9 poznamena\u0165, \u017ee tento v\u00fdber nie je v\u017edy zrozumite\u013en\u00fd, a preto sa v\u00fdvoj\u00e1ri aplik\u00e1ci\u00ed \u010dasto riadia intu\u00edciou. <\/p>\n<p><b>Celkovo:<\/b><\/p>\n<ol>\n<li>\u010c\u00edm \u010falej, t\u00fdm aktu\u00e1lnej\u0161ia sa st\u00e1va ot\u00e1zka v\u00fdberu. A aj ke\u010f sa pozrieme len na GCP, managed services a SaaS, nejak\u00e9 zmienky o RDBMS sa objavuj\u00fa a\u017e na 4. kroku (a ved\u013ea je Spanner). Plus, v\u00fdber PostgreSQL sa objav\u00ed v\u00f4bec a\u017e na 5. kroku, a ved\u013ea s\u00fa e\u0161te MySQL a SQL Server, tak\u017ee <b>toho je ve\u013ea, ale treba si vybra\u0165<\/b>. <\/li>\n<li>Nesm\u00fa sa zab\u00fada\u0165 ani na obmedzenia vzh\u013eadom na poku\u0161enia. V\u00e4\u010d\u0161inou v\u0161etci chc\u00fa Spanner, ale je drah\u00fd. V\u00fdsledkom je, \u017ee typick\u00fd dopyt vyzer\u00e1 pribli\u017ene takto: <i>\u201eUrobte n\u00e1m, pros\u00edm, Spanner, ale za cenu Cloud SQL, ve\u010f ste profesion\u00e1li!\u201c<\/i><\/li>\n<\/ol>\n<p>\n<img decoding=\"async\" alt=\"Ako pre\u017ei\u0165 SQL datab\u00e1ze v 21. storo\u010d\u00ed: Cloudy, Kubernetes a PostgreSQL multimaster\" src=\"\/wp-content\/uploads\/2019\/12\/cc656b46d37d08fc6aeca937e1e9085b.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<\/p>\n<h3>A \u010do robi\u0165?<\/h3>\n<p>\nBez n\u00e1roku na kone\u010dn\u00fa pravdu m\u00f4\u017eeme poveda\u0165 nasledovn\u00e9:<\/p>\n<p><b>Je potrebn\u00e9 zmeni\u0165 pr\u00edstup k vzdel\u00e1vaniu:<\/b><\/p>\n<ul>\n<li>u\u010di\u0165 tak, ako sa u\u010dilo predt\u00fdm DBA, nem\u00e1 zmysel;<\/li>\n<li>znalosti jedn\u00e9ho produktu s\u00fa dnes u\u017e nedostato\u010dn\u00e9;<\/li>\n<li>a pozna\u0165 desiatky na \u00farovni jedn\u00e9ho \u2014 nemo\u017en\u00e9.<\/li>\n<\/ul>\n<p>\nJe potrebn\u00e9 pozna\u0165 nielen produkt, ale aj:<\/p>\n<ul>\n<li>pr\u00edpad jeho pou\u017eitia;<\/li>\n<li>r\u00f4zne met\u00f3dy nasadenia;<\/li>\n<li>v\u00fdhody a nev\u00fdhody ka\u017edej z met\u00f3d;<\/li>\n<li>podobn\u00e9 a alternat\u00edvne produkty, aby bolo mo\u017en\u00e9 robi\u0165 uvedomel\u00fd a optim\u00e1lny v\u00fdber, nie v\u017edy v prospech zn\u00e1meho produktu.<\/li>\n<\/ul>\n<p>\nA e\u0161te treba vedie\u0165 migrova\u0165 d\u00e1ta a rozumie\u0165 z\u00e1kladn\u00fdm princ\u00edpom integr\u00e1cie s ETL.<\/p>\n<h3>Skuto\u010dn\u00fd pr\u00edpad<\/h3>\n<p>\nV ned\u00e1vnej minulosti bolo potrebn\u00e9 vyrobi\u0165 backend pre mobiln\u00fa aplik\u00e1ciu. V \u010dase za\u010datia pr\u00e1ce na \u0148om bol backend u\u017e vyvinut\u00fd a pripraven\u00fd na nasadenie a t\u00edm v\u00fdvoj\u00e1rov str\u00e1vil na tomto projekte asi dva roky. Pri tom boli stanoven\u00e9 nasledovn\u00e9 \u00falohy:<\/p>\n<ul>\n<li>vybudova\u0165 CI\/CD;<\/li>\n<li>vykona\u0165 rev\u00edziu architekt\u00fary;<\/li>\n<li>spusti\u0165 toto cel\u00e9 do prev\u00e1dzky. <\/li>\n<\/ul>\n<p>\nAplik\u00e1cia bola mikroslu\u017ebov\u00e1 a k\u00f3d na Python\/Django bol vyvinut\u00fd od nuly a hne\u010f v GCP. Pokia\u013e ide o cie\u013eov\u00fa skupinu, predpokladalo sa, \u017ee bud\u00fa dve regi\u00f3ny \u2014 USA a E\u00da, a prem\u00e1vka sa rozde\u013eovala cez glob\u00e1lny load balancer. V\u0161etky pracovn\u00e9 n\u00e1klady a v\u00fdpo\u010dtov\u00e1 z\u00e1\u0165a\u017e fungovali v Google Kubernetes Engine. <\/p>\n<p>Pokia\u013e ide o \u00fadaje, existovali 3 \u0161trukt\u00fary:<\/p>\n<ul>\n<li>Cloud Storage;<\/li>\n<li>Datastore;<\/li>\n<li>Cloud SQL (PostgreSQL).<\/li>\n<\/ul>\n<p>\n<img decoding=\"async\" alt=\"Ako pre\u017ei\u0165 SQL datab\u00e1ze v 21. storo\u010d\u00ed: Cloudy, Kubernetes a PostgreSQL multimaster\" src=\"\/wp-content\/uploads\/2019\/12\/e6e38b1d861b2871edf20fb09378b491.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nM\u00f4\u017ee vznikn\u00fa\u0165 ot\u00e1zka, pre\u010do bol vybran\u00fd Cloud SQL? Pravdupovediac, tak\u00e1to ot\u00e1zka v posledn\u00fdch rokoch vyvol\u00e1va ak\u00fasi nepr\u00edjemn\u00fa pauzu \u2014 zd\u00e1 sa, \u017ee sa \u013eudia za\u010dali hanbi\u0165 za rela\u010dn\u00e9 datab\u00e1zy, ale napriek tomu ich na\u010falej akt\u00edvne pou\u017e\u00edvaj\u00fa ;-). <\/p>\n<p>Pokia\u013e ide o n\u00e1\u0161 pr\u00edpad, Cloud SQL bol vybran\u00fd z nasleduj\u00facich d\u00f4vodov:<\/p>\n<ol>\n<li>Ako bolo spomenut\u00e9, aplik\u00e1cia bola vyvinut\u00e1 pomocou Django a obsahuje model, ktor\u00fd zobrazuje trval\u00e9 d\u00e1ta z SQL datab\u00e1zy ako objekty Python (Django ORM).<\/li>\n<li>Samotn\u00fd framework podporoval dos\u0165 obmedzen\u00fd zoznam SDB:<\/li>\n<\/ol>\n<p><\/p>\n<ul>\n<li>PostgreSQL;<\/li>\n<li>MariaDB;<\/li>\n<li>MySQL;<\/li>\n<li>Oracle;<\/li>\n<li>SQLite.<\/li>\n<\/ul>\n<p>\nNa z\u00e1klade toho sme intuit\u00edvne vybrali PostgreSQL z tohto zoznamu (predsa len nie Oracle, \u017ee?).<\/p>\n<p><b>\u010co ch\u00fdbalo:<\/b><\/p>\n<ul>\n<li>aplik\u00e1cia bola nasaden\u00e1 len v dvoch regi\u00f3noch a v pl\u00e1noch bol pridan\u00fd tret\u00ed (\u00c1zia);<\/li>\n<li>DB sa nach\u00e1dzala v severoamerickom regi\u00f3ne (Iowa);<\/li>\n<li>z poh\u013eadu klienta boli obavy o mo\u017en\u00e9 <b>oneskorenia v pr\u00edstupe<\/b> z Eur\u00f3py a \u00c1zie a <b>v\u00fdpadky <\/b><b>v prev\u00e1dzke<\/b> v pr\u00edpade v\u00fdpadku SDB. <\/li>\n<\/ul>\n<p>\nHoci samotn\u00e9 Django m\u00f4\u017ee pracova\u0165 s viacer\u00fdmi DB paralelne a deli\u0165 ich na \u010d\u00edtanie a z\u00e1pis, z\u00e1pisov v aplik\u00e1cii nebolo a\u017e tak ve\u013ea (viac ako 90 % \u2014 \u010d\u00edtanie). V celku, ak by sa dalo spravi\u0165 <b>read-repliku hlavnej datab\u00e1zy v Eur\u00f3pe a \u00c1zii<\/b>, bolo by to kompromisn\u00e9 rie\u0161enie. A \u010do je na tom tak\u00e9 zlo\u017eit\u00e9?<\/p>\n<p>Zlo\u017eitost spo\u010d\u00edvala v tom, \u017ee klient nechcel ust\u00fapi\u0165 od pou\u017e\u00edvania spravovan\u00fdch slu\u017eieb a Cloud SQL. Mo\u017enosti Cloud SQL s\u00fa moment\u00e1lne obmedzen\u00e9. Cloud SQL podporuje vysok\u00fa dostupnos\u0165 (HA) a read repliku (RR), ale t\u00e1to RR je podporovan\u00e1 len v jednom regi\u00f3ne. Vytvoren\u00edm DB v americkom regi\u00f3ne nie je mo\u017en\u00e9 spravi\u0165 read-repliku v eur\u00f3pskom regi\u00f3ne pomocou Cloud SQL, hoci PostgreSQL to nebr\u00e1ni. Korespondencia so zamestnancami Google nepriniesla v\u00fdsledky a skon\u010dila pr\u00eds\u013eubmi typu \"pozn\u00e1me probl\u00e9m a pracujeme na \u0148om, niekedy sa to vyrie\u0161i\". <\/p>\n<p>Ak by sme zhrnuli mo\u017enosti Cloud SQL v bodoch, vyzeralo by to pribli\u017ene takto:<\/p>\n<p><b>1. Vysok\u00e1 dostupnos\u0165 (HA):<\/b><\/p>\n<ul>\n<li>v r\u00e1mci jedn\u00e9ho regi\u00f3nu;<\/li>\n<li>prostredn\u00edctvom diskovej replik\u00e1cie;<\/li>\n<li>nepou\u017e\u00edvaj\u00fa sa mechanizmy PostgreSQL;<\/li>\n<li>mo\u017en\u00e9 automatick\u00e9 a ru\u010dn\u00e9 spravovanie \u2014 failover\/failback;<\/li>\n<li>pri prepnut\u00ed SDB nie je k dispoz\u00edcii po\u010das nieko\u013ek\u00fdch min\u00fat.<\/li>\n<\/ul>\n<p>\n<b>2. Read replika (RR):<\/b><\/p>\n<ul>\n<li>v r\u00e1mci jedn\u00e9ho regi\u00f3nu;<\/li>\n<li>hot standby;<\/li>\n<li>PostgreSQL streaming replication.<\/li>\n<\/ul>\n<p>\nNavy\u0161e, ako je to obvykl\u00e9, pri v\u00fdbere technol\u00f3gie sa v\u017edy stret\u00e1va\u0161 s nejak\u00fdmi <b>obmedzeniami,<\/b>:<\/p>\n<ul>\n<li>klient nechcel vytv\u00e1ra\u0165 entity a pou\u017e\u00edva\u0165 IaaS, okrem GKE;<\/li>\n<li>klient by nechcel nasadzova\u0165 self service PostgreSQL\/MySQL;<\/li>\n<li>A Google Spanner would actually be suitable if not for its price, but Django ORM cannot work with it; otherwise, it's a good tool.<\/li>\n<\/ul>\n<p>\nGiven the situation, the client posed a tricky question: <i>\u201cCan you create something similar that behaves like Google Spanner but also works with Django ORM?\u201d<\/i><\/p>\n<h3>Solution Option #0<\/h3>\n<p>\nThe first thought that came to mind was:<\/p>\n<ul>\n<li>to stick with CloudSQL;<\/li>\n<li>there will be no built-in replication between regions in any form;<\/li>\n<li>to try to connect a replica to the existing Cloud SQL by PostgreSQL;<\/li>\n<li>to somehow launch a PostgreSQL instance without touching the master.<\/li>\n<\/ul>\n<p>\nUnfortunately, it turned out that this approach isn\u2019t possible since there is no access to the host (it is in another project)\u2014pg_hba, etc., and there is no superuser access either.<\/p>\n<h3>Solution Option #1<\/h3>\n<p>\nAfter further reflection and considering the previous circumstances, the thought process shifted somewhat:<\/p>\n<ul>\n<li>we still try to stay within the framework of CloudSQL, but switch to MySQL, since Cloud SQL by MySQL has an external master, which:<\/li>\n<\/ul>\n<p>\n\u2014 acts as a proxy for an external MySQL;<br \/>\n\u2014 appears as a MySQL instance;<br \/>\n\u2014 is designed for migrating data from other clouds or on-premises.<\/p>\n<p>Since setting up MySQL replication does not require access to the host, it generally worked, but it was very unstable and inconvenient. As we pushed forward, it became quite scary, since we were deploying the entire structure using Terraform, and it turned out that the external master is not supported by Terraform. Yes, Google has a CLI, but for some reason, it only worked intermittently\u2014sometimes it would create it, sometimes it wouldn't. Perhaps this is because the CLI was designed for external data migration, not for replicas.<\/p>\n<p>It became clear that Cloud SQL is completely unsuitable. As they say, we did all we could. <\/p>\n<h3>Solution Option #2<\/h3>\n<p>\nAs we couldn't stay within the bounds of Cloud SQL, we tried to articulate the requirements for a compromise solution. The requirements turned out to be as follows:<\/p>\n<ul>\n<li>pr\u00e1ca v Kubernetes, maxim\u00e1lne vyu\u017eitie zdrojov a mo\u017enost\u00ed Kubernetes (DCS, &#8230;) a GCP (LB, &#8230;);<\/li>\n<li>eliminating clutter from a bunch of unnecessary things in the cloud like HA proxy;<\/li>\n<li>the ability to run HA PostgreSQL or MySQL in the main region; in other regions\u2014HA from the main region's RR plus its copy (for reliability);<\/li>\n<li>multi-master (we didn't want to interface with it, but this wasn\u2019t very critical)<\/li>\n<\/ul>\n<p>. <br \/>\nAs a result of these requirements, a potential solution finally appeared on the horizon.<b>vhodn\u00e9 mo\u017enosti SDB a obvazov<\/b>:<\/p>\n<ul>\n<li>MySQL Galera;<\/li>\n<li>CockroachDB;<\/li>\n<li>n\u00e1stroje PostgreSQL<\/li>\n<\/ul>\n<p>:<br \/>\n\u2014 pgpool-II;<br \/>\n\u2014 Patroni.<\/p>\n<h3>MySQL Galera<\/h3>\n<p>\nTechnol\u00f3gia MySQL Galera bola vyvinut\u00e1 spolo\u010dnos\u0165ou Codership a predstavuje plugin pre InnoDB. Vlastnosti:<\/p>\n<ul>\n<li>multi master;<\/li>\n<li>synchronn\u00e1 replik\u00e1cia;<\/li>\n<li>\u010d\u00edta\u0165 z ak\u00e9hoko\u013evek uzla;<\/li>\n<li>zapisova\u0165 na ak\u00fdko\u013evek uzol;<\/li>\n<li>vstavan\u00fd mechanizmus HA;<\/li>\n<li>existuje Helm chart od Bitnami.<\/li>\n<\/ul>\n<p><\/p>\n<h3>CockroachDB<\/h3>\n<p>\nPod\u013ea opisu je to \u00faplne \u00fa\u017easn\u00e1 vec a predstavuje open source projekt nap\u00edsan\u00fd v Go. Hlavn\u00fdm \u00fa\u010dastn\u00edkom je Cockroach Labs (zalo\u017een\u00e1 b\u00fdval\u00fdmi zamestnancami z Googlu). T\u00e1to rela\u010dn\u00e1 SDB bola od za\u010diatku vytvoren\u00e1 ako rozdelen\u00e1 (s horizont\u00e1lnym \u0161k\u00e1lovan\u00edm \"z krabice\") a odoln\u00e1 vo\u010di zlyhaniu. Jej autori zo spolo\u010dnosti si stanovili cie\u013e \"kombinova\u0165 bohatstvo funk\u010dnosti SQL s horizont\u00e1lnou dostupnos\u0165ou be\u017enou pre rie\u0161enia NoSQL.\"<\/p>\n<p>Z pr\u00edjemn\u00e9ho bonusu \u2014 podpora postgres protokolu pripojenia. <\/p>\n<h3>Pgpool<\/h3>\n<p>\nJe to nadstavba nad PostgreSQL, v skuto\u010dnosti nov\u00e1 entita, ktor\u00e1 preber\u00e1 v\u0161etky pripojenia a spracov\u00e1va ich. M\u00e1 svoj load balancer a parser, licencia je BSD. Poskytuje \u0161irok\u00e9 mo\u017enosti, ale vyzer\u00e1 trochu desivo, preto\u017ee existencia novej entity by mohla by\u0165 zdrojom niektor\u00fdch dodato\u010dn\u00fdch dobrodru\u017estiev. <\/p>\n<h3>Patroni<\/h3>\n<p>\nTo je posledn\u00e9, na \u010do padol poh\u013ead, a ako sa uk\u00e1zalo, nie nadarmo. Patroni je open source n\u00e1stroj, ktor\u00fd v podstate predstavuje daemon na Pythone, umo\u017e\u0148uj\u00faci automaticky spravova\u0165 klastre PostgreSQL s r\u00f4znymi typmi replik\u00e1cie a automatick\u00fdm prep\u00ednan\u00edm rol\u00ed. Zariadenie sa uk\u00e1zalo by\u0165 ve\u013emi zauj\u00edmav\u00e9, preto\u017ee je dobre integrovan\u00e9 s K8s a neprin\u00e1\u0161a \u017eiadne nov\u00e9 entity. <\/p>\n<h3>\u010co sme nakoniec vybrali<\/h3>\n<p>\nV\u00fdber nebol jednoduch\u00fd:<\/p>\n<ol>\n<li><b>CockroachDB <\/b>\u2014 skvel\u00e9, ale riskantn\u00e9;<\/li>\n<li><b>MySQL Galera<\/b> \u2014 tie\u017e celkom dobr\u00e9, pou\u017e\u00edva sa na viacer\u00fdch miestach, ale MySQL;<\/li>\n<li><b>Pgpool <\/b>\u2014 pr\u00edli\u0161 ve\u013ea zbyto\u010dn\u00fdch ent\u00edt, tak\u00e1 si integrovanos\u0165 s cloudom a K8s;<\/li>\n<li><b>Patroni <\/b>\u2014 vynikaj\u00faca integr\u00e1cia s K8s, \u017eiadne zbyto\u010dn\u00e9 entity, dobre sa integruje s GCP LB.<\/li>\n<\/ol>\n<p>\nTak\u017ee v\u00fdber padol na Patroni. <\/p>\n<h3>Zhrnutie<\/h3>\n<p>\nNastal \u010das zhrn\u00fa\u0165 kr\u00e1tke v\u00fdsledky. \u00c1no, svet IT infra\u0161trukt\u00fary sa z\u00e1sadne zmenil a toto je len za\u010diatok. A ak kedysi boli cloudov\u00e9 rie\u0161enia len in\u00fdm typom infra\u0161trukt\u00fary, teraz je to in\u00e9. Navy\u0161e, inov\u00e1ci\u00ed v cloude sa neust\u00e1le objavuj\u00fa a mohli by sa objavova\u0165 len v cloude a a\u017e nesk\u00f4r, pomocou startupov, pren\u00e1\u0161a\u0165 do on-premises.<\/p>\n<p>Pokia\u013e ide o SQL, SQL tu zostane. To znamen\u00e1, \u017ee je d\u00f4le\u017eit\u00e9 pozna\u0165 PostgreSQL a MySQL a vedie\u0165 s nimi pracova\u0165, no e\u0161te d\u00f4le\u017eitej\u0161ie je vedie\u0165 ich spr\u00e1vne aplikova\u0165.<br \/>\n<br \/>Zdroj: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/otus\/blog\/481644\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u0438\u0432\u0435\u0442, \u0445\u0430\u0431\u0440\u043e\u0432\u0447\u0430\u043d\u0435. \u0421\u0435\u0433\u043e\u0434\u043d\u044f \u0441\u0442\u0430\u0440\u0442\u0443\u044e\u0442 \u0437\u0430\u043d\u044f\u0442\u0438\u044f \u0432 \u043f\u0435\u0440\u0432\u043e\u0439 \u0433\u0440\u0443\u043f\u043f\u0435 \u043a\u0443\u0440\u0441\u0430 \u00abPostgreSQL\u00bb. \u0412 \u0441\u0432\u044f\u0437\u0438 \u0441 \u044d\u0442\u0438\u043c, \u0445\u043e\u0442\u0438\u043c \u0440\u0430\u0441\u0441\u043a\u0430\u0437\u0430\u0442\u044c \u0432\u0430\u043c \u043e \u0442\u043e\u043c, \u043a\u0430\u043a \u043f\u0440\u043e\u0445\u043e\u0434\u0438\u043b \u043e\u0442\u043a\u0440\u044b\u0442\u044b\u0439 \u0432\u0435\u0431\u0438\u043d\u0430\u0440 \u043f\u043e \u0434\u0430\u043d\u043d\u043e\u043c\u0443 \u043a\u0443\u0440\u0441\u0443. \u0412 \u043e\u0447\u0435\u0440\u0435\u0434\u043d\u043e\u043c \u043e\u0442\u043a\u0440\u044b\u0442\u043e\u043c \u0443\u0440\u043e\u043a\u0435 \u043f\u043e\u0433\u043e\u0432\u043e\u0440\u0438\u043b\u0438 \u043e \u0442\u043e\u043c, \u0441 \u043a\u0430\u043a\u0438\u043c\u0438 \u0432\u044b\u0437\u043e\u0432\u0430\u043c\u0438 \u0441\u0442\u043e\u043b\u043a\u043d\u0443\u043b\u0438\u0441\u044c SQL-\u0431\u0430\u0437\u044b \u0432 \u044d\u0440\u0443 \u043e\u0431\u043b\u0430\u043a\u043e\u0432 \u0438 Kubernetes. \u0410 \u0437\u0430\u043e\u0434\u043d\u043e \u0440\u0430\u0441\u0441\u043c\u043e\u0442\u0440\u0435\u043b\u0438, \u043a\u0430\u043a \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 SQL \u043f\u0440\u0438\u0441\u043f\u043e\u0441\u0430\u0431\u043b\u0438\u0432\u0430\u044e\u0442\u0441\u044f \u0438 \u043c\u0443\u0442\u0438\u0440\u0443\u044e\u0442 \u043f\u043e\u0434 \u0432\u043e\u0437\u0434\u0435\u0439\u0441\u0442\u0432\u0438\u0435\u043c [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-54366","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.5 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041f\u0440\u0438\u0432\u0435\u0442, \u0445\u0430\u0431\u0440\u043e\u0432\u0447\u0430\u043d\u0435. \u0421\u0435\u0433\u043e\u0434\u043d\u044f \u0441\u0442\u0430\u0440\u0442\u0443\u044e\u0442 \u0437\u0430\u043d\u044f\u0442\u0438\u044f \u0432 \u043f\u0435\u0440\u0432\u043e\u0439 \u0433\u0440\u0443\u043f\u043f\u0435 \u043a\u0443\u0440\u0441\u0430 \u00abPostgreSQL\u00bb.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/kak-vyzhit-sql-baze-v-21-veke-oblaka-kubernetes-i-postgresql-multimaster\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.5\" \/>\n\t\t<meta property=\"og:locale\" content=\"sk_SK\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u041a\u0430\u043a \u0432\u044b\u0436\u0438\u0442\u044c SQL-\u0431\u0430\u0437\u0435 \u0432 21 \u0432\u0435\u043a\u0435: \u043e\u0431\u043b\u0430\u043a\u0430, Kubernetes \u0438 PostgreSQL multimaster | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041f\u0440\u0438\u0432\u0435\u0442, \u0445\u0430\u0431\u0440\u043e\u0432\u0447\u0430\u043d\u0435. \u0421\u0435\u0433\u043e\u0434\u043d\u044f \u0441\u0442\u0430\u0440\u0442\u0443\u044e\u0442 \u0437\u0430\u043d\u044f\u0442\u0438\u044f \u0432 \u043f\u0435\u0440\u0432\u043e\u0439 \u0433\u0440\u0443\u043f\u043f\u0435 \u043a\u0443\u0440\u0441\u0430 \u00abPostgreSQL\u00bb.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/kak-vyzhit-sql-baze-v-21-veke-oblaka-kubernetes-i-postgresql-multimaster\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2019-12-23T21:00:00+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-02-18T11:02:22+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47 Ako pre\u017ei\u0165 SQL datab\u00e1zu v 21. storo\u010d\u00ed: cloudov\u00e9 slu\u017eby, Kubernetes a PostgreSQL multimaster | ProHoster","description":"Ahojte, Habr\u00e1ci. Dnes za\u010d\u00ednaj\u00fa hodiny prvej skupiny kurzu \u201ePostgreSQL\u201c.","canonical_url":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/kak-vyzhit-sql-baze-v-21-veke-oblaka-kubernetes-i-postgresql-multimaster","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sk_SK","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041a\u0430\u043a \u0432\u044b\u0436\u0438\u0442\u044c SQL-\u0431\u0430\u0437\u0435 \u0432 21 \u0432\u0435\u043a\u0435: \u043e\u0431\u043b\u0430\u043a\u0430, Kubernetes \u0438 PostgreSQL multimaster | ProHoster","og:description":"\u041f\u0440\u0438\u0432\u0435\u0442, \u0445\u0430\u0431\u0440\u043e\u0432\u0447\u0430\u043d\u0435. \u0421\u0435\u0433\u043e\u0434\u043d\u044f \u0441\u0442\u0430\u0440\u0442\u0443\u044e\u0442 \u0437\u0430\u043d\u044f\u0442\u0438\u044f \u0432 \u043f\u0435\u0440\u0432\u043e\u0439 \u0433\u0440\u0443\u043f\u043f\u0435 \u043a\u0443\u0440\u0441\u0430 \u00abPostgreSQL\u00bb.","og:url":"https:\/\/prohoster.info\/sk\/blog\/administrirovanie\/kak-vyzhit-sql-baze-v-21-veke-oblaka-kubernetes-i-postgresql-multimaster","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-12-23T21:00:00+00:00","article:modified_time":"2020-02-18T11:02:22+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"54366","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-24 11:06:21","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 10:30:12","updated":"2026-01-24 11:06:21","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/54366","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/comments?post=54366"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/54366\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media?parent=54366"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/categories?post=54366"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/tags?post=54366"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}