How to install PgHero on Ubuntu 20.04

Hello, friends. In this post, we will help you to install PgHero on Ubuntu 20.04 using an external repository and get to know better your PostgreSQL instance.

What is PgHero?

When we use a database manager, one of the main doubts is about performance. This can crash some of our systems, so we have to keep an eye on this.

One tool that can help us to keep an eye on the performance of our PostgreSQL is PgHero.

PgHero defines itself as A performance dashboard for Postgres. So, we can monitor through a comfortable web interface how our PostgreSQL is doing.

The installation is effortless, so there are no excuses to have it installed. Let’s go for it.

Install PostgreSQL on Ubuntu 20.04

For this post, we just need the version of PostgreSQL that is in the official Ubuntu repositories. So, we are going to open a terminal or connect via SSH and update the whole system

sudo apt update
sudo apt upgrade

Thereafter, install PostgreSQL as follows:

sudo apt install postgresql-12

Then, make sure it is enabled and started.

sudo systemctl enable postgresql
sudo systemctl start postgresql

Next, check the status of the service

sudo systemctl status postgresql

Create a new Ubuntu 20.04 database

The first thing you have to do is to change the postgres user password.

sudo passwd postgres

Thereafter, change the session to this user.

su - postgres

And create a new user for PostgreSQL

createuser angelo

Now go to the console

psql

Define a password for the new user

ALTER USER angelo WITH ENCRYPTED password 'pss';

And then create a new database that belongs to this user.

CREATE DATABASE dbname OWNER angelo;

Grant appropriate permissions to the user

GRANT ALL PRIVILEGES ON DATABASE dbname to angelo;

And exit the console

\q

Finally, log back in to your current user

exit

Install PgHero on Ubuntu 20.04

PgHero has an external repository that provides us with the latest stable version of the tool.

First refresh APT.

sudo apt update

Next, add the GPG key from the repository

wget -qO- https://dl.packager.io/srv/pghero/pghero/key | sudo apt-key add -
OK

Subsequently, add the repository to the system.

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

Refresh APT again

sudo apt update

Now install PgHero by running the following command

sudo apt install pghero

Configuring PgHero

Before using it and having information about our PostgreSQL, we have to make some configurations.

The first thing will be to define which database it will query. This requires the user, the database, and the password.

sudo pghero config:set DATABASE_URL=postgres://[user]:[password]@localhost:[port]/[dbname]

For example:

sudo pghero config:set DATABASE_URL=postgres://angelo:pss@localhost:5432/dbname

Then, define the port through which you can access PgHero

sudo pghero config:set PORT=3001

And some more settings

sudo pghero config:set AILS_LOG_TO_STDOUT=disabled
sudo pghero scale web=1

Then start the PgHero service

sudo systemctl start pghero

And check its status.

sudo systemctl status pghero

Configuring Nginx as reverse proxy

It is convenient to use Nginx as a reverse proxy to facilitate access to the application.

So, first install it.

sudo apt install nginx

Create a new configuration file

sudo nano /etc/nginx/sites-available/pghero.conf

And add the following content

server {
    listen 80;
    server_name pg.unixcop.test;

    location / {
        proxy_pass http://localhost:3001;
    }
}

Replace server_name with your domain name. Then, save the changes and close the editor.

Enable the new configuration and restart the service.

sudo ln -s /etc/nginx/sites-available/pghero.conf /etc/nginx/sites-enabled/pghero.conf
sudo systemctl restart nginx

Access PgHero from the web interface

Now you can open your favorite web browser and visit your domain to check PgHero’s performance.

So, the installation was a success, and now you can use it without any problems.

Conclusion

In this post, you have learned how to install the PgHero tool on Ubuntu 20.04. I hope you found it useful. Thank you.