Skip to content

Grafana High Availability: Using PostgreSQL Backend

Use PostgreSQL instead of SQLite as Grafana’s remote storage backend for better performance and availability.

You can use PostgreSQL as Grafana’s backend database.

This tutorial explains the Pigsty deployment system through these tasks:

TL;DR

vi pigsty.yml # Uncomment DB/User definitions: dbuser_grafana  grafana
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;' # Verify connection string works

vi /etc/grafana/grafana.ini # Modify [database] type url
systemctl restart grafana-server

Create Database Cluster

You can define a grafana database on pg-meta. You can also create a dedicated pg-grafana cluster on new nodes.

Define Cluster

To create a new dedicated cluster pg-grafana on machines 10.10.10.11 and 10.10.10.12, use this config:

pg-grafana:
  hosts:
    10.10.10.11: {pg_seq: 1, pg_role: primary}
    10.10.10.12: {pg_seq: 2, pg_role: replica}
  vars:
    pg_cluster: pg-grafana
    pg_databases:
      - name: grafana
        owner: dbuser_grafana
        revokeconn: true
        comment: grafana primary database
    pg_users:
      - name: dbuser_grafana
        password: DBUser.Grafana
        pgbouncer: true
        roles: [dbrole_admin]
        comment: admin user for grafana database

Create Cluster

Use this command to create the pg-grafana cluster: pgsql.yml.

./pgsql.yml -l pg-grafana    # Initialize pg-grafana cluster

This command is the Ansible Playbook pgsql.yml for creating database clusters.

Cluster initialization automatically creates the users and databases in pg_users and pg_databases. After cluster creation, any of these connection strings provides database access without DNS:

postgres://dbuser_grafana:[email protected]:5432/grafana # Direct primary connection
postgres://dbuser_grafana:[email protected]:5436/grafana # Direct default service
postgres://dbuser_grafana:[email protected]:5433/grafana # Primary read-write service

postgres://dbuser_grafana:[email protected]:5432/grafana # Direct primary connection
postgres://dbuser_grafana:[email protected]:5436/grafana # Direct default service
postgres://dbuser_grafana:[email protected]:5433/grafana # Primary read-write service

Pigsty defaults to a single meta node. The following steps use its existing pg-meta cluster for the Grafana user and database. They do not use the pg-grafana cluster created above.

Create Grafana Business User

Create the user before the database. A database with an owner depends on that user.

Define User

To create user dbuser_grafana on the pg-meta cluster, first add this user definition to pg-meta’s cluster definition:

Location: all.children.pg-meta.vars.pg_users

- name: dbuser_grafana
  password: DBUser.Grafana
  comment: admin user for grafana database
  pgbouncer: true
  roles: [ dbrole_admin ]

If you define a different password here, replace the corresponding parameter in subsequent steps

Create User

Use this command to create the dbuser_grafana user (either works):

bin/pgsql-user pg-meta dbuser_grafana # Create `dbuser_grafana` user on pg-meta cluster

This actually calls the Ansible Playbook pgsql-user.yml to create the user:

./pgsql-user.yml -l pg-meta -e pg_user=dbuser_grafana  # Ansible

The dbrole_admin role has permission to execute DDL changes in the database, which is exactly what Grafana needs.

Create Grafana Business Database

Define Database

Creating a business database follows the same pattern as users. First add the new database grafana definition to pg-meta’s cluster definition.

Location: all.children.pg-meta.vars.pg_databases

- { name: grafana, owner: dbuser_grafana, revokeconn: true }

Create Database

Use this command to create the grafana database (either works):

bin/pgsql-db pg-meta grafana # Create `grafana` database on `pg-meta` cluster

This actually calls the Ansible Playbook pgsql-db.yml to create the database:

./pgsql-db.yml -l pg-meta -e pg_database=grafana # Actual Ansible playbook executed

Use Grafana Business Database

Verify Connection String Reachability

You can access the database using different services or access methods, for example:

postgres://dbuser_grafana:DBUser.Grafana@meta:5432/grafana # Direct connection
postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana # Default service
postgres://dbuser_grafana:DBUser.Grafana@meta:5433/grafana # Primary service

Here, we’ll use the Default service that directly accesses the primary through load balancer.

First verify the connection string is reachable and has DDL execution permissions:

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

Directly Modify Grafana Config

To make Grafana use a Postgres datasource, edit /etc/grafana/grafana.ini and modify the config:

[database]
;type = sqlite3
;host = 127.0.0.1:3306
;name = grafana
;user = root
# If the password contains # or ; you have to wrap it with triple quotes. Ex """#password;"""
;password =
;url =

Change the default config to:

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

Then restart Grafana:

systemctl restart grafana-server

Activity in the new grafana database indicates that Grafana now uses PostgreSQL as its main backend.

The original Grafana dashboards and datasources are absent from this new backend. Import the dashboards and Postgres datasources again.

Manage Grafana Dashboards

As admin user, navigate to the files/grafana directory under the Pigsty directory and run grafana.py init to reload Pigsty dashboards.

cd ~/pigsty/files/grafana
./grafana.py init    # Initialize Grafana dashboards using Dashboards in current directory

Execution result:

vagrant@meta:~/pigsty/files/grafana
$ ./grafana.py init
Grafana API: admin:pigsty @ http://10.10.10.10:3000
init dashboard : home.json
init folder pgcat
init dashboard: pgcat / pgcat-table.json
init dashboard: pgcat / pgcat-bloat.json
init dashboard: pgcat / pgcat-query.json
init folder pgsql
init dashboard: pgsql / pgsql-replication.json
...

The script detects the environment defined in ~/pigsty during installation. It gets Grafana access information and replaces dashboard URL placeholders (*.pigsty) with actual domains.

export GRAFANA_ENDPOINT=http://10.10.10.10:3000
export GRAFANA_USERNAME=admin
export GRAFANA_PASSWORD=pigsty

export NGINX_UPSTREAM_YUMREPO=yum.pigsty
export NGINX_UPSTREAM_CONSUL=c.pigsty
export NGINX_UPSTREAM_PROMETHEUS=p.pigsty
export NGINX_UPSTREAM_ALERTMANAGER=a.pigsty
export NGINX_UPSTREAM_GRAFANA=g.pigsty
export NGINX_UPSTREAM_HAPROXY=h.pigsty

Use grafana.py clean to delete target dashboards. Use grafana.py load to load all dashboards from the current directory. When Pigsty dashboards change, use these two commands to upgrade all dashboards.

Manage Postgres Datasources

Pigsty registers PostgreSQL datasources in Grafana when pgsql.yml creates a cluster or pgsql-db.yml creates a business database. You can directly access target database instances through Grafana using the default monitoring user. Most pgcat application features depend on this.

To register Postgres databases, use the register_grafana task in pgsql.yml:

./pgsql.yml -t register_grafana             # Re-register all Postgres datasources in current environment
./pgsql.yml -t register_grafana -l pg-test  # Re-register all databases in pg-test cluster

One-Step Grafana Upgrade

Change the Pigsty configuration to switch the Grafana backend database in one step. Edit the grafana_pgurl parameter in pigsty.yml:

grafana_pgurl: postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana

Then re-run the grafana task from infra.yml to complete the Grafana upgrade:

./infra.yml -t grafana

Was this page helpful?

Last Modified 2026-10-12 · update extensions (4d4b1d96)