{"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\/en\/blog\/administrirovanie\/u-nas-tam-postgres-no-ya-hz-chto-s-nim-delat-s","title":{"rendered":"We have Postgres there, but I have no idea what to do with it (c)","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

This is a quote from one of my acquaintances who once approached me with a question about Postgres. Back then, we solved his problem in a couple of days and after thanking me, he added: \u201cIt's nice to have a DBA acquaintance.\u201d<\/p>\n

But what should you do if you don't have a DBA friend? There can be many answers, starting from looking among your friends' friends to exploring the issue on your own. Regardless of the solution that comes to mind, I have good news for you. We have launched a recommendation service for Postgres and everything related to it in a test mode. What is it and how did we reach this stage?<\/p>\n

Why all this?<\/h2>\n

Postgres is at least not simple and can sometimes be very complex. It depends on the level of involvement and responsibility.<\/p>\n

For those in operations, it is essential to ensure that Postgres functions as a reliable and stable service \u2014 monitoring resource utilization, availability, configuration adequacy, periodically performing updates, and regular health checks. For developers writing applications, generally, they need to monitor how the application interacts with the database and ensure it does not create emergency situations that might crash the database. If someone is unfortunate enough to be a tech lead or tech director, it\u2019s important that Postgres runs reliably and predictably without causing issues, ideally without having to dive deep into Postgres by themselves.<\/p>\n

In any of these cases, there is you and Postgres. To effectively manage Postgres, one must have a good understanding of how it is structured. If Postgres is not a direct specialization, one can spend quite a bit of time studying it. Ideally, when there is time and desire, it is not always clear where to start and how to proceed.<\/p>\n

Even if you arm yourself with monitoring tools that are supposed to simplify operation, the issue of expert knowledge remains open. To be able to read and understand graphs, one still needs a solid understanding of how Postgres is structured. Otherwise, any monitoring simply turns into gloomy graphs and random alerts at all hours of the day.<\/p>\n

Weaponry<\/a><\/noindex> is specifically designed to make Postgres operations easier. The service collects and analyzes data about Postgres and provides recommendations on what can be improved. <\/p>\n

The main goal of the service is to provide clear recommendations that offer insights into what is happening and what steps to take next.<\/em> <\/p>\n

For specialists without expert knowledge, the recommendations serve as a starting point for skill enhancement. For advanced specialists, the recommendations highlight specific aspects to focus on. In this regard, Weaponry acts as an assistant that handles routine tasks related to identifying issues or deficiencies that require attention. Weaponry can be compared to a linter that checks Postgres and points out shortcomings.<\/p>\n

Current Status<\/h2>\n

Currently, Weaponry<\/a><\/noindex> is in a testing phase and is offered for free, with registration currently limited. Together with several volunteers, we are refining the recommendation engine using near-production databases, identifying false positives, and improving the recommendation text. <\/p>\n

By the way, the recommendations are currently quite straightforward \u2014 they simply tell you what and how to do, without additional details \u2014 so in the beginning, you\u2019ll have to follow supplementary links or do further research. Checks and recommendations cover system and hardware settings, Postgres configurations, the internal schema, and the resources used. There are still many things planned to be added. <\/p>\n

Of course, we are also looking for volunteers who are willing to try the service and provide feedback. We also have demo<\/a><\/noindex>, feel free to visit and explore. If you understand that you need this and are ready to give it a try, please contact us at email<\/a><\/noindex>.<\/p>\n

Updated 2020-09-16. Getting started.<\/h2>\n

After registering, users are prompted to create a project \u2014 which allows grouping database instances. After creating a project, the user is directed to the instructions for setting up and installing the agent. In short, users need to create accounts for the agent, then download the agent installation script and run it. In shell commands, it looks something like this:<\/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
If there is a pgbouncer on the host, you'll need to create a user for the agent connection as well. The specific method for setting up the user in pgbouncer can vary greatly and is highly dependent on the configuration being used. Generally, the setup involves adding the user to stats_users<\/em> the configuration file (usually pgbouncer.ini<\/em>) and specifying the password (or its hash) in the file indicated by the auth_file.<\/em> If you change stats_users, you will need to restart pgbouncer.<\/p>\n
The install.sh script accepts a couple of mandatory arguments that are unique to each project, and through environment variables, it takes the credentials of the created users. Next, the script runs the agent in bootstrap mode \u2014 the agent copies itself to PATH, creates a config with the credentials, a systemd unit, and runs as a systemd service. 
This concludes the installation. Within a few minutes, the database instance will appear in the list of hosts in the interface, and you can start looking at the first recommendations. However, an important point is that many recommendations require a large amount of accumulated metrics (at least for a day).<\/p>\n
Source: 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\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\udd47We have Postgres there, but I have no idea what to do with it (c) | ProHoster","description":"This is a quote from one of my acquaintances who once reached out to me about a question regarding Postgres.","canonical_url":"https:\/\/prohoster.info\/en\/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":"en_US","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\/en\/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\/en\/wp-json\/wp\/v2\/posts\/94417","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=94417"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/94417\/revisions"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=94417"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=94417"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=94417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}