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, listening 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. Only services with a domain defined will generate corresponding Nginx config files.

• 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 allow accessing all monitoring components through a single entry point, no need for multiple domain configurations.

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 that Nginx can serve. The current package candidate is SOW 0.3.0; the source uses SOW to generate metadata for both repository types instead of calling 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 and installs SOW from the Pigsty INFRA upstream repository.

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

On a fresh installation, if /www does not exist, the role creates /data/nginx and makes /www point to it. Existing directories and symlinks are preserved rather than forcibly replaced.

Build Flow

The command executed by repo_create is:

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

--pigsty removes unneeded or conflict-prone packages and atomically publishes the result only after all metadata has been generated. A typical layout is:

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

Do not treat repo_complete as an empty sentinel; it contains SHA-256 checksums. 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 Pigsty local repository itself is configured with module_hotfixes=1 so local PostgreSQL packages are not hidden by system module streams. This is distinct from generating fake 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 OS mapping; always use roles/node_id/vars/<os>.<arch>.yml as the authority.

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: Uses Host header to route requests to different sites on same port (80/443).

Default Domains

Pigsty predefines the following default domains:

Grafana, VictoriaMetrics, and Alertmanager are accessed by default through the /ui/, /vmetrics/, and /alertmgr/ subpaths under i.pigsty. 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

This document covers daily management operations for the INFRA module, including installation, uninstallation, 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, whose public default is pigsty, and grafana_view_password, whose default is DBUser.Viewer.

Change the public defaults before production. After Grafana initializes, changing grafana_admin_password in inventory does not by itself reset the live Grafana account password; change it through Grafana (or its supported administration interface), keep the inventory consistent, and rerender 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 that database role password changes, update both the declared value and the Grafana data source; changing 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, not 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 are for a controlled internal trust domain; they are not publicly browser-trusted. Clients must explicitly trust ca.crt, while 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 but ca.crt does not, the role still creates a new self-signed CA certificate from that 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 keep it in controlled offline media or a secrets-management system; do not leave an unencrypted tar archive. Restore into an isolated temporary directory first, then verify file count, type, permissions, public-key match, and certificate fingerprint before replacement.

Losing ca.key does not immediately make existing certificates unverifiable: they remain verifiable while clients trust ca.crt and the certificates remain valid and unrevoked. You can no longer issue, renew, or revoke with the original CA, so recovery usually requires a new CA, reissuing every certificate, and rolling out 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 with the private key at mode 0600. With sslmode=verify-full, the connection hostname must appear in the server certificate SAN.

Trust the CA Certificate

Distribute only public ca.crt, never 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, groups domain and domains entries that share a certificate name, and then /etc/nginx/link-cert links the result 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 and verify a renewal deploy hook such as systemctl reload nginx, or an equivalent managed process. Treat automatic renewal as proven only after a real or staging renewal exercise.

Troubleshooting and Acceptance

Final acceptance should separately prove correct certificate content and SANs, successful chain validation, that the service loaded the new certificate, that target clients trust it, and that renewal exists and passes a dry run. A generated file or successful playbook alone proves none of those later layers.

6 - Grafana High Availability: Using PostgreSQL Backend

You can use PostgreSQL as Grafana’s backend database.

This is a great opportunity to understand Pigsty’s deployment system. By completing this tutorial, you’ll learn:

• 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

We can define a new database grafana on pg-meta, or create a dedicated Grafana database cluster pg-grafana 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.

Users and databases defined in pg_users and pg_databases are automatically created during cluster initialization. With this config, after cluster creation (without DNS), you can access the database using these connection strings (any one works):

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

Since Pigsty is installed on a single meta node by default, the following steps will create Grafana’s user and database on the existing pg-meta cluster, not the pg-grafana cluster created here.

Create Grafana Business User

The usual convention for business object management: create user first, then database. Because if the database has an owner configured, it depends on the corresponding 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

When you see activity in the newly added grafana database from the monitoring system, Grafana is now using Postgres as its primary backend database. But there’s a new issue—the original Dashboards and Datasources in Grafana have disappeared! You need to re-import dashboards and Postgres datasources.

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

This script detects the current environment (defined in ~/pigsty during installation), gets Grafana access info, and replaces dashboard URL placeholder domains (*.pigsty) with actual domains used.

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

As a side note, use grafana.py clean to clear target dashboards, and 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

When creating a new PostgreSQL cluster with pgsql.yml or a new business database with pgsql-db.yml, Pigsty registers new PostgreSQL datasources in Grafana. 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

You can directly modify the Pigsty config file to change Grafana’s backend datasource, completing the database switch 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