Skip to content

2025-11-21 pghero on off-query#

I want to configure replication on off-query postgresql.

Has I will have to handle configuration parameters, it's the occasion to realize we did no specific tuning to the postqres, while on production there is an heavy use of it.

To look at how to tune it, a good idea is to use pghero. I can use it from docker.

I follow https://github.com/ankane/pghero/blob/master/guides/Docker.md

I first pull the image 

docker pull ankane/pghero

I then want to run it inside the network of off-query. A simple way to see the network name, is to get the information from docker compose: 

docker compose ps query_postgres --format json|jq ".Networks"
# common_net

Then I run the docker in the right network (after sourcing .env to get the env variables for user and password): 

docker run -ti \
  -e DATABASE_URL=postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@query_postgres:5432/${POSTGRES_DB} \
  --network common_net -p 8080:8080 ankane/pghero

But I got a bug: 

initialize': the scheme postgres does not accept registry part: postgres:******@query_postgres:5432 (or bad hostname?) (URI::InvalidURIError)
this is because ruby does not support "_" in host name …

A workaround is to use internal ip of container… A very complicated way to get the ip: docker compose exec query_postgres ip a show dev eth0|grep -P -o "(?<=inet )\d+\.\d+\.\d+\.\d+"

So that I can run: 

INTERNAL_IP=$(docker compose exec query_postgres ip a show dev eth0|grep -P -o "(?<=inet )\d+\.\d+\.\d+\.\d+")
docker run -ti \
  -e DATABASE_URL=postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${INTERNAL_IP}:5432/${POSTGRES_DB} \
  --network common_net -p 8080:8080 ankane/pghero

I then use a SSH tunnel to connect.

I did not get anything interesting from this because query stats are not activated…

The link to https://pgtune.leopard.in.ua/? is interesting though.