How to install and Use PgHero on Ubuntu 20.04

Introduction

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.

Prerequisites

• Deploy a fully updated Vultr Ubuntu 20.04 Server.
• Create a non-root user with sudo access.

1. Install PostgreSQL Server

1. Install PostgreSQL database server.

    $ sudo apt -y install postgresql-12
   

2. Enable the database server to start automatically on system startup.

    $ sudo systemctl enable postgresql
   

3. Start the database server.

    $ sudo systemctl start postgresql

2. Create Database

1. Secure PostgreSQL by changing the default PostgreSQL password.

    $ sudo passwd postgres
   

2. Switch to the postgres user.

    $ su - postgres
   

3. Create a new database user named dbuser.

    $ createuser dbuser
   

4. Log in to the PostgreSQL instance.

    $ psql
   

5. Set a secure password for the user.

    ALTER USER dbuser WITH ENCRYPTED password 'ReplaceThisWithASecurePassword';
   

6. Create a database named dbname and set the owner to dbuser.

    CREATE DATABASE dbname OWNER dbuser;
   

7. Grant all the privileges on the database to the user.

    GRANT ALL PRIVILEGES ON DATABASE dbname to dbuser;
   

8. Exit PostgreSQL instance.

    \q
   

9. Return to your non-root sudo user account.

    $ exit

3. Install PgHero

1. Update the system packages.

    $ sudo apt update
   

2. Download and add the PgHero GPG key.

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

3. 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
   

4. Update the system packages.

    $ sudo apt update
   

5. Install PgHero.

    $ sudo apt -y install pghero

4. Configure PgHero

1. 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
   

2. 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
   

3. Start the PgHero service.

    $ sudo systemctl start pghero
   

4. Verify the status of PgHero service.

    $ sudo systemctl status pghero
   

5. Enable the PgHero service to run on system startup.

    $ sudo systemctl enable pghero
   

6. Allow associated ports.

    $ sudo ufw allow 3001/tcp
   
    $ sudo ufw allow 80/tcp

5. Create Reverse Proxy

1. Test the web interface, go to your browser and visit http://Server_IP:3001. For example:

    http://192.0.2.11:3001
   

2. Install Nginx server.

    $ sudo apt install -y nginx
   

3. Unlink Nginx default configuration file.

    $ sudo unlink /etc/nginx/sites-enabled/default
   

4. Create a new Nginx configuration file named pghero.conf.

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

5. 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;
        }
    }
   

6. Enable the new configuration file.

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

7. 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

More Resources

• PgHero - PostgreSQL Performance Dashboard