PgHero is an open-source performance dashboard for Postgres database server. It shows system metrics such as resource usage and health checks, among other features. This article explains how to install PgHero on Ubuntu 20.04.
Deploy a fully updated Vultr Ubuntu 20.04 Server.
Create a non-root user with sudo access.
Install PostgreSQL database server.
$ sudo apt -y install postgresql-12
Enable the database server to start automatically on system startup.
$ sudo systemctl enable postgresql
Start the database server.
$ sudo systemctl start postgresql
Secure PostgreSQL by changing the default PostgreSQL password.
$ sudo passwd postgres
Switch to the postgres user.
$ su - postgres
Create a new database user named dbuser.
$ createuser dbuser
Log in to the PostgreSQL instance.
$ psql
Set a secure password for the user.
ALTER USER dbuser WITH ENCRYPTED password 'ReplaceThisWithASecurePassword';
Create a database named dbname and set the owner to dbuser.
CREATE DATABASE dbname OWNER dbuser;
Grant all the privileges on the database to the user.
GRANT ALL PRIVILEGES ON DATABASE dbname to dbuser;
Exit PostgreSQL instance.
\q
Return to your non-root sudo user account.
$ exit
Update the system packages.
$ sudo apt update
Download and add the PgHero GPG key.
$ wget -qO- https://dl.packager.io/srv/pghero/pghero/key | sudo apt-key add -
Add the downloaded repository.
$ sudo 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
Update the system packages.
$ sudo apt update
Install PgHero.
$ sudo apt -y install pghero
Add your database. Change the values dbuser, SecurePassword, localhost, and dbname with your preferred database credentials.
$ sudo pghero config:set DATABASE_URL=postgres://dbuser:SecurePassword@localhost:5432/dbname
Set up PgHero web server.
$ sudo pghero config:set PORT=3001
$ sudo pghero config:set RAILS_LOG_TO_STDOUT=disabled
$ sudo pghero scale web=1
Start the PgHero service.
$ sudo systemctl start pghero
Verify the status of PgHero service.
$ sudo systemctl status pghero
Enable the PgHero service to run on system startup.
$ sudo systemctl enable pghero
Allow associated ports.
$ sudo ufw allow 3001/tcp
$ sudo ufw allow 80/tcp
Test the web interface, go to your browser and visit http://Server_IP:3001. For example:
http://192.0.2.11:3001
Install Nginx server.
$ sudo apt install -y nginx
Unlink Nginx default configuration file.
$ sudo unlink /etc/nginx/sites-enabled/default
Create a new Nginx configuration file named pghero.conf.
$ sudo nano /etc/nginx/sites-available/pghero.conf
Add the following code to the file. Save and close the file.
server {
listen 80;
server_name example.com;
location / {
proxy_pass http://localhost:3001;
}
}
Enable the new configuration file.
$ sudo ln -s /etc/nginx/sites-available/pghero.conf /etc/nginx/sites-enabled/pghero.conf
Restart the Nginx service.
$ sudo service nginx restart
You can now access the web interface directly without using a port. Go to your browser and visit http://Server_IP. For example:
http://192.0.2.11