Skip to content

Instantly share code, notes, and snippets.

@jlinoff

jlinoff/README.md

Last active February 3, 2022 01:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jlinoff/6e2127ee8b9522da9bc20b20be980818 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jlinoff/6e2127ee8b9522da9bc20b20be980818 to your computer and use it in GitHub Desktop.
Download ZIP
How I Setup a Latop Prototyping Environment for Grafana and Postgres
Raw
README.md

How I Setup a Laptop Prototyping Environment for Grafana and Postgres

Table of Contents

Click to expand!

Introduction

This is basically a note to myself to help me remember the details. It talks about how run grafana with a postgres database on your laptop using two docker or podman containers that are networked together using the default docker or podman bridge network.

This is extremely useful for doing local prototyping.

You must have docker or podman, bash (4.x or later), grep and awk available.

Note that i deliberately avoid the use of volumes or bind mounts in the most of examples because they do not work on MacOS with podman. I will update this doc if/when they are supported.

Grafana

These are actions associated with running grafana.

Start grafana using docker

These are the commands used to run the container and get the container internal network IP address.

docker run -d --name mygr -h mygr -p 4700:3000 grafana/grafana
docker inspect mygr | grep '"IPAddress"' | awk -F'"' '{print $4}' | head -1

If you want to avoid periodically copying the data to save it, you bind mounts or volume mounts if not do something like this periodically to back things up.

docker cp mygr:/var/lib/grafana/grafana.db grafana.db
docker cp mygr:/etc/grafana/grafana.ini grafana.ini

and restore them.

docker cp grafana.db mygr:/var/lib/grafana/grafana.db
dodcker cp grafana.ini mygr:/etc/grafana.ini

You can avoid all that in docker using bind mounts which, unfortunately, do not work in podman (yet?).

mkdir grafana
docker run -d --name mygr -h mygr -p 4700:3000 \
   -v $(pwd)/grafana:/var/lib/grafana/grafana.db \
   -v $(pwd)/grafana:/etc/grafana/grafana.ini \
   grafana/grafana

Start grafana using podman

These are the commands used to run the container and get the container internal network IP address.

podman run -d --name mygr -h mygr -p 4700:3000 grafana/grafana
podman inspect mygr | grep '"IPAddress"' | awk -F'"' '{print $4}' | head -1

Since podman does not support bind mounts, you must periodically copy the database and configuration to avoid losing changes when the container is destroyed.

podman cp mygr:/var/lib/grafana/grafana.db grafana.db
podman cp mygr:/etc/grafana/grafana.ini grafana.ini

To restore them copy them back.

podman cp grafana.db mygr:/var/lib/grafana/grafana.db
podman cp grafana.ini mygr:/etc/grafana.ini

Update grafana using docker

Note that you can also use a bind mount to do this but this approach is identical for both docker and podman.

View the current version

docker exec -it mygr grafana-cli -v

Backup the dashboards database and the configuration

docker cp mygr:/var/lib/grafana/grafana.db grafana.db
docker cp mygr:/etc/grafana/grafana.ini grafana.ini

Delete the container and the image.

docker stop mygr
docker rm mygr
docker rmi -f grafana/grafana

Pull in the latest version

docker pull grafana/grafana:latest

Restart the container then restore the database and the configuration.

docker run -d --name mygr -h mygr -p 4700:3000 grafana/grafana
docker cp grafana.db mygr:/var/lib/grafana/grafana.db
docker cp grafana.ini mygr:/etc/grafana.ini

Check the datasource password, you may have to re-enter it. You might also have to restart.

Update grafana using podman

View the current version

podman exec -it mygr grafana-cli -v

Backup the dashboards database and the configuration

podman cp mygr:/var/lib/grafana/grafana.db grafana.db
podman cp mygr:/etc/grafana/grafana.ini grafana.ini

Delete the container and the image.

podman stop mygr
podman rm mygr
podman rmi -f grafana/grafana

Pull in the latest version

podman pull grafana/grafana:latest

Restart the container then restore the database and the configuration.

podman run -d --name mygr -h mygr -p 4700:3000 grafana/grafana
podman cp grafana.db mygr:/var/lib/grafana/grafana.db
podman cp grafana.ini mygr:/etc/grafana.ini

Check the datasource password, you may have to re-enter it. You might also have to restart.

Postgres

These are actions associated with running postgres

Start postgres using docker

These are the commands used to run the container and get the container internal network IP address.

docker run -d --name mypg -h mypg -p 4701:5432 \
    -e PGDATA=/var/lib/postgresql/data -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password \
    postgres
docker inspect mypg | grep '"IPAddress"' | awk -F'"' '{print $4}' | head -1

If you want to take advantage of bind mounts in docker do something like this.

mkdir pgdata
docker run -d --name mypg -h mypg -p 4701:5432 \
    -e PGDATA=/var/lib/postgresql/data -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password \
    -v $(pwd)/pgdata:/var/lib/postgresql/data
    postgres

If you do not use bind mounts, you will have to periodically backup the database to avoid losing data if the container stops.

docker exec -it mypg pg_dump -c -f /mnt/backup.sql -U postgres -d postgres
docker cp mypg:/mnt/backup.sql backup.sql

Start postgres using podman

These are the commands used to run the container and get the container internal network IP address.

podman run -d --name mypg -h mypg -p 4701:5432 -e PGDATA=/var/lib/postgresql/data -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=password postgres
podman inspect mypg | grep '"IPAddress"' | awk -F'"' '{print $4}' | head -1

Since you cannot not use bind mounts in podman, you will have to periodically backup the database to avoid losing data.

podman exec -it mypg pg_dump -c -f /mnt/backup.sql -U postgres -d postgres
podman cp mypg:/mnt/backup.sql backup.sql

Copy an SQL file to the database container

docker cp my.sql mypg:/tmp/my.sql

Access the database using psql interactively

docker exec -it mypg psql -h mypg -U postgres

Populate the database using docker

Click to expand! 
$ # Create the SQL table
$ cat > test.sql <<EOF
CREATE TABLE IF NOT EXISTS test01 (
    id serial primary key,
    x integer not null,
    y integer not null
)
INSERT INTO test01(x, y) values
  (1, 1),
  (2, 4),
  (3, 9),
  (4, 16),
  (5, 25),
  (6, 36),
  (7, 49),
  (8, 64),
  (9, 81),
  (10, 100),
  (11, 121),
  (12, 144),
  (13, 169),
  (14, 186),
  (15, 225),
  (16, 256);
EOF
$ # Copy into the container
$ docker cp test.sql mypg:/mnt/test.sql

$ # Add it to the database
$ docker exec -it mypg psql -h mypg -U postgres
Password for user postgres:
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.

postgres=# \! ls /mnt/
test.sql

postgres=# \i /mnt/test.sql
CREATE TABLE
INSERT 0 16

postgres=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | test01 | table | postgres
(1 row)

postgres=# select * from test01;
 id | x  |  y
----+----+-----
  1 |  1 |   1
  2 |  2 |   4
  3 |  3 |   9
  4 |  4 |  16
  5 |  5 |  25
  6 |  6 |  36
  7 |  7 |  49
  8 |  8 |  64
  9 |  9 |  81
 10 | 10 | 100
 11 | 11 | 121
 12 | 12 | 144
 13 | 13 | 169
 14 | 14 | 186
 15 | 15 | 225
 16 | 16 | 256
(16 rows)

postgres=# \q

Populate the database using podman

Click to expand! 
$ # Create the SQL table
$ cat > test.sql <<EOF
CREATE TABLE IF NOT EXISTS test01 (
    id serial primary key,
    x integer not null,
    y integer not null
)
INSERT INTO test01(x, y) values
  (1, 1),
  (2, 4),
  (3, 9),
  (4, 16),
  (5, 25),
  (6, 36),
  (7, 49),
  (8, 64),
  (9, 81),
  (10, 100),
  (11, 121),
  (12, 144),
  (13, 169),
  (14, 186),
  (15, 225),
  (16, 256);
EOF
$ # Copy into the container
$ podman cp test.sql mypg:/mnt/test.sql

$ # Add it to the database
$ podman exec -it mypg psql -h mypg -U postgres
Password for user postgres:
psql (14.1 (Debian 14.1-1.pgdg110+1))
Type "help" for help.

postgres=# \! ls /mnt/
test.sql

postgres=# \i /mnt/test.sql
CREATE TABLE
INSERT 0 16

postgres=# \dt
         List of relations
 Schema |  Name  | Type  |  Owner
--------+--------+-------+----------
 public | test01 | table | postgres
(1 row)

postgres=# select * from test01;
 id | x  |  y
----+----+-----
  1 |  1 |   1
  2 |  2 |   4
  3 |  3 |   9
  4 |  4 |  16
  5 |  5 |  25
  6 |  6 |  36
  7 |  7 |  49
  8 |  8 |  64
  9 |  9 |  81
 10 | 10 | 100
 11 | 11 | 121
 12 | 12 | 144
 13 | 13 | 169
 14 | 14 | 186
 15 | 15 | 225
 16 | 16 | 256
(16 rows)

postgres=# \q

Backup the database using docker

docker exec -it mypg pg_dump --help
docker exec -it mypg pg_dump -c -f /mnt/backup.sql -U postgres -d postgres
docker cp mypg:/mnt/backup.sql backup.sql

Backup the database using podman

podman exec -it mypg pg_dump --help
podman exec -it mypg pg_dump -c -f /mnt/backup.sql -U postgres -d postgres
podman cp mypg:/mnt/backup.sql backup.sql

Restore the database using docker

docker cp backup.sql mypg:/mnt/backup.sql
docker exec -it mypg psql -U postgres -d postgres -f /mnt/backup.sql

Restore the database using podman

podman cp backup.sql mypg:/mnt/backup.sql
podman exec -it mypg psql -U postgres -d postgres -f /mnt/backup.sql

Connect Grafana to Postgres

You connect Grafana to the Postgres database using the standard datasource interface through http://localhost:4700. The username is admin and the password is admin.

Use the GrafanaDataSource data output by the scripts for this example they are:

Parameter Value Notes
Host 172.17.0.4 Internal docker container IP address. This could be different in each environment.
Database postgres Always the same.
User postgres Always the same.
Password password Always the same.

Get the Container Internal IP Address

You can find the docker container internal IP address manually by doing this:

docker

# docker inspect mypg --format '{{.NetworkSettings.IPAddress}}'
172.17.0.4

podman

# podman inspect mypg --format '{{.NetworkSettings.IPAddress}}'
172.17.0.4

Define the datasource

Defines the data source using the above settings. Make sure that you turn SSL/TLS off.

Take particular notice of the host IP address. That is the docker/podman internal IP address of the container.

datasource-setup

Verify that the data source works

This occurs when click on the Save and Test button.

datasource-ok

Example dashboard

This is a very simple dashboard with a table panel that shows the contents.

This is the SQL used from the Postgresql data source.

SELECT * FROM test01

dashboard

@jlinoff
Copy link
Author

jlinoff commented Dec 28, 2021

Images

dashboard

datasource-ok

datasource-setup

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment