This is a quote from one of my friends who once contacted me with a question about Postgres. Then we solved his problem in a couple of days, and after thanking me, he added: “It’s good when there is a familiar DBA.”
But what if there is no familiar DBA? There can be quite a lot of answer options, ranging from looking among friends for friends and ending with studying the question on your own. But whatever answer comes to your mind, I have good news for you. In test mode, we launched a recommendation service for Postgres and everything around it. What is it and how did we come to such a life
Why is this all?
Postgres is at least not easy, and sometimes very difficult. Depends on the degree of involvement and responsibility.
Those who are in operations need to ensure that Postgres as a service works properly and stably - monitor resource utilization, availability, configuration adequacy, periodically update and regularly check health. Those who are in development and writing applications, in general terms, need to monitor how the application interacts with the base and that it does not create emergency situations that could bring down the base. If a person is not so lucky that he is a tech lead / tech lead, then it is important for him that Postgres as a whole works reliably, predictably and does not create problems, while it is advisable not to dive into Postgres deeply and for a long time.
In any of these cases, it's you and Postgres. To serve Postgres well, you need to understand it well and understand how it works. If Postgres is not a direct specialization, then you can spend quite a lot of time learning it. In the ideal case, when there is time and desire, it is not always clear where to start, how and where to move.
Even if we impose monitoring, which, in theory, should facilitate operation, the question of expert knowledge remains open. To be able to read and understand graphs, you still need to have a good understanding of how Postgres works. Otherwise, any monitoring turns into sad pictures and spam from alerts at random times of the day.
just made to make Postgres easier to use. The service collects and analyzes data about Postgres and gives recommendations on what can be improved.
The main goal of the service is to give clear recommendations that give an idea of what is happening and what needs to be done next.
For professionals who do not have expert knowledge, the recommendations provide a starting point for further training. For advanced specialists, recommendations indicate those points that should be paid attention to. In this regard, Weaponry acts as an assistant who performs routine tasks to find problems or shortcomings and require special attention. Weaponry can be compared to a linter that checks Postgres and points out flaws.
How are things now
At the moment is in test mode and free of charge, registration is temporarily limited. Together with several volunteers, we are finalizing the recommendation engine at near-combat bases, identifying false positives and working on the text of recommendations.
By the way, the recommendations are still quite straightforward - they just say what and how to do, without additional details - so at first you will have to follow the accompanying links, or googling. The checks and recommendations cover system and hardware settings, Postgres itself settings, schema inside, resources used. There are still quite a few things in the plans that need to be added.
And of course we are looking for volunteers who are ready to try the service and give feedback. We also have you can come and have a look. If you understand what you need and are ready to try, then write to us at .
Updated 2020-09-16. Getting started.
After registration, the user is prompted to create a project - which allows you to combine DB instances into groups. After creating the project, the user is directed to the instructions for configuring and installing the agent. In a nutshell, you need to create users for the agent, then download the agent installation script and run it. In shell commands it looks like this:
psql -c "CREATE ROLE pgscv WITH LOGIN SUPERUSER PASSWORD 'A7H8Wz6XFMh21pwA'"
export PGSCV_PG_PASSWORD=A7H8Wz6XFMh21pwA
curl -s https://dist.weaponry.io/pgscv/install.sh |sudo -E sh -s - 1 6ada7a04-a798-4415-9427-da23f72c14a5If the host has pgbouncer, then you will also need to create a user to connect the agent. The specific way in which a user is configured in pgbouncer can be very variable and highly dependent on the configuration being used. In general terms, the setup comes down to adding a user to stats_users configuration file (usually pgbouncer.ini) and writing the password (or its hash) to the file specified in the parameter auth_file. Changing stats_users will require restarting pgbouncer.
The install.sh script takes a couple of mandatory arguments that are unique for each project, and through environment variables it accepts the details of the created users. Next, the script starts the agent in bootstrap mode - the agent copies itself to PATH, creates a config with details, a systemd unit, and starts as a systemd service.
This completes the installation. Within a couple of minutes, the DB instance will appear in the list of hosts in the interface, and you can already see the first recommendations. But an important point, many recommendations require a large number of accumulated metrics (at least for a day).
Source: habr.com