Administration

• 1: Nginx Management
• 2: Software Repository
• 3: Domain Management
• 4: Module Management
• 5: CA and Certificates
• 6: Grafana High Availability: Using PostgreSQL Backend

1 - Nginx Management

Pigsty installs Nginx on INFRA nodes as the entry point for all web services. It listens on standard ports 80/443.

In Pigsty, you can configure Nginx to provide various services through inventory:

• Expose web interfaces for monitoring components like Grafana, VictoriaMetrics (VMUI), Alertmanager, and VictoriaLogs
• Serve static files (software repos, documentation sites, websites, etc.)
• Proxy custom application services (internal apps, database management UIs, Docker application interfaces, etc.)
• Automatically issue self-signed HTTPS certificates, or use Certbot to obtain free Let’s Encrypt certificates
• Expose services through a single port using different subdomains for unified access

Basic Configuration

Customize Nginx behavior via infra_portal parameter:

infra_portal:
  home: { domain: i.pigsty }

infra_portal is a dictionary where each key defines a service and the value is the service configuration. Pigsty generates Nginx configuration files only for services with a defined domain.

• home: Special default server for homepage and built-in monitoring component reverse proxies
• Proxy services: Specify upstream service address via endpoint for reverse proxy
• Static services: Specify local directory via path for static file serving

Server Parameters

Basic Parameters

SSL/TLS Options

Advanced Settings

Configuration Examples

Reverse Proxy Services

grafana: { domain: g.pigsty, endpoint: "${admin_ip}:3000", websocket: true }
pgadmin: { domain: adm.pigsty, endpoint: "127.0.0.1:8885" }

Static Files and Directory Listing

repo: { domain: repo.pigsty.io, path: "/www/repo", index: true }

Custom SSL Certificate

secure_app:
  domain: secure.pigsty.io
  endpoint: "${admin_ip}:8443"
  cert: "/etc/ssl/certs/custom.crt"
  key: "/etc/ssl/private/custom.key"

Using Let’s Encrypt Certificates

grafana:
  domain: demo.pigsty.io
  endpoint: "${admin_ip}:3000"
  websocket: true
  certbot: pigsty.demo    # Cert name, multiple domains can share one cert

Force HTTPS Redirect

web.io:
  domain: en.pigsty.io
  path: "/www/web.io"
  certbot: pigsty.doc
  enforce_https: true

Custom Config Snippet

web.cc:
  domain: pigsty.io
  path: "/www/web.io"
  domains: [ en.pigsty.io ]
  certbot: pigsty.doc
  config: |
    # rewrite /en/ to /
        location /en/ {
            rewrite ^/en/(.*)$ /$1 permanent;
        }

Management Commands

./infra.yml -t nginx           # Full Nginx reconfiguration
./infra.yml -t nginx_config    # Regenerate config files
./infra.yml -t nginx_launch    # Restart Nginx service
./infra.yml -t nginx_cert      # Regenerate SSL certificates
./infra.yml -t nginx_certbot   # Sign certificates with certbot
./infra.yml -t nginx_reload    # Reload Nginx configuration

Domain Resolution

Three ways to resolve domains to Pigsty servers:

1. Public domains: Configure via DNS provider
2. Internal DNS server: Configure internal DNS resolution
3. Local hosts file: Modify /etc/hosts

For local development, add to /etc/hosts:

<your_public_ip_address> i.pigsty

Pigsty includes dnsmasq service, configurable via dns_records parameter for internal DNS resolution.

HTTPS Configuration

Configure HTTPS via nginx_sslmode parameter:

For self-signed certificates, several access options:

• Trust the self-signed CA in browser (download at http://<ip>/ca.crt)
• Use browser security bypass (type “thisisunsafe” in Chrome)
• Configure proper CA-signed certs or Let’s Encrypt for production

Certbot Certificates

Pigsty supports using Certbot to request free Let’s Encrypt certificates.

Enable Certbot

1. Add certbot parameter to services in infra_portal, specifying cert name
2. Configure certbot_email with a valid email
3. Set certbot_sign to true for auto-signing during deployment

certbot_sign: true
certbot_email: [email protected]

Manual Certificate Signing

./infra.yml -t nginx_certbot   # Sign Let's Encrypt certificates

Or run the scripts directly on the server:

/etc/nginx/sign-cert           # Sign certificates
/etc/nginx/link-cert           # Link certificates to Nginx config directory

For more info, see Certbot: Request and Renew HTTPS Certificates

Default Homepage

Pigsty’s default home server provides these built-in routes:

These routes provide one entry point for all monitoring components. You do not need to configure multiple domains.

Best Practices

• Use domain names instead of IP:PORT for service access
• Properly configure DNS resolution or hosts file
• Enable WebSocket for real-time apps (e.g., Grafana, Jupyter)
• Enable HTTPS for production
• Use meaningful subdomains to organize services
• Monitor Let’s Encrypt certificate expiration
• Use config parameter for custom Nginx configurations

Full Example

Here’s the Nginx configuration used by Pigsty’s public demo site demo.pigsty.io:

infra_portal:
  home         : { domain: i.pigsty }
  io           : { domain: pigsty.io      ,path: "/www/pigsty.io"   ,cert: /etc/cert/pigsty.io.crt ,key: /etc/cert/pigsty.io.key }
  minio        : { domain: m.pigsty.io    ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
  postgrest    : { domain: api.pigsty.io  ,endpoint: "127.0.0.1:8884" }
  pgadmin      : { domain: adm.pigsty.io  ,endpoint: "127.0.0.1:8885" }
  pgweb        : { domain: cli.pigsty.io  ,endpoint: "127.0.0.1:8886" }
  bytebase     : { domain: ddl.pigsty.io  ,endpoint: "127.0.0.1:8887" }
  jupyter      : { domain: lab.pigsty.io  ,endpoint: "127.0.0.1:8888" ,websocket: true }
  gitea        : { domain: git.pigsty.io  ,endpoint: "127.0.0.1:8889" }
  wiki         : { domain: wiki.pigsty.io ,endpoint: "127.0.0.1:9002" }
  noco         : { domain: noco.pigsty.io ,endpoint: "127.0.0.1:9003" }
  supa         : { domain: supa.pigsty.io ,endpoint: "10.10.10.10:8000" ,websocket: true }
  dify         : { domain: dify.pigsty.io ,endpoint: "10.10.10.10:8001" ,websocket: true }
  odoo         : { domain: odoo.pigsty.io ,endpoint: "127.0.0.1:8069"   ,websocket: true }
  mm           : { domain: mm.pigsty.io   ,endpoint: "10.10.10.10:8065" ,websocket: true }

2 - Software Repository

Pigsty’s REPO role downloads required packages and creates a local YUM/APT repository under /www/pigsty. Nginx can serve this repository.

The current package candidate is SOW 0.3.0. SOW generates metadata for both repository types. The source no longer calls createrepo_c, modifyrepo_c, or dpkg-scanpackages separately.

Quick Start

Add packages to repo_packages or repo_extra_packages, then run:

./infra.yml -t repo_build   # Download and build only when the repository is absent
./node.yml -t node_repo     # Refresh repository definitions and caches on each node

If /www/pigsty/repo_complete already exists, the default repo_build skips the build. To force a rebuild, override it explicitly:

./infra.yml -t repo_build -e repo_build=true

To rebuild metadata for packages already present without downloading new ones:

./infra.yml -t repo_create

SOW Prerequisites

Both repo_create and cache_create require sow on the target node. A fresh online build automatically adds infra to the effective repo_modules list. It installs SOW from the Pigsty INFRA upstream repository.

Offline bundles and local repositories created before this change may not contain SOW. Before you rebuild from old media, refresh the bundle or local repository. Alternatively, install the current SOW 0.3.0 candidate from Pigsty INFRA. Do not assume that older environments can still fall back to createrepo_c.

On a fresh installation, the role checks whether /www exists. If absent, it creates /data/nginx and points /www to it. It preserves existing directories and symlinks.

Build Flow

The command executed by repo_create is:

sow create --pigsty --timeout 10m -- /www/pigsty

--pigsty removes unneeded packages and packages likely to cause conflicts. It atomically publishes the result only after all metadata is complete. A typical layout is:

/www/pigsty/
├── *.rpm / *.deb
├── repodata/            # RPM repository
├── Packages             # APT repository
├── Packages.gz
└── repo_complete        # SHA-256 manifest and completion marker

repo_complete contains SHA-256 checksums. Do not treat it as an empty marker. Its presence means SOW completed publication of local repository metadata. It does not prove that remote mirrors, signed repositories, or offline bundles have been synchronized.

DNF Module Streams

Pigsty no longer fabricates modules.yaml / ModuleMD metadata for the aggregated local repository. System upstream repositories retain native DNF module filtering. Only an upstream repository that truly needs to replace an EL module stream should set meta explicitly:

- name: example
  module: pgsql
  # ... releases, arch, baseurl ...
  meta: { module_hotfixes: 1 }

The aggregated local repository uses module_hotfixes=1. This prevents system module streams from hiding local PostgreSQL packages. It does not generate artificial ModuleMD metadata.

Package Aliases

The default repo_packages uses these alias groups:

[node-bootstrap, infra-package, infra-addons, node-package1,
 node-package2, node-package3, pgsql-utility, extra-modules]

node-bootstrap includes Ansible, Python dependencies, SOW, and SSH tools. infra-package includes Nginx, etcd, HAProxy, Victoria exporters, Redis/Valkey, Silo, mcli, SOW, and Pig. Exact names vary by operating-system mapping. Use roles/node_id/vars/<os>.<arch>.yml as the authoritative source.

Common Commands

./infra.yml -t repo                           # Check, prepare, or build, then start repository service
./infra.yml -t repo_check,repo_prepare        # Only check and use an existing repository
./infra.yml -t repo_upstream                  # Refresh upstream repository definitions
./infra.yml -t repo_pkg                       # Download configured packages and dependencies
./infra.yml -t repo_create                    # Rebuild metadata in the existing directory with SOW
./infra.yml -t repo_build -e repo_build=true  # Force the complete build stage
./infra.yml -t repo_nginx                     # Configure/start repository Nginx
./node.yml -t node_repo                       # Refresh managed-node repository caches
./cache.yml                                   # Rebuild metadata with SOW, then create an offline bundle

3 - Domain Management

Use domain names instead of IP addresses to access Pigsty’s various web services.

Quick Start

Add the following static resolution records to /etc/hosts:

10.10.10.10 i.pigsty

Replace IP address with your actual Pigsty node’s IP.

Why Use Domain Names

• Easier to remember than IP addresses
• Flexible pointing to different IPs
• Unified service management through Nginx
• Support for HTTPS encryption
• Prevent ISP hijacking in some regions
• Allow access to internally bound services via proxy

DNS Mechanism

DNS Protocol: Resolves domain names to IP addresses. Multiple domains can point to same IP.

HTTP Protocol: The Host header routes requests to different sites on the same port (80/443).

Default Domains

Pigsty predefines the following default domains:

By default, access Grafana, VictoriaMetrics, and Alertmanager under i.pigsty. Their paths are /ui/, /vmetrics/, and /alertmgr/, respectively. To use dedicated domains such as g.pigsty, p.pigsty, and a.pigsty, configure them explicitly in infra_portal and dns_records.

Resolution Methods

Local Static Resolution

Add entries to /etc/hosts on the client machine:

# Linux/macOS
sudo vim /etc/hosts

# Windows
notepad C:\Windows\System32\drivers\etc\hosts

Add content:

10.10.10.10 i.pigsty m.pigsty

Internal Dynamic Resolution

Pigsty includes dnsmasq as an internal DNS server. Configure managed nodes to use INFRA node as DNS server:

node_dns_servers: ['${admin_ip}']   # Use INFRA node as DNS server
node_dns_method: add                # Add to existing DNS server list

Configure domain records resolved by dnsmasq via dns_records:

dns_records:
  - "${admin_ip} i.pigsty"
  - "${admin_ip} m.pigsty sss.pigsty api.pigsty adm.pigsty cli.pigsty ddl.pigsty"

Public Domain Names

Purchase a domain and add DNS A record pointing to public IP:

1. Purchase domain from registrar (e.g., example.com)
2. Configure A record pointing to server public IP
3. Use real domain in infra_portal

Built-in DNS Service

Pigsty runs dnsmasq on INFRA nodes as a DNS server.

Related Parameters

Default DNS records:

dns_records:
  - "${admin_ip} i.pigsty"
  - "${admin_ip} m.pigsty sss.pigsty api.pigsty adm.pigsty cli.pigsty ddl.pigsty"

Dynamic DNS Registration

Pigsty automatically registers DNS records for PostgreSQL clusters and instances:

• Instance-level DNS: <pg_instance> points to instance IP (e.g., pg-meta-1)
• Cluster-level DNS: <pg_cluster> points to primary IP or VIP (e.g., pg-meta)

Cluster-level DNS target controlled by pg_dns_target:

Add suffix to cluster DNS via pg_dns_suffix.

Node DNS Configuration

Pigsty manages DNS configuration on managed nodes.

Static hosts Records

Configure static /etc/hosts records via node_etc_hosts:

node_etc_hosts:
  - "${admin_ip} i.pigsty"
  - "${admin_ip} sss.pigsty"      # Optional: Silo S3 endpoint domain
  - "10.10.10.20 db.example.com"

DNS Server Configuration

node_dns_method options:

Default DNS options:

node_dns_options:
  - options single-request-reopen timeout:1

HTTPS Certificates

Pigsty uses self-signed certificates by default. Options include:

• Ignore warnings, use HTTP
• Trust self-signed CA certificate (download at http://<ip>/ca.crt)
• Use real CA or get free public domain certs via Certbot

See CA and Certificates documentation for details.

Extended Domains

Pigsty reserves the following domains for various application services:

Using these domains requires configuring corresponding services in infra_portal.

Management Commands

./infra.yml -t dns            # Full DNS service configuration
./infra.yml -t dns_config     # Regenerate dnsmasq config
./infra.yml -t dns_record     # Update default DNS records
./infra.yml -t dns_launch     # Restart dnsmasq service

./node.yml -t node_hosts      # Configure node /etc/hosts
./node.yml -t node_resolv     # Configure node DNS resolver

./pgsql.yml -t pg_dns         # Register PostgreSQL DNS records
./pgsql.yml -t pg_dns_ins     # Register instance-level DNS only
./pgsql.yml -t pg_dns_cls     # Register cluster-level DNS only

4 - Module Management

Use this guide for daily INFRA administration. It covers installation, removal, scaling, and component maintenance.

Install INFRA Module

Use the infra.yml playbook to install the INFRA module on the infra group:

./infra.yml     # Install INFRA module on infra group

Uninstall INFRA Module

Use the infra-rm.yml playbook to uninstall the INFRA module from the infra group:

./infra-rm.yml -l infra # Full removal: deregister, stop, remove config/environment/data, and uninstall packages

This playbook has no deletion safeguard. Full execution removes infra_data, nginx_data, nginx_home (default: /www), and /var/lib/grafana. If you only need to stop services or deregister targets, use -t service or -t deregister. Read the complete removal scope and back up required data before running it.

Scale Out INFRA Module

Assign infra_seq to new nodes and add them to the infra group in the inventory:

all:
  children:
    infra:
      hosts:
        10.10.10.10: { infra_seq: 1 }  # Existing node
        10.10.10.11: { infra_seq: 2 }  # New node

Use the -l limit option to execute the playbook on the new node only:

./infra.yml -l 10.10.10.11    # Install INFRA module on new node

Manage Local Repository

Local repository management tasks:

./infra.yml -t repo              # Create repo from internet or offline packages
./infra.yml -t repo_upstream     # Add upstream repositories
./infra.yml -t repo_pkg          # Download packages and dependencies
./infra.yml -t repo_create       # Create local yum/apt repository

Complete subtask list:

./infra.yml -t repo_dir          # Create local repository directory
./infra.yml -t repo_check        # Check if local repo exists
./infra.yml -t repo_prepare      # Use existing repo directly
./infra.yml -t repo_build        # Build repo from upstream
./infra.yml -t repo_upstream     # Add upstream repositories
./infra.yml -t repo_remove       # Delete existing repo files
./infra.yml -t repo_add          # Add repo to system directory
./infra.yml -t repo_url_pkg      # Download packages from internet
./infra.yml -t repo_cache        # Create metadata cache
./infra.yml -t repo_boot_pkg     # Install bootstrap packages
./infra.yml -t repo_pkg          # Download packages and dependencies
./infra.yml -t repo_create       # Create local repository
./infra.yml -t repo_use          # Add new repo to system
./infra.yml -t repo_nginx        # Start Nginx file server

Manage Nginx

Nginx management tasks:

./infra.yml -t nginx                       # Reset Nginx component
./infra.yml -t nginx_index                 # Re-render homepage
./infra.yml -t nginx_config,nginx_reload   # Re-render config and reload

Request HTTPS certificate:

./infra.yml -t nginx_certbot,nginx_reload -e certbot_sign=true

Manage Infrastructure Components

Management commands for various infrastructure components:

./infra.yml -t infra           # Configure infrastructure
./infra.yml -t infra_user      # Set up OS user
./infra.yml -t infra_dir       # Create infrastructure directories
./infra.yml -t infra_env       # Configure environment variables
./infra.yml -t infra_pkg       # Install packages
./infra.yml -t infra_cert      # Issue certificates
./infra.yml -t dns             # Configure DNSMasq
./infra.yml -t nginx           # Configure Nginx
./infra.yml -t victoria        # Configure VictoriaMetrics/Logs/Traces
./infra.yml -t alertmanager    # Configure AlertManager
./infra.yml -t blackbox        # Configure Blackbox Exporter
./infra.yml -t grafana         # Configure Grafana
./infra.yml -t infra_register  # Register to VictoriaMetrics/Grafana

Common maintenance commands:

./infra.yml -t nginx_index                        # Re-render homepage
./infra.yml -t nginx_config,nginx_reload          # Reconfigure and reload
./infra.yml -t vmetrics_config,vmetrics_launch    # Regenerate VictoriaMetrics config and restart
./infra.yml -t vlogs_config,vlogs_launch          # Update VictoriaLogs config
./infra.yml -t grafana_provision                  # Reload Grafana dashboards and data-source definitions

Manage Grafana Passwords

Grafana uses two password parameters. grafana_admin_password defaults to the public value pigsty. grafana_view_password defaults to DBUser.Viewer.

Change the public defaults before production. After initialization, a change to grafana_admin_password in the inventory does not reset the live Grafana account password. Change it through Grafana or a supported administration interface. Keep the inventory consistent and regenerate the environment when needed:

./infra.yml -t env_var            # Re-render environment variables

grafana_view_password is the password used by the default PostgreSQL metadb data source as dbuser_view. If the database role password changes, update both the declared value and the Grafana data source. A change on only one side breaks the dashboard connection.

5 - CA and Certificates

Pigsty maintains a self-signed Certificate Authority (CA) on the admin node by default. It signs certificates for PostgreSQL, Patroni, etcd, Silo, Nginx, and other internal services. Public Nginx entries can use Certbot/Let’s Encrypt certificates configured through infra_portal.

Self-Signed CA

The ca stage of infra.yml creates or reuses the CA locally on the admin node running Ansible. It does not create the CA on a remote Infra node. The default layout is:

files/pki/
├── ca/                       # CA key, certificate, and OpenSSL CA state
│   ├── ca.key
│   └── ca.crt
├── csr/                      # certificate signing requests
├── misc/                     # generic certificates issued by cert.yml
├── etcd/
├── infra/
├── kafka/
├── minio/                    # MINIO module (Silo) certificates
├── mongo/
├── mysql/
├── nginx/
└── pgsql/

The core defaults match the v4.5.0 roles:

The role hard-codes the CA certificate lifetime to 36500d (about 100 years). These long-lived certificates serve a controlled internal trust domain. Public browsers do not trust them by default. Clients must explicitly trust ca.crt. Public endpoints should use a publicly trusted CA.

Initialize the local CA stage:

./infra.yml -t ca

Running ./infra.yml -t ca may create a missing key or certificate and therefore changes PKI state. Confirm the admin node, configuration, and existing CA backup first.

Use an External CA

To reuse an enterprise CA:

1. Set ca_create: false in pigsty.yml.
2. Place a matching files/pki/ca/ca.key and files/pki/ca/ca.crt pair on the admin node.
3. Set permissions and verify that the public-key digests match.

chmod 700 files/pki/ca
chmod 600 files/pki/ca/ca.key
chmod 644 files/pki/ca/ca.crt

# The public-key digests must match; neither command prints private-key material
openssl pkey -in files/pki/ca/ca.key -pubout -outform PEM | openssl sha256
openssl x509 -in files/pki/ca/ca.crt -pubkey -noout | openssl sha256

ca_create: false prevents generation of a new private key only when ca.key is missing. If the key exists and ca.crt is missing, the role still creates a new self-signed CA certificate from the key. Always restore the pair together instead of relying on certificate regeneration.

Before running the CA stage, verify the files that will be used, the existing CA backup, and the admin node.

Back Up and Restore the CA

Retain at least:

• files/pki/ca/ca.key and ca.crt
• CA state such as ca.srl, index.txt, and CRL files if issuance/revocation management uses them
• backup time, the CA certificate SHA-256 fingerprint, and restoration instructions

# Inspect the public CA certificate only
openssl x509 -in files/pki/ca/ca.crt -noout -subject -issuer -dates -fingerprint -sha256

Encrypt the backup and store it on controlled offline media or in a secrets-management system. Do not keep an unencrypted tar archive.

First, restore it to an isolated temporary directory. Before replacement, verify the file count, types, permissions, public-key match, and certificate fingerprint.

Loss of ca.key does not immediately prevent verification of existing certificates. Verification still works if clients trust ca.crt and the certificates remain valid and unrevoked.

You can no longer issue, renew, or revoke certificates with the original CA. Recovery usually requires a new CA, reissuance of every certificate, and deployment of a new trust chain.

Issue Certificates with cert.yml

cert.yml runs locally on the admin node and issues generic certificates with the Pigsty CA. Pass cn explicitly instead of relying on the generic script default:

./cert.yml -e cn=dbuser_dba

Default outputs:

files/pki/misc/<cn>.key   # 0600
files/pki/misc/<cn>.crt   # 0600
files/pki/csr/<cn>.csr

Advanced examples:

# Pass DNS/IP SANs as a JSON list
./cert.yml -e cn=myservice \
  -e '{"san":["DNS:myservice.local","DNS:myservice","IP:10.10.10.50"]}'

# Issue a one-year certificate
./cert.yml -e cn=myservice \
  -e '{"san":["DNS:myservice.local","DNS:myservice","IP:10.10.10.50"]}' \
  -e expire=365d

# When customizing key/crt, provide both paths together
./cert.yml -e cn=custom \
  -e key=/secure/path/custom.key \
  -e crt=/secure/path/custom.crt

Verify the certificate without displaying or copying private-key content:

openssl x509 -in files/pki/misc/myservice.crt -noout -subject -issuer -dates -ext subjectAltName
openssl verify -CAfile files/pki/ca/ca.crt files/pki/misc/myservice.crt

For PostgreSQL client certificates, cn must match the database role expected by HBA/cert authentication. Install the certificate, key, and root certificate on the client. Set the private key permissions to 0600. With sslmode=verify-full, the connection hostname must appear in the server certificate SAN.

Trust the CA Certificate

Distribute only the public ca.crt. Never distribute ca.key. Verify its SHA-256 fingerprint through a separate trusted channel before installation.

Debian / Ubuntu

sudo cp ca.crt /usr/local/share/ca-certificates/pigsty-ca.crt
sudo update-ca-certificates

RHEL / Rocky / AlmaLinux

sudo cp ca.crt /etc/pki/ca-trust/source/anchors/pigsty-ca.crt
sudo update-ca-trust

macOS

sudo security add-trusted-cert -d -r trustRoot \
  -k /Library/Keychains/System.keychain ca.crt

Windows (Administrator PowerShell)

Import-Certificate -FilePath .\ca.crt -CertStoreLocation Cert:\LocalMachine\Root

Infra Nginx normally exposes the public CA certificate at http://<infra_ip>/ca.crt. Verify the fingerprint after download. HTTP transport alone does not prove certificate authenticity.

Nginx and Let’s Encrypt

Each infra_portal entry can name a certbot certificate. Pigsty’s /etc/nginx/sign-cert uses Certbot webroot mode. It groups domain and domains entries that share a certificate name. Then /etc/nginx/link-cert links the resulting certificate into Nginx.

Prerequisites:

• Public DNS A/AAAA records resolve to the intended Infra node.
• Port 80 is publicly reachable for HTTP-01, and Nginx serves the ACME webroot.
• certbot_email is valid and the Certbot package is installed.
• Portal domains, additional domains, and certificate names are exact.

certbot_email: [email protected]
infra_portal:
  home:
    domain: example.com
    domains: [www.example.com]
    certbot: example.com
  grafana:
    domain: grafana.example.com
    endpoint: "${admin_ip}:3000"
    websocket: true
    certbot: grafana.example.com

Update the Nginx configuration and issue certificates:

dig +short example.com
./infra.yml -l infra -t nginx_config,nginx_launch

./infra.yml -l infra -t nginx_certbot,nginx_reload -e certbot_sign=true

certbot certificates
test -r /etc/letsencrypt/live/example.com/fullchain.pem
nginx -t
openssl s_client -connect example.com:443 -servername example.com </dev/null

Renewal scheduling depends on the Certbot package for the operating system. Do not add a duplicate cron job before checking existing timers and cron configuration:

systemctl list-timers --all | grep -i certbot
certbot renew --dry-run

After Certbot replaces certificates on disk, Nginx still needs a reload to use them. Configure a renewal deploy hook, such as systemctl reload nginx, or an equivalent managed process. Verify that it works. Treat automatic renewal as proven only after a real or staging renewal exercise.

Troubleshooting and Acceptance

For final acceptance, verify each result separately:

• The certificate content and SANs are correct.
• Chain validation succeeds.
• The service has loaded the new certificate.
• Target clients trust the certificate.
• Renewal is configured and passes a dry run.

A generated file or successful playbook alone does not prove these results.

6 - Grafana High Availability: Using PostgreSQL Backend

You can use PostgreSQL as Grafana’s backend database.

This tutorial explains the Pigsty deployment system through these tasks:

• How to create a new database cluster
• How to create new business users in an existing cluster
• How to create new business databases in an existing cluster
• How to access databases created by Pigsty
• How to manage Grafana dashboards
• How to manage PostgreSQL datasources in Grafana
• How to upgrade Grafana database in one step

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