Use Postgres as Grafana Database

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

    In this tutorial, you will learn about the following.

    Create Postgres Cluster

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

    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.         comment: admin user for grafana database

    Create Cluster

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

    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 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.

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

    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
    3.   comment: admin user for grafana database
    4.   pgbouncer: true
    5.   roles: [ dbrole_admin ]

    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

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

    Create Biz Database

    Create business databases in the same way as business users. First, add the 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 to create the database.

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

    You can access the database using different services or 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 =

    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 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 folder pgcat
    5. init dashboard: pgcat / pgcat-table.json
    6. init dashboard: pgcat / pgcat-bloat.json
    7. init dashboard: pgcat / pgcat-query.json
    8. init folder pgsql
    10. init dashboard: pgsql / pgsql-table.json
    11. init dashboard: pgsql / pgsql-activity.json
    12. init dashboard: pgsql / pgsql-cluster.json
    13. init dashboard: pgsql / pgsql-node.json
    14. init dashboard: pgsql / pgsql-database.json
    15. init dashboard: pgsql / pgsql-xacts.json
    16. init dashboard: pgsql / pgsql-overview.json
    17. init dashboard: pgsql / pgsql-session.json
    18. init dashboard: pgsql / pgsql-tables.json
    19. init dashboard: pgsql / pgsql-instance.json
    20. init dashboard: pgsql / pgsql-queries.json
    21. init dashboard: pgsql / pgsql-alert.json
    22. init dashboard: pgsql / pgsql-service.json
    23. init dashboard: pgsql / pgsql-persist.json
    24. init dashboard: pgsql / pgsql-proxy.json
    25. init dashboard: pgsql / pgsql-query.json
    26. init folder pglog
    27. init dashboard: pglog / pglog-instance.json
    28. init dashboard: pglog / pglog-analysis.json
    29. 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.

    When creating a new PostgreSQL cluster with 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 .

    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 and grafana_pgurl parameters in pigsty.yml and change them.

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

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

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