pgFirstAid, your PostgreSQL Server’s health checkup

I started my career in data as a SQL Server DBA and was quickly introduced to Brent Ozar. Many SQL Server DBAs will know the name, as well as Ola Hallengren’s infamous maintenance.sql SQL Server maintenance plan. In the years I supported SQL Server, these tools were the backbone of my workflow when deploying and troubleshooting various versions of SQL Server. Combine this with the extremely active community that Brent has, it was a huge adjustment starting to support PostgreSQL without a tool like the FirstResponderKit. There are plenty of extensions and, being the best (yes I said it) open-source database platform, I was able to manage.

Yet, there was still something missing. Bouncing around supporting managed and self-hosted/on-prem PostgreSQL servers showed me how crucial it was to have some sort of function that I could just run on any version of PostgreSQL without a worry of compatability. Managed services, like Aurora Postgres, prevent you from truly having superuser rights and only “approved” extensions can be used. This burned my team more than I can count and I wanted to find a solution that was fast, easy to use, and could be used by ANYONE. I loved the FirstResponderKit and the format; it was only a matter of replicating that feel in PostgreSQL without the need of actually installing anything else. That meant, anyone tasked with jumping into the server to troubleshoot could copy the function(or view), deploy, run, and immediately get a status check on their PostgreSQL server.

Thus, pgFirstAid was born.

Health Checks

The bread and butter of pgFirstAid is to give the user health checks with remediation actions to take. In addition, it provides a link to documentation to help support the suggested action. The health checks provide prioritized results (ranked by CRITICAL, HIGH, MEDIUM, LOW, and INFO) and can be easily filtered by severity or category.

    --Using the function
    SELECT * FROM pg_firstAid() WHERE category LIKE '%Index%';

    --Using the view
    SELECT * FROM v_pgfirstAid WHERE severity = 'MEDIUM';

As pgFirstAid grows, the list of health checks will expand to other categories. My dream is that others will find something missing in pgFirstAid and open up an issue with a feature request to add it. This is an open-source project, so I want to make sure others can contribute to this and help build out the features in the hopes of becoming the go to tool for quickly identifying issues on a target server and how to address them. pgFirstAid will enable users to have confidence in what to tackle first and at the same time teaching best practices for managing a PostgreSQL server.

Server Info Checks

Similar to the health checks, the server info checks will provide details on the server configuration. The target here is to identify settings that should be adjusted out of the gate or flag parameters that are set incorrectly. They can also be used to identify key information like server uptime, current version, and location of logs that can be queried and export inputs to your automation. Since pgFirstAid will work for all versions 15 and up, regardless if you are using a managed deployment or not, you will still get details relevant to perfecting your server build.

Inspirations

Like I mentioned early on, Brent Ozar’s FirstResponderKit takes most of the inspiration points. I absolutely loved the tool set it provided when I was in the trenches as a SQL Server DBA. It was the first thing I put on every server I could when I accessed them for the first time and it saved my butt more times than I could count. I cannot replicate what Brent and the community have done with things like sp_blitz, but I can use it as inspiration to keep the project going.

Additionally, I wanted to make sure this project remained FREE of charge, OPEN to the community, and a SAFE place for people to make suggestions/comments/critiques of the tool itself. I strongly believe in open-source and that PostgreSQL is the most versatile database engine out there. I want others to see its true power and how sometimes the shiny new database engine isn’t always the answer to your problem.

Future of pgFirstAid

What do you think we should add to pgFirstAid? Are there checks we should include that we do not already have? Is there something in one of the checks you think we could do better? Let us know in github or message me through socials. The one thing I can promise you all on is that this will be a community driven project. Which means it will only get better with your input and not just mine alone.

As this is a project I am doing in my free time, do not expect fast responses to messages or issues. My goal is to at least let you know that I saw your message but will respond later in detail. I find this respects my work/life balance and will carry that into my side work. Until then, star the repo or follow me on github to monitor the releases!

Github Repo