This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Tasks

Look up common tasks and how to perform them using a short sequence of steps

1: DNS: Access Pigsty Web Services with Domain Names
2: Nginx: Expose Web Service with the Infra Portal
3: Certbot: Free HTTPS Certs
4: Docker: Setup & Proxy
5: Use PostgreSQL as Ansible Config Inventory CMDB
6: Use PG as Grafana Backend
7: Use PG as Prometheus Backend
8: Bind a L2 VIP to Node Cluster with Keepalived
9: Bind a L2 VIP to PostgreSQL Primary with VIP-Manager
10: HugePage: Enable HP for Node and PostgreSQL
11: Citus: HA Cluster
12: HA Drill: 2/3 Failure
13: Restic: FS Backup/Recovery
14: JuiceFS: S3FS & PGFS

1 - DNS: Access Pigsty Web Services with Domain Names

How to configure DNS records to access Pigsty web services using domain names?

After installing Pigsty, users can access most Infra components’ web interfaces via IP + Port.

Let’s say your node’s internal IP is 10.10.10.10, then by default:

http://10.10.10.10:3000 is Grafana dashboard (your daily command center)
http://10.10.10.10:9090 is Prometheus TSDB console
http://10.10.10.10:9093 is AlertManager console
http://10.10.10.10 is Nginx HTTP entry point (default port 80)

While IP + Port works fine for dev/test environments (hey, we’re all lazy sometimes!), for more serious deployments, I strongly recommend accessing these services via domain names.

Using domains has numerous advantages, doesn’t cost extra, and requires just one simple config line.

Let’s dive into these topics:

TL;DR

Add this static resolution record to your /etc/hosts (Linux/MacOS) or C:\Windows\System32\drivers\etc\hosts (Windows):

sudo tee -a /etc/hosts <<EOF
10.10.10.10 h.pigsty g.pigsty p.pigsty a.pigsty
EOF

Replace placeholder IP 10.10.10.10 with your Pigsty node’s IP (public/private, as long as it’s reachable).

If you modified default domains in infra_portal, replace them with your custom domains.

Why Use Domains?

Pigsty strongly recommends using domains instead of direct IP+Port access for several reasons:

Domains are easier to remember (unless you’re a robot 🤖)
More flexible - point to different IPs without changing configs
Consolidate all services behind Nginx for better management, auditing, and reduced attack surface
Enable HTTPS encryption to prevent traffic snooping
In China, HTTP access to unregistered domains gets hijacked by ISPs, but HTTPS doesn’t
Access services bound to 127.0.0.1 or internal Docker networks via Nginx proxy

Pigsty uses internal static domains by default - just add DNS records locally, no need to register real domains.

For internet-facing deployments, consider using real domains with free HTTPS certs.

How DNS Works

If you’re not familiar with HTTP/DNS protocols, here’s a quick primer on how Nginx serves multiple domains on a single port (80 + HTTPS 443):

DNS Protocol

When a client (e.g., browser) accesses https://a.pigsty.cc, it first resolves the domain via DNS
Resolution can use local static files, internal DNS servers, or public DNS
DNS returns an IP - multiple domains can point to the same IP
The client just needs to know: which IP to send requests to

HTTP Protocol

HTTP requests (HTTP/1.1+) include a Host header with the requested domain
This Host header is crucial - HTTP/1.1 spec requires clients to include it
Nginx uses the Host header to match and route requests to different sites
Thus, one port can serve different content based on the Host value

Pigsty Default Domains

Pigsty configures these four internal domains by default:

Domain	Name	Port	Component	Description
`h.pigsty`	`home`	80/443	Nginx	Default server, local repo
`g.pigsty`	`grafana`	3000	Grafana	Monitoring & visualization
`p.pigsty`	`prometheus`	9090	Prometheus	Time series DB
`a.pigsty`	`alertmanager`	9093	AlertManager	Alert aggregation & routing

Since these domains don’t use TLDs, you’ll need local static or internal dynamic resolution.

Don’t worry - it’s just one config line away! 🚀

Local Static Resolution

Assuming Pigsty’s internal IP is 10.10.10.10, add this to your client machine’s hosts file:

# Pigsty core components & default domains
10.10.10.10 h.pigsty g.pigsty p.pigsty a.pigsty

Adding Resolution

The client machine is where you browse Pigsty services - your laptop, desktop, VM, etc.

For Linux/MacOS: sudo nano /etc/hosts For Windows: Run notepad as admin, edit C:\Windows\System32\drivers\etc\hosts

After adding the record, you can access Pigsty web services via these domains.

Custom Domains

Not a fan of default domains? Modify them in infra_portal before installation:

infra_portal:
  home         : { domain: h.pigsty.xxx }
  grafana      : { domain: g.pigsty.xxx ,endpoint: "${admin_ip}:3000" ,websocket: true }
  prometheus   : { domain: p.pigsty.xxx ,endpoint: "${admin_ip}:9090" }
  alertmanager : { domain: a.pigsty.xxx ,endpoint: "${admin_ip}:9093" }
  blackbox     : { endpoint: "${admin_ip}:9115" }
  loki         : { endpoint: "${admin_ip}:3100" }

Then update your hosts file accordingly:

10.10.10.10 h.pigsty.xxx g.pigsty.xxx p.pigsty.xxx a.pigsty.xxx

Use any domain you like - real or made-up - as long as it resolves to Pigsty’s IP via local, internal, or public DNS.

Additional Records

Running other Pigsty extensions? Add these records too:

# Pigsty extension tools & default domains
10.10.10.10 adm.pigsty   # pgAdmin GUI
10.10.10.10 ddl.pigsty   # Bytebase DDL management
10.10.10.10 cli.pigsty   # pig CLI reserved
10.10.10.10 api.pigsty   # Pigsty API reserved
10.10.10.10 lab.pigsty   # JupyterLab reserved
10.10.10.10 git.pigsty   # Gitea reserved
10.10.10.10 wiki.pigsty  # Wiki.js reserved
10.10.10.10 noco.pigsty  # NocoDB reserved
10.10.10.10 supa.pigsty  # Supabase reserved
10.10.10.10 dify.pigsty  # Dify reserved
10.10.10.10 odoo.pigsty  # Odoo reserved
10.10.10.10 mm.pigsty    # MinIO reserved

Public IP Resolution

For cloud deployments, resolve to your public IP, not internal IP.

If your server has internet access, it typically has two NICs - one for internet (public IP) and one for internal network (private IP).

Example: If your cloud server’s public IP is 1.2.3.4 and VPC IP is 10.10.10.10:

# For cloud deployments, resolve to public IP! Just change the IP part:
1.2.3.4 h.pigsty g.pigsty p.pigsty a.pigsty

Internal Dynamic Resolution

Want your office colleagues to access Pigsty via domains? Use internal dynamic resolution.

The simplest way: Ask your network admin to add the DNS records to your internal DNS server.

Using Internal DNS

If your internal DNS server is 192.168.1.1, on Linux/MacOS edit /etc/resolv.conf:

nameserver 192.168.1.1

On Windows: Network Settings → Network Adapter → TCP/IPv4 Properties → DNS config

Test internal DNS resolution:

dig h.pigsty @192.168.1.1

Using Pigsty’s DNS

Pigsty Infra module includes DNS server (port 53).

⚠️ Warning for China deployments: Public servers typically cannot run DNS services (port 53)!

Local HTTPS Access

HTTP access to Pigsty shows “Not Secure” - it’s plaintext, susceptible to MITM attacks.

By default, Pigsty uses a local self-signed CA to issue certs for all Nginx virtual hosts.

HTTPS access shows “Certificate Error” - these are self-signed certs, not from a trusted CA.

Your options:

Ignore it, use HTTP or IP+Port (it’s internal anyway, right? 😅)
Use HTTPS, click “Advanced → Proceed anyway”
Chrome users: Type thisisunsafe when warned (magic words!)
Trust the self-signed certs by adding Pigsty’s CA to your browser/OS
Use a real CA cert for Pigsty
Use real domains with proper HTTPS certs

For internal access needing HTTPS without constant warnings, trust Pigsty’s self-signed CA.

For production, we recommend using public domains with free HTTPS certs via certbot.

Trust Self-signed CA

Pigsty generates a self-signed CA in the admin node source directory (~/pigsty) during init.

To use HTTPS, distribute Pigsty’s CA cert to client trust stores (or use real CAs - expensive!).

Pigsty-managed Linux nodes auto-trust the CA. For other Linux systems:

rm -rf /etc/pki/ca-trust/source/anchors/ca.crt
ln -s /etc/pki/ca.crt /etc/pki/ca-trust/source/anchors/ca.crt
/bin/update-ca-trust

rm -rf /usr/local/share/ca-certificates/ca.crt
ln -s /etc/pki/ca.crt /usr/local/share/ca-certificates/ca.crt
/usr/sbin/update-ca-certificates

MacOS: Double-click ca.crt, add to Keychain, search pigsty-ca, open and “Trust” the root cert.

Windows: Add ca.crt to “Trusted Root Certification Authorities”.

After trusting Pigsty’s CA, no more “untrusted certificate” warnings! 🎉

Public Domain Resolution

Use DNS providers like Cloudflare, Godaddy, Aliyun, or Tencent Cloud DNSPod.

Requires purchasing a domain - basic ones cost ~$10/year.

Add DNS records via provider’s console/API to point domains to Pigsty’s public IP.

Example: With domain pigsty.xxx, add wildcard * A record or individual A records:

h.pigsty.xxx → 1.2.3.4
a.pigsty.xxx → 1.2.3.4
p.pigsty.xxx → 1.2.3.4
g.pigsty.xxx → 1.2.3.4

Pigsty includes Certbot support for free HTTPS certs (renew every 3 months).

2 - Nginx: Expose Web Service with the Infra Portal

How to expose, proxy, and forward upstream & serve local path using Nginx?

Pigsty will install Nginx on INFRA Node, as a Web service proxy.

Nginx is the access entry for all WebUI services of Pigsty, and it defaults to the use the 80/443 port on INFRA nodes.

Pigsty provides a global parameter infra_portal to configure Nginx proxy rules and corresponding upstream services.

infra_portal:  # domain names and upstream servers
  home         : { domain: h.pigsty }
  grafana      : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
  prometheus   : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
  alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
  blackbox     : { endpoint: "${admin_ip}:9115" }
  loki         : { endpoint: "${admin_ip}:3100" }
  #minio        : { domain: sss.pigsty  ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }

If you access Nginx directly through the ip:port, it will route to h.pigsty, which is the Pigsty homepage (/www/ directory, served as software repo).

Because Nginx provides multiple services through the same port, it must be distinguished by the domain name (HOST header by the browser). Therefore, by default, Nginx only exposes services with the domain parameter.

And Pigsty will expose grafana, prometheus, and alertmanager services by default in addition to the home server.

How to configure nginx upstream?

Pigsty has a built-in configuration template full.yml, could be used as a reference, and also exposes some Web services in addition to the default services.

    infra_portal:                     # domain names and upstream servers
      home         : { domain: h.pigsty }
      grafana      : { domain: g.pigsty ,endpoint: "${admin_ip}:3000" , websocket: true }
      prometheus   : { domain: p.pigsty ,endpoint: "${admin_ip}:9090" }
      alertmanager : { domain: a.pigsty ,endpoint: "${admin_ip}:9093" }
      blackbox     : { endpoint: "${admin_ip}:9115" }
      loki         : { endpoint: "${admin_ip}:3100" }
      
      minio        : { domain: sss.pigsty  ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
      postgrest    : { domain: api.pigsty  ,endpoint: "127.0.0.1:8884" }
      pgadmin      : { domain: adm.pigsty  ,endpoint: "127.0.0.1:8885" }
      pgweb        : { domain: cli.pigsty  ,endpoint: "127.0.0.1:8886" }
      bytebase     : { domain: ddl.pigsty  ,endpoint: "127.0.0.1:8887" }
      jupyter      : { domain: lab.pigsty  ,endpoint: "127.0.0.1:8888", websocket: true }
      gitea        : { domain: git.pigsty  ,endpoint: "127.0.0.1:8889" }
      wiki         : { domain: wiki.pigsty ,endpoint: "127.0.0.1:9002" }
      noco         : { domain: noco.pigsty ,endpoint: "127.0.0.1:9003" }
      supa         : { domain: supa.pigsty ,endpoint: "10.10.10.10:8000", websocket: true }

Each record in infra_portal is a key-value pair, where the key is the name of the service, and the value is a dictionary. Currently, there are four available configuration items in the configuration dictionary:

endpoint: REQUIRED, specifies the address of the upstream service, which can be IP:PORT or DOMAIN:PORT.
- In this parameter, you can use the placeholder ${admin_ip}, and Pigsty will fill in the value of admin_ip.
domain: OPTIONAL, specifies the domain name of the proxy. If not filled in, Nginx will not expose this service.
- For services that need to know the endpoint address but do not want to expose them (such as Loki, Blackbox Exporter), you can leave the domain blank.
scheme: OPTIONAL, specifies the protocol (http/https) when forwarding, leave it blank to default to http.
- For services that require HTTPS access (such as the MinIO management interface), you need to specify scheme: https.
websocket: OPTIONAL, specifies whether to enable WebSocket, leave it blank to default to off.
- Services that require WebSocket (such as Grafana, Jupyter) need to be set to true to work properly.

If you need to add a new Web service exposed by Nginx, you need to add the corresponding record in the infra_portal parameter in the pigsty.yml file, and then execute the playbook to take effect:

./infra.yml -t nginx           # Tune nginx into desired state

To avoid service interruption, you can also execute the following tasks separately:

./infra.yml -t nginx_config    # re-generate nginx upstream config in /etc/nginx/conf.d
./infra.yml -t nginx_cert      # re-generate nginx ssl cert to include new domain names
nginx -s reload                # online reload nginx configuration

Or the simple way:

Nginx related configuration parameters are located at: Parameters: INFRA - NGINX

How to access via domain names?

Nginx distinguishes between different services using the domain name in the HOST header set by the browser. Thus, by default, except for the software repository, you need to access services via domain name.

You can directly access these services via IP address + port, but we recommend accessing all components through Nginx on ports 80/443 using domain names.

When accessing the Pigsty WebUI via domain name, you need to configure DNS resolution or modify the local /etc/hosts file for static resolution. There are several typical methods:

If your service needs to be publicly accessible, you should resolve the internet domain name via a DNS provider (Cloudflare, Aliyun DNS, etc.). Note that in this case, you usually need to modify the Pigsty infra_portalparameter, as the default *.pigsty is not suitable for public use.
If your service needs to be shared within an office network, you should resolve the internal domain name via an internal DNS provider (company internal DNS server) and point it to the IP of the Nginx server. You can request the network administrator to add the appropriate resolution records in the internal DNS server, or ask the system users to manually configure static DNS resolution records.
If your service is only for personal use or a few users (e.g., DBA), you can ask these users to use static domain name resolution records. On Linux/MacOS systems, modify the /etc/hosts file (requires sudo permissions) or C:\Windows\System32\drivers\etc\hosts (Windows) file.

We recommend ordinary single-machine users use the third method, adding the following resolution records on the machine used to access the web system via a browser:

<your_public_ip_address>  h.pigsty a.pigsty p.pigsty g.pigsty

The IP address here is the public IP address where the Pigsty service is installed, and then you can access Pigsty subsystems in the browser via a domain like: http://g.pigsty.

Other web services and custom domains can be added similarly. For example, the following are possible domain resolution records for the Pigsty sandbox demo:

10.10.10.10 h.pigsty a.pigsty p.pigsty g.pigsty
10.10.10.10 api.pigsty ddl.pigsty adm.pigsty cli.pigsty lab.pigsty
10.10.10.10 supa.pigsty noco.pigsty odoo.pigsty dify.pigsty

How to access via HTTPS?

If nginx_sslmode is set to enabled or enforced, you can trust self-signed ca: files/pki/ca/ca.crt to use https in your browser.

Pigsty will generate self-signed certs for Nginx, if you wish to access via HTTPS without “Warning”, here are some options:

Apply & add real certs from trusted CA: such as Let’s Encrypt
Trust your generated CA crt as root ca in your OS and browser
Type thisisunsafe in Chrome will supress the warning

You can access these web UI directly via IP + port. While the common best practice would be access them through Nginx and distinguish via domain names. You’ll need configure DNS records, or use the local static records (/etc/hosts) for that.

How to access Pigsty Web UI by domain name?

There are several options:

Resolve internet domain names through a DNS service provider, suitable for systems accessible from the public internet.
Configure internal network DNS server resolution records for internal domain name resolution.
Modify the local machine’s /etc/hosts file to add static resolution records. (For Windows, it’s located at:)

We recommend the third method for common users. On the machine (which runs the browser), add the following record into /etc/hosts (sudo required) or C:\Windows\System32\drivers\etc\hosts in Windows:

<your_public_ip_address>  h.pigsty a.pigsty p.pigsty g.pigsty

You have to use the external IP address of the node here.

How to configure server side domain names?

The server-side domain name is configured with Nginx. If you want to replace the default domain name, simply enter the domain you wish to use in the parameter infra_portal. When you access the Grafana monitoring homepage via http://g.pigsty, it is actually accessed through the Nginx proxy to Grafana’s WebUI:

http://g.pigsty ️-> http://10.10.10.10:80 (nginx) -> http://10.10.10.10:3000 (grafana)

3 - Certbot: Free HTTPS Certs

How to apply free Let’s Encrypt HTTPS certificates with certbot?

Pigsty comes with the Certbot tool pre-installed and enabled by default on the Infra node.

This means you can directly use the certbot command-line tool to request a genuine Let’s Encrypt free HTTPS certificate for your Nginx server and public domain, instead of using the self-signed HTTPS certificates provided by Pigsty.

To achieve this, you need to:

Determine which domains require certificates
Point these domains to your server
Use Certbot to apply for certificates
Configure a scheduled task to renew the certificates
Be aware of some important considerations when applying for certificates

Here are the detailed steps:

Determine Which Domains Need Certificates

First, decide which “upstream services” need genuine public certificates.

infra_portal:
  home         : { domain: h.pigsty.cc }
  grafana      : { domain: g.pigsty.cc ,endpoint: "${admin_ip}:3000" ,websocket: true  }
  prometheus   : { domain: p.pigsty.cc ,endpoint: "${admin_ip}:9090" }
  alertmanager : { domain: a.pigsty.cc ,endpoint: "${admin_ip}:9093" }
  blackbox     : { endpoint: "${admin_ip}:9115" }
  loki         : { endpoint: "${admin_ip}:3100" }
  minio        : { domain: m.pigsty.cc    ,endpoint: "${admin_ip}:9001" ,scheme: https ,websocket: true }
  web          : { domain: pigsty.cc      ,path: "/www/web.cc" }
  repo         : { domain: repo.pigsty.cc ,path: "/www/repo"   }

For example, in infra_portal, suppose we need to expose the following five services:

Grafana monitoring dashboard at g.pigsty.cc
Prometheus time-series database at p.pigsty.cc
AlertManager alert dashboard at a.pigsty.cc
Pigsty documentation site at pigsty.cc, pointing to the local documentation directory
Pigsty software repository at repo.pigsty.cc, pointing to the software repository

In this example, we intentionally did not choose to apply for a genuine Let’s Encrypt certificate for the home page. The reason will be explained in the last section.

Point These Domains to Your Server

Next, you need to point the selected domains to your server’s public IP address. For example, if the Pigsty CC site’s IP address is 47.83.172.23, you can set the following A records for domain resolution in your domain registrar’s DNS control panel (e.g., Alibaba Cloud DNS Console):

47.83.172.23 pigsty.cc
47.83.172.23 g.pigsty.cc
47.83.172.23 p.pigsty.cc
47.83.172.23 a.pigsty.cc
47.83.172.23 repo.pigsty.cc

After making the changes, you can verify using

Use Certbot to Apply for Certificates

The first time you apply, certbot will prompt you to enter your email and agree to the terms of service. Just follow the instructions.

$ certbot --nginx -d pigsty.cc -d repo.pigsty.cc -d g.pigsty.cc -d p.pigsty.cc -d a.pigsty.cc
Saving debug log to /var/log/letsencrypt/letsencrypt.log
Enter email address (used for urgent renewal and security notices)
 (Enter 'c' to cancel): [email protected]

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Please read the Terms of Service at
https://letsencrypt.org/documents/LE-SA-v1.4-April-3-2024.pdf. You must agree in
order to register with the ACME server. Do you agree?
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
(Y)es/(N)o: Y

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Would you be willing, once your first certificate is successfully issued, to
share your email address with the Electronic Frontier Foundation, a founding
partner of the Let's Encrypt project and the non-profit organization that
develops Certbot? We'd like to send you email about our work encrypting the web,
EFF news, campaigns, and ways to support digital freedom.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
(Y)es/(N)o: N
Account registered.
Requesting a certificate for pigsty.cc and 4 more domains

Successfully received certificate.
Certificate is saved at: /etc/letsencrypt/live/pigsty.cc/fullchain.pem
Key is saved at:         /etc/letsencrypt/live/pigsty.cc/privkey.pem
This certificate expires on 2025-05-18.
These files will be updated when the certificate renews.
Certbot has set up a scheduled task to automatically renew this certificate in the background.

Deploying certificate
Successfully deployed certificate for pigsty.cc to /etc/nginx/conf.d/web.conf
Successfully deployed certificate for repo.pigsty.cc to /etc/nginx/conf.d/repo.conf
Successfully deployed certificate for g.pigsty.cc to /etc/nginx/conf.d/grafana.conf
Successfully deployed certificate for p.pigsty.cc to /etc/nginx/conf.d/prometheus.conf
Successfully deployed certificate for a.pigsty.cc to /etc/nginx/conf.d/alertmanager.conf
Congratulations! You have successfully enabled HTTPS on https://pigsty.cc, https://repo.pigsty.cc, https://g.pigsty.cc, https://p.pigsty.cc, and https://a.pigsty.cc

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
If you like Certbot, please consider supporting our work by:
 * Donating to ISRG / Let's Encrypt:   https://letsencrypt.org/donate
 * Donating to EFF:                    https://eff.org/donate-le
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

After the first application, you can skip these steps and use the command directly for future applications.

Configure a Scheduled Task to Renew the Certificate

By default, certificates are valid for three months, so you should renew them before they expire by using certbot renew.

To renew the certificate, run the following command:

certbot renew

Before executing for real, you can use the DryRun mode to test if the renewal works correctly:

certbot renew --dry-run

If you’ve modified the Nginx configuration file, make sure that Certbot’s changes do not interfere with your configuration.

You can set this command as a crontab job to run on the first day of each month at midnight to renew the certificate and print the log.

Caveats

Special attention should be paid to the SSL certificate for home. When you apply for a certificate for it, Certbot will modify the Nginx configuration file to redirect HTTP on port 80 to HTTPS on port 443. However, this will affect the default repo_upstream local software repository unless you make corresponding adjustments.

4 - Docker: Setup & Proxy

How to configure container support in Pigsty? and how to configure mirror & proxy for DockerHub?

Pigsty has a DOCKER module, which provides a set of playbooks to install and manage Docker on the target nodes.

This document will guide you through how to enable Docker support in Pigsty, and how to configure a proxy server for DockerHub.

Install Docker

To install docker on specified nodes, you can use the docker.yml playbook:

./docker.yml -l <ip|group|cls>

That’s it.

Proxy 101

Assuming you have a working HTTP(s) proxy server, if you wish to connect to docker hub or other registry sites via the proxy server:

You proxy service should provide you with something like

http://<ip|domain>:<port> | https://[user]:[pass]@<ip|domain>:<port>

For example, if you have a proxy server configuring like this:

export ALL_PROXY=http://192.168.0.106:8118
export HTTP_PROXY=http://192.168.0.106:8118
export HTTPS_PROXY=http://192.168.0.106:8118
export NO_PROXY="localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,*.pigsty,*.aliyun.com,mirrors.*,*.myqcloud.com,*.tsinghua.edu.cn"

You can check the proxy server by using the curl command, for example:

curl -x http://192.168.0.106:8118 -I http://www.google.com

How to configure proxy for Docker Daemon?

If you wish to use a proxy server when Docker pulls images, you should specify the proxy_env parameter in the global variables of the pigsty.yml configuration file:

all:
  vars:
    proxy_env:                        # global proxy env when downloading packages
      no_proxy: "localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,*.pigsty,*.aliyun.com,mirrors.*,*.myqcloud.com,*.tsinghua.edu.cn"
      http_proxy: http://192.168.0.106:8118
      all_proxy: http://192.168.0.106:8118
      https_proxy: http://192.168.0.106:8118

And when the Docker playbook is executed, these configurations will be rendered as proxy configurations in /etc/docker/daemon.json:

{
  "proxies": {
    "http-proxy": "{{ proxy_env['http_proxy'] }}",
    "https-proxy": "{{ proxy_env['http_proxy'] }}",
    "no-proxy": "{{ proxy_env['no_proxy'] }}"
  }
}

Please note that Docker Daemon does not use the all_proxy parameter

If you wish to manually specify a proxy server, you can directly modify the proxies configuration in /etc/docker/daemon.json;

Or you can modify the service definition in /lib/systemd/system/docker.service (Debian/Ubuntu) and /usr/lib/systemd/system/docker.service to add environment variable declarations in the [Service] section

[Service]
Environment="HTTP_PROXY=http://192.168.0.106:8118"
Environment="HTTPS_PROXY=http://192.168.0.106:8118"
Environment="NO_PROXY=localhost,127.0.0.1,10.0.0.0/8,192.168.0.0/16,*.pigsty,*.aliyun.com,mirrors.*,*.myqcloud.com,*.tsinghua.edu.cn"

And restart dockerd service to take effect:

systemctl restart docker

How to use other registry?

You can specify other registry sites in the docker_registry_mirrors parameter.

It may look like this:

[ "https://mirror.ccs.tencentyun.com" ]         # tencent cloud mirror, intranet only
["https://registry.cn-hangzhou.aliyuncs.com"]   # aliyun cloud mirror, login required

You can also log in to other mirror sites, such as quay.io, by executing:

docker login quay.io
username #> # input your username
password #> # input your password

5 - Use PostgreSQL as Ansible Config Inventory CMDB

Use PostgreSQL instead of static YAML config file as Ansible config inventory

You can use PostgreSQL as a configuration source for Pigsty, replacing the static YAML configuration file.

There are some advantages to using CMDB as a dynamic inventory: metadata is presented in a highly structured way in the form of data tables, and consistency is ensured through database constraints. At the same time, using CMDB allows you to use third-party tools to edit and manage Pigsty metadata, making it easier to integrate with external systems.

Ansible Inventory

Pigsty’s default configuration file path is specified in ansible.cfg as inventory = pigsty.yml.

Changing this parameter will change the default configuration file path used by Ansible. If you point it to an executable script file, Ansible will use the dynamic inventory mechanism, execute the script, and expect the script to return a configuration file.

Using CMDB is implemented by editing the ansible.cfg in the Pigsty directory:

---
inventory = pigsty.yml
+++
inventory = inventory.sh

And the inventory.sh is a simple script that generates equivalent YAML/JSON configuration files from the records in the PostgreSQL CMDB.

You can use bin/inventory_cmdb to switch to CMDB inventory, and use bin/inventory_conf to switch back to the local YAML configuration file.

You may also need bin/inventory_load to load the YAML configuration file into the CMDB.

Load Configuration

Pigsty will init a CMDB (optional) on the default pg-meta cluster’s meta database, under pigsty schema.

the CMDB is available only after infra.yml is fully executed on the admin node.

You can load YAML config file into CMDB with bin/inventory_load:

usage: inventory_load [-h] [-p PATH] [-d CMDB_URL]

load config arguments

optional arguments:
  -h, --help            show this help message and exit„
  -p PATH, --path PATH  config path, ${PIGSTY_HOME}/pigsty.yml by default
  -d DATA, --data DATA  postgres cmdb pgurl, ${METADB_URL} by default

If you run bin/inventory_load without any arguments, it will load the default pigsty.yml into the default CMDB.

bin/inventory_load
bin/inventory_load -p conf/demo.yml
bin/inventory_load -p conf/prod.yml -d postgresql://dbuser_meta:[email protected]:5432/meta

You can switch to dynamic CMDB inventory with:

bin/inventory_cmdb

To switch back to local YAML config file:

bin/inventory_conf

6 - Use PG as Grafana Backend

Use PostgreSQL instead of default SQLite, as the backend storage for Grafana

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

In this tutorial, you will learn about the following.

How to create a new cluster
How to create a new biz user in an existing database cluster
How to create a new biz database in an existing database cluster
How to access databases created by Pigsty
How to manage dashboards in Grafana
How to manage PostgreSQL DataSources in Grafana
How to do upgrade the grafana database

TL; DR

vi pigsty.yml   # uncomment user/db definition: 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;'    # check pgurl connectivity
  
vi /etc/grafana/grafana.ini            # edit [database] section: type & url
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.

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

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

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

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

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

postgres://dbuser_grafana:[email protected]:5432/grafana # direct connection to the primary
postgres://dbuser_grafana:[email protected]:5436/grafana # direct connection to the default service
postgres://dbuser_grafana:[email protected]:5433/grafana # Connect to the string read/write service

postgres://dbuser_grafana:[email protected]:5432/grafana # direct connection to the primary
postgres://dbuser_grafana:[email protected]:5436/grafana # Direct connection to default service
postgres://dbuser_grafana:[email protected]: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.

- name: dbuser_grafana
  password: DBUser.Grafana
  comment: admin user for grafana database
  pgbouncer: true
  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.

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

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

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

Create database

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

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

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

. /pgsql-db.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.

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

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.

psql postgres://dbuser_grafana:DBUser.Grafana@meta:5436/grafana -c \
  '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.

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

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

Subsequently, restart Grafana.

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.

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

Execution results in:

vagrant@meta:~/pigsty/files/ui
$ ./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
init dashboard: pgsql / pgsql-table.json
init dashboard: pgsql / pgsql-activity.json
init dashboard: pgsql / pgsql-cluster.json
init dashboard: pgsql / pgsql-node.json
init dashboard: pgsql / pgsql-database.json
init dashboard: pgsql / pgsql-xacts.json
init dashboard: pgsql / pgsql-overview.json
init dashboard: pgsql / pgsql-session.json
init dashboard: pgsql / pgsql-tables.json
init dashboard: pgsql / pgsql-instance.json
init dashboard: pgsql / pgsql-queries.json
init dashboard: pgsql / pgsql-alert.json
init dashboard: pgsql / pgsql-service.json
init dashboard: pgsql / pgsql-persist.json
init dashboard: pgsql / pgsql-proxy.json
init dashboard: pgsql / pgsql-query.json
init folder pglog
init dashboard: pglog / pglog-instance.json
init dashboard: pglog / pglog-analysis.json
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.

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

./pgsql.yml -t register_grafana # Re-register all Postgres data sources in the current environment
./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.

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

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

./infra.yml -t grafana

7 - Use PG as Prometheus Backend

Persist prometheus metrics with PostgreSQL + TimescaleDB through Promscale

It is not recommended to use PostgreSQL as a backend for Prometheus, but it is a good opportunity to understand the Pigsty deployment system.

Postgres Preparation

vi pigsty.yml # dbuser_prometheus  prometheus

pg_databases:                           # define business users/roles on this cluster, array of user definition
  - { name: prometheus, owner: dbuser_prometheus , revokeconn: true, comment: prometheus primary database }
pg_users:                           # define business users/roles on this cluster, array of user definition
  - {name: dbuser_prometheus , password: DBUser.Prometheus ,pgbouncer: true , createrole: true,  roles: [dbrole_admin], comment: admin user for prometheus database }

Create the database and user:

bin/pgsql-user  pg-meta  dbuser_prometheus
bin/pgsql-db    pg-meta  prometheus

Check the connection string:

psql postgres://dbuser_prometheus:[email protected]:5432/prometheus -c 'CREATE EXTENSION timescaledb;'

Promscale Configuration

Install promscale package:

yum install -y promscale

If the package is not available in the default repository, you can download it directly:

wget https://github.com/timescale/promscale/releases/download/0.6.1/promscale_0.6.1_Linux_x86_64.rpm
sudo rpm -ivh promscale_0.6.1_Linux_x86_64.rpm

Edit the promscale configuration file /etc/sysconfig/promscale.conf

PROMSCALE_DB_HOST="127.0.0.1"
PROMSCALE_DB_NAME="prometheus"
PROMSCALE_DB_PASSWORD="DBUser.Prometheus"
PROMSCALE_DB_PORT="5432"
PROMSCALE_DB_SSL_MODE="disable"
PROMSCALE_DB_USER="dbuser_prometheus"

Launch promscale service, it will create schema in prometheus database.

# launch 
cat /usr/lib/systemd/system/promscale.service
systemctl start promscale && systemctl status promscale

Prometheus Configuration

Prometheus can use Remote Write/ Remote Read to store metrics in Postgres via Promscale.

Edit the Prometheus configuration file:

vi /etc/prometheus/prometheus.yml

Add the following configuration to the remote_write and remote_read sections:

remote_write:
  - url: "http://127.0.0.1:9201/write"
remote_read:
  - url: "http://127.0.0.1:9201/read"

Metrics are loaded into Postgres after Prometheus is restarted.

systemctl restart prometheus

8 - Bind a L2 VIP to Node Cluster with Keepalived

How to bind an optional L2 VIP to a node cluster with keepalived?

You can bind an optional L2 VIP on a node cluster with vip_enabled.

proxy:
  hosts:
    10.10.10.29: { nodename: proxy-1 } 
    10.10.10.30: { nodename: proxy-2 } # , vip_role: master }
  vars:
    node_cluster: proxy
    vip_enabled: true
    vip_vrid: 128
    vip_address: 10.10.10.99
    vip_interface: eth1

./node.yml -l proxy -t node_vip     # enable for the first time
./node.yml -l proxy -t vip_refresh  # refresh vip config (e.g. designated master)

9 - Bind a L2 VIP to PostgreSQL Primary with VIP-Manager

How to bind an optional L2 VIP to a PostgreSQL cluster with VIP-Manager?

You can define an OPTIONAL L2 VIP on a PostgreSQL cluster, provided that all nodes in the cluster are in the same L2 network.

This VIP works on Master-Backup mode and always points to the node where the primary instance of the database cluster is located.

This VIP is managed by the VIP-Manager, which reads the Leader Key written by Patroni from DCS (etcd) to determine whether it is the master.

Enable VIP

Define pg_vip_enabled parameter as true in the cluster level:

# pgsql 3 node ha cluster: pg-test
pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary }   # primary instance, leader of cluster
    10.10.10.12: { pg_seq: 2, pg_role: replica }   # replica instance, follower of leader
    10.10.10.13: { pg_seq: 3, pg_role: replica, pg_offline_query: true } # replica with offline access
  vars:
    pg_cluster: pg-test           # define pgsql cluster name
    pg_users:  [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
    pg_databases: [{ name: test }]

    # 启用 L2 VIP
    pg_vip_enabled: true
    pg_vip_address: 10.10.10.3/24
    pg_vip_interface: eth1

Beware that pg_vip_address must be a valid IP address with subnet and available in the current L2 network.

Beware that pg_vip_interface must be a valid network interface name and should be the same as the one using IPv4 address in the inventory.

If the network interface name is different among cluster members, users should explicitly specify the pg_vip_interface parameter for each instance, for example:

pg-test:
  hosts:
    10.10.10.11: { pg_seq: 1, pg_role: primary , pg_vip_interface: eth0  }
    10.10.10.12: { pg_seq: 2, pg_role: replica , pg_vip_interface: eth1  }
    10.10.10.13: { pg_seq: 3, pg_role: replica , pg_vip_interface: ens33 }
  vars:
    pg_cluster: pg-test           # define pgsql cluster name
    pg_users:  [{ name: test , password: test , pgbouncer: true , roles: [ dbrole_admin ] }]
    pg_databases: [{ name: test }]

    # 启用 L2 VIP
    pg_vip_enabled: true
    pg_vip_address: 10.10.10.3/24
    #pg_vip_interface: eth1

To refresh the VIP configuration and restart the VIP-Manager, use the following command:

./pgsql.yml -t pg_vip

10 - HugePage: Enable HP for Node and PostgreSQL

How to allocate precise huge pages for PostgreSQL clusters?

Pros and Cons of Huge Pages

For databases, enabling huge pages has both advantages and disadvantages.

Advantages:

Significant performance benefits in OLAP scenarios: large data scanning and batch computing
More controlled memory allocation model: “lock” required memory at startup
Improved memory access efficiency, reduced TLB misses
Lower kernel page table maintenance overhead

Disadvantages:

Additional configuration and maintenance complexity
Locked huge page memory, less flexibility for systems requiring high resource elasticity
Limited benefits in small memory scenarios, potentially counterproductive

Note: Huge Pages and Transparent Huge Pages (THP) are different concepts. Pigsty will force disable Transparent Huge Pages to follow database best practices.

When to Enable Huge Pages?

We recommend enabling huge pages if your scenario meets the following conditions:

OLAP analytical workloads
Memory exceeding several tens of GB
PostgreSQL 15+
Linux kernel version > 3.10 (> EL7, > Ubuntu 16)

Pigsty does not enable huge pages by default, but you can easily enable them through configuration and dedicate them to PostgreSQL.

Allocate Node Huge Pages

To enable huge pages on a node, users can use these two parameters:

node_hugepage_count: Precisely specify the number of 2MB huge pages to allocate
node_hugepage_ratio: Specify a percentage to allocate a portion of memory as huge pages

Choose one of these parameters. You can either specify the exact number of (2MB) huge pages or the memory ratio to be allocated as huge pages (0.00 - 0.90), with the former having higher priority.

node_hugepage_count: 0            # Precisely specify number of 2MB huge pages, higher priority
node_hugepage_ratio: 0            # Memory ratio allocated as 2MB huge pages, lower priority

Apply changes:

./node.yml -t node_tune

Essentially, this writes the vm.nr_hugepages parameter to /etc/sysctl.d/hugepage.conf and executes sysctl -p to apply changes.

./node.yml -t node_tune -e node_hugepage_count=3000    # Allocate 3000 2MB huge pages (6GB)
./node.yml -t node_tune -e node_hugepage_ratio=0.30    # Allocate 30% of memory as huge pages

Note: These parameters enable huge pages for the entire node, not just PostgreSQL.

PostgreSQL server attempts to use huge pages by default at startup. If insufficient huge pages are available, PostgreSQL will continue to start using regular pages.

When reducing huge page count, only unused and free huge pages will be released. Used huge pages will be released after process termination.

Pigsty allows allocating up to 90% of memory as huge pages, but for PostgreSQL databases, a reasonable range is typically 25% - 40% of memory.

We recommend setting: node_hugepage_ratio=0.30 and further adjusting huge page count after PostgreSQL startup as needed.

Monitor Huge Page Status

The most intuitive way is to use Pigsty’s monitoring system. Here’s a sample monitoring chart when adjusting huge pages:

Node Instance - Memory - HugePages Allocation

Default state
Huge pages enabled, unused
PG restarted, using/reserving some huge pages
Further PG usage, using more huge pages
Reduced huge page count, reclaimed unused pages
PG restarted, completely released reserved pages

You can directly check huge page status with cat /proc/meminfo | grep Huge:

$ cat /proc/meminfo  | grep Huge

By default, with no huge pages enabled, the total count is 0:

AnonHugePages:      8192 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB

With huge pages enabled, showing 6015 total huge pages, all free and available:

AnonHugePages:      8192 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    6015
HugePages_Free:     6015
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:        12318720 kB

If PostgreSQL is restarted now (it attempts to use huge pages by default):

sudo su - postgres
pg-restart

PostgreSQL will reserve (Rsvd, Reserved) the required huge pages for shared buffers, for example reserving 5040 here:

AnonHugePages:      8192 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    6015
HugePages_Free:     5887
HugePages_Rsvd:     5040
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:        12318720 kB

If we add some load to PostgreSQL, like pgbench -is10, PostgreSQL will start using more huge pages (Alloc = Total - Free).

Note: Once huge pages are (allocated or reserved), even if you reduce the system’s vm.nr_hugepages parameter, these pages will remain reserved until used up. Therefore, to truly reclaim these huge pages, you need to restart the PostgreSQL service.

./node.yml -t node_tune -e node_hugepage_count=3000    # Allocate 3000 huge pages

Precise Huge Page Allocation

Before PostgreSQL starts, you need to allocate sufficient huge pages, otherwise PostgreSQL won’t be able to use them.

In Pigsty, the default SharedBuffer doesn’t exceed 25% of memory, so you can allocate 26% ~ 27% of memory as huge pages to ensure PostgreSQL can use them.

node_hugepage_ratio: 0.27  # Allocate 27% of memory as huge pages, definitely enough for PG

If you don’t mind slight resource waste, you can directly allocate around 27% of memory as huge pages.

Reclamation Script

After PG starts, you can query the actual number of huge pages PostgreSQL uses with this SQL:

SHOW shared_memory_size_in_huge_pages;

Finally, you can precisely specify the required number of huge pages:

node_hugepage_count: 3000   # Precisely allocate 3000 2MB huge pages (6GB)

However, to precisely count the required number of huge pages without missing any, you typically need to wait until the PostgreSQL server starts.

A compromise approach is to:

Initially over-allocate huge pages
Start PostgreSQL
Query the exact required number from PG
Then precisely modify the huge page count

Dedicate Huge Pages to PG

By default, all processes can use huge pages. If you want to restrict huge page usage to PostgreSQL only, you can modify the vm.hugetlb_shm_group kernel parameter.

You can adjust the node_sysctl_params parameter to include PostgreSQL’s GID.

node_sysctl_params:
  vm.hugetlb_shm_group: 26

node_sysctl_params:
  vm.hugetlb_shm_group: 543

Note: Default PostgreSQL UID/GID values differ between EL/Debian, being 26 and 543 respectively (can be explicitly modified via pg_dbsu_uid)

To remove this change:

sysctl -p /etc/sysctl.d/hugepage.conf

Quick Adjustment Script

The pg-tune-hugepage script can reclaim wasted huge pages, but it’s only available for PostgreSQL 15+.

If your PostgreSQL is already running, you can enable huge pages (PG15+ only) using:

sync; echo 3 > /proc/sys/vm/drop_caches   # Flush disk, free system cache (be prepared for performance impact)
sudo /pg/bin/pg-tune-hugepage             # Write nr_hugepages to /etc/sysctl.d/hugepage.conf
pg restart <cls>                          # Restart postgres to use hugepage

Sample output of executing pg-tune-hugepage:

$ /pg/bin/pg-tune-hugepage
[INFO] Querying PostgreSQL for hugepage requirements...
[INFO] Added safety margin of 0 hugepages (5168 → 5168)
[INFO] ==================================
PostgreSQL user: postgres
PostgreSQL group ID: 26
Required hugepages: 5168
Configuration file: /etc/sysctl.d/hugepage.conf
[BEFORE] ================================
Current memory information:
AnonHugePages:      8192 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:   10025
HugePages_Free:     9896
HugePages_Rsvd:     5039
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:        20531200 kB
Current sysctl settings:
vm.hugetlb_shm_group = 26
vm.nr_hugepages = 10025
vm.nr_hugepages_mempolicy = 10025
[EXECUTE] ===============================
Writing new hugepage configuration...
Applying new settings...
vm.nr_hugepages = 5168
vm.hugetlb_shm_group = 26
[AFTER] =================================
Updated memory information:
AnonHugePages:      8192 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    5168
HugePages_Free:     5039
HugePages_Rsvd:     5039
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:        10584064 kB
Updated sysctl settings:
vm.hugetlb_shm_group = 26
vm.nr_hugepages = 5168
vm.nr_hugepages_mempolicy = 5168
[DONE] ==================================
PostgreSQL hugepage configuration complete.

Consider adding the following to your inventory file:
node_hugepage_count: 5168
node_sysctl_params: {vm.hugetlb_shm_group: 26}

References

https://www.cybertec-postgresql.com/en/huge-pages-postgresql/

11 - Citus: HA Cluster

How to deploy a native HA citus cluster with Pigsty & Patroni?

Citus is a PostgreSQL extension that transforms PostgreSQL into a distributed database, enabling horizontal scaling across multiple nodes to handle large amounts of data and queries.

Since Patroni v3.0, native support for Citus high availability has been provided, simplifying the setup of Citus clusters. Pigsty also offers native support for this.

Note: The latest version of Citus (12.1.6) supports PostgreSQL versions 16, 15, and 14, but does not support PostgreSQL 17 and lacks official ARM64 support. Pigsty’s extension repository provides ARM64 packages for Citus, but caution is advised when using it on ARM architecture.

Citus Cluster

Pigsty natively supports Citus. Refer to conf/citus.yml.

This example uses a four-node sandbox with a Citus cluster named pg-citus, consisting of a two-node coordinator cluster pg-citus0 and two worker clusters pg-citus1 and pg-citus2.

pg-citus:
  hosts:
    10.10.10.10: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.2/24 ,pg_seq: 1, pg_role: primary }
    10.10.10.11: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.2/24 ,pg_seq: 2, pg_role: replica }
    10.10.10.12: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.3/24 ,pg_seq: 1, pg_role: primary }
    10.10.10.13: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.4/24 ,pg_seq: 1, pg_role: primary }
  vars:
    pg_mode: citus                            # pgsql cluster mode: citus
    pg_version: 16                            # Citus does not support pg16 yet
    pg_shard: pg-citus                        # Citus shard name: pg-citus
    pg_primary_db: citus                      # primary database used by Citus
    pg_vip_enabled: true                      # enable VIP for Citus cluster
    pg_vip_interface: eth1                    # VIP interface for all members
    pg_dbsu_password: DBUser.Postgres         # all DBSU passwords for Citus cluster
    pg_extensions: [ citus, postgis, pgvector, topn, pg_cron, hll ]  # install these extensions
    pg_libs: 'citus, pg_cron, pg_stat_statements' # Citus will be added automatically by Patroni
    pg_users: [{ name: dbuser_citus ,password: DBUser.Citus ,pgbouncer: true ,roles: [ dbrole_admin ]    }]
    pg_databases: [{ name: citus ,owner: dbuser_citus ,extensions: [ citus, vector, topn, pg_cron, hll ] }]
    pg_parameters:
      cron.database_name: citus
      citus.node_conninfo: 'sslmode=require sslrootcert=/pg/cert/ca.crt sslmode=verify-full'
    pg_hba_rules:
      - { user: 'all' ,db: all  ,addr: 127.0.0.1/32  ,auth: ssl   ,title: 'all user ssl access from localhost' }
      - { user: 'all' ,db: all  ,addr: intra         ,auth: ssl   ,title: 'all user ssl access from intranet'  }

Compared to a standard PostgreSQL cluster, Citus cluster configuration has some specific requirements. First, ensure that the Citus extension is downloaded, installed, loaded, and enabled. This involves the following four parameters:

repo_packages: Must include the citus extension, or you need to use a PostgreSQL offline package with the Citus extension.
pg_extensions: Must include the citus extension, meaning you need to install the citus extension on each node.
pg_libs: Must include the citus extension, and it must be first in the list, but now Patroni will automatically handle this.
pg_databases: Define a primary database with the citus extension installed.

Additionally, ensure the configuration for the Citus cluster is correct:

pg_mode: Must be set to citus to inform Patroni to use the Citus mode.
pg_primary_db: Specify the primary database name, which must have the citus extension (named citus here).
pg_shard: Specify a unified name as a prefix for all horizontal shard PG clusters (e.g., pg-citus).
pg_group: Specify a shard number, starting from zero for the coordinator cluster and incrementing for worker clusters.
pg_cluster: Must match the combination of [pg_shard] and [pg_group].
pg_dbsu_password: Set a non-empty plain-text password for proper Citus functionality.
pg_parameters: It is recommended to set the citus.node_conninfo parameter, which enforces SSL access and requires node-to-node client certificate verification.

Once configured, deploy the Citus cluster just like a regular PostgreSQL cluster using pgsql.yml.

Managing Citus Clusters

After defining the Citus cluster, use the same playbook pgsql.yml to deploy the Citus cluster:

./pgsql.yml -l pg-citus    # Deploy Citus cluster pg-citus

Any DBSU user (postgres) can use patronictl (alias: pg) to list the status of the Citus cluster:

$ pg list
+ Citus cluster: pg-citus ----------+---------+-----------+----+-----------+--------------------+
| Group | Member      | Host        | Role    | State     | TL | Lag in MB | Tags               |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     0 | pg-citus0-1 | 10.10.10.10 | Leader  | running   |  1 |           | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 20C.40G.125G |
|       |             |             |         |           |    |           | version: '16'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     1 | pg-citus1-1 | 10.10.10.11 | Leader  | running   |  1 |           | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 10C.20G.125G |
|       |             |             |         |           |    |           | version: '16'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     2 | pg-citus2-1 | 10.10.10.12 | Leader  | running   |  1 |           | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 10C.20G.125G |
|       |             |             |         |           |    |           | version: '16'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+
|     2 | pg-citus2-2 | 10.10.10.13 | Replica | streaming |  1 |         0 | clonefrom: true    |
|       |             |             |         |           |    |           | conf: tiny.yml     |
|       |             |             |         |           |    |           | spec: 10C.20G.125G |
|       |             |             |         |           |    |           | version: '16'      |
+-------+-------------+-------------+---------+-----------+----+-----------+--------------------+

Each horizontal shard cluster can be treated as a separate PGSQL cluster, managed with the pg (patronictl) command. Note that when using pg to manage the Citus cluster, the --group parameter must be used to specify the cluster shard number:

pg list pg-citus --group 0   # Use --group 0 to specify the shard number

Citus has a system table called pg_dist_node to record node information, which Patroni automatically maintains.

PGURL=postgres://postgres:[email protected]/citus

psql $PGURL -c 'SELECT * FROM pg_dist_node;'       # View node information

Additionally, you can view user authentication information (restricted to superusers):

$ psql $PGURL -c 'SELECT * FROM pg_dist_authinfo;'   # View node authentication info (superuser only)

You can then access the Citus cluster with regular business users (e.g., dbuser_citus with DDL permissions):

psql postgres://dbuser_citus:[email protected]/citus -c 'SELECT * FROM pg_dist_node;'

Using the Citus Cluster

When using a Citus cluster, we highly recommend reading the Citus Official Documentation to understand its architecture and core concepts.

Key to this is understanding the five types of tables in Citus, their characteristics, and use cases:

Distributed Table
Reference Table
Local Table
Local Management Table
Schema Table

On the coordinator node, you can create distributed and reference tables and query them from any data node. Since version 11.2, any Citus database node can act as a coordinator.

We can use pgbench to create some tables, distributing the main table (pgbench_accounts) across the nodes, and using other smaller tables as reference tables:

PGURL=postgres://dbuser_citus:[email protected]/citus
pgbench -i $PGURL

psql $PGURL <<-EOF
SELECT create_distributed_table('pgbench_accounts', 'aid'); SELECT truncate_local_data_after_distributing_table('public.pgbench_accounts');
SELECT create_reference_table('pgbench_branches')         ; SELECT truncate_local_data_after_distributing_table('public.pgbench_branches');
SELECT create_reference_table('pgbench_history')          ; SELECT truncate_local_data_after_distributing_table('public.pgbench_history');
SELECT create_reference_table('pgbench_tellers')          ; SELECT truncate_local_data_after_distributing_table('public.pgbench_tellers');
EOF

Run read-write bench:

pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus      # 直连协调者 5432 端口
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]:6432/citus # 通过连接池，减少客户端连接数压力，可以有效提高整体吞吐。
pgbench -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus      # 任意 primary 节点都可以作为 coordinator
pgbench --select-only -nv -P1 -c10 -T500 postgres://dbuser_citus:[email protected]/citus # 可以发起只读查询

Production Deployment

Production citus deployment usually requires physical replication for both coordinator and each worker cluster.

For example, in simu.yml there’s a 10-node cluster cluster:

pg-citus: # citus group
  hosts:
    10.10.10.50: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.60/24 ,pg_seq: 0, pg_role: primary }
    10.10.10.51: { pg_group: 0, pg_cluster: pg-citus0 ,pg_vip_address: 10.10.10.60/24 ,pg_seq: 1, pg_role: replica }
    10.10.10.52: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.61/24 ,pg_seq: 0, pg_role: primary }
    10.10.10.53: { pg_group: 1, pg_cluster: pg-citus1 ,pg_vip_address: 10.10.10.61/24 ,pg_seq: 1, pg_role: replica }
    10.10.10.54: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.62/24 ,pg_seq: 0, pg_role: primary }
    10.10.10.55: { pg_group: 2, pg_cluster: pg-citus2 ,pg_vip_address: 10.10.10.62/24 ,pg_seq: 1, pg_role: replica }
    10.10.10.56: { pg_group: 3, pg_cluster: pg-citus3 ,pg_vip_address: 10.10.10.63/24 ,pg_seq: 0, pg_role: primary }
    10.10.10.57: { pg_group: 3, pg_cluster: pg-citus3 ,pg_vip_address: 10.10.10.63/24 ,pg_seq: 1, pg_role: replica }
    10.10.10.58: { pg_group: 4, pg_cluster: pg-citus4 ,pg_vip_address: 10.10.10.64/24 ,pg_seq: 0, pg_role: primary }
    10.10.10.59: { pg_group: 4, pg_cluster: pg-citus4 ,pg_vip_address: 10.10.10.64/24 ,pg_seq: 1, pg_role: replica }
  vars:
    pg_mode: citus                            # pgsql cluster mode: citus
    pg_version: 16                            # citus does not have pg16 available
    pg_shard: pg-citus                        # citus shard name: pg-citus
    pg_primary_db: citus                      # primary database used by citus
    pg_vip_enabled: true                      # enable vip for citus cluster
    pg_vip_interface: eth1                    # vip interface for all members
    pg_dbsu_password: DBUser.Postgres         # enable dbsu password access for citus
    pg_extensions: [ citus, postgis, pgvector, topn, pg_cron, hll ]  # install these extensions
    pg_libs: 'citus, pg_cron, pg_stat_statements' # citus will be added by patroni automatically
    pg_users: [{ name: dbuser_citus ,password: DBUser.Citus ,pgbouncer: true ,roles: [ dbrole_admin ]    }]
    pg_databases: [{ name: citus ,owner: dbuser_citus ,extensions: [ citus, vector, topn, pg_cron, hll ] }]
    pg_parameters:
      cron.database_name: citus
      citus.node_conninfo: 'sslrootcert=/pg/cert/ca.crt sslmode=verify-full'
    pg_hba_rules:
      - { user: 'all' ,db: all  ,addr: 127.0.0.1/32  ,auth: ssl   ,title: 'all user ssl access from localhost' }
      - { user: 'all' ,db: all  ,addr: intra         ,auth: ssl   ,title: 'all user ssl access from intranet'  }

We’ll cover a range of advanced topics in subsequent tutorials:

Read-write separation
Failover handling
Consistent backup and restore
Advanced monitoring and troubleshooting
Connection pool

12 - HA Drill: 2/3 Failure

How to recover from emergency scenario with 2-node broken in a 3-node setup?

When two nodes (majority) in a classic 3-node HA deployment fail simultaneously, automatic failover becomes impossible. Time for some manual intervention - let’s roll up our sleeves!

First, assess the status of the failed nodes. If they can be restored quickly, prioritize bringing them back online. Otherwise, initiate the Emergency Response Protocol.

The Emergency Response Protocol assumes your management node is down, leaving only a single database node alive. In this “last man standing” scenario, here’s the fastest recovery path:

Adjust HAProxy configuration to direct traffic to the primary
Stop Patroni and manually promote the PostgreSQL replica to primary

Adjusting HAProxy Configuration

If you’re accessing the cluster through means other than HAProxy, you can skip this part (lucky you!). If you’re using HAProxy to access your database cluster, you’ll need to adjust the load balancer configuration to manually direct read/write traffic to the primary.

Edit /etc/haproxy/<pg_cluster>-primary.cfg, where <pg_cluster> is your PostgreSQL cluster name (e.g., pg-meta)
Comment out health check configurations
Comment out the server entries for the two failed nodes, keeping only the current primary

listen pg-meta-primary
    bind *:5433
    mode tcp
    maxconn 5000
    balance roundrobin

    # Comment out these four health check lines
    #option httpchk                               # <---- remove this
    #option http-keep-alive                       # <---- remove this
    #http-check send meth OPTIONS uri /primary    # <---- remove this
    #http-check expect status 200                 # <---- remove this

    default-server inter 3s fastinter 1s downinter 5s rise 3 fall 3 on-marked-down shutdown-sessions slowstart 30s maxconn 3000 maxqueue 128 weight 100
    server pg-meta-1 10.10.10.10:6432 check port 8008 weight 100

    # Comment out the failed nodes
    #server pg-meta-2 10.10.10.11:6432 check port 8008 weight 100 <---- comment this
    #server pg-meta-3 10.10.10.12:6432 check port 8008 weight 100 <---- comment this

Don’t rush to systemctl reload haproxy just yet - we’ll do that after promoting the primary. This configuration bypasses Patroni’s health checks and directs write traffic straight to our soon-to-be primary.

Manual Replica Promotion

SSH into the target server, switch to dbsu user, execute a CHECKPOINT to flush disk buffers, stop Patroni, restart PostgreSQL, and perform the promotion:

sudo su - postgres                     # Switch to database dbsu user
psql -c 'checkpoint; checkpoint;'      # Double CHECKPOINT for good luck (and clean buffers)
sudo systemctl stop patroni            # Bid farewell to Patroni
pg-restart                             # Restart PostgreSQL
pg-promote                             # Time for a promotion! 
psql -c 'SELECT pg_is_in_recovery();'  # 'f' means we're primary - mission accomplished!

If you modified the HAProxy config earlier, now’s the time to systemctl reload haproxy and direct traffic to our new primary.

systemctl reload haproxy                # Route write traffic to our new primary

Preventing Split-Brain

After stopping the bleeding, priority #2 is: Prevent Split-Brain. We need to ensure the other two servers don’t come back online and start a civil war with our current primary.

The simple approach:

Pull the plug (power/network) on the other two servers - ensure they can’t surprise us with an unexpected comeback
Update application connection strings to point directly to our lone survivor primary

Next steps depend on your situation:

A: The two servers have temporary issues (network/power outage) and can be restored in place
B: The two servers are permanently dead (hardware failure) and need to be decommissioned

Recovery from Temporary Failure

If the other two servers can be restored, follow these steps:

Handle one failed server at a time, prioritizing the management/INFRA node
Start the failed server and immediately stop Patroni

Once ETCD quorum is restored, start Patroni on the surviving server (current primary) to take control of PostgreSQL and reclaim cluster leadership. Put Patroni in maintenance mode:

systemctl restart patroni
pg pause <pg_cluster>

On the other two instances, create a touch /pg/data/standby.signal file as postgres user to mark them as replicas, then start Patroni:

systemctl restart patroni

After confirming Patroni cluster identity/roles are correct, exit maintenance mode:

pg resume <pg_cluster>

Recovery from Permanent Failure

After permanent failure, first recover the ~/pigsty directory on the management node - particularly the crucial pigsty.yml and files/pki/ca/ca.key files.

No backup of these files? You might need to deploy a fresh Pigsty and migrate your existing cluster via backup cluster.

Pro tip: Keep your pigsty directory under version control (Git). Learn from this experience - future you will thank present you.

Config Repair

Use your surviving node as the new management node. Copy the ~/pigsty directory there and adjust the configuration. For example, replace the default management node 10.10.10.10 with the surviving node 10.10.10.12:

all:
  vars:
    admin_ip: 10.10.10.12               # New management node IP
    node_etc_hosts: [10.10.10.12 h.pigsty a.pigsty p.pigsty g.pigsty sss.pigsty]
    infra_portal: {}                    # Update other configs referencing old admin_ip

  children:

    infra:                              # Adjust Infra cluster
      hosts:
        # 10.10.10.10: { infra_seq: 1 } # Old Infra node
        10.10.10.12: { infra_seq: 3 }   # New Infra node

    etcd:                               # Adjust ETCD cluster
      hosts:
        #10.10.10.10: { etcd_seq: 1 }   # Comment out failed node
        #10.10.10.11: { etcd_seq: 2 }   # Comment out failed node
        10.10.10.12: { etcd_seq: 3 }    # Keep survivor
      vars:
        etcd_cluster: etcd

    pg-meta:                            # Adjust PGSQL cluster config
      hosts:
        #10.10.10.10: { pg_seq: 1, pg_role: primary }
        #10.10.10.11: { pg_seq: 2, pg_role: replica }
        #10.10.10.12: { pg_seq: 3, pg_role: replica , pg_offline_query: true }
        10.10.10.12: { pg_seq: 3, pg_role: primary , pg_offline_query: true }
      vars:
        pg_cluster: pg-meta

ETCD Repair

Reset ETCD to a single-node cluster:

./etcd.yml -e etcd_safeguard=false -e etcd_clean=true

Follow ETCD Config Reload to adjust ETCD endpoint references.

INFRA Repair

If the surviving node lacks INFRA module, configure and install it:

./infra.yml -l 10.10.10.12

Fix monitoring on the current node:

./node.yml -t node_monitor

PGSQL Repair

./pgsql.yml -t pg_conf                            # Regenerate PG config
systemctl reload patroni                          # Reload Patroni config on survivor

After module repairs, follow the standard scale-out procedure to add new nodes and restore HA.

13 - Restic: FS Backup/Recovery

How to use Restic for regular file system backup and recovery

While Pigsty handles PostgreSQL database backup and recovery, how do we handle backup and recovery for regular files and directories?

For various business applications using PostgreSQL (such as Odoo, GitLab), you can use Restic to regularly backup file system data (e.g., /data/odoo).

Restic is an excellent open-source backup tool that supports snapshots, incremental backups, encryption, compression, and can use various services including S3/MinIO as backup repositories. For detailed information, please refer to the restic documentation.

Quick Start

Pigsty Infra repository provides ready-to-use latest Restic RPM/DEB packages, while most Linux distributions’ official repositories offer older versions.

yum install -y restic
apt install -y restic

For demonstration purposes, we’ll use a local directory as the backup repository. Repository initialization only needs to be done once:

mkdir -p /data/backups/restic
export RESTIC_REPOSITORY=/data/backups/restic
export RESTIC_PASSWORD=some-strong-password
restic init

Next, you can perform backup operations, view snapshots, and restore files:

restic backup /www/web.cc               # Backup /www/web.cc directory to repository
restic snapshots                        # List backup snapshots
restic restore -t /tmp/web.cc 0b11f778  # Restore snapshot 0b11f778 to /tmp/web.cc
restic check                            # Regularly check repository integrity

Complete Command Output

$ restic backup /www/web.cc
repository fcd37256 opened (repository version 2) successfully, password is correct
created new cache in /root/.cache/restic
no parent snapshot found, will read all files

Files:        5934 new,     0 changed,     0 unmodified
Dirs:         1622 new,     0 changed,     0 unmodified
Added to the repository: 1.570 GiB (1.167 GiB stored)

processed 5934 files, 1.694 GiB in 0:20
snapshot 0b11f778 saved

$ restic snapshots               # View backup snapshots
repository fcd37256 opened (repository version 2) successfully, password is correct
ID        Time                 Host        Tags        Paths
------------------------------------------------------------------
0b11f778  2025-03-19 15:25:21  pigsty.cc               /www/web.cc
------------------------------------------------------------------
1 snapshots

$ restic backup /www/web.cc
repository fcd37256 opened (repository version 2) successfully, password is correct
using parent snapshot 0b11f778

Files:           0 new,     0 changed,  5934 unmodified
Dirs:            0 new,     0 changed,  1622 unmodified
Added to the repository: 0 B   (0 B   stored)

processed 5934 files, 1.694 GiB in 0:00
snapshot 06cd9b5c saved

[03-19 15:25:59] [email protected]:/data/backups
$ restic snapshots               # View backup snapshots
repository fcd37256 opened (repository version 2) successfully, password is correct
ID        Time                 Host        Tags        Paths
------------------------------------------------------------------
0b11f778  2025-03-19 15:25:21  pigsty.cc               /www/web.cc
06cd9b5c  2025-03-19 15:25:58  pigsty.cc               /www/web.cc
------------------------------------------------------------------
2 snapshots

$ restic restore -t /www/web.cc 0b11f778
repository fcd37256 opened (repository version 2) successfully, password is correct
restoring <Snapshot 0b11f778 of [/www/web.cc] at 2025-03-19 15:25:21.514089814 +0800 HKT by [email protected]> to /www/web.cc

Using Object Storage

You can use various methods to store Restic backup data. Here’s how to use Pigsty’s built-in MinIO as a backup repository.

export AWS_ACCESS_KEY_ID=minioadmin              # Default MinIO account
export AWS_SECRET_ACCESS_KEY=minioadmin          # Default MinIO password
restic -r s3:http://sss.pigsty:9000/infra init   # Use the default infra bucket as backup destination

14 - JuiceFS: S3FS & PGFS

How to build a distributed cloud-native filesystem JuiceFS using PostgreSQL and MinIO provided by Pigsty.

JuiceFS is a high-performance, cloud-native distributed filesystem.

This guide demonstrates how to build a production-grade JuiceFS cluster using PostgreSQL as the metadata engine and MinIO as the object storage engine, both provided by Pigsty.

Quick Start

Create a four-node sandbox using the full mode.

./configure -c full
./install.yml

Install JuiceFS and configure object storage:

JFSNAME=jfs
METAURL=postgres://dbuser_meta:[email protected]:5432/meta
DATAURL=(
  --storage minio
  --bucket https://sss.pigsty:9000/infra
  --access-key minioadmin
  --secret-key minioadmin
)

juicefs format "${DATAURL[@]}" ${METAURL} jfs    # Format filesystem
juicefs mount ${METAURL} ~/jfs -d                # Mount in background
juicefs umount ~/jfs                             # Unmount

For a more advanced approach: PGFS - Using database as filesystem

JFSNAME=jfs
METAURL=postgres://dbuser_meta:[email protected]:5432/meta
DATAURL=(
  --storage postgres
  --bucket 10.10.10.10:5432/meta
  --access-key dbuser_meta
  --secret-key DBUser.Meta
)

juicefs format "${DATAURL[@]}" ${METAURL} ${JFSNAME}
juicefs mount ${METAURL} ~/jfs -d                # Mount in background
juicefs umount ~/jfs                             # Unmount

Standalone Mode

Pigsty Infra repository provides the latest JuiceFS RPM/DEB packages. Install directly using your package manager.

The following commands create a local JuiceFS filesystem using local SQLite and filesystem (/var/jfs):

juicefs format sqlite3:///tmp/jfs.db myjfs

Format Output

$ juicefs format sqlite3:///jfs.db myjfs
2025/03/19 12:07:56.956222 juicefs[62924] <INFO>: Meta address: sqlite3:///jfs.db [interface.go:504]
2025/03/19 12:07:56.958055 juicefs[62924] <INFO>: Data use file:///var/jfs/myjfs/ [format.go:484]
2025/03/19 12:07:56.966150 juicefs[62924] <INFO>: Volume is formatted as {
  "Name": "myjfs",
  "UUID": "1568ee2a-dc4c-4a0e-9788-be0490776dda",
  "Storage": "file",
  "Bucket": "/var/jfs/",
  "BlockSize": 4096,
  "Compression": "none",
  "EncryptAlgo": "aes256gcm-rsa",
  "TrashDays": 1,
  "MetaVersion": 1,
  "MinClientVersion": "1.1.0-A",
  "DirStats": true,
  "EnableACL": false
} [format.go:521]

Then mount locally using the following commands:

juicefs mount sqlite3:///tmp/jfs.db ~/jfs      # Foreground mount, auto-unmounts on exit
juicefs mount sqlite3:///tmp/jfs.db ~/jfs -d   # Daemon mount, requires manual unmount
juicefs umount ~/jfs                           # Unmount and exit process

Data Cleanup

Clear JuiceFS metadata from PostgreSQL using:

DROP TABLE IF EXISTS jfs_acl,jfs_chunk,jfs_chunk_ref,jfs_counter,jfs_delfile,jfs_delslices,jfs_detached_node,jfs_dir_quota,jfs_dir_stats,jfs_edge,jfs_flock,jfs_node,jfs_plock,jfs_session2,jfs_setting,jfs_sustained,jfs_symlink,jfs_xattr CASCADE;

Clear object storage bucket using:

mcli rm --recursive --force infra/jfs

PGFS Performance Summary

Benchmark results on second-hand physical hardware:

METAURL=postgres://dbuser_meta:DBUser.Meta@:5432/meta
OPTIONS=(
  --storage postgres
  --bucket :5432/meta
  --access-key dbuser_meta
  --secret-key DBUser.Meta
  ${METAURL}
  jfs
)

juicefs format "${OPTIONS[@]}"
juicefs mount ${METAURL} ~/jfs -d  # Mount in background
juicefs bench ~/jfs                # Run benchmark
juicefs umount ~/jfs               # Unmount

$ juicefs bench ~/jfs                # Performance test
  Write big blocks: 1024/1024 [==============================================================]  178.5/s  used: 5.73782533s
   Read big blocks: 1024/1024 [==============================================================]  31.7/s   used: 32.314547037s
Write small blocks: 100/100 [==============================================================]  149.2/s  used: 670.127171ms
 Read small blocks: 100/100 [==============================================================]  543.4/s  used: 184.109596ms
  Stat small files: 100/100 [==============================================================]  1723.4/s used: 58.087752ms
Benchmark finished!
BlockSize: 1.0 MiB, BigFileSize: 1.0 GiB, SmallFileSize: 128 KiB, SmallFileCount: 100, NumThreads: 1
Time used: 42.2 s, CPU: 687.2%, Memory: 179.4 MiB
+------------------+------------------+---------------+
|       ITEM       |       VALUE      |      COST     |
+------------------+------------------+---------------+
|   Write big file |     178.51 MiB/s |   5.74 s/file |
|    Read big file |      31.69 MiB/s |  32.31 s/file |
| Write small file |    149.4 files/s |  6.70 ms/file |
|  Read small file |    545.2 files/s |  1.83 ms/file |
|        Stat file |   1749.7 files/s |  0.57 ms/file |
|   FUSE operation | 17869 operations |    3.82 ms/op |
|      Update meta |  1164 operations |    1.09 ms/op |
|       Put object |   356 operations |  303.01 ms/op |
|       Get object |   256 operations | 1072.82 ms/op |
|    Delete object |     0 operations |    0.00 ms/op |
| Write into cache |   356 operations |    2.18 ms/op |
|  Read from cache |   100 operations |    0.11 ms/op |
+------------------+------------------+---------------+

Tasks

1 - DNS: Access Pigsty Web Services with Domain Names

TL;DR

Why Use Domains?

How DNS Works

Pigsty Default Domains

Local Static Resolution

Adding Resolution

Custom Domains

Additional Records

Public IP Resolution

Internal Dynamic Resolution

Using Internal DNS

Using Pigsty’s DNS

Local HTTPS Access

Trust Self-signed CA

Public Domain Resolution

Further Reading

2 - Nginx: Expose Web Service with the Infra Portal

How to configure nginx upstream?

How to access via domain names?

How to access via HTTPS?

3 - Certbot: Free HTTPS Certs

Determine Which Domains Need Certificates

Point These Domains to Your Server

Use Certbot to Apply for Certificates

Configure a Scheduled Task to Renew the Certificate

Caveats

4 - Docker: Setup & Proxy

Install Docker

Proxy 101

How to configure proxy for Docker Daemon?

How to use other registry?

5 - Use PostgreSQL as Ansible Config Inventory CMDB

Ansible Inventory

Load Configuration

6 - Use PG as Grafana Backend

TL; DR

Create Postgres Cluster

Define Cluster

Create Cluster

Create Biz User

Define User

Create User

Create Biz Database

Define database

Create database

Access Database

Check Connectivity

Config Grafana

Manage Dashboard

Manage DataSources

Update Grafana Database

7 - Use PG as Prometheus Backend

Postgres Preparation

Promscale Configuration

Prometheus Configuration

8 - Bind a L2 VIP to Node Cluster with Keepalived

9 - Bind a L2 VIP to PostgreSQL Primary with VIP-Manager

Enable VIP

10 - HugePage: Enable HP for Node and PostgreSQL

Pros and Cons of Huge Pages

When to Enable Huge Pages?

Allocate Node Huge Pages

Monitor Huge Page Status

Precise Huge Page Allocation

Reclamation Script

Dedicate Huge Pages to PG

Quick Adjustment Script

References

11 - Citus: HA Cluster

Citus Cluster

Managing Citus Clusters

Using the Citus Cluster

Production Deployment

12 - HA Drill: 2/3 Failure

Adjusting HAProxy Configuration

Manual Replica Promotion

Preventing Split-Brain

Recovery from Temporary Failure