# 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`](https://github.com/Vonng/pigsty/blob/v2.7.0/roles/infra/templates/grafana/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:

```yaml
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:

```bash
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:

```bash
bin/pgsql-add pg-grafana
```

The helpers correspond to
[`pgsql-user.yml`](https://github.com/Vonng/pigsty/blob/v2.7.0/pgsql-user.yml),
[`pgsql-db.yml`](https://github.com/Vonng/pigsty/blob/v2.7.0/pgsql-db.yml), and
[`pgsql.yml`](https://github.com/Vonng/pigsty/blob/v2.7.0/pgsql.yml).

## Configure Grafana

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

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

Restart Grafana after the connection has been verified:

```bash
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`](https://github.com/Vonng/pigsty/tree/v2.7.0/files/grafana):

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

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

Register the databases declared in `pg_databases` as Grafana PostgreSQL data
sources with the v2.7 task:

```bash
./pgsql.yml -t register_grafana
```

This calls
[`roles/pgsql/tasks/register_grafana.yml`](https://github.com/Vonng/pigsty/blob/v2.7.0/roles/pgsql/tasks/register_grafana.yml),
which uses the monitor user and skips a database only when its
`register_datasource` flag is false.