Use PostgreSQL as Grafana Backend

Use a Pigsty v2.7 PostgreSQL database instead of Grafana’s default SQLite metadata store.

Grafana uses SQLite for its metadata by default. Pigsty v2.7 can provision a PostgreSQL user and database for Grafana, after which Grafana can be pointed at that database manually.

v2.7 scope

The old grafana_database and grafana_pgurl Pigsty parameters were already obsolete in v2.7.0. The release’s grafana.ini.j2 does not manage a PostgreSQL backend URL, so re-running the grafana_config task can overwrite a manual edit. Keep the change under your own configuration management.

Provision the database

Add the user and database to a PGSQL cluster definition:

pg_users:
  - { name: dbuser_grafana, password: DBUser.Grafana, pgbouncer: true, roles: [dbrole_admin] }
pg_databases:
  - { name: grafana, owner: dbuser_grafana, revokeconn: true }

For an existing pg-meta cluster, create them with the v2.7 helper scripts:

bin/pgsql-user pg-meta dbuser_grafana
bin/pgsql-db   pg-meta grafana
psql postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana -c \
  'CREATE TABLE t(); DROP TABLE t;'

For a new dedicated cluster, define the same objects in its inventory and run:

bin/pgsql-add pg-grafana

The helpers correspond to pgsql-user.yml, pgsql-db.yml, and pgsql.yml.

Configure Grafana

Back up the existing Grafana metadata before changing backends. Then edit the [database] section of /etc/grafana/grafana.ini:

[database]
type = postgres
url = postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana
ssl_mode = disable

Restart Grafana after the connection has been verified:

systemctl restart grafana-server

Grafana performs its own metadata migrations when it starts. Pointing it at a new empty database does not copy dashboards or data sources from the previous SQLite database.

Restore dashboards and data sources

Pigsty v2.7 keeps dashboard definitions and the loader in files/grafana:

cd ~/pigsty/files/grafana
./grafana.py init

The loader reads GRAFANA_ENDPOINT, GRAFANA_USERNAME, and GRAFANA_PASSWORD when non-default credentials are required.

./pgsql.yml -t register_grafana

This calls roles/pgsql/tasks/register_grafana.yml, which uses the monitor user and skips a database only when its register_datasource flag is false.