Click to expand!
- Introduction
- Grafana
- Start the Postgres Database
- Start postgres using docker
- Start postgres using podman
- Copy an SQL file to the database container
- Access the database using psql interactively
- Populate the database using docker
- Populate the database using podman
- Backup the database using docker
- Backup the database using podman
- Restore the database using docker
- Restore the database using podman
- Connect Grafana to Postgres
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.
These are actions associated with running grafana.
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
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
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.
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.
These are actions associated with running postgres
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
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
docker cp my.sql mypg:/tmp/my.sql
docker exec -it mypg psql -h mypg -U postgres
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
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
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
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
docker cp backup.sql mypg:/mnt/backup.sql
docker exec -it mypg psql -U postgres -d postgres -f /mnt/backup.sql
podman cp backup.sql mypg:/mnt/backup.sql
podman exec -it mypg psql -U postgres -d postgres -f /mnt/backup.sql
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. |
You can find the docker container internal IP address manually by doing this:
# docker inspect mypg --format '{{.NetworkSettings.IPAddress}}'
172.17.0.4
# podman inspect mypg --format '{{.NetworkSettings.IPAddress}}'
172.17.0.4
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.
This occurs when click on the Save and Test
button.
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
Images