{"id":94417,"date":"2020-09-16T19:42:31","date_gmt":"2020-09-16T17:42:31","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/u-nas-tam-postgres-no-ya-hz-chto-s-nim-delat-s"},"modified":"2020-09-16T19:42:31","modified_gmt":"2020-09-16T17:42:31","slug":"u-nas-tam-postgres-no-ya-hz-chto-s-nim-delat-s","status":"publish","type":"post","link":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/u-nas-tam-postgres-no-ya-hz-chto-s-nim-delat-s","title":{"rendered":"M\u00e1me tam Postgres, ale nev\u00edm, co s n\u00edm d\u011blat (c)","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Toto je cit\u00e1t jednoho z m\u00fdch zn\u00e1m\u00fdch, kter\u00fd se kdysi obr\u00e1til na m\u011b s dotazem ohledn\u011b Postgresu. Tehdy jsme za p\u00e1r dn\u00ed vy\u0159e\u0161ili jeho probl\u00e9m a pod\u011bkoval mi s t\u00edm, \u017ee dodal: \u201eDob\u0159e, kdy\u017e m\u00e1te zn\u00e1m\u00e9ho DBA.\u201c<\/p>\n

Ale co d\u011blat, kdy\u017e nem\u00e1te zn\u00e1m\u00e9ho DBA? Odpov\u011bd\u00ed m\u016f\u017ee b\u00fdt mnoho, od hled\u00e1n\u00ed mezi p\u0159\u00e1teli p\u0159\u00e1tel po studium ot\u00e1zky samostatn\u011b. Ale a\u0165 u\u017e v\u00e1s napadne jak\u00e1koliv odpov\u011b\u010f, m\u00e1m pro v\u00e1s dobrou zpr\u00e1vu. V testovac\u00edm re\u017eimu jsme spustili doporu\u010dovac\u00ed slu\u017ebu pro Postgres a v\u0161e kolem n\u011bj. Co to vlastn\u011b je a jak jsme se k tomu dostali?<\/p>\n

Pro\u010d to v\u0161echno?<\/h2>\n

Postgres nen\u00ed rozhodn\u011b jednoduch\u00fd, a n\u011bkdy je to velmi slo\u017eit\u00e9. Z\u00e1vis\u00ed to na m\u00ed\u0159e zapojen\u00ed a odpov\u011bdnosti.<\/p>\n

Ti, kte\u0159\u00ed pracuj\u00ed v operac\u00edch, by m\u011bli db\u00e1t na to, aby Postgres jako slu\u017eba fungoval spr\u00e1vn\u011b a stabiln\u011b \u2014 sledovat vyu\u017eit\u00ed zdroj\u016f, dostupnost, adekv\u00e1tnost konfigurace, pravideln\u011b prov\u00e1d\u011bt aktualizace a kontroly zdrav\u00ed. Ti, kte\u0159\u00ed se zab\u00fdvaj\u00ed v\u00fdvojem a p\u00ed\u0161\u00ed aplikace, by m\u011bli ve zjednodu\u0161en\u00e9 podob\u011b sledovat, jak aplikace interaguje se z\u00e1kladnou a zda nevytv\u00e1\u0159\u00ed havarijn\u00ed situace, kter\u00e9 by mohly syst\u00e9m zni\u010dit. Pokud m\u00e1 \u010dlov\u011bk sm\u016flu, \u017ee je technick\u00fdm leadem\/nebo \u0159editelem, je pro n\u011bj d\u016fle\u017eit\u00e9, aby Postgres celkov\u011b fungoval spolehliv\u011b, p\u0159edv\u00eddateln\u011b a nep\u0159in\u00e1\u0161el probl\u00e9my, p\u0159i\u010dem\u017e by bylo dobr\u00e9, kdy\u017e se s\u00e1m p\u0159\u00edli\u0161 hloub\u011bji a dlouho nezaplet\u00e1 do Postgresu.<\/p>\n

V ka\u017ed\u00e9m z t\u011bchto p\u0159\u00edpad\u016f jste vy a Postgres. Aby bylo mo\u017en\u00e9 dob\u0159e spravovat Postgres, je pot\u0159eba v n\u011bm m\u00edt slu\u0161n\u00e9 znalosti a p\u0159edstavovat si, jak je postaven. Pokud nen\u00ed Postgres p\u0159\u00edmou specializac\u00ed, m\u016f\u017eete v\u011bnovat jeho studiu pom\u011brn\u011b hodn\u011b \u010dasu. V ide\u00e1ln\u00edm p\u0159\u00edpad\u011b, kdy m\u00e1te \u010das a chu\u0165, nen\u00ed v\u017edy jasn\u00e9, odkud za\u010d\u00edt, jak\u00fdm sm\u011brem se ub\u00edrat.<\/p>\n

I kdy\u017e se obklop\u00edte monitoringem, kter\u00fd by m\u011bl zjednodu\u0161it provoz, ot\u00e1zka odborn\u00fdch znalost\u00ed z\u016fst\u00e1v\u00e1 otev\u0159en\u00e1. Aby bylo mo\u017en\u00e9 \u010d\u00edst a ch\u00e1pat grafy, je pot\u0159eba m\u00edt st\u00e1le dobr\u00e9 pov\u011bdom\u00ed o tom, jak Postgres funguje. Jinak se ka\u017ed\u00fd monitoring prom\u011bn\u00ed v smutn\u00e9 obr\u00e1zky a spam od alert\u016f v n\u00e1hodn\u00e9m \u010dase b\u011bhem dne.<\/p>\n

Weaponry<\/a><\/noindex> je speci\u00e1ln\u011b navr\u017een pro to, aby usnadnil provoz Postgresu. Slu\u017eba shroma\u017e\u010fuje a analyzuje data o Postgresu a poskytuje doporu\u010den\u00ed, co by se dalo vylep\u0161it. <\/p>\n

Hlavn\u00edm c\u00edlem slu\u017eby je poskytovat jasn\u00e1 doporu\u010den\u00ed, kter\u00e1 d\u00e1vaj\u00ed p\u0159edstavu o tom, co se d\u011bje a co je t\u0159eba ud\u011blat d\u00e1l.<\/em> <\/p>\n

Pro specialisty, kte\u0159\u00ed nemaj\u00ed odborn\u00e9 znalosti, poskytuj\u00ed doporu\u010den\u00ed v\u00fdchodisko pro dal\u0161\u00ed vzd\u011bl\u00e1v\u00e1n\u00ed. Pokro\u010dil\u00fdm specialist\u016fm doporu\u010den\u00ed ukazuj\u00ed na aspekty, na kter\u00e9 by se m\u011bli zam\u011b\u0159it. V tomto ohledu slou\u017e\u00ed Weaponry jako pomocn\u00edk, kter\u00fd vykon\u00e1v\u00e1 rutinn\u00ed \u00fakoly p\u0159i vyhled\u00e1v\u00e1n\u00ed probl\u00e9m\u016f nebo nedostatk\u016f, kter\u00e9 si zaslou\u017e\u00ed zvl\u00e1\u0161tn\u00ed pozornost. Weaponry lze p\u0159irovnat k linteru, kter\u00fd kontroluje Postgres a upozor\u0148uje na nedostatky.<\/p>\n

Jak\u00e1 je aktu\u00e1ln\u00ed situace<\/h2>\n

V tuto chv\u00edli Weaponry<\/a><\/noindex> je v testovac\u00edm re\u017eimu a na bezplatn\u00e9 b\u00e1zi, registrace je zat\u00edm do\u010dasn\u011b omezen\u00e1. Spolu s n\u011bkolika dobrovoln\u00edky vylep\u0161ujeme doporu\u010dovac\u00ed engine na t\u00e9m\u011b\u0159 bojov\u00fdch datab\u00e1z\u00edch, odhalujeme fale\u0161n\u00e1 pozitivn\u00ed upozorn\u011bn\u00ed a pracujeme na textu doporu\u010den\u00ed. <\/p>\n

Mimochodem, doporu\u010den\u00ed jsou zat\u00edm pom\u011brn\u011b p\u0159\u00edmo\u010dar\u00e1 \u2014 jednodu\u0161e \u0159\u00edkaj\u00ed, co a jak d\u011blat, bez dal\u0161\u00edch podrobnost\u00ed \u2014 tak\u017ee v prvn\u00edch chv\u00edl\u00edch bude nutn\u00e9 p\u0159ech\u00e1zet na souvisej\u00edc\u00ed odkazy nebo to dodate\u010dn\u011b vyhled\u00e1vat. Kontroly a doporu\u010den\u00ed pokr\u00fdvaj\u00ed nastaven\u00ed syst\u00e9mu a hardwaru, konfiguraci samotn\u00e9ho Postgresu, vnit\u0159n\u00ed sch\u00e9ma, pou\u017eit\u00e9 zdroje. V pl\u00e1nech je je\u0161t\u011b spousta v\u011bc\u00ed, kter\u00e9 je t\u0159eba p\u0159idat. <\/p>\n

A samoz\u0159ejm\u011b hled\u00e1me dobrovoln\u00edky, kte\u0159\u00ed jsou ochotni vyzkou\u0161et slu\u017ebu a poskytnout zp\u011btnou vazbu. M\u00e1me tak\u00e9 demo<\/a><\/noindex>, m\u016f\u017eete se pod\u00edvat. Pokud jste pochopili, \u017ee to pot\u0159ebujete a jste p\u0159ipraveni to vyzkou\u0161et, napi\u0161te n\u00e1m na e-mail<\/a><\/noindex>.<\/p>\n

Aktualizov\u00e1no 2020-09-16. Jak za\u010d\u00edt.<\/h2>\n

Po registraci je u\u017eivateli nab\u00eddnuto vytvo\u0159it projekt \u2014 ten umo\u017e\u0148uje sdru\u017eovat instance datab\u00e1z\u00ed do skupin. Po vytvo\u0159en\u00ed projektu je u\u017eivatel p\u0159esm\u011brov\u00e1n na instrukce pro nastaven\u00ed a instalaci agenta. Pokud to shrneme, je pot\u0159eba vytvo\u0159it u\u017eivatele pro agenta, pot\u00e9 st\u00e1hnout instala\u010dn\u00ed skript pro agenta a spustit ho. V shell p\u0159\u00edkazech to vypad\u00e1 p\u0159ibli\u017en\u011b takto:<\/p>\n

psql -c \"CREATE ROLE pgscv WITH LOGIN SUPERUSER PASSWORD 'A7H8Wz6XFMh21pwA'\"\nexport PGSCV_PG_PASSWORD=A7H8Wz6XFMh21pwA\ncurl -s https:\/\/dist.weaponry.io\/pgscv\/install.sh |sudo -E sh -s - 1 6ada7a04-a798-4415-9427-da23f72c14a5<\/code><\/pre>\n
Pokud je na hostiteli nainstalov\u00e1n pgbouncer, je t\u0159eba pro p\u0159ipojen\u00ed agenta tak\u00e9 vytvo\u0159it u\u017eivatele. Konkr\u00e9tn\u00ed zp\u016fsob nastaven\u00ed u\u017eivatele v pgbouncer m\u016f\u017ee b\u00fdt velmi variabiln\u00ed a siln\u011b z\u00e1vis\u00ed na pou\u017eit\u00e9 konfiguraci. Obecn\u011b se nastaven\u00ed skl\u00e1d\u00e1 z p\u0159id\u00e1n\u00ed u\u017eivatele do stats_users<\/em> konfigura\u010dn\u00edho souboru (obvykle pgbouncer.ini<\/em>) a zaps\u00e1n\u00ed hesla (nebo jeho hash) do souboru uveden\u00e9ho v parametru auth_file.<\/em> Po zm\u011bn\u011b stats_users je t\u0159eba restartovat pgbouncer.<\/p>\n
Skript install.sh p\u0159ij\u00edm\u00e1 n\u011bkolik povinn\u00fdch argument\u016f, kter\u00e9 jsou jedine\u010dn\u00e9 pro ka\u017ed\u00fd projekt, a prost\u0159ednictv\u00edm prom\u011bnn\u00fdch prost\u0159ed\u00ed p\u0159ij\u00edm\u00e1 \u00fadaje o vytvo\u0159en\u00fdch u\u017eivatel\u00edch. N\u00e1sledn\u011b skript spou\u0161t\u00ed agenta v bootstrap re\u017eimu \u2014 agent se zkop\u00edruje do PATH, vytvo\u0159\u00ed konfiguraci s \u00fadaji, syst\u00e9movou jednotku systemd a spou\u0161t\u00ed se jako slu\u017eba systemd. 
T\u00edmto se instalace kon\u010d\u00ed. B\u011bhem p\u00e1r minut se datab\u00e1zov\u00fd instance objev\u00ed v seznamu hostitel\u016f v rozhran\u00ed a m\u016f\u017eete ji\u017e zkontrolovat prvn\u00ed doporu\u010den\u00ed. D\u016fle\u017eit\u00fd bod je, \u017ee mnoh\u00e1 doporu\u010den\u00ed vy\u017eaduj\u00ed velk\u00e9 mno\u017estv\u00ed nahromad\u011bn\u00fdch metrik (alespo\u0148 za den).<\/p>\n
Zdroj: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u042d\u0442\u043e \u0446\u0438\u0442\u0430\u0442\u0430 \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u043c\u043e\u0438\u0445 \u0437\u043d\u0430\u043a\u043e\u043c\u044b\u0445 \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043a\u043e\u0433\u0434\u0430-\u0442\u043e \u0434\u0430\u0432\u043d\u043e \u043e\u0431\u0440\u0430\u0449\u0430\u043b\u0441\u044f \u043a\u043e \u043c\u043d\u0435 \u0441 \u0432\u043e\u043f\u0440\u043e\u0441\u043e\u043c \u043f\u0440\u043e Postgres. \u0422\u043e\u0433\u0434\u0430 \u043c\u044b \u0437\u0430 \u043f\u0430\u0440\u0443 \u0434\u043d\u0435\u0439 \u043f\u043e\u0440\u0435\u0448\u0430\u043b\u0438 \u0435\u0433\u043e \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u0443 \u0438 \u043f\u043e\u0431\u043b\u0430\u0433\u043e\u0434\u0430\u0440\u0438\u0432 \u043c\u0435\u043d\u044f \u043e\u043d \u0434\u043e\u0431\u0430\u0432\u0438\u043b: «\u0425\u043e\u0440\u043e\u0448\u043e, \u043a\u043e\u0433\u0434\u0430 \u0435\u0441\u0442\u044c \u0437\u043d\u0430\u043a\u043e\u043c\u044b\u0439 DBA». \u041d\u043e \u0447\u0442\u043e \u0434\u0435\u043b\u0430\u0442\u044c \u0435\u0441\u043b\u0438 \u043d\u0435\u0442 \u0437\u043d\u0430\u043a\u043e\u043c\u043e\u0433\u043e DBA? \u0412\u0430\u0440\u0438\u0430\u043d\u0442\u043e\u0432 \u043e\u0442\u0432\u0435\u0442\u0430 \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c \u0434\u043e\u0432\u043e\u043b\u044c\u043d\u043e \u043c\u043d\u043e\u0433\u043e, \u043d\u0430\u0447\u0438\u043d\u0430\u044f \u043e\u0442 \u043f\u043e\u0438\u0441\u043a\u0430\u0442\u044c \u0441\u0440\u0435\u0434\u0438 \u0434\u0440\u0443\u0437\u0435\u0439 \u0434\u0440\u0443\u0437\u0435\u0439 \u0438 \u0437\u0430\u043a\u0430\u043d\u0447\u0438\u0432\u0430\u044f […]<\/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-94417","post","type-post","status-publish","format-standard","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47M\u00e1me tam Postgres, ale nev\u00edm, co s n\u00edm d\u011blat (c) | ProHoster","description":"To je cit\u00e1t jednoho z m\u00fdch zn\u00e1m\u00fdch, kter\u00fd se d\u0159\u00edve na m\u011b obracel s dotazem ohledn\u011b Postgres.","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/u-nas-tam-postgres-no-ya-hz-chto-s-nim-delat-s","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","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\u0423 \u043d\u0430\u0441 \u0442\u0430\u043c Postgres, \u043d\u043e \u044f \u0445\u0437 \u0447\u0442\u043e \u0441 \u043d\u0438\u043c \u0434\u0435\u043b\u0430\u0442\u044c (\u0441) | ProHoster","og:description":"\u042d\u0442\u043e \u0446\u0438\u0442\u0430\u0442\u0430 \u043e\u0434\u043d\u043e\u0433\u043e \u0438\u0437 \u043c\u043e\u0438\u0445 \u0437\u043d\u0430\u043a\u043e\u043c\u044b\u0445 \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043a\u043e\u0433\u0434\u0430-\u0442\u043e \u0434\u0430\u0432\u043d\u043e \u043e\u0431\u0440\u0430\u0449\u0430\u043b\u0441\u044f \u043a\u043e \u043c\u043d\u0435 \u0441 \u0432\u043e\u043f\u0440\u043e\u0441\u043e\u043c \u043f\u0440\u043e Postgres.","og:url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/u-nas-tam-postgres-no-ya-hz-chto-s-nim-delat-s","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":"2020-09-16T17:42:31+00:00","article:modified_time":"2020-09-16T17:42:31+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"94417","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":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 10:49:20","updated":"2022-10-03 07:44:37","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/94417","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=94417"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/94417\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=94417"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=94417"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=94417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}