This is a quote from one of my acquaintances who once approached me with a question about Postgres. We resolved his issue in a couple of days, and after thanking me, he added: "It's great to have a DBA friend."
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?
Why all this?
Postgres is at least not simple and can sometimes be very complex. It depends on the level of involvement and responsibility.
Those in operations need to ensure that Postgres as a service runs smoothly and stably — monitoring resource utilization, availability, configuration adequacy, periodically performing updates, and regular health checks. Developers who write applications need to generally monitor how the application interacts with the database and ensure it doesn't create catastrophic situations that could crash the database. If someone is unfortunate enough to be a tech lead/tech director, it’s important that Postgres works reliably, predictably, and doesn't cause problems, ideally without delving too deeply and for too long into Postgres.
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.
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.
is specifically designed to ease the operation of Postgres. The service collects and analyzes data about Postgres and provides recommendations on what can be improved.
The main goal of the service is to provide clear recommendations that offer insights into what is happening and what steps to take next.
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.
Current Status
Currently, 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.
Currently, the recommendations are quite straightforward — simply stating what to do and how to do it, without additional details — so initially, you'll need to follow related links or conduct further searches. The checks and recommendations cover the system and hardware settings, the Postgres configuration, internal schema, and utilized resources. There are still many features planned for addition.
Of course, we are also looking for volunteers who are willing to try the service and provide feedback. We also have , 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 .
Updated 2020-09-16. Getting started.
After registration, users are prompted to create a project — which allows grouping database instances. Once the project is created, users are directed to the setup and installation instructions for the agent. In brief, you need to create users for the agent, after which you will download the agent installation script and run it. In shell commands, this looks like:
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 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 the configuration file (usually pgbouncer.ini) and specifying the password (or its hash) in the file indicated by the auth_file. If you change stats_users, you will need to restart pgbouncer.
The script install.sh takes a couple of mandatory arguments that are unique to each project, and it accepts the credentials of the created users via environment variables. The script then launches the agent in bootstrap mode — it copies itself to PATH, creates a config file 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).
Source: habr.com
