Quick deploy PgHero with docker

Introduce

PGHero is a performance optimization tool specifically designed for PostgreSQL databases. It provides a set of features and insights to help developers and database administrators monitor and improve the performance of their PostgreSQL databases. Here’s a quick summary:

• Dashboard: PGHero offers a web-based dashboard that gives a comprehensive overview of your PostgreSQL database’s performance metrics.
• Query Analysis: It identifies and displays information about slow or frequently executed queries, allowing users to optimize query performance.
• Index Usage Analysis: PGHero helps you understand how indexes are utilized in your database, assisting in optimizing or adding indexes for better performance.
• Table and Index Statistics: It provides insights into the size and performance of tables and indexes, aiding in the identification of areas that may need optimization.
• Connection Statistics: PGHero offers information about active connections to your database, allowing for efficient management and optimization.
• Cache Hit Ratios: Understanding cache efficiency is crucial for performance, and PGHero provides metrics related to cache hit ratios in PostgreSQL.
• Replication Monitoring: For databases with replication, PGHero offers insights into replication lag and other relevant metrics.

Preparation

In this blog post, we will discuss how to quickly deploy PGHero using Docker. To get started, you’ll need to install Docker and Docker Compose. You can find the installation instructions at this link

Additionally, you need to have a running PostgreSQL database. You can learn how to deploy PostgreSQL and pgAdmin using Docker Compose

Start with docker run

The straightforward method to initiate PGHero with Docker is to use the ‘docker run‘ command directly, specifying your PostgreSQL connection string

sudo docker run -p 8080:8080 -e DATABASE_URL=postgres://[username]:[password]@[hostname]:[port]/[database_name] ankane/pghero

If you want to run PGHero with Docker in a private mode, requiring authentication with a username and password, you can set environment variables to configure PGHero with authentication.

docker run -p 8080:8080 \ 
-e DATABASE_URL=postgres://[username]:[password]@[hostname]:[port]/[database_name] \ 
-e PGHERO_USERNAME=myusername \
-e PGHERO_PASSWORD=mypassword \
 ankane/pghero

Add the -d option if you want to run it in daemon mode.

docker run -d -p 8080:8080 \ 
-e DATABASE_URL=postgres://[username]:[password]@[hostname]:[port]/[database_name] \ 
-e PGHERO_USERNAME=myusername \
-e PGHERO_PASSWORD=mypassword \
 ankane/pghero

Adjust the connection string with your PostgreSQL credentials and database information.
Open your web browser and go to http://localhost:8080 to access the PGHero web interface.

Now you can use PGHero to analyze your PostgreSQL database

Start with docker compose

In case you want to use Docker Compose, create a docker-compose.yaml file and include the following code:

version: '3.5'
services:
  pghero:
    image: ankane/pghero
    ports:
      - 8080:8080
    environment:
      DATABASE_URL=postgres://[username]:[password]@[hostname]:[port]/[database_name]
      PGHERO_USERNAME=myusername
      PGHERO_PASSWORD=mypassword \

Save the file and start Docker Compose using the following command:

docker compose up

or

docker compose up -d

to start it as a daemon.

PGHero is now ready to use. Visit http://localhost:8080 to access the PGHero web interface

Clean up after running Docker Compose

After usage, you can tidy up by running the command

docker compose down

Summary

With Docker or Docker Compose, you can quickly and easily deploy and use PGHero. I hope you will succeed in deploying it. Check out other useful tips on the Toan Thien blog.