PgHero is an open-source and web-based tool designed to monitor PostgreSQL via a web browser. It helps users who are not familiar with the command line. It offers a simple and user-friendly control panel to monitor PostgreSQL and shows system metrics such as resource usage, health checks, etc. It offers an easy-to-interpret overview – green in good, not green, you should pay attention, etc. PgHero provides a quick overall status of your database, a look at the queries that are running, table indexes, and table space, and can give you a way to get explanations of queries without having to jump down to the psql console.
This post will show you how to install PgHero PostgreSQL monitoring software on Ubuntu 22.04.
Prerequisites
- A server running Ubuntu 22.04.
- A valid domain name is pointed with your server IP.
- A root password is configured on the server.
Getting Started
Before starting, update and upgrade all system packages to the latest version using the following command:
apt update -y
apt upgrade -y
Once all the packages are updated, you can install other required dependencies using the following command:
apt-get install wget curl gnupg2 -y
Once you are finished, you can proceed to the next step.
Install PostgreSQL Server
At the time of writing this tutorial, the latest version of PostgreSQL is 14. You can install it with the following command:
apt-get install postgresql-14 -y
After the successfull instalation, you can start and enable the PostgreSQL service with the following command:
systemctl start postgresql
systemctl enable postgresql
To check the status of the PostgreSQL, run the following command:
systemctl status postgresql
You will get the following output:
? postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Mon 2022-11-28 16:29:36 UTC; 17s ago
Process: 20245 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 20245 (code=exited, status=0/SUCCESS)
Nov 28 16:29:36 ubuntu22041 systemd[1]: Starting PostgreSQL RDBMS...
Nov 28 16:29:36 ubuntu22041 systemd[1]: Finished PostgreSQL RDBMS.
At this point, the PostgreSQL service is installed and running. You can now proceed to the next step.
Create a PostgreSQL Database and User
Next, you will need to create a PostgreSQL database and user. First, connect to the PostgreSQL console with the following command:
su - postgres
Next, create a user named pghero using the following command:
createuser pghero
Next, connect to the PostgreSQL shell with the following command:
psql
Next, set a password to for the pghero user:
ALTER USER pghero WITH ENCRYPTED password 'password';
Next. create a database named pgdb with the following command:
CREATE DATABASE pgdb OWNER pghero;
Next, grant all the privileges to the pgdb with the following command:
GRANT ALL PRIVILEGES ON DATABASE pgdb to pghero;
Next, exit from the PostgreSQL shell with the following command:
\q
exit
Install and Configure PgHero
By default, PgHero package is not included in the Ubuntu default repository. So you will need to add the PgHero repository to your server.
First, download and import the GPG key with the following command:
wget -qO- https://dl.packager.io/srv/pghero/pghero/key | apt-key add -
Next, add the PgHero repository to the APT with the following command:
wget -O /etc/apt/sources.list.d/pghero.list https://dl.packager.io/srv/pghero/pghero/master/installer/ubuntu/$(. /etc/os-release && echo $VERSION_ID).repo
Next, update the repository cache and install PgHero with the following command:
apt-get update -y
apt-get install pghero -y
After installing PgHero, you will need to define your PostgreSQL database for PgHero. You can define it with the following commad:
pghero config:set DATABASE_URL=postgres://pghero:password@localhost:5432/pgdb
Next, set up PgHero web server with the following commands:
pghero config:set PORT=3001
pghero config:set AILS_LOG_TO_STDOUT=disabled
pghero scale web=1
You will get the following output:
Created symlink /etc/systemd/system/multi-user.target.wants/pghero.service → /etc/systemd/system/pghero.service. Created symlink /etc/systemd/system/pghero.service.wants/pghero-web.service → /etc/systemd/system/pghero-web.service. Scaling up... Created symlink /etc/systemd/system/pghero-web.service.wants/pghero-web-1.service → /etc/systemd/system/pghero-web-1.service. --> done.
Now, start the PgHero service with the following command:
systemctl start pghero
You can check the status of the PgHero with the following command:
systemctl status pghero
You will get the following output:
? pghero.service
Loaded: loaded (/etc/systemd/system/pghero.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2022-11-28 16:33:23 UTC; 19s ago
Main PID: 21242 (sleep)
Tasks: 1 (limit: 464122)
Memory: 176.0K
CGroup: /system.slice/pghero.service
??21242 /bin/sleep infinity
Nov 28 16:33:23 ubuntu22041 systemd[1]: Started pghero.service.
PgHero is now running and listens on port 3001. You can check it with the following command:
ss -antpl | grep 3001
You should see the following output:
LISTEN 0 1024 0.0.0.0:3001 0.0.0.0:* users:(("ruby",pid=21278,fd=5),("ruby",pid=21277,fd=5),("ruby",pid=21276,fd=5),("ruby",pid=21245,fd=5))
At this point, PgHero is installed and configured. You can now proceed to the next step.
Configure Nginx as a Reverse Proxy for PgHero
It is a good idea to install and configure Nginx as a reverse proxy for PgHero. First, install the Nginx web server with the following command:
apt-get install nginx -y
Once the Nginx is installed, create an Nginx configuration file with the following command:
nano /etc/nginx/conf.d/pghero.conf
Add the following lines:
server {
listen 80;
server_name pghero.example.com;
location / {
proxy_pass http://localhost:3001;
}
}
Save and close the file then verify the Nginx for any syntax error:
nginx -t
You will get the following output:
nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful
Next, restart the Nginx service to apply the changes:
systemctl restart nginx
You can now check the status of the Nginx with the following command:
systemctl status nginx
You will get the following output:
? nginx.service - A high performance web server and a reverse proxy server
Loaded: loaded (/lib/systemd/system/nginx.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2022-11-28 16:36:27 UTC; 7s ago
Docs: man:nginx(8)
Process: 21878 ExecStartPre=/usr/sbin/nginx -t -q -g daemon on; master_process on; (code=exited, status=0/SUCCESS)
Process: 21879 ExecStart=/usr/sbin/nginx -g daemon on; master_process on; (code=exited, status=0/SUCCESS)
Main PID: 21880 (nginx)
Tasks: 3 (limit: 464122)
Memory: 3.4M
CGroup: /system.slice/nginx.service
??21880 "nginx: master process /usr/sbin/nginx -g daemon on; master_process on;"
??21881 "nginx: worker process" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
??21882 "nginx: worker process" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" "" ""
Nov 28 16:36:27 ubuntu22041 systemd[1]: Starting A high performance web server and a reverse proxy server...
Nov 28 16:36:27 ubuntu22041 systemd[1]: Started A high performance web server and a reverse proxy server.
Access PgHero Dashboard
Now, open your web browser and access the PgHero web interface using the URL http://pghero.example.com. You should see the PgHero dashboard on the following screen:
Click on the Space in the left pane, you should see the PostgreSQL database size on the following screen:
Click on the Connections. You should see all your PostgreSQL connection requests on the following screen:
Click on the Tune. You should see all your default settings on the following screen:
Conclusion
Congratulations! You have successfully installed PgHero on the Ubuntu 22.04 server. You can now deploy PgHero in the production environment to easily manage and monitor your PostgreSQL databases from the web browser. Feel free to ask me if you have any questions.
