Use Postgres as Grafana Database

Store grafana backend metadata with PostgreSQL instead of SQLite.

You can use postgres as the database used by the Grafana backend.

In this tutorial, you will learn about the following.

TL; DR

  1. vi pigsty.yml   # uncomment user/db definition：dbuser_grafana  grafana 
  2. bin/createuser  pg-meta  dbuser_grafana
  3. bin/createdb    pg-meta  grafana
  5. psql postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana -c \
  6.   'CREATE TABLE t(); DROP TABLE t;'    # check pgurl connectivity
  8. vi /etc/grafana/grafana.ini            # edit [database] section: type & url
  9. systemctl restart grafana-server

Create Postgres Cluster

We can define a new database grafana on pg-meta. A Grafana-specific database cluster can also be created on a new machine node: pg-grafana.

Define Cluster

To create a new dedicated database cluster pg-grafana on two bare nodes 10.10.10.11, 10.10.10.12, define it in the config file.

  1. pg-grafana: 
  2.   hosts: 
  3.     10.10.10.11: {pg_seq: 1, pg_role: primary}
  4.     10.10.10.12: {pg_seq: 2, pg_role: replica}
  5.   vars:
  6.     pg_cluster: pg-grafana
  7.     pg_databases:
  8.       - name: grafana
  9.         owner: dbuser_grafana
  10.         revokeconn: true
  11.         comment: grafana primary database
  12.     pg_users:
  13.       - name: dbuser_grafana
  14.         password: DBUser.Grafana
  15.         pgbouncer: true
  16.         roles: [dbrole_admin]
  17.         comment: admin user for grafana database

Create Cluster

Complete the creation of the database cluster pg-grafana with the following command: pgsql.yml.

  1. bin/createpg pg-grafana # Initialize the pg-grafana cluster

This command calls Ansible Playbook pgsql.yml to create the database cluster.

  1. . /pgsql.yml -l pg-grafana # The actual equivalent Ansible playbook command executed

The business users and databases defined in pg_users and pg_databases are created automatically when the cluster is initialized. After creating the cluster using this configuration, the following connection string access database can be used.

  1. postgres://dbuser_grafana:DBUser.Grafana@10.10.10.11:5432/grafana # direct connection to the primary
  2. postgres://dbuser_grafana:DBUser.Grafana@10.10.10.11:5436/grafana # direct connection to the default service
  3. postgres://dbuser_grafana:DBUser.Grafana@10.10.10.11:5433/grafana # Connect to the string read/write service
  5. postgres://dbuser_grafana:DBUser.Grafana@10.10.10.12:5432/grafana # direct connection to the primary
  6. postgres://dbuser_grafana:DBUser.Grafana@10.10.10.12:5436/grafana # Direct connection to default service
  7. postgres://dbuser_grafana:DBUser.Grafana@10.10.10.12:5433/grafana # Connected string read/write service

By default, Pigsty is installed on a single meta node. Then the required users and databases for Grafana are created on the existing pg-meta database cluster instead of using the pg-grafana cluster.

Create Biz User

The convention for business object management is to create users first and then create the database.

Define User

To create a user dbuser_grafana on a pg-meta cluster, add the following user definition to pg-meta’s cluster definition.

Add location: all.children.pg-meta.vars.pg_users.

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

If you have defined a different password here, replace the corresponding parameter with the new password.

Create User

Complete the creation of the dbuser_grafana user with the following command.

  1. bin/createuser pg-meta dbuser_grafana # Create the `dbuser_grafana` user on the pg-meta cluster

Calls Ansible Playbook pgsql-createuser.yml to create the user

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

The dbrole_admin role has the privilege to perform DDL changes in the database, which is precisely what Grafana needs.

Create Biz Database

Define database

Create business databases in the same way as business users. First, add the definition of the new database grafana to the cluster definition of pg-meta.

Add location: all.children.pg-meta.vars.pg_databases.

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

Create database

Use the following command to complete the creation of the grafana database.

  1. bin/createdb pg-meta grafana # Create the `grafana` database on the `pg-meta` cluster

Calls Ansible Playbook pgsql-createdb.yml to create the database.

  1. . /pgsql-createdb.yml -l pg-meta -e pg_database=grafana # The actual Ansible playbook to execute

Access Database

Check Connectivity

You can access the database using different services or access methods.

  1. postgres://dbuser_grafana:DBUser.Grafana@meta:5432/grafana # Direct connection
  2. postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana # default service
  3. postgres://dbuser_grafana:DBUser.Grafana@meta:5433/grafana # primary service

We will use the default service that accesses the database directly from the primary through the LB.

First, check if the connection string is reachable and if you have privileges to execute DDL commands.

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

Config Grafana

For Grafana to use the Postgres data source, you need to edit /etc/grafana/grafana.ini and modify the config entries.

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

Change the default config entries.

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

Subsequently, restart Grafana.

  1. systemctl restart grafana-server

See from the monitor system that the new grafana database is already active, then Grafana has started using Postgres as the primary backend database. However, the original Dashboards and Datasources in Grafana have disappeared. You need to re-import Dashboards and Postgres Datasources.

Manage Dashboard

You can reload the Pigsty monitor dashboard by going to the files/ui dir in the Pigsty dir using the admin user and executing grafana.py init.

  1. cd ~/pigsty/files/ui
  2. . /grafana.py init # Initialize the Grafana monitor dashboard using the Dashboards in the current directory

Execution results in:

  1. vagrant@meta:~/pigsty/files/ui
  2. $ ./grafana.py init
  3. Grafana API: admin:pigsty @ http://10.10.10.10:3000
  4. init dashboard : home.json
  5. init folder pgcat
  6. init dashboard: pgcat / pgcat-table.json
  7. init dashboard: pgcat / pgcat-bloat.json
  8. init dashboard: pgcat / pgcat-query.json
  9. init folder pgsql
  10. init dashboard: pgsql / pgsql-replication.json
  11. init dashboard: pgsql / pgsql-table.json
  12. init dashboard: pgsql / pgsql-activity.json
  13. init dashboard: pgsql / pgsql-cluster.json
  14. init dashboard: pgsql / pgsql-node.json
  15. init dashboard: pgsql / pgsql-database.json
  16. init dashboard: pgsql / pgsql-xacts.json
  17. init dashboard: pgsql / pgsql-overview.json
  18. init dashboard: pgsql / pgsql-session.json
  19. init dashboard: pgsql / pgsql-tables.json
  20. init dashboard: pgsql / pgsql-instance.json
  21. init dashboard: pgsql / pgsql-queries.json
  22. init dashboard: pgsql / pgsql-alert.json
  23. init dashboard: pgsql / pgsql-service.json
  24. init dashboard: pgsql / pgsql-persist.json
  25. init dashboard: pgsql / pgsql-proxy.json
  26. init dashboard: pgsql / pgsql-query.json
  27. init folder pglog
  28. init dashboard: pglog / pglog-instance.json
  29. init dashboard: pglog / pglog-analysis.json
  30. init dashboard: pglog / pglog-session.json

This script detects the current environment (defined at ~/pigsty during installation), gets Grafana access information, and replaces the URL connection placeholder domain name (*.pigsty) in the monitor dashboard with the real one in use.

  1. export GRAFANA_ENDPOINT=http://10.10.10.10:3000
  2. export GRAFANA_USERNAME=admin
  3. export GRAFANA_PASSWORD=pigsty
  5. export NGINX_UPSTREAM_YUMREPO=yum.pigsty
  6. export NGINX_UPSTREAM_CONSUL=c.pigsty
  7. export NGINX_UPSTREAM_PROMETHEUS=p.pigsty
  8. export NGINX_UPSTREAM_ALERTMANAGER=a.pigsty
  9. export NGINX_UPSTREAM_GRAFANA=g.pigsty
  10. export NGINX_UPSTREAM_HAPROXY=h.pigsty

As a reminder, using grafana.py clean will clear the target monitor dashboard, and using grafana.py load will load all the monitor dashboards in the current dir. When Pigsty’s monitor dashboard changes, you can use these two commands to upgrade all the monitor dashboards.

Manage DataSources

When creating a new PostgreSQL cluster with pgsql.yml or a new business database with pgsql-createdb.yml, Pigsty will register the new PostgreSQL data source in Grafana, and you can access the target database instance directly through Grafana using the default admin user. Most of the functionality of the application pgcat relies on this.

To register a Postgres database, you can use the register_grafana task in pgsql.yml.

  1. ./pgsql.yml -t register_grafana # Re-register all Postgres data sources in the current environment
  2. ./pgsql.yml -t register_grafana -l pg-test # Re-register all the databases in the pg-test cluster

Update Grafana Database

You can directly change the backend data source used by Grafana by modifying the Pigsty config file. Edit the grafana_database and grafana_pgurl parameters in pigsty.yml and change them.

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

Then re-execute the grafana task in infral.yml to complete the Grafana upgrade.

  1. ./infra.yml -t grafana

Last modified 2022-06-03: add scaffold for en docs (6a6eded)